pg_stat_statements: How to Find the Queries That Really Load PostgreSQL

pg_stat_statements is the PostgreSQL extension that records planning and execution statistics for every distinct SQL statement the server runs. When a database is slow and nobody knows why, it is the first place we look, because it answers the question the slow query log cannot: which statements consume the most database time in total.

A query that takes 40 seconds once a night looks scary in the log. A query that takes 3 ms but runs 2,000 times per second costs far more. pg_stat_statements groups statements by their normalized shape (constants become $1, $2), so those 2,000 calls per second show up as one row with real totals. The official documentation describes every column. Below is how we use it in practice.

Enable pg_stat_statements

The module needs shared memory, so it must be listed in shared_preload_libraries, and adding or removing it requires a server restart. Plan that restart in a maintenance window. The view itself is created per database with CREATE EXTENSION.

# postgresql.conf (restart required)
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = auto          # the default; must not be off
pg_stat_statements.max = 10000   # default 5000, server start only

-- then, in each database where you want the view
CREATE EXTENSION pg_stat_statements;

Query identifiers must be computed for the module to work. The default compute_query_id = auto enables this automatically when pg_stat_statements is loaded, so only check it if someone set it to off. On managed services such as RDS or Cloud SQL the same settings are changed through the parameter or flag configuration rather than postgresql.conf.

Top queries by total time

This is the query we run first. It ranks statements by total execution time and shows each one's share of the total:

SELECT queryid,
       calls,
       round(total_exec_time::numeric)    AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows / nullif(calls, 0)            AS rows_per_call,
       round((100 * total_exec_time
              / sum(total_exec_time) OVER ())::numeric, 1) AS pct,
       left(query, 80)                    AS query
FROM pg_stat_statements
WHERE toplevel
ORDER BY total_exec_time DESC
LIMIT 20;

The column names matter. Since PostgreSQL 13 execution time is in total_exec_time and mean_exec_time; on PostgreSQL 12 and older they were total_time and mean_time, which is why many copied scripts fail. Times are in milliseconds. The toplevel filter (PostgreSQL 14+) keeps statements called by the application and hides statements run inside functions, which are only tracked when pg_stat_statements.track = all; the default is top.

How to read the numbers

• High total, low mean, huge calls. Usually an N+1 pattern in the application or a missing cache. Making the query faster helps a little; calling it less helps a lot.
• High mean, few calls. A classic slow query. Take its text, run EXPLAIN (ANALYZE, BUFFERS) on a realistic copy, and work through the plan as in our PostgreSQL slow query tuning checklist.
• High rows_per_call. The application fetches far more rows than it shows. Pagination or a missing filter is often the real fix.
• Mean hides spikes. Look at max_exec_time and stddev_exec_time too. A statement with a 5 ms mean and a 30 s max is a lock or plan problem, not a CPU problem.
• Only successful executions count. The documentation notes statistics are updated for successful operations, so a query that keeps hitting statement_timeout can look cheaper than it is. Check the logs for cancellations.

Rank by I/O, temp files and WAL

Time is not the only cost. On a database that is bigger than RAM, the statements that read the most blocks from outside shared buffers are usually the ones pushing everything else out of cache:

SELECT queryid,
       calls,
       shared_blks_read,
       round(100.0 * shared_blks_hit
             / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
       temp_blks_written,
       pg_size_pretty(wal_bytes)          AS wal,
       left(query, 60)                    AS query
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 20;

Sort the same query by temp_blks_written to find sorts and hashes spilling to disk, or by wal_bytes to find the writes that drive replication lag and WAL archive volume. The shared_blk_read_time column (blk_read_time before PostgreSQL 17) is only filled when track_io_timing is on. It is off by default because timing calls can be expensive on some platforms; the documentation suggests measuring with pg_test_timing first.

Snapshots beat resets

The view holds cumulative counters since each entry was created. To see what happened between 14:00 and 14:15, store snapshots and subtract. Passing false to the function skips the query texts, which keeps frequent snapshots cheap:

-- take a snapshot without query texts (cheap), e.g. every 5-15 minutes
INSERT INTO pgss_snap
SELECT now(), userid, dbid, toplevel, queryid, stats_since,
       calls, total_exec_time, rows, shared_blks_read,
       temp_blks_written, wal_bytes
FROM pg_stat_statements(false);

-- how full is the hash table, and is it churning?
SELECT dealloc, stats_reset FROM pg_stat_statements_info;

Join snapshots on userid, dbid, toplevel, queryid. Since PostgreSQL 17 each entry has stats_since; if it changed between two snapshots, the entry was evicted and recreated, and the difference is meaningless. These deltas are also the raw material for alerting on the database metrics that predict incidents.

Gotchas worth knowing

• Entries get evicted. pg_stat_statements.max defaults to 5000. When more distinct statements appear, the least-executed ones are discarded and pg_stat_statements_info.dealloc grows. If dealloc rises steadily, raise max (it needs a restart) or find what generates so many unique statements.
• Query text can vanish. Texts live in an external file, not in shared memory. If that file grows too large, the module may discard all texts and the query column becomes NULL while the counters stay.
• Permissions. Only superusers and roles with pg_read_all_stats see the text and queryid of other users' statements. A monitoring user without it still sees the counters, but not which SQL they belong to.
• Planning time is off by default. pg_stat_statements.track_planning fills the *_plan_time columns, but the documentation warns it can cause a noticeable performance penalty. Enable it to investigate, not permanently by habit.
• PostgreSQL 18 groups more. According to the PostgreSQL 18 release notes, query IDs for constant lists such as IN (1, 2, 3) consider only the first and last constants, SET values are parameterized, and queries on tables with the same name in different schemas are grouped together. Good for noise, but per-schema numbers merge after an upgrade.

What changes on large, busy databases

• Statement churn matters more than size. Generated SQL with varying column lists, ORMs that inline values, and schema-per-tenant designs can produce far more than 5000 distinct statements. Constant eviction means your top list is incomplete exactly when you need it. Watch dealloc and size max to the workload; shared memory use grows with it.
• Each instance has its own statistics. Replicas and the primary keep separate counters. If read traffic goes to replicas, query them too, or the read-heavy statements simply will not appear on the primary.
• Keep snapshots outside the database you measure. Polling the full view with texts every few seconds on a server with tens of thousands of entries is itself load. Poll counters with pg_stat_statements(false), fetch texts only for new queryid values, and store history in your monitoring system.
• Connect the dots with queryid. With query identifiers computed, pg_stat_activity.query_id and the %Q escape in log_line_prefix use the same identifier, so a statement you see in the logs or blocking right now can be matched to its long-term totals.
• Test fixes on full-size data. A plan that looks fine on a small copy can change completely at terabyte scale. Verify index or query changes against production-sized data, then compare the before and after deltas in pg_stat_statements.