9.5: pg_stat_statement and pgbench execution time discrepancies
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.
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