low riskinformational~45 min

Runbook: Investigate a Slow Query

1 · Prerequisites

Confirm every item is in place before any state change.

The complaint, stated precisely: which operation is slow, since when, and how slow compared with what
A database connection with permission to run EXPLAIN on the query and to read pg_stat_statements if it is installed
The query text with representative parameter values, because a plan for one set of values may not be the plan the application gets
A non-production copy with comparable data volume, if any change will be tested before it is applied
Knowledge of whether anything changed recently: a deploy, a data load, a version upgrade, or a configuration change
Agreement that no index will be created on production during this investigation without a separate change

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

· Confirm the problem is a query and not a wait. SELECT wait_event_type, wait_event, count(*) FROM pg_stat_activity WHERE state = 'active' GROUP BY 1,2 ORDER BY 3 DESC; Waits concentrated in Lock mean contention, and waits in IPC/SyncRep mean replication. Neither is a query problem and neither is fixed by a plan.
· Confirm the cluster is not simply overloaded. A cluster past the knee of its throughput curve makes every query slow without any of them being wrong. Check connection count against the measured knee before reading a single plan.
· Find the queries that actually cost the most. SELECT calls, round(total_exec_time::numeric,1) AS total_ms, round(mean_exec_time::numeric,2) AS mean_ms, rows, left(query,80) FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20; A 40 ms query called two million times costs more than a four-second report run once.
· **Note stats_reset.** SELECT stats_reset FROM pg_stat_statements_info; Statistics accumulated since the last restart describe a different period from the one being complained about.
· Check when statistics were last gathered on the tables involved. SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze FROM pg_stat_user_tables WHERE relname IN (...); A large n_mod_since_analyze means the planner is working from a stale picture.
· Establish whether the plan changed or the data did. A query that was fast last week and is slow now has either a new plan or more rows. These have different fixes and the difference is usually visible in the row counts.

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1Get the plan with actual numbers, not estimates. EXPLAIN (ANALYZE, BUFFERS) <query>; On PostgreSQL 18 BUFFERS is included by default with ANALYZE, so the buffer counts appear without asking. Add SETTINGS to record any non-default planner settings in effect.
2**Beware of running EXPLAIN ANALYZE on a writing statement.** It executes the statement. Wrap it in BEGIN; ... ROLLBACK; for anything that modifies data.
3Compare estimated rows against actual rows, node by node. This is the single most informative comparison in a plan. rows=1 ... actual rows=48231 means the planner chose its whole strategy on a number that was wrong by four orders of magnitude, and every join above it is a consequence.
4Read the row estimates as fractional numbers. PostgreSQL 18 reports actual rows with two decimal places on looped nodes, which distinguishes "one row per loop" from "0.03 rows per loop" — a difference that matters when there are a million loops.
5Find the node that costs the time, not the node that looks unusual. Read actual time on each node; the outermost figure is cumulative. A sequential scan on a small table is not a problem, and an index scan can be the slowest node in a plan.
6Check the buffer counts. shared hit is cache; shared read is disk. A node reading far more buffers than the rows it returns is scanning to discard, which is what an index would prevent.
7Look for the specific signatures. Sort Method: external merge Disk: NNNkB means a spill — which is not automatically bad. Batches: 4 on a hash node means the hash spilled. Rows Removed by Filter far exceeding rows returned means the scan is reading rows it does not want.
8If estimates are wrong, fix the statistics before considering an index. ANALYZE <table>; and re-read the plan. A wrong estimate corrected by fresh statistics frequently produces a different plan at no cost.
9**If estimates are still wrong after ANALYZE, consider the sample size.** ALTER TABLE t ALTER COLUMN c SET STATISTICS 500; ANALYZE t; raises the sample for a skewed column. For correlated columns, CREATE STATISTICS on the pair teaches the planner about a dependency it cannot otherwise see.
10Test an index on a copy, not on production. Build it on a non-production cluster with comparable data and measure the plan and the timing. An index changes write cost on every insert, update and delete, and that cost is not visible in the query you are optimising.
11Change one thing at a time, and measure on both sides. Statistics, then index, then query text. Two changes at once produce a result nobody can attribute.
12Record the before and after: the plan, the timing, the buffer counts, and what changed. A query that was investigated and left alone is as worth recording as one that was fixed.

4 · Verification

Confirm the procedure actually fixed the problem.

✓The query's mean_exec_time in pg_stat_statements has fallen, measured over a comparable period rather than on a single run.
✓The plan is the one you intended. A correct index that the planner ignores is a different problem from a missing index, and only the plan distinguishes them.
✓Estimated rows and actual rows are within an order of magnitude on the nodes that matter.
✓Buffer counts have fallen in proportion to the improvement. A query that got faster without reading fewer buffers probably benefited from cache warmth rather than from the change.
✓If an index was added, write latency on the affected table has been measured after the change and recorded. An index makes reads faster and writes slower, always.
✓The application's own timing for the affected operation has improved, not just the database's. They are different measurements and only one of them is the complaint.
✓No other query regressed. Check the top entries in pg_stat_statements before and after; a new index changes plans for queries nobody was looking at.

5 · Rollback

If verification fails, undo the procedure in reverse order.

