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 slow queries:

test=# SELECT substring(query, 1, 50) AS short_query,
              round(total_exec_time::numeric, 2) AS total_exec_time,
              calls,
              round(mean_exec_time::numeric, 2) AS mean,
              round((100 * total_exec_time /
              sum(total_exec_time::numeric) OVER ())::numeric, 2) AS percentage_cpu
FROM    pg_stat_statements
ORDER BY total_exec_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 tota_execl_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_exec_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
SELECT  substring(query, ?, ?) AS short_query,      |            1.82 |      1 |   1.82 |           0.00
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.

See also my blog about speeding up slow queries.