Troubleshooting
EXPLAIN Plan Review Checklist
A practical checklist for reviewing PostgreSQL EXPLAIN ANALYZE BUFFERS output: row estimates, loops, filters, buffers, temp files, joins, and sort methods.
Always start with real execution when safe
Plain EXPLAIN is a prediction. EXPLAIN (ANALYZE, BUFFERS) shows what the query actually did. Use real parameters and a representative environment whenever possible.
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.created_at, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.tenant_id = $1
AND o.created_at >= now() - interval '7 days'
ORDER BY o.created_at DESC
LIMIT 100;Checklist
- Find the slow branch: read from the bottom up and follow actual time plus loops.
- Compare estimated rows with actual rows: large gaps usually explain bad join order or index choice.
- Watch loops: a cheap node can become expensive when it runs hundreds of thousands of times.
- Look for rows removed by filter: this can mean the index found too broad a candidate set.
- Read buffers: high shared reads suggest disk or cache pressure; high shared hits can still mean CPU-heavy page walking.
- Check temp activity: external sorts and multi-batch hashes point to spill risk.
- Check sort method: an external merge sort in a request path is usually worth investigating.
What row estimate errors usually mean
| Symptom | Common cause | Next check |
|---|---|---|
| Estimate far below actual | Stale stats or correlated predicates | Run ANALYZE, inspect extended statistics |
| Estimate far above actual | Filter more selective than planner knows | Check stats target and predicate shape |
| Nested loop runs too many times | Outer side underestimated | Find first upstream estimate error |
| Sort spills to disk | Large input or low memory | Reduce input before raising work_mem |
Plan review habit
Do not jump straight from "Seq Scan" to "add index." A sequential scan on a small or broad result can be correct. The job is to understand whether PostgreSQL read more data than the query should require, and why.