Skip to main content
OpsRoadmaps

Runbooks / postgresql-slow-query

PostgreSQL slow query

A query that used to be fast is now slow — read the real plan with EXPLAIN (ANALYZE, BUFFERS), then fix the cause: stats, index, bloat, or lock queue.

12 canonical sections · 5 command blocks · last reviewed · updated 2026-09-21

Related focus areas:DataOpsObservability EngineeringSRE

Fix the underlying gap:Cloud Cost OptimizationIncident Management MaturityObservability MaturityPlatform Engineering AdoptionProduction-Ready KubernetesResilience and DR ImprovementSRE Adoption

On this page
  1. Symptoms
  2. Severity / impact
  3. Immediate safety actions
  4. Fast checks
  5. Diagnostic commands
  6. Likely causes
  7. Decision tree
  8. Remediation
  9. Verification
  10. Escalation
  11. Prevention
  12. Related metrics / alerts
  13. Sources

Symptoms

  • p99 latency for specific endpoints jumps; the query shows up at the top of pg_stat_statements by total time (or your APM names it).
  • Users see slowness or timeouts on the affected path only — the rest of the database feels fine (that framing separates this from instance-wide CPU incidents).
  • Sometimes self-inflicted: "the same query" got a new plan after a deploy, a data-size threshold, or a statistics refresh.

Severity / impact

  • Sev-2 when a user-facing path degrades but serves; Sev-1 when the slow query holds locks others queue behind (one slow query becomes a pile-up) or drags the instance (see the RDS high CPU runbook — same family).

Immediate safety actions

  • Capture the CURRENT plan (EXPLAIN ANALYZE) before anyone "tries an index" — the plan is the evidence and it changes after every fix attempt.
  • Do NOT blindly ANALYZE/REINDEX everything at once: shotgun maintenance destroys the evidence chain for what actually flipped.
  • If a deploy introduced it, roll back first — the query text in the new release (extra WHERE, changed JOIN) is part of the diagnosis.

Fast checks

  • Was it always slow on this data size, or did it flip? Compare plan timestamps / deploy history / stats-refresh times.
  • Rows actually returned vs rows scanned (EXPLAIN buffers): scanning a million rows to return ten is a plan problem, full stop.
  • Waiting on locks or doing work? pg_stat_activity.wait_event for the session answers in one glance.

Diagnostic commands

-- Top statements by total time since stats reset (needs pg_stat_statements):
SELECT calls, round(mean_exec_time::numeric, 1) AS mean_ms, round(max_exec_time::numeric, 0) AS max_ms, left(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;
-- The real plan with real costs — run with production-representative parameters:
EXPLAIN (ANALYZE, BUFFERS) SELECT /* the exact slow query with its real bind values */ ;
-- Bloat / stats signals for the tables in the plan (dead tuples = vacuum debt):
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
-- Is the session waiting on a lock instead of working?
SELECT pid, state, wait_event_type, wait_event, now() - query_start AS runtime, left(query, 80)
FROM pg_stat_activity
WHERE state = 'active' ORDER BY runtime DESC LIMIT 10;
# Refresh planner statistics for the table (safe, online; do this BEFORE deeper fixes):
psql "$DATABASE_URL" -c 'ANALYZE the_affected_table;'

Likely causes

1. Plan flip: stale statistics made the planner choose seq-scan/nested-loop over index scan — common right after big writes or a restore. 2. Missing index for a NEW query shape (new filter/sort introduced by a release) — the query was never fast; the feature is new. 3. Bloat: table/index bloat from vacuum debt inflates pages scanned; everything on that table slows gradually. 4. Data threshold: the plan that won at 10k rows loses at 10M (statistics can be fine; the crossover is real). 5. Lock queue: the query is fast but waits behind another transaction's lock — slowness is a symptom of someone else.

Decision tree

  • Plan does seq scan / wrong join where an index exists → stats or costing: ANALYZE the table; if stale-by-bloat, vacuum it (a).
  • No usable index for the new predicate/sort → create one CONCURRENTLY (b).
  • Plan fine, buffers huge, table bloated → VACUUM/(REINDEX CONCURRENTLY) in a low-traffic window (c).
  • Plan "fine" but row estimates wildly off → raise statistics targets on the skewed columns, re-ANALYZE (a+).
  • Session waiting on locks → it is the blocker's incident: find and resolve the blocker (e).

Remediation

  • (a) Statistics: ANALYZE the table now (seconds, safe); raise ALTER TABLE … ALTER COLUMN … SET STATISTICS to 500–1000 for skewed keys if estimates stay off; check autovacuum keeps up with write volume.
  • (b) Index: CREATE INDEX CONCURRENTLY matching the predicate + sort order (partial/covering variants where the access pattern justifies); verify the planner adopts it in EXPLAIN after creation.
  • (c) Bloat: VACUUM (or pg_repack for the heavyweight case) in a window; fix the autovacuum thresholds that let debt accumulate on hot tables.
  • (d) Crossover: hint-free options first — better statistics, covering index; pg_hint_plan/SQL-level overrides only with a written reason and a removal date (pinned plans rot).
  • (e) Locks: identify and safely end or let-finish the blocker; fix the transaction pattern (short transactions, consistent lock order) so the queue does not reform.
  • Stopgap when users are hurting: kill the offending query and let the app's retry absorb it — with the plan already captured.

Verification

  • EXPLAIN ANALYZE after the fix shows the intended plan; execution time at/below the pre-incident baseline.
  • pg_stat_statements mean_ms for the statement drops to baseline and stays there for 24h of real traffic.
  • No regressions elsewhere (check top-10 statements again — indexes and ANALYZE affect neighbors too).

Escalation

  • Service owners (they ship the query and the index); DBRE for vacuum/bloat surgery and any plan-pinning decision; escalate to the capacity path if the honest conclusion is "the data outgrew the design".

Prevention

  • pg_stat_statements always on; alert on per-statement mean_exec_time regressions (a query doubling is an early signal).
  • Regular ANALYZE hygiene via autovacuum tuned for hot tables; bloat monitoring with an owner for the worst offenders.
  • Migration review step: every new/changed query ships with the index it needs and an EXPLAIN at production scale.

Sources

Know which failure modes you actually have?

A free OpsRoadmaps assessment scores your controls with explainable findings and builds a prioritized roadmap — runbook coverage is one of the things it measures.

Assess my stack

Runbooks document self-reported practice. Commands and thresholds were last reviewed 2026-09-19 — verify against your own environment before acting.