Thread: 9.5: pg_stat_statement and pgbench execution time discrepancies

9.5: pg_stat_statement and pgbench execution time discrepancies

From

Pavel Suderevsky

Date:

21 July 2016, 22:38:21

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.

Re: 9.5: pg_stat_statement and pgbench execution time discrepancies

From

Tom Lane

Date:

21 July 2016, 22:54:56

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