Diagnose a slow PostgreSQL query with EXPLAIN and EXPLAIN ANALYZE, reading buffer statistics and actual-vs-estimated row counts

Documented steps

1. Run EXPLAIN <query> to see the planner's chosen plan and cost estimates without executing; run EXPLAIN (ANALYZE, BUFFERS) <query> to execute it and collect actual timings, row counts, and buffer usage.
2. EXPLAIN ANALYZE actually executes the statement — wrap DML in a transaction to discard effects: BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;
3. Compare 'rows' (estimated) vs 'actual rows' per plan node: large discrepancies indicate stale planner statistics. Look for Seq Scan on large tables where an Index Scan was expected.
4. Read the BUFFERS output: high 'read' counts vs 'hit' counts indicate the data was not in shared_buffers and required I/O; 'dirtied' and 'written' show write side effects.
5. Use EXPLAIN (ANALYZE, VERBOSE) for per-node output columns, and TIMING FALSE to reduce per-row timing overhead when only row counts matter.
6. Plan node costs are in arbitrary planner units (conventionally seq-page fetches), not milliseconds — compare them relatively, not absolutely.
7. If estimates are far off, refresh statistics with ANALYZE <table>; then re-run EXPLAIN to see whether the plan changes.
8. Official docs: https://www.postgresql.org/docs/current/sql-explain.html and https://www.postgresql.org/docs/current/using-explain.html