9.5: pg_stat_statement and pgbench execution time discrepancies

Started by Pavel Suderevskyover 9 years ago2 messagesgeneral
Jump to latest
#1Pavel Suderevsky
psuderevsky@gmail.com

Hi,

During performance tests it was mentioned that pgbench common results are
being different from what pg_stat_statements provides.

PostgreSQL version 9.5.3, centos 7.2. First run after pg_stat_extension
created and first pgbench run.

*pgbench *-c 30 -T 300 -U postgres -P 10 -r -v

statement latencies in milliseconds:

0.003431 \set nbranches 1 * :scale

0.001046 \set ntellers 10 * :scale

0.000891 \set naccounts 100000 * :scale

0.001356 \setrandom aid 1 :naccounts

0.000938 \setrandom bid 1 :nbranches

0.000879 \setrandom tid 1 :ntellers

0.000974 \setrandom delta -5000 5000

0.102258 BEGIN;

0.228744 UPDATE pgbench_accounts SET abalance = abalance +

:delta WHERE aid = :aid;

0.156087 SELECT abalance FROM pgbench_accounts WHERE aid =

:aid;

17.715645 UPDATE pgbench_tellers SET tbalance = tbalance +

:delta WHERE tid = :tid;

6.222220 UPDATE pgbench_branches SET bbalance = bbalance +

:delta WHERE bid = :bid;

0.294592 INSERT INTO pgbench_history (tid, bid, aid,

delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

0.502843 END;

*pg_stat_statements *
Example 1

query | UPDATE pgbench_accounts SET abalance = abalance + ?

WHERE aid = ?;

calls | 118804

total_time | 5944.19700000145

min_time | 0.017

max_time | 146.501

mean_time | 0.0500336436483624

stddev_time | 0.860838186600729

rows | 118804

Example 2

query | INSERT INTO pgbench_history (tid, bid, aid, delta,

mtime) VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP);

calls | 118804

total_time | 16533.2040000006

min_time | 0.075

max_time | 1.583

mean_time | 0.139163698191979

stddev_time | 0.0342061695940176

rows | 118804

Could anybody explain why results are different? Where is an inaccuracy -
in pg_stat_statements or pgbench?

Thanks in advance.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Pavel Suderevsky (#1)
Re: 9.5: pg_stat_statement and pgbench execution time discrepancies

Pavel Suderevsky <psuderevsky@gmail.com> writes:

During performance tests it was mentioned that pgbench common results are
being different from what pg_stat_statements provides.

Well, they're not measuring even approximately the same thing, so I'm
not sure why that surprises you. pg_stat_statements only measures
the executor runtime, omitting parse/plan times as well as network
transmission times. pgbench is reporting the overall time as seen
from the client side.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general