PostgreSQL query is fast then slow: find the changing data or plan state

The symptoms

• •Reported query latency rises after data grows past a threshold or after an analytics/batch job runs, but the query itself and its parameters are unchanged.
• •The same query plan shape still appears in EXPLAIN but the per-node row estimates stop matching actual rows by orders of magnitude once the table is no longer near-empty.
• •Latency is stable on first execution of the day but balloons after the buffer cache is cold or after checkpoint storms resume.
• •The query times out only when another workload is active: long-running transactions, an autovacuum run, a replication client, or a parallel maintenance task holding AccessShareLock or ShareLock on a referenced table.
• •Throughput collapses for a single query but companions with identical schema patterns remain fast, indicating the cause is per-query state and not a global server overload.

Likely causes

• •Plan regression driven by stale or default statistics: planner switches from an index scan to a sequential scan, or from a nested loop to a hash join, as the table leaves the very small-nstats regime sampled during the last ANALYZE.
• •Buffer cache pressure: working set no longer fits in shared_buffers or OS page cache, so the same logical reads now require physical reads, multiplying the cost of sequential paths or large hash operations.
• •Lock or contention drift: a previously free AccessShareLock is now contested by an autovacuum worker, a logical replication subscriber, a long-lived report transaction, or a concurrent DDL, forcing the fast query to queue behind heavyweight state.
• •WAL, hint-bit, or visibility-map churn: tables that were recently bulk-loaded or DELETEd without vacuum show many heap pages that must be visited even though the rows are not returned, distorting I/O and plan cost.
• •Connection-pool or worker saturation: the query's statement_timeout, idle_in_transaction_session_timeout, or work_mem binding changes because the connection now arrives via a different pool or role, capping memory or interrupting execution.

First ten minutes

1. 01Capture the offending query verbatim, parameter set, and the exact time it was last fast and first slow; record p50, p95, and p99 as you have them, not only averages, because the fast-then-slow pattern is typically a tail-story.
2. 02Confirm you can reproduce the latency using psql or a read-only role with the same parameters; do not rely on the application's pooled connection for the first measurement because pool-side state is one of the failure boundaries.
3. 03Run EXPLAIN (ANALYZE, BUFFERS, VERBOSE) on the slow query and compare the plan against an EXPLAIN captured when the query was fast; note where actual rows diverge from estimated rows, because that gap is the planner's signal that something changed.
4. 04Pull pg_stat_user_tables for the referenced relations and check seq_scan, idx_scan, n_live_tup, n_mod_since_analyze, and last_analyze / last_autoanalyze timestamps; a high n_mod_since_analyze alongside rising seq_scan is the smoking gun for plan regression.
5. 05Pull pg_stat_activity for waits: filter on wait_event_type and wait_event, and on state = 'active' where xact_start is older than the query's typical runtime; long idle-in-transaction sessions are a frequent cross-tenant cause.
6. 06Pull pg_stat_database for the database and pg_stat_io or pg_stat_bgwriter for I/O wait behavior; rising BufFile or temp file counts tied to the slow query indicate work_mem is now a bottleneck instead of a comfort.
7. 07Decide which boundary owns the symptom before changing anything: data stats, buffer cache, locks/waits, or plan shape, then jump to the matching diagnostic path in this guide.

Evidence to collect

• •EXPLAIN (ANALYZE, BUFFERS, VERBOSE) output from a slow run and a fast run, with parameter values identical, so plan shape, row estimates, and buffer hits can be diffed.
• •pg_stat_user_tables row showing seq_scan, idx_scan, n_live_tup, n_mod_since_analyze, last_analyze, last_autoanalyze for every relation touched by the query.
• •pg_stat_activity snapshot filtered to the slow query's database and user, capturing pid, state, wait_event_type, wait_event, xact_start, query_start, and the leading line of the query text.
• •pg_stat_statements entry for the query, identified via queryid or normalized query text, showing calls, mean_exec_time, and shared_blks_hit versus shared_blks_read over the window.
• •pg_stat_io and pg_stat_bgwriter counters around the slow window, scoped to the database's tablespace, so background writer and backend read increases can be tied to the incident.
• •Lock view entries for the query's relations from pg_locks, joined to pg_class, to confirm whether blocking transactions exist and which transaction id is the head of the queue.

