Before you start
pg_stat_statements is a PostgreSQL extension that keeps running totals for every query shape the server has run: how many times, how long in total, and how many 8 KB buffer blocks it touched. You need permission to edit the server configuration and restart it once.
Turn it on
- Add it to the preload list. In
postgresql.conf, setshared_preload_libraries = 'pg_stat_statements'. If the setting already lists other libraries, add it to the list, comma-separated. On a managed service such as RDS or Cloud SQL this is a parameter-group or flag setting. - Restart PostgreSQL. A reload is not enough for this setting.
- Create the extension in the database you want to watch.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
Rank queries by total time
On PostgreSQL 13 and later the columns are total_exec_time and mean_exec_time (in milliseconds). On 12 and earlier they are total_time and mean_time.
SELECT calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;Sort by total time, not mean. A query that takes 2 ms but runs 5 million times a day costs 10,000 seconds. A report that takes 3 seconds and runs twice costs 6. Fixing the first one frees far more of the server.
Read the numbers with care
Next step: explain the top query
Take the query at the top, put your own values in for the $1 placeholders, and run plain EXPLAIN on it. Then read the plan with Read a PostgreSQL EXPLAIN plan, or paste it into dbtempo.
dbtempo can rank these for you. Give it a user that can read the view; see Create a least-privilege database user.