Pick a depth. Each prompt opens in your AI pre-loaded with the lesson. Click a row to preview the prompt.
pg_stat_statements is the single highest-value thing you can turn on in a PostgreSQL database, and most installations run without it. It normalises every statement it sees — replacing literal values with placeholders so that ten thousand executions of the same shape collapse into one row — and then accumulates calls, total time, rows, and buffer counters against that shape. That normalisation is what makes it useful: without it you have a log of ten thousand unrelated-looking statements, and with it you have a leaderboard. The skill is in choosing the sort. Sorting by mean time surfaces the rare monster that runs twice a day and nobody cares about. Sorting by total time surfaces the query that takes 8ms and runs four million times an hour, which is where your database is actually spending its life. Those are different queries and they need different fixes, and knowing which list you are looking at is the difference between a real improvement and a week of tuning something nobody runs.
The same database, ranked three ways. Note that the top entry is different in each ranking, and that only one of the three rankings tells you where the time goes.
-- Requires shared_preload_libraries=pg_stat_statements and a restart,
-- then: CREATE EXTENSION pg_stat_statements;
-- ── Ranking 1: by TOTAL time. This is the one that matters. ───────
SELECT
round(total_exec_time::numeric / 1000, 1) AS total_sec,
calls,
round(mean_exec_time::numeric, 2) AS mean_ms,
round(100 * total_exec_time
/ sum(total_exec_time) OVER ())::numeric(5,1) AS pct_of_db,
left(regexp_replace(query, '\s+', ' ', 'g'), 70) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;
-- total_sec | calls | mean_ms | pct_of_db | query
-- -----------+---------+---------+-----------+---------------------------
-- 4210.3 | 8412033 | 0.50 | 38.4 | SELECT * FROM users WHERE id = $1
-- 2980.1 | 41022 | 72.65 | 27.2 | SELECT ... FROM orders WHERE user_id = $1
-- 611.7 | 12 | 50975.0 | 5.6 | SELECT count(*) FROM events WHERE ...
--
-- Read that top row again. A 0.5ms query is 38% of your database.
-- No amount of EXPLAIN will help; the fix is to stop calling it
-- 8.4 million times (caching, batching, or an N+1 in the app).
-- ── Ranking 2: by MEAN time. Interesting, rarely urgent. ─────────
SELECT round(mean_exec_time::numeric, 1) AS mean_ms, calls,
left(query, 60) AS query
FROM pg_stat_statements
WHERE calls > 5 -- exclude one-off migrations and ad-hoc psql
ORDER BY mean_exec_time DESC
LIMIT 5;
-- The 51-second count(*) tops this list. It runs 12 times a day.
-- Fixing it saves 10 minutes of database time per day. The $1 lookup
-- above wastes 70 minutes per day. Mean-time ranking sent you to the
-- smaller problem.
-- ── Ranking 3: by I/O, not time. Finds cache thrashers. ─────────
SELECT
shared_blks_read AS blocks_from_disk,
shared_blks_hit AS blocks_from_cache,
round(100.0 * shared_blks_hit
/ nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
calls, left(query, 50) AS query
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 5;
-- A query with a low hit_pct and high blocks_from_disk is evicting
-- everyone else's pages from shared_buffers. It slows down queries
-- that have nothing to do with it. Module 8 covers this properly.
-- Reset the counters before a measurement window so you are looking at
-- this hour rather than the last six weeks:
SELECT pg_stat_statements_reset();