↶Reading plans changes nothing, so rollback covers only what was applied.
↶To remove an index without blocking: DROP INDEX CONCURRENTLY <name>;. A plain DROP INDEX takes ACCESS EXCLUSIVE on the table and forms a lock queue behind it.
↶To revert a statistics target: ALTER TABLE t ALTER COLUMN c SET STATISTICS -1; restores the cluster default, then ANALYZE t;.
↶To remove extended statistics: DROP STATISTICS <name>;
↶To revert a planner setting applied per role: ALTER ROLE <role> RESET <param>;. If a planner setting was changed cluster-wide to fix one query, revert it — that is almost always the wrong scope for a query-specific problem.
↶If an index was created concurrently and the build was cancelled, check for what it left behind: SELECT c.relname, i.indisvalid, i.indisready FROM pg_class c JOIN pg_index i ON i.indexrelid = c.oid WHERE NOT i.indisvalid;. An index with indisvalid = false and indisready = true is maintained on every write and used by nothing.

6 · Escalation

When the runbook isn't enough, contact:

· The query is slow because the cluster is saturated: escalate to whoever owns capacity. Tuning individual queries on an overloaded cluster produces small wins and a long investigation.
· The plan is correct and the query is inherently expensive for the data volume: escalate to the application owner. The fix is a different query, a materialised view, or a different expectation, and none of those are database configuration.
· The query needs an index that would materially slow writes on a hot table: escalate with both measurements. That trade belongs to whoever owns the write path.
· Statistics are correct, the plan is reasonable, and the query is still slow: escalate to the storage owner with the buffer counts. A plan reading a sensible number of buffers slowly is an I/O problem.
· The plan changed overnight with no deploy: escalate only after checking last_autoanalyze and the row counts. Most such changes are a statistics refresh crossing a cost boundary, and that is a tuning problem rather than a mystery.
· The query is generated by an ORM and cannot be changed: escalate to the application owner rather than working around it in the database. An index added to compensate for a query nobody can change is a permanent cost with no owner.

Three questions, in order, and most investigations end at the first two:

Is this a query problem at all, or a wait?
Which query actually costs the most?
Where in the plan does the time go?

Question one: is it a wait?

SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity WHERE state = 'active'
GROUP BY 1,2 ORDER BY 3 DESC;

Lock dominating means contention. IPC/SyncRep means synchronous replication. IO means storage. None of these is fixed by a better plan, and all of them make every query look slow.

Question two: cost, not duration

A 40 ms query called two million times beats a four-second report

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

Order by total_exec_time, not mean_exec_time. The query somebody complained about is often not the one consuming the cluster, and the one consuming the cluster is often nobody’s complaint.

Check stats_reset in pg_stat_statements_info first — statistics from a different period describe a different system.

Question three: read the plan against reality

EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT ...;

On PostgreSQL 18 BUFFERS is on by default with ANALYZE. SETTINGS records any non-default planner settings in effect, which is how you discover that somebody set enable_seqscan = off in a session six months ago.

The comparison that matters most

What you see	What it means
`rows=1 ... actual rows=48231`	The planner chose everything above this node on a wrong number
`actual rows=0.03 loops=1000000`	Fractional rows per loop — 30,000 rows total, not 30,000 loops of one
`Rows Removed by Filter: 4821992`	Reading rows to throw away; an index would not
`shared read` ≫ `shared hit`	Coming from disk, not cache
`Sort Method: external merge Disk: 184864kB`	Spilled — not automatically bad
`Batches: 4` on a hash node	The hash spilled; more memory may genuinely help here

Spilling is not automatically a defect

Measured on PostgreSQL 18.6, the same two-million-row sort at four work_mem settings:

work_mem = 4MB:    external merge, Disk: 184864kB   1295 ms
work_mem = 256MB:  quicksort, Memory: 252269kB      1689 ms

The on-disk sort was faster. And a hash aggregate over two million groups took 674 ms at 4 MB and 1473 ms at 1 GB, because the larger allowance made a worse plan look cheap.

More memory does not make the same plan faster. It makes different plans look affordable, and some of them are not.

Fix statistics before reaching for an index

ANALYZE orders;

A wrong estimate corrected by fresh statistics frequently produces a different plan at no cost at all. If it is still wrong afterwards:

-- a skewed column
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

-- correlated columns the planner treats as independent
CREATE STATISTICS orders_region_country (dependencies)
  ON region, country FROM orders;
ANALYZE orders;

Blast radius

Action	Reversible?	What it costs if wrong
`EXPLAIN` without `ANALYZE`	Yes	Nothing — it does not execute
`EXPLAIN ANALYZE` on a `SELECT`	Yes	The query runs once
`EXPLAIN ANALYZE` on a write	Only inside a transaction	The write happens
`ANALYZE`	Yes	Brief I/O; plans may change immediately
`CREATE INDEX` on production	Yes, `DROP INDEX CONCURRENTLY`	Write latency on every insert, update and delete, permanently
A cancelled `CREATE INDEX CONCURRENTLY`	Leaves wreckage	An invalid index: unused by the planner, maintained on every write

Check for invalid indexes after any cancelled concurrent build

SELECT c.relname, i.indisvalid, i.indisready,
       pg_size_pretty(pg_relation_size(c.oid))
FROM pg_class c JOIN pg_index i ON i.indexrelid = c.oid
WHERE NOT i.indisvalid;

indisvalid = f with indisready = t is the expensive case: real pages on disk, maintained by every write, used by nothing. REINDEX INDEX CONCURRENTLY finishes it; DROP INDEX CONCURRENTLY removes it.

One change at a time

Statistics, then index, then query text — with a measurement on each side. Two changes at once produce an improvement nobody can attribute, and a regression nobody can find.