Where to look

• •Planner/optimizer boundary: pg_stat_user_tables.n_mod_since_analyze and last_autoanalyze, alongside EXPLAIN row estimates, sit at the boundary between statistics truth and plan choice.
• •Buffer cache boundary: shared_buffers hit ratio from pg_stat_database (blks_hit versus blks_read) and per-query buffer counters from EXPLAIN (ANALYZE, BUFFERS) expose whether the working set still fits in cache.
• •Concurrency boundary: pg_stat_activity wait_event_type and wait_event, and pg_locks.granted versus granted = false, expose whether the query is paused on an AccessShareLock, an LWLock, or a heavyweight lock held by another transaction.
• •I/O subsystem boundary: pg_stat_io reads and pg_stat_bgwriter buffers_clean and buffers_backend versus buffers_alloc, alongside WAL and checkpoint metrics, show whether write amplification is spilling into the read path.
• •Work_mem boundary: EXPLAIN's BUFFERS temp blocks, plus pg_stat_database.temp_bytes and temp_files, mark the threshold where a previously in-memory operator now spills to disk.

Diagnostic steps

1. 01Compute planner honesty by subtracting estimated rows from actual rows at every node in the slow run's EXPLAIN (ANALYZE); a single node with estimates off by 10x or more is the candidate cause for a plan shape change.
2. 02Compare EXPLAIN shape between fast and slow runs: if the only difference is estimated cost and the chosen path, you have a soft plan regression driven by statistics; if the shape itself changed, suspect a settings or role-level change.
3. 03Compare shared_blks_hit and shared_blks_read between fast and slow runs of the same query; a steep drop in hit ratio with equal logical work proves the cache boundary is the failure mode.
4. 04Inspect pg_stat_activity during a slow sample and group wait_event values; dominant waits on Lock, LWLock, or BufferPin map directly to contention or cache-miss I/O, while waits on DataFileRead or DataFileWrite map to storage I/O.
5. 05Walk pg_locks for the relation OIDs in the query plan and check whether any granted = false tuple shares the relation; the head of the wait queue tells you which transaction is starving the fast path.
6. 06Cross-check pg_stat_statements.calls against pg_stat_user_tables.n_mod_since_analyze: if calls are constant and n_mod_since_analyze has grown but no ANALYZE has run, the planner is making decisions on stale facts.
7. 07Re-run EXPLAIN with SET (enable_seqscan = off) or SET (enable_hashjoin = off) only as a diagnostic; if latency drops, you have confirmed the planner would have used a better path if its cost estimates had been honest.
8. 08Confirm the failure is per-query, not per-cluster: run a synthetic pgbench or a same-schema companion query in the same window and see whether it stays fast; if it does, the cause is local to the slow query's data and plan, not the server.

Common mistakes

• •Blaming the slow query's index when the real defect is that the planner stopped choosing it; an unused index is not the cause, the missing statistics are.
• •Forcing ANALYZE on a single table and then declaring the system healthy when the query joins several tables whose stats are independently stale.
• •Reading only mean_exec_time from pg_stat_statements and missing that mean is diluted by a long warm-cache window; the fast-then-slow pattern is almost always a tail-latency pattern first.
• •Ignoring wait_event in pg_stat_activity and reporting "no locks" because pg_locks looked empty at the exact millisecond you sampled; long waits are often transient and require multiple captures.
• •Treating n_mod_since_analyze as harmless above some threshold; the threshold is query-dependent, and a single hot column whose histogram is now stale can swing a plan while total modifications look modest.
• •Adding an index as the first reflex; if the slowdown is a visibility-map or hint-bit issue caused by recent bulk changes, the index will not change anything until VACUUM rewrites visibility.

Safe fixes

