Troubleshooting slow queries

Slow queries have a small number of causes, and EXPLAIN ANALYZE shows which one you're dealing with. Work through the steps in order, and change one thing at a time.

The checklist

1. Reproduce and measure. Get the exact query and parameters. Run EXPLAIN (ANALYZE, BUFFERS) a few times; note the plan and a typical time. (In this course: compare on the same device only.)
2. Find the expensive node. Look for the node whose actual time (× loops) accounts for most of the total, or the node that reads the most rows or buffers. Everything else is noise.
3. Compare estimated and actual rows, bottom-up. A large gap points to statistics: stale (run ANALYZE), skewed values, correlated columns, or expressions without statistics.
4. Check the scans. A Seq Scan with a large Rows Removed by Filter and a small output is an index candidate. Is there an index? Is the condition sargable? Is the value selective?
5. Check the joins. A Nested Loop with a high loops count over a Seq Scan means a missing index on the inner join column, or a bad estimate. A Hash with Batches > 1 means too little memory.
6. Check sorts and limits. A Sort feeding a Limit can often be replaced by an index in the right order. Sort Method: external merge means it spilled to disk.
7. Check row counts against the business question. If a join produces many more rows than the report has facts, rows are being multiplied, and the result is probably wrong as well as slow.
8. Fix one thing, re-measure, and verify the result is unchanged. Keep the before/after plans as evidence.