Finding slow queries with pg_stat_statements

conceptThe concept

pg_stat_statements must be in shared_preload_libraries (restart required), then CREATE EXTENSION pg_stat_statements. It normalises queries by replacing constants with $1, $2, so WHERE id = 5 and WHERE id = 7 are one entry, identified by queryid.

Sort by total_exec_time to find where the server spends its time: a 2 ms query called 5 million times a day outweighs a 30 s report run once. mean_exec_time and stddev_exec_time show per-call cost and its variance; shared_blks_read shows I/O; rows / calls shows result size.

The loop: pick the top entry, reproduce it with representative parameters, EXPLAIN (ANALYZE, BUFFERS), change one thing (an index, statistics, a rewrite), measure again, and reset or note the counters. auto_explain logs plans of slow queries in production automatically.

whyWhy it matters

Without it you optimise whatever someone complained about last. With it you optimise what actually consumes CPU and I/O, and you can prove the improvement.

Use caseReal-world scenario

After a release, CPU rises 30%. The top entry by total time is a new query from the product page, called 400 times per second, doing a Seq Scan. One index brings CPU back down.

Try itExamples

Run and edit these in the interactive lesson.

SELECT pg_stat_statements_reset();
SELECT count(*) FROM orders WHERE customer_id = 7;
SELECT count(*) FROM orders WHERE customer_id = 8;
SELECT count(*) FROM orders WHERE customer_id = 9;
SELECT c.country, sum(o.total) FROM orders o JOIN customers c ON c.id = o.customer_id GROUP BY 1;
SELECT * FROM products WHERE sku = 'SKU-0042';

SELECT calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 3) AS mean_ms,
       rows,
       shared_blks_hit + shared_blks_read AS blocks,
       left(regexp_replace(query, '\s+', ' ', 'g'), 70) AS query
FROM pg_stat_statements
WHERE query NOT ILIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 10;

SELECT calls,
       round((shared_blks_hit + shared_blks_read)::numeric / greatest(calls, 1), 1) AS blocks_per_call,
       round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
       left(regexp_replace(query, '\s+', ' ', 'g'), 70) AS query
FROM pg_stat_statements
ORDER BY blocks_per_call DESC
LIMIT 5;

Avoid / preferDo this, not that

SELECT query, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC;

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC;

-- compare today's numbers with last month's, never resetting or snapshotting

-- snapshot pg_stat_statements periodically and diff,
-- or reset after a deploy and compare windows

PitfallsCommon mistakes

• Sorting only by mean time. The query that matters most is usually the cheap one called millions of times.
• Comparing numbers across a stats reset or a restart without noting when it happened. Track pg_stat_statements_info.stats_reset.
• Tuning with unrepresentative parameters. A query can be fast for a small customer and slow for the biggest one.

LimitsWhen not to use it

pg_stat_statements aggregates by normalized query; it cannot show one slow request with its parameters. Use auto_explain or application tracing for individual executions.

SecuritySecurity notes

Only superusers and members of pg_read_all_stats see other users' query texts. Constants are normalized to placeholders, but comments and identifiers remain, so keep secrets out of SQL text.

ChallengeTry it yourself

SELECT left(query, 60) AS query, calls, round(mean_exec_time::numeric, 3) AS mean_ms
FROM pg_stat_statements
WHERE calls >= 2
ORDER BY mean_exec_time DESC
LIMIT 3;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• pg_stat_statements
• auto_explain