pg_stat_statements — Postgres Query Analysis Without Guessing

pg_stat_statements — Postgres Query Analysis Without Guessing#

For years my approach to Postgres performance was: notice slow queries when users complain, EXPLAIN ANALYZE them, add indexes, repeat. It works but it's reactive. pg_stat_statements turns the conversation around — it aggregates every query the database has run, sorted by total cost. Reading it weekly catches issues weeks before they become incidents.

This is the most useful Postgres extension you might not be using. Enabling it takes two minutes; the discipline of reading it pays off forever.

What it actually does#

pg_stat_statements normalizes every query the database sees (replacing literals with placeholders) and aggregates statistics per normalized query:

• Total execution time across all runs
• Number of calls
• Mean time per call
• Total rows returned
• Total shared blocks read / hit
• I/O timing (if enabled)

You query the pg_stat_statements view to see the aggregated data. The output is "this query has been called 1.2M times, taken 4 hours of total CPU, averages 12ms per call" — actionable, not just "slow queries."

Enabling it#

Two changes:

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

Restart Postgres. Then in your DB:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

That's it. The view is now populated and updates continuously. On RDS or other managed Postgres, the extension is usually pre-installed; you just need to enable it via parameter groups.

The queries we run#

A handful of pg_stat_statements queries we keep in a notes file:

Top 20 by total execution time#

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;

This is the "where is the database spending its CPU" view. The percent column tells you whether the top 20 are 20% or 95% of the workload. If 5 queries are 80% of the time, those are the queries that matter; optimize them and the database is meaningfully faster.

Slow per-call queries#

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
ORDER BY mean_exec_time DESC
LIMIT 20;

Slow queries are different from frequent queries. A 5-second query called 10 times is less urgent than a 50ms query called 1M times — but both matter for different reasons. The WHERE calls > 100 filters out one-off ad-hoc queries (a migration that ran once, a DBA's debugging session).

High I/O queries#

SELECT
    substring(query for 100),
    calls,
    shared_blks_read,
    shared_blks_hit,
    round(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct
FROM pg_stat_statements
WHERE shared_blks_read + shared_blks_hit > 10000
ORDER BY shared_blks_read DESC
LIMIT 20;

Queries that read a lot from disk. Low cache hit percentage = the query is reading data that's not in memory, which is slow. High shared_blks_read is often the signature of a missing index.

Queries returning lots of rows but rarely used#

SELECT
    substring(query for 100),
    calls,
    rows,
    round((rows::numeric / NULLIF(calls, 0))::numeric, 2) AS rows_per_call
FROM pg_stat_statements
WHERE calls > 10
ORDER BY rows_per_call DESC
LIMIT 20;

A query returning 50,000 rows per call is suspicious. Possible: it's a batch job (fine), it's missing a LIMIT (probably bug), or it's iterating through a too-large result set when pagination would work better.

What the data tells us#

A few patterns we keep seeing once teams start reading pg_stat_statements:

One missing index is dominating. Top query is a SELECT FROM orders WHERE user_id = $1. Mean time is 45ms (sounds OK), calls is 2M (uh-oh), total time is 25 hours. Adding an index on user_id drops mean to 0.5ms; total time drops to 15 minutes per equivalent period. The database "feels" faster across everything because we freed up so much CPU.

A query is running way more often than expected. "Why does this query run 500k times an hour?" Investigating reveals an N+1 in the ORM or a polling loop you forgot about. Sometimes the fix is in the code, not the database.

A function is dragging things down. A query that calls a Postgres function in the WHERE clause. Each call evaluates the function; if it's slow, every row evaluation is slow. Refactor or memoize.

A specific query is slow only sometimes. Mean is 50ms but max is 8 seconds. Tail latency issue. Usually a query plan that flips between fast (index scan) and slow (seq scan) depending on parameter values — pg_stat_statements shows the pattern; further investigation reveals the cause.

Operational rhythm#

The discipline:

• Weekly review. 15 minutes. Run the top-20 queries. Note any that moved into the top (new feature? regression?). Note any that left the top (good optimization or feature removal).
• Pre-deploy check. Before a release with significant DB changes, snapshot pg_stat_statements; compare after. Did any query get dramatically slower? Was a new heavyweight query introduced?
• Reset periodically. SELECT pg_stat_statements_reset(); clears the stats. Useful after major optimizations to measure the new baseline cleanly.

We've embedded this in a small Slack bot that posts the weekly top-20 to a channel. The conversation becomes "why did this query move up the list?" which is the conversation you want to be having.

Costs and caveats#

Overhead. pg_stat_statements adds ~5-10% overhead in our benchmarks (varies by workload). Worth it for the visibility. If you have a system where every microsecond counts, measure first.

Memory. The extension stores normalized queries in shared memory. pg_stat_statements.max = 10000 is plenty for most workloads; bump higher if your application generates many distinct queries.

Reset on restart. Stats accumulate since last reset (or restart). After a Postgres restart, you'll need traffic before the view is useful.

Doesn't show plans. pg_stat_statements knows about queries but not their EXPLAIN plans. For a specific slow query you find, you still need to EXPLAIN ANALYZE it.

Normalized queries can be confusing. SELECT * FROM users WHERE id = ? is one entry; the actual values are replaced. Sometimes you want to know what values were used — that information isn't here. Pair with log_statement for specific investigation.

What we don't do#

A few patterns we tried and dropped:

Real-time alerting on pg_stat_statements changes. Generates noise. The view is meant for periodic review, not minute-by-minute monitoring.

Tracking per-database when we have many. We have one DB per service mostly. If you have many DBs on one instance, you'll want per-database breakdowns.

Trying to act on every entry in the top 50. The top 5-10 usually dominate. Below that, marginal wins. Focus.

What to read next#

• Postgres query plans — reading them and the indexes we wish we'd added — what to do once you've found a slow query
• Postgres autovacuum — tuning from production stalls — stats are only useful if VACUUM is keeping them current
• Postgres connection pooling — PgBouncer in front of RDS — adjacent operational discipline
• Postgres replication lag — monitoring and failover practice — replication side of the same picture

pg_stat_statements is the cheapest big upgrade in Postgres operations. Enabling it is a 5-minute task; reading it weekly is a 15-minute habit; the cumulative effect on database health is measured in dropped query times and avoided incidents. If you're running Postgres without it, fix that today.

For a concrete example of the single-incident version of this — one query eating 41% of CPU and how we found it — see hunting slow queries with pg_stat_statements.