pg_stat_statements: Query Performance Monitoring

pg_stat_statements is the single most useful Postgres extension for production performance work. It tracks every query shape executed against the database — normalized to remove literal values — and accumulates execution statistics: total time, mean time, call count, rows, I/O blocks, and more. Every performance investigation should start here.

Enabling pg_stat_statements

The extension must be loaded at server start via shared_preload_libraries:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all          # top (default) = top-level only; all = nested too
pg_stat_statements.max = 10000          # max query fingerprints to track
pg_stat_statements.track_utility = off  # exclude COPY, VACUUM, etc.

After restarting Postgres, enable the extension in each database you want to monitor:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

On managed services (RDS, Cloud SQL, AlloyDB, Supabase), the extension is usually pre-installed. Check with SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements'.

Query normalization and query_id

pg_stat_statements normalizes queries by replacing literal values with placeholders. The queries:

SELECT * FROM users WHERE id = 42;
SELECT * FROM users WHERE id = 99;

produce a single normalized entry:

SELECT * FROM users WHERE id = $1

The query_id column (added in Postgres 14) is a stable 64-bit hash of the normalized query text. Use it to track a specific query shape across resets and across tools. Prior to PG14, the queryid column existed but was not stable across server restarts.

Top queries by total_time

Total time is the right metric for finding where the database spends the most wall-clock time. A query that runs in 1ms but executes 100,000 times per hour costs more than a query that takes 500ms but runs once an hour.

SELECT
  round(total_exec_time::numeric, 2)              AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2)               AS mean_ms,
  round(stddev_exec_time::numeric, 2)             AS stddev_ms,
  round(100.0 * total_exec_time /
    sum(total_exec_time) OVER (), 2)              AS pct_total,
  left(query, 120)                                AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 20;

The pct_total column immediately shows which query shapes dominate total database time. Optimize the top few — they typically account for 80%+ of total load.

Top queries by mean_time

Mean time surfaces queries that are individually slow. High mean time with low call count often indicates missing indexes on infrequent but user-facing paths:

SELECT
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(max_exec_time::numeric, 2)  AS max_ms,
  left(query, 120)                  AS query
FROM pg_stat_statements
WHERE calls > 100
  AND mean_exec_time > 100
ORDER BY mean_exec_time DESC
LIMIT 20;

The max_exec_time column catches outliers — a query with mean 5ms but max 45 seconds has a tail latency problem (usually lock waits or planner instability).

I/O-heavy queries

shared_blks_read counts page reads from disk (cache miss) shared_blks_hit counts reads from shared_buffers. High shared_blks_read relative to shared_blks_hit indicates a query that bypasses cache — either because it scans a large table or because the working set doesn't fit in shared_buffers:

SELECT
  calls,
  round(total_exec_time::numeric / calls, 2)      AS mean_ms,
  shared_blks_hit,
  shared_blks_read,
  round(100.0 * shared_blks_read /
    nullif(shared_blks_hit + shared_blks_read, 0), 1) AS miss_pct,
  left(query, 120) AS query
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 10000
ORDER BY shared_blks_read DESC
LIMIT 20;

A high miss_pct on a query you expect to be index-scan-heavy suggests either the index is bloated, the buffer cache is too small, or the query is doing a sequential scan.

Temp block usage (sort and hash spills)

temp_blks_written counts blocks written to temporary disk files — this happens when sorts, hash joins, or hash aggregates exceed work_mem. Spills add order-of-magnitude latency:

SELECT
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  temp_blks_written,
  left(query, 120) AS query
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;

For queries that spill frequently, increase work_mem for that session or role rather than globally. Global increases can cause OOM on instances with many concurrent queries.

Reset cadence and baselines

pg_stat_statements accumulates statistics since the last reset (or server start). Resetting too frequently loses historical signal; never resetting means a burst of slow queries from two weeks ago still shows up in averages.

A practical approach: snapshot the top queries daily into a monitoring table, then reset weekly or after major deployments. Snapshotting lets you diff before/after a release:

-- reset stats (requires pg_stat_statements privilege or superuser)
SELECT pg_stat_statements_reset();

In Postgres 14+, you can reset statistics for a specific query_id without clearing everything:

SELECT pg_stat_statements_reset(0, 0, query_id)
FROM pg_stat_statements
WHERE query LIKE '%expensive_pattern%';

Quick checklist

Enable via shared_preload_libraries and CREATE EXTENSION — no restart needed for the extension, only for the preload.
Start performance investigations with ORDER BY total_exec_time DESC — find the highest-cost query shapes first.
Use max_exec_time to catch tail latency outliers hiding in a low mean.
High shared_blks_read on an index scan path → check for index bloat.
Non-zero temp_blks_written → consider per-session work_mem increases.
Snapshot daily; reset after major releases to isolate regression signal.