PostgreSQL slow-query checklist

The symptoms

• •Query latency p95/p99 rises above an application-defined SLO while the database CPU and disk I/O remain within historical bands.
• •EXPLAIN (ANALYZE, BUFFERS) reports an estimated row count that is one or more orders of magnitude away from the actual rows returned for the same plan node.
• •pg_stat_activity shows the session stuck on a wait_event such as Lock, LWLock, IO:DataFileRead, or ClientRead/ClientWrite rather than executing.
• •Application logs show intermittent timeouts or statement_timeout cancellations on a query that previously completed in single-digit milliseconds.
• •Auto-vacuum or auto-analyze lag grows on a table referenced by the slow query, and planner estimates drift further between runs.

Likely causes

• •Stale planner statistics: ANALYZE has not been run after bulk loads or deletes, so the planner chooses nested loop with a small inner side instead of hash join, or picks an index scan where a sequential scan would be cheaper.
• •Predicate sargability or type coercion: implicit casts (e.g., text compared to uuid, or text compared to timestamptz) prevent an index match and force a sequential scan or filter on top of an index scan.
• •Lock contention: another transaction holds a row, page, relation, or advisory lock that the slow query needs; the bottleneck is wait_event = Lock, not CPU.
• •Buffer cache pressure: shared_buffers undersized or working set recently invalidated, so hot pages are re-read from disk and the plan is correct but IO-bound (wait_event = IO:DataFileRead, high 'read' in BUFFERS).
• •Schema or definition drift: an index was dropped, a column type changed, a partial index no longer matches the WHERE clause, or the query now filters on a non-indexed expression.
• •Parameter sniffing with prepared statements: one bad plan is cached by the session and reused across inputs of very different cardinality.

First ten minutes

1. 01Confirm the slow query text and bind values from the application or from pg_stat_statements; record the literal SQL, not a paraphrased version.
2. 02Capture pg_stat_activity rows for the database and user involved; record state, wait_event_type, wait_event, query_start, and xact_start for each.
3. 03Run EXPLAIN (ANALYZE, BUFFERS) on the exact query with its actual bind values against a non-production copy, or during a maintenance window if data-sensitive; capture both estimated and actual rows per node.
4. 04Compare n_live_tup, n_mod_since_analyze, and last_analyze / last_autoanalyze on each referenced table against the volume of recent DML.
5. 05Check for blockers: query pg_locks joined to pg_stat_activity to identify the holder of any lock the slow session is waiting on.

Evidence to collect

• •EXPLAIN (ANALYZE, BUFFERS) output showing per-node actual time, actual rows, shared hit vs shared read, and any 'rows removed by filter' or 'loops' anomalies.
• •pg_stat_activity snapshot with wait_event_type, wait_event, state, query_id (pg_stat_statements), and backend_start.
• •pg_stat_user_tables rows for each referenced relation: seq_scan, idx_scan, n_live_tup, n_mod_since_analyze, last_analyze, last_autoanalyze.
• •pg_locks rows joined to pg_stat_activity showing granted = false for the slow backend and the granted = true session holding the conflicting lock.
• •pg_stat_statements entry for the query with calls, total_exec_time, mean_exec_time, rows per call, and planid if pg_stat_statements.planid is enabled.

Where to look

• •PostgreSQL statistics views: pg_stat_activity, pg_stat_user_tables, pg_stat_statements, pg_stat_user_indexes in the target database.
• •Planner introspection: EXPLAIN (ANALYZE, BUFFERS) output for the query, plus pg_stat_statements planid entries when plan capture is enabled.
• •Lock layer: pg_locks joined with pg_stat_activity on pid, filtered by relation, page, tuple, transactionid, and advisory lock modes.
• •Storage layer: wait_event = IO:DataFileRead / IO:DataFileWrite entries in pg_stat_activity, and the shared hit vs shared read counters in the EXPLAIN BUFFERS output.
• •Schema layer: information_schema.columns and pg_indexes to confirm column types, index definitions, and whether a partial index predicate still matches the slow query.

Diagnostic steps

1. 01If actual rows diverge from estimated rows by 10x or more at the outermost or join-driving node, attribute the slow path to bad estimates and verify with ANALYZE on the referenced tables before considering plan hints.
2. 02If BUFFERS shows high 'shared read' on an index scan that returns few rows, attribute the slow path to cache pressure and verify with pg_buffercache on the index relfilenode before raising shared_buffers.
3. 03If wait_event_type = Lock, read pg_locks to identify locktype and mode; tuple-level locks imply concurrent UPDATE of the same row, relation-level locks imply DDL or ACCESS EXCLUSIVE work.
4. 04If wait_event_type = LWLock and the event is BufferContent or WALWrite, attribute the slow path to write amplification, full-page writes, or checkpoint pressure rather than the query itself.
5. 05If the plan uses an index on a cast or function expression, compare the WHERE clause to the index definition in pg_indexes; an implicit cast on the parameter is sufficient to bypass the index.
6. 06If pg_stat_statements shows one planid reused across calls with very different row counts, treat the slow path as parameter sniffing inside the session, not as a server-wide regression.