• •If EXPLAIN row estimates diverge sharply and the plan shape changed, run ANALYZE on the referenced tables (VACUUM ANALYZE only if you have evidence dead tuples are accumulating) and re-capture EXPLAIN; treat ANALYZE as evidence-gathering, not magic.
• •If buffer cache hit ratio fell and the working set grew, first reduce the query's footprint by tightening its filter or its fetch size; raising shared_buffers should be a deliberate, sized change, not a reflex.
• •If pg_stat_activity shows a long-lived transaction holding AccessShareLock on a hot table, terminate only the blocking session whose xact_start predates the slow query's first wait, and only when the application's transaction model is understood.
• •If work_mem spills (temp blocks in EXPLAIN, temp_files in pg_stat_database) appear only in the slow window, raise work_mem at the session or database level just for that workload and re-measure; do not raise it cluster-wide based on a single query.
• •If a planner path is provably better but the optimizer will not pick it without a hint, set enable_* locally for the query as a stopgap; record it as a hint to be made permanent via a comment, a prepared statement plan, or a structural change, not a global setting.
• •If statistics churn is the recurring cause, schedule per-table ANALYZE intensity (alter table ... set (autovacuum_analyze_scale_factor)) for the hot relations rather than relying on the global default.

Prove the fix

1. 01The query's p50 and p95 over a representative window return to within an order of magnitude of the pre-incident baseline, not merely to "improved", and the gap is reproducible on a cold cache as well as a warm one.
2. 02EXPLAIN (ANALYZE, BUFFERS) post-fix shows estimated rows within roughly 10x of actual rows at every node and shared_blks_read counts consistent with the cache state you measured before the incident.
3. 03pg_stat_user_tables for the touched relations shows n_mod_since_analyze staying below the configured autovacuum_analyze_scale_factor threshold and last_analyze timestamps within the cadence you intend.
4. 04pg_stat_activity during the same workload shows no wait_event tied to locks or buffer pinning for the affected query during a re-run of the original latency scenario.
5. 05A second-order regression check: companion queries on the same relations do not regress after any work_mem, shared_buffers, or ANALYZE change, proving the fix is local to the slow query's failure boundary.

Prevention and next steps

• •Treat autovacuum and autoanalyze cadence as a per-relation setting, not a global default; tune autovacuum_analyze_scale_factor and autovacuum_vacuum_scale_factor for the hot tables the fast-then-slow query touches.
• •Capture and archive EXPLAIN (ANALYZE, BUFFERS) baseline output for canonical queries at deploy time, so any later plan or buffer regression is a diff against a known good run rather than a guess.
• •Keep pg_stat_statements enabled and dashboard calls, mean_exec_time, and shared_blks_hit/read ratios for the slow query as first-class metrics, not as a curiosity, with alerts on p95 rather than on mean.
• •Bound the duration of every application transaction at the client and at idle_in_transaction_session_timeout so the lock boundary does not silently become the slowest path on the server.
• •When a workload changes cardinality, retune statistics targets (default_statistics_target) for the columns whose histograms the planner relies on, rather than waiting for an incident to expose the drift.

Safe commands and checks

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT <select_list> FROM <relation> WHERE <filters>;
SELECT schemaname, relname, seq_scan, idx_scan, n_live_tup, n_mod_since_analyze, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname IN (<relation_list>);
SELECT pid, state, wait_event_type, wait_event, xact_start, query_start, left(query, 120) FROM pg_stat_activity WHERE state <> 'idle' AND datname = current_database();
SELECT queryid, calls, mean_exec_time, rows, shared_blks_hit, shared_blks_read FROM pg_stat_statements WHERE queryid = <queryid>;
SELECT blks_hit, blks_read FROM pg_stat_database WHERE datname = current_database();
SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size, pg_size_pretty(setting::bigint * 8192) AS shared_buffers FROM pg_settings WHERE name = 'shared_buffers';
SELECT granted, mode, relation::regclass, pid FROM pg_locks WHERE relation IS NOT NULL AND relation::regclass::text IN (<relation_list_oid_or_name>);
ANALYZE VERBOSE <relation>;