pg_stat_statements: The way I like it

If you really want to track down slow queries, massive I/O and lousy performance, there is no way around the pg_stat_statements extension.

However, the pg_stat_statements system view is full of information and many people get lost. Therefore it can make sense, to come up with a clever query to provide administrators with really relevant information. Without finding the really relevant information, tuning is somewhat pointless.

Here is my personal favorite query to track down issues:


test=#       SELECT  substring(query, 1, 50) AS short_query,
round(total_time::numeric, 2) AS total_time,
calls,
round(mean_time::numeric, 2) AS mean,
round((100 * total_time /
sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu
FROM    pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

The output contains a short version of the query (this can be handy if you are using text terminal as I do). Then there is the total_time of the query in the first column along with the number of calls and the mean execution time.

Personally I have found it useful to calculate an overall percentage for each query. It helps me to get a feeling of what lies in stock for me in case I can manage to optimize a certain query. To me the percentage value provides me with relevance because it is pretty pointless to work on queries, which only need 0.5% of time:


short_query                     | total_time | calls  |  mean  | percentage_cpu

----------------------------------------------------+------------+--------+--------+----------------

UPDATE pgbench_tellers SET tbalance = tbalance + ? |  585005.76 | 143881 |   4.07 |          54.03

UPDATE pgbench_branches SET bbalance = bbalance +  |  481968.27 | 143881 |   3.35 |          44.51

UPDATE pgbench_accounts SET abalance = abalance +  |    9801.43 | 143881 |   0.07 |           0.91

SELECT abalance FROM pgbench_accounts WHERE aid =  |    2928.61 | 143881 |   0.02 |           0.27

INSERT INTO pgbench_history (tid, bid, aid, delta, |    2236.59 | 143881 |   0.02 |           0.21

copy pgbench_accounts from stdin                   |     371.97 |      1 | 371.97 |           0.03

vacuum analyze pgbench_accounts                    |     128.22 |      1 | 128.22 |           0.01

BEGIN;                                             |     108.18 | 143881 |   0.00 |           0.01

END;                                               |      88.91 | 143881 |   0.00 |           0.01

alter table pgbench_accounts add primary key (aid) |      74.88 |      1 |  74.88 |           0.01

vacuum analyze pgbench_branches                    |      14.99 |      1 |  14.99 |           0.00

vacuum pgbench_branches                            |      11.57 |      1 |  11.57 |           0.00

create table pgbench_history(tid int,bid int,aid   |       4.36 |      1 |   4.36 |           0.00

vacuum analyze pgbench_tellers                     |       2.28 |      1 |   2.28 |           0.00

alter table pgbench_branches add primary key (bid) |       2.03 |      1 |   2.03 |           0.00

SELECT  substring(query, ?, ?) AS short_query,    +|       1.93 |      2 |   0.96 |           0.00

ro                                         |            |        |        |

SELECT  substring(query, ?, ?) AS short_query,    +|       1.82 |      1 |   1.82 |           0.00

ro                                         |            |        |        |

SELECT a.attname,                                 +|       1.39 |      2 |   0.69 |           0.00

pg_catalog.format_type(a.attty                   |            |        |        |

alter table pgbench_tellers add primary key (tid)  |       1.07 |      1 |   1.07 |           0.00

vacuum analyze pgbench_history                     |       1.02 |      1 |   1.02 |           0.00

(20 rows)

Working top down is usually a good idea.

Of course everybody will have his own ideas of how to approach the problem and the information provided by the query is not sufficient to fully optimize a system. However, I have found it useful to gain a quick overview of what is going on.

Hans-Juergen Schoenig
Hans-Jürgen Schönig has 15 years of experience with PostgreSQL. He is consultant and CEO of the company „Cybertec Schönig & Schönig GmbH“ (www.postgresq-support.de), which has served countless customers around the globe.