pg_stat_statements

pg_stat_activity shows the present. pg_stat_statements shows the aggregate, which is where the answer usually is.

Installing it

# postgresql.conf — RESTART REQUIRED
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = top      # or 'all' to include nested statements

CREATE EXTENSION pg_stat_statements;

The restart is the constraint that matters. It cannot be added during the incident that needs it, which is the whole argument for installing it on day one on every cluster.

Ranking correctly

$ SELECT calls, round(total_exec_time::numeric,1) AS total_ms,
     round(mean_exec_time::numeric,3) AS mean_ms,
     round(100*total_exec_time/NULLIF(sum(total_exec_time) OVER (),0))::int AS pct,
     left(query,48) AS query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 6;

 calls | total_ms | mean_ms | pct |                      query
-------+----------+---------+-----+--------------------------------------------------
9012 |   8790.1 |   0.975 |  85 | UPDATE pgbench_branches SET bbalance = bbalance
9012 |   1215.3 |   0.135 |  12 | UPDATE pgbench_tellers SET tbalance = tbalance +
9012 |    204.8 |   0.023 |   2 | UPDATE pgbench_accounts SET abalance = abalance
9012 |     68.0 |   0.008 |   1 | SELECT abalance FROM pgbench_accounts WHERE aid
9012 |     48.3 |   0.005 |   0 | INSERT INTO pgbench_history (tid, bid, aid, delt
9012 |      3.9 |   0.000 |   0 | END

Other rankings, each answering a different question:

-- what a user waits for: the slow ones
ORDER BY mean_exec_time DESC

-- unpredictable ones: sometimes fast, sometimes not
ORDER BY stddev_exec_time DESC

-- reading the most: candidates for indexing or a smaller working set
ORDER BY shared_blks_read DESC

-- spilling to disk: work_mem candidates, lesson XI-05
WHERE temp_blks_written > 0 ORDER BY temp_blks_written DESC

Normalisation, and its limit

$ SELECT * FROM pgbench_accounts WHERE aid = 42;
SELECT * FROM pgbench_accounts WHERE aid = 99;
SELECT calls, query FROM pg_stat_statements WHERE query LIKE '%pgbench_accounts WHERE aid%';

 calls |                     query
-------+-----------------------------------------------
   2 | SELECT * FROM pgbench_accounts WHERE aid = $1

This is what makes the view useful: a million executions with different values aggregate into one row.

Three things it does not normalise:

Utility statements. CREATE ROLE, ALTER ROLE, DDL — stored verbatim, which is lesson XVI-02’s security problem.

Structurally different queries. WHERE a = 1 and WHERE a = 1 AND b = 2 are different entries, correctly.

Long IN lists identically. PostgreSQL 18 changed this — the release notes describe it as making query id computation of constant lists consider only the first and last constants — so IN (1,2,3) and IN (1,2,3,4,5) now collapse together where previously they did not. That is a large reduction in entry churn on applications that build IN lists dynamically.

Working with it

The three instruments answer different questions and are strongest together:

1. pg_stat_statements — which statement costs most in aggregate?
2. Wait events — what is it blocked on? Lesson XVI-04.
3. EXPLAIN (ANALYZE, BUFFERS) — why is that statement expensive? Lesson X-02.

Take the top statement by total time, sample the wait events while it runs, then explain it. If the wait events say Lock, EXPLAIN will not help and the answer is in Part IX.

auto_explain closes the loop by capturing plans of statements that ran naturally, rather than plans you produced by hand under different conditions — which lesson X-03 argued is strictly better evidence.

What to take from this

• Requires a restart. Install it before you need it.
• Rank by total_exec_time. Calls and mean each answer a different, narrower question.
• Measured: identical call counts, and the statement touching five rows cost 85% while the one touching 500,000 cost 2%. That inversion is contention.
• Normalisation collapses constants; utility statements are stored verbatim.
• PostgreSQL 18 collapses long IN lists that previously churned entries.
• Watch pg_stat_statements_info.dealloc for eviction.
• total_exec_time excludes planning and includes waiting.

Cross-course references

• Observability for Production Sysadmins — Part CII (Slow queries) covers ranking by total time rather than by mean, which is the habit this view rewards, and Part XV (Histograms and latency) covers why a mean hides the tail that users actually experience.
• Linux for Production Sysadmins — Part XLIV (Central monitoring) covers retaining the series across the stats_reset that discards the view’s own history.

Quiz