Waiting for 9.5 – Add stats for min, max, mean, stddev times to pg_stat_statements.

On 27th of March, Andrew Dunstan committed patch:

Add stats for min, max, mean, stddev times to pg_stat_statements.
 
The new fields are min_time, max_time, mean_time and stddev_time.
 
Based on an original patch from Mitsumasa KONDO, modified by me. Reviewed by Petr Jelínek.

While pg_stat_statements provides a lot of information about statements, it's timing information left some to be desired – basically we only got total_time and number of calls. So we could get average time, but that's about it.

This made it hard to say whether 100 calls and total time of 1000 means that really most of the calls are 10ms, per perhaps usually it takes less than 1ms, but there was just one very long, perhaps locked, call.

Now, this has changed, as we got 4 new columns. So after some pg_bench runs, I can now see:

$ SELECT calls, total_time, min_time, max_time, mean_time, stddev_time, query FROM pg_stat_statements WHERE calls > 100 ORDER BY mean_time DESC LIMIT 5;
 calls |    total_time    | min_time | max_time |     mean_time      |     stddev_time     |                                               query                                               
-------+------------------+----------+----------+--------------------+---------------------+---------------------------------------------------------------------------------------------------
 15733 | 1506.70200000001 |    0.016 |    5.922 |  0.095766986588699 |  0.0613101683466606 | UPDATE pgbench_accounts SET abalance = abalance + ? WHERE aid = ?;
 15732 | 1490.35500000001 |    0.012 |   23.813 | 0.0947339816933635 |    0.56105562280499 | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?;
 15732 | 609.951999999985 |    0.007 |    5.581 |   0.03877142130689 |   0.132033456326659 | UPDATE pgbench_tellers SET tbalance = tbalance + ? WHERE tid = ?;
 15732 | 309.382999999998 |    0.006 |    0.185 | 0.0196658403254515 | 0.00826286883812054 | SELECT abalance FROM pgbench_accounts WHERE aid = ?;
 15732 | 215.899999999988 |    0.004 |      0.3 | 0.0137236206458175 | 0.00831143691767018 | INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP);
(5 ROWS)

Great. This is definitely very helpful, thanks Mitsumasa, Andrew and Petr.

One thought on “Waiting for 9.5 – Add stats for min, max, mean, stddev times to pg_stat_statements.”

Comments are closed.