Common mistakes

• •Adding an index without first reading EXPLAIN; if the slow path is a bad estimate or a cast, a new index changes nothing and increases write cost.
• •Increasing shared_buffers or work_mem in response to a single query without BUFFERS evidence that the bottleneck is shared reads or in-memory sort spill.
• •Killing the blocking transaction reflexively; the blocker may be a legitimate batch job whose lock duration is bounded, so the fix is scheduling, not termination.
• •Running ANALYZE on an empty production table or during a heavy DML window and treating the resulting statistics as ground truth; analyze after representative data is present.
• •Assuming a sequential scan is always wrong; for low-cardinality filters on wide tables, Seq Scan can be optimal and forcing an index can make latency worse.

Safe fixes

• •If EXPLAIN shows large estimate drift, run ANALYZE on the referenced tables and re-run EXPLAIN; only consider index changes if the new plan still mis-estimates after fresh statistics.
• •If an implicit cast bypasses an index, normalize the bind type in the application driver so the WHERE clause matches the column type, then verify EXPLAIN switches to an Index Scan.
• •If wait_event = Lock is sustained, identify the holder via pg_locks and either move the long transaction to a maintenance window or reduce its lock footprint; do not raise lock_timeout as a substitute for diagnosis.
• •If wait_event = IO:DataFileRead dominates and BUFFERS shows shared read on a hot index, expand the cache incrementally and verify the hit ratio improves with pg_stat_io before declaring success.
• •If a partial or expression index is unused, rewrite the query so its predicate matches the index expression literally, then re-check EXPLAIN; do not drop and recreate the index without confirming the predicate.

Prove the fix

1. 01EXPLAIN (ANALYZE, BUFFERS) of the same query with the same bind values shows actual rows within roughly 2x of estimated rows at every node, and shared read has dropped relative to shared hit.
2. 02pg_stat_statements reports mean_exec_time for the normalized query (to constant) has decreased relative to the pre-fix baseline over at least one full business cycle, not a single sample.
3. 03Repeated sampling of pg_stat_activity for the query text shows no entries with wait_event_type = Lock for the same query_id over a representative window.
4. 04Application-side latency telemetry (e.g., the calling endpoint's histogram bucket) returns to within its pre-incident p95 band for the same traffic shape.
5. 05n_mod_since_analyze on each referenced table stays below the configured autovacuum_analyze_scale_factor threshold between runs, so estimates remain fresh under normal load.

Prevention and next steps

• •Enable pg_stat_statements and pg_stat_io on every production database; track mean_exec_time and shared reads as first-class SLO inputs, not afterthoughts.
• •Keep autovacuum_naptime, autovacuum_analyze_scale_factor, and autovacuum_analyze_threshold tuned for the write volume of each table so statistics age slowly.
• •Standardize bind-parameter typing in the application driver layer so WHERE clauses never rely on implicit casts; enforce the rule in code review.
• •Capture representative EXPLAIN plans in version control alongside the query text so regressions are visible at code-review time, not only after an incident.
• •Monitor pg_locks counts per locktype and per database over time so lock-storm patterns surface before a single slow query becomes an outage.

Safe commands and checks

SELECT pid, usename, state, wait_event_type, wait_event, query_start, xact_start, left(query, 200) FROM pg_stat_activity WHERE datname = current_database() AND state <> 'idle';
SELECT relname, seq_scan, idx_scan, n_live_tup, n_mod_since_analyze, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname IN (<table_list>);
SELECT query, calls, mean_exec_time, rows FROM pg_stat_statements WHERE query ILIKE '%<query_signature>%' ORDER BY mean_exec_time DESC LIMIT 5;
SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, blocked.wait_event_type, blocked.wait_event, blocking.query FROM pg_stat_activity blocked JOIN pg_locks bl ON bl.pid = blocked.pid JOIN pg_locks kl ON kl.locktype = bl.locktype AND kl.database IS NOT DISTINCT FROM bl.database AND kl.relation IS NOT DISTINCT FROM bl.relation AND kl.page = bl.page AND kl.tuple = bl.tuple AND kl.transactionid = bl.transactionid AND kl.pid <> bl.pid AND kl.granted JOIN pg_stat_activity blocking ON blocking.pid = kl.pid WHERE NOT bl.granted;
EXPLAIN (ANALYZE, BUFFERS) <slow_query_with_actual_bind_values>;
SELECT indexrelid::regclass, indisunique, indispartial, pg_get_indexdef(indexrelid) FROM pg_index JOIN pg_stat_user_indexes USING (indexrelid) WHERE relid = '<referenced_table>'::regclass;
SELECT relname, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit FROM pg_statio_user_tables WHERE relname = '<referenced_table>';
SELECT wait_event_type, wait_event, count(*) FROM pg_stat_activity WHERE datname = current_database() GROUP BY 1,2 ORDER BY 3 DESC;