pg_stat_statements: Postgres Query Analysis Without Guessing

For years our approach to Postgres performance was reactive: notice slow queries when users complain, EXPLAIN ANALYZE them, add indexes, repeat. pg_stat_statements turns that around. It aggregates every query the database has run by query shape, so you can sort by what actually costs you, and it finds the problem the slow query log can't: a fast query run millions of times. Enabling it takes a restart; reading it weekly catches issues weeks before they become incidents.

What it actually does

pg_stat_statements normalizes every query (literals become placeholders like $1) and aggregates per normalized query: total execution time, calls, mean and max time per call, rows returned, shared blocks hit versus read, and I/O timing if enabled. The output reads "this query ran 1.2M times, took 4 hours in total, averages 12ms," which is a finding rather than a list of slow statements.

Enabling it

It ships with Postgres but isn't loaded by default, and loading it means a restart. Plan for that.

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000   # how many normalized queries to keep
track_io_timing = on             # adds I/O timing data (small overhead)

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

track decides what gets recorded. top (the default) records only top-level statements, so queries issued inside functions vanish into the function call. If much of your logic lives in PL/pgSQL, use all, accepting a little more overhead and a fuller table. On RDS and most managed Postgres, the extension is available; you enable it through the parameter group.

The incident that taught us to sort by total time

CPU on our primary was pinned at 85% every afternoon, and the on-call graph just said "database busy," which is a shrug, not a finding. The slow query log was quiet, because nothing crossed our 500ms log_min_duration_statement threshold. The cause wasn't one slow query. It was a fast query run 40,000 times a minute.

Everyone sorts by mean_exec_time first and finds an analytics query that runs once a day for nine seconds. That's nine seconds a day. Sort by total_exec_time, because total time is what consumes CPU:

SELECT
    substring(query for 100) AS query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round((100 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2) AS percent_total
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Read percent_total first. It tells you whether the top 20 are 20% or 95% of the workload. Our culprit appeared on line one:

 query                                          | calls    | total_ms  | mean_ms | percent_total
------------------------------------------------+----------+-----------+---------+--------------
 SELECT * FROM sessions WHERE token = $1        | 38412007 | 4820193   |    0.13 |         41.2
 UPDATE users SET last_seen = $1 WHERE id = $2  |  9120334 | 1210556   |    0.13 |         10.3

0.13ms per call, 38 million calls, 41% of all database time. It was already an index-only scan, so no index would help. The fix was in the app: we moved session validation to Redis instead of hitting Postgres on every request, and the line dropped off the chart the next day.

The other queries we keep in a notes file

Slow per call. Slow and frequent are different problems: a 5-second query called 10 times is less urgent than a 50ms query called 1M times, but both matter.

SELECT substring(query for 100), calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(max_exec_time::numeric, 2) AS max_ms
FROM pg_stat_statements
WHERE calls > 100            -- drop one-off migrations and DBA sessions
ORDER BY mean_exec_time DESC
LIMIT 20;

High I/O. Swap the columns for shared_blks_read, shared_blks_hit, and round(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct, filter to shared_blks_read + shared_blks_hit > 10000, and order by shared_blks_read DESC. A low hit percentage with high reads means the query goes to disk: it scans more than it should, or your working set doesn't fit in RAM. We found a reporting query at a 62% hit ratio; a partial index on status = 'active' pushed it to 99%.

Rows per call. rows / NULLIF(calls, 0) ordered descending. A query returning 50,000 rows per call is either a batch job (fine), a missing LIMIT (a bug), or iteration that should be pagination.

The patterns the data keeps showing

One missing index dominates. SELECT FROM orders WHERE user_id = $1 at 45ms mean sounds fine until you see 2M calls and 25 hours of total time. An index on user_id drops the mean to 0.5ms, and the whole database feels faster because of the CPU freed.

A query runs far more often than it should. "Why does this run 500k times an hour?" is usually an ORM N+1 or a forgotten polling loop, as with our session check. The fix is often in code, not the database.

A function in the WHERE clause. It's evaluated per row; if it's slow, everything is slow. Refactor or memoize.

Slow only sometimes. Mean 50ms, max 8 seconds: usually a plan that flips between an index scan and a seq scan depending on parameters. pg_stat_statements shows the pattern; reading the query plan shows the cause.

Reset before you investigate, review weekly after

Counters are cumulative since the last reset or restart. During an incident, three weeks of totals drown the signal from the last hour, so we reset at the start of every investigation:

SELECT pg_stat_statements_reset();
-- wait ~10 minutes inside the problem window, then rerun the total-time query

The counters that matter are the ones that move during the problem. The same reset gives you a clean baseline after a major optimization.

Outside incidents, the discipline is a 15-minute weekly review of the top 20: note what moved in (new feature? regression?) and what moved out. Before a release with significant database changes, snapshot the view and compare afterwards. A small Slack bot posts our weekly top 20, and the conversation becomes "why did this query move up?", which is the conversation you want.

We tried real-time alerting on changes in the view and dropped it as noise; it's built for periodic review. We also stopped trying to act on the whole top 50. The top 5-10 dominate; below that, wins are marginal.

Costs and caveats

Overhead. We measured 5-10% depending on workload. Worth it for the visibility; measure first if every microsecond counts.

Memory. Normalized queries live in shared memory. max = 10000 covers most workloads; raise it if your app generates many distinct statements.

Replicas track themselves. On a replica, the view shows that replica's own query traffic, not the primary's. If reads go to a replica pool, check each node; your slow read may live on one you weren't looking at.

No plans, no values. It knows queries, not their plans, and normalization discards the actual parameter values. Pair it with EXPLAIN ANALYZE and targeted statement logging. Stats are also only as good as the planner's statistics, which depends on autovacuum keeping up.

Questions people ask

Why doesn't the slow query log show what's eating my Postgres CPU?

log_min_duration_statement logs only individual executions above a threshold. A query that takes 0.13ms never crosses a 500ms threshold, even if it runs 38 million times and owns 41% of database time. pg_stat_statements aggregates by query shape, so sorting by total_exec_time exposes that death-by-a-million-fast-queries pattern the log is blind to.

Should I sort pg_stat_statements by mean_exec_time or total_exec_time?

Start with total_exec_time, because it shows where the database actually spends its time. mean_exec_time surfaces individually slow queries, which matter for user-facing latency but are often rare. Run both, filter the mean-time view to calls > 100 to drop one-offs, and fix the top of the total-time list first.

The decision, concretely

• CPU high but the slow query log is quiet? Reset the stats, wait ten minutes, sort by total_exec_time.
• Top query fast per call but huge in total? Cut the call count (caching, N+1 fix) before touching indexes.
• Low cache_hit_pct with high reads? Look for a missing or partial index, then check whether the working set fits in RAM.
• Nothing on fire? Fifteen minutes a week on the top 20, plus a snapshot around risky deploys.

The call we'd make

Install pg_stat_statements on every Postgres instance you run, and always sort by total_exec_time first. It costs a restart and a single-digit percentage of overhead, and it has paid for itself in the first incident every time. Then make the weekly review a habit, alongside the rest of the operational basics like pooling connections with PgBouncer and watching replication lag. The caveat: it tells you which queries cost the most, not why, so budget time for the plan reading that follows.

Sources

• PostgreSQL docs: pg_stat_statements