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.