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.