pg_stat_statements: The Definitive Guide to PostgreSQL Query Statistics

pg_stat_statements is a PostgreSQL extension that records cumulative execution statistics - call counts, total and mean execution time, rows returned, buffer I/O, temp file usage, and WAL volume - for every normalized query the server runs. It is the standard first tool for answering "which queries are consuming this database's time?" without enabling verbose logging.

Unlike the slow query log, which records individual statements as they cross a duration threshold, pg_stat_statements aggregates. A query that takes 5ms but runs 40,000 times a minute never appears in a slow query log, yet it can dominate total load - and it is exactly what this view surfaces.

How to Enable pg_stat_statements

Enabling takes three steps, and the first one requires a restart:

1. Preload the library. Add it to shared_preload_libraries in postgresql.conf:

   shared_preload_libraries = 'pg_stat_statements'
   

   The module allocates shared memory at server start, so a pg_ctl reload or SELECT pg_reload_conf(); is not enough - you must restart PostgreSQL. Skipping this step is the cause of the pg_stat_statements must be loaded via "shared_preload_libraries" error.

2. Create the extension in each database where you want to query the view:

   CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
   

   Statistics are collected server-wide regardless; CREATE EXTENSION only installs the view and functions in the current database.

3. Verify:

   SHOW shared_preload_libraries;
   SELECT count(*) FROM pg_stat_statements;
   

The module also requires query identifiers, controlled by compute_query_id. The default auto enables them automatically when the extension is loaded, so on a modern PostgreSQL you rarely need to touch it.

Configuration Parameters

All parameters are documented in the official pg_stat_statements docs:

The Columns That Matter

The view has grown to dozens of columns; these are the ones you will actually use:

• queryid - a hash of the normalized query's parse tree. Stable enough to join against pg_stat_activity.query_id and log lines, but not guaranteed stable across PostgreSQL major versions or machine architectures.
• query - the normalized text, with constants replaced by $1, $2, ...
• calls - number of executions.
• total_exec_time / mean_exec_time / min_exec_time / max_exec_time / stddev_exec_time - execution time in milliseconds. Before PostgreSQL 13 the column was called total_time; PG13 split planning from execution and renamed it to total_exec_time (with total_plan_time populated when track_planning is on). Scripts written for PG12 and earlier break on this rename.
• rows - total rows retrieved or affected.
• shared_blks_hit / shared_blks_read - buffer cache hits versus blocks read from disk (or the OS page cache). Their ratio is the per-query cache hit ratio.
• temp_blks_read / temp_blks_written - blocks of temp file I/O from sorts and hashes that spilled past work_mem. Note that pg_stat_statements counts temp usage in 8KB blocks; the temp_bytes column lives in pg_stat_database and aggregates per database, so multiply temp_blks_written by the block size to compare.
• wal_bytes / wal_records / wal_fpi - WAL generated by the statement, useful for finding write amplification.
• stats_since / minmax_stats_since (PG17+) - when tracking for this entry began, so you can normalize counters to a time window.

PostgreSQL 17 also renamed the I/O timing columns blk_read_time and blk_write_time to shared_blk_read_time and shared_blk_write_time, another rename worth knowing when upgrading dashboards.

Practical Queries

Top queries by total execution time - the queries that consume the database, whether slow or merely frequent:

SELECT queryid, calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

Slowest per call, filtered to queries that run often enough to matter:

SELECT queryid, calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms,
       left(query, 80) AS query
FROM pg_stat_statements
WHERE calls > 50
ORDER BY mean_exec_time DESC
LIMIT 15;

Heaviest disk readers and their cache hit ratio:

SELECT queryid, calls,
       shared_blks_read,
       round(100.0 * shared_blks_hit /
             nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 15;

Temp file spillers - candidates for a work_mem bump or a better plan:

SELECT queryid, calls,
       pg_size_pretty(temp_blks_written * 8192) AS temp_written,
       left(query, 80) AS query
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 15;

Resetting Statistics

Counters accumulate since the last reset, so "top by total time" is biased toward whatever has been running longest. Reset before a comparison window:

SELECT pg_stat_statements_reset();               -- everything
SELECT pg_stat_statements_reset(0, 0, 12345);    -- one queryid

The function accepts optional userid, dbid, and queryid arguments to reset a subset, and since PostgreSQL 17 a fourth minmax_only argument that clears only the min/max timings while preserving cumulative totals; it returns the reset timestamp. Execution requires superuser or an explicit GRANT (the pg_read_all_stats role covers reading the view, not resetting it). The companion pg_stat_statements_info view records stats_reset and dealloc - how many times entries were evicted because the number of distinct statements exceeded pg_stat_statements.max.

Performance Impact

The overhead of pg_stat_statements is low. It updates counters in shared memory per execution, and the PostgreSQL docs note the cost is small relative to query execution; typical measurements on realistic workloads land in the low single digits of a percent, which is why managed providers ship it enabled. Two caveats: pg_stat_statements.track_planning = on adds measurable overhead on high-throughput workloads of short queries (it is off by default for this reason), and workloads generating enormous numbers of distinct normalized statements (for example, dynamically generated IN lists of varying length) cause constant entry eviction and query-text file churn. Watch dealloc in pg_stat_statements_info; if it climbs steadily, raise pg_stat_statements.max or fix the statement generation.

Limitations

• Normalization hides parameters. All executions of SELECT * FROM orders WHERE id = $1 share one row, so you cannot see which specific value was slow. Pair with the slow query log and auto_explain for that.
• No per-execution history. The view holds cumulative counters, not a time series. Anything that wants "what changed at 14:05" needs periodic snapshots stored elsewhere.
• Eviction at max. Only the pg_stat_statements.max most recent distinct statements are kept; low-frequency queries can silently disappear.
• No plans. You get timings, not EXPLAIN output; join queryid to auto_explain logs to connect them.
• Restart semantics. With save = on, stats survive a clean shutdown but are lost on a crash or immediate shutdown.

pg_stat_statements vs pg_stat_monitor vs Log-Based Analysis

pg_stat_monitor is Percona's extension that layers time bucketing, histograms, and query examples on top of the same idea; it can run alongside or instead of pg_stat_statements. It solves the "no history" limitation but is not available on most managed platforms, which is why pg_stat_statements remains the portable default.

Availability on Managed Services

• Amazon RDS and Aurora PostgreSQL: the library is in shared_preload_libraries by default on PostgreSQL 11+ engines, per the AWS documentation - just run CREATE EXTENSION pg_stat_statements;. Performance Insights uses it for its SQL statistics.
• Supabase: enabled by default; the dashboard's query performance reports are built on it.
• Google Cloud SQL: shared_preload_libraries is not a user-settable flag; the service loads the library itself (Query Insights depends on it), so CREATE EXTENSION pg_stat_statements; is all you need per the Cloud SQL extensions docs.

If CREATE EXTENSION or querying the view fails, see fixing "pg_stat_statements must be loaded via shared_preload_libraries".

From Snapshots to Continuous Analysis

Everything above is point-in-time: you query the view, eyeball the top offenders, reset, repeat. NeverBlink automates the loop for PostgreSQL fleets - it snapshots pg_stat_statements continuously, turns the cumulative counters into per-interval rates, and its agentic root-cause analysis connects a regression in mean_exec_time to the deploy, plan flip, or missing index behind it instead of leaving you to diff two query results by hand.

Frequently Asked Questions

Q: What is the difference between total_exec_time and mean_exec_time?
A: total_exec_time is the sum of execution time across all calls, in milliseconds; mean_exec_time is that total divided by calls. Sort by total time to find what loads the server, and by mean time to find what feels slow to users.

Q: Why do I get "relation pg_stat_statements does not exist"?
A: CREATE EXTENSION pg_stat_statements was never run in the database you are connected to. The extension is per-database even though collection is server-wide. See the dedicated error guide.

Q: Does enabling pg_stat_statements require a restart?
A: Yes, adding it to shared_preload_libraries requires a full server restart because shared memory is allocated at startup. CREATE EXTENSION itself needs no restart. On RDS, Aurora, Supabase, and Cloud SQL the library is already loaded, so no restart is needed there.

Q: How much overhead does pg_stat_statements add?
A: Low single-digit percent on typical workloads, which is why major managed providers enable it by default. track_planning = on and workloads with huge numbers of distinct statement shapes are the cases where overhead becomes noticeable.

Q: Where is temp_bytes in pg_stat_statements?
A: There is no temp_bytes column in this view; temp usage appears as temp_blks_read and temp_blks_written in 8KB blocks. temp_bytes is a per-database counter in pg_stat_database. Multiply temp_blks_written * 8192 for bytes.

Q: Why did my monitoring query break after upgrading to PostgreSQL 13 or 17?
A: PG13 renamed total_time to total_exec_time (splitting out total_plan_time), and PG17 renamed blk_read_time/blk_write_time to shared_blk_read_time/shared_blk_write_time. Update the column names in your queries.

Q: How do I reset pg_stat_statements for a single query?
A: Call SELECT pg_stat_statements_reset(0, 0, <queryid>);. Passing zeros means "any" for that argument; with no arguments everything resets. Since PG17 the call returns the timestamp of the reset.

Related Reading

• Fix: pg_stat_statements must be loaded via shared_preload_libraries: the errors you hit when setup is incomplete.
• PostgreSQL Slow Query Log: per-statement logging that complements these aggregates.
• Diagnosing Slow Queries in PostgreSQL: the wider workflow this view fits into.
• PostgreSQL EXPLAIN ANALYZE: getting the plan behind a queryid you found here.
• PostgreSQL Performance Tuning: acting on what pg_stat_statements reveals.
• Debugging Low Cache Hit Ratio in PostgreSQL: the fleet-wide view of the per-query hit ratio above.