# pg_stat_statements: Postgres Query Analysis Without Guessing URL: https://www.devopsness.com/blog/postgres-pg-stat-statements-query-analysis Author: Kiril Urbonas Published: 2026-06-05 Updated: 2026-10-12 Categories: Infrastructure Tags: Postgres, Database, Performance, Indexes, SQL, Monitoring Summary: The single most useful Postgres extension you might not be using. The queries it surfaces, the indexes it implies, and the operational discipline of reading it weekly. --- # 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. ```ini # 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) ``` ```sql 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: ```sql 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. ```sql 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](/blog/postgres-query-plans-and-indexes) 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: ```sql 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](/blog/postgres-autovacuum-tuning-from-production-stalls). ## 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](/blog/postgres-connection-pooling-pgbouncer) and [watching replication lag](/blog/postgres-replication-lag-failover-practice). 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](https://www.postgresql.org/docs/current/pgstatstatements.html)