Reading a PostgreSQL query plan with EXPLAIN ANALYZE

Goal

Find out why a query is slow by reading what the database actually did, rather than guessing which index to add.

Prerequisites

A slow query with realistic parameters, run against data of realistic size and distribution; fresh statistics (ANALYZE).

Steps

1. Run EXPLAIN (ANALYZE, BUFFERS) <query>; for a query with side effects, wrap it in a transaction and roll back.
2. Read the tree from the innermost nodes outward; each node shows estimated cost and rows and actual time and rows, with loops.
3. Compare estimated with actual row counts. A large mismatch (10× or more) means the planner chose on wrong assumptions: outdated statistics, correlated columns, or functions it cannot estimate.
4. Find the node with the largest actual time that is not just the sum of its children; that is where the work happens.
5. Look at scan types: a sequential scan over a large table filtered to few rows suggests a missing or unusable index; an index scan with high loop counts inside a nested loop suggests a join-order or estimate problem.
6. Check buffers: high shared read indicates data coming from disk; temp blocks indicate sorts or hashes spilling to disk (work_mem).
7. Change one thing (index, rewritten predicate, statistics target), re-run, compare.

Expected result

A concrete cause for the slowness and a verified change, instead of an index added on a hunch.

Limits and test basis

Plans differ between environments with different data sizes; a plan from a small development database proves little. ANALYZE in EXPLAIN executes the query, so avoid it on destructive statements outside a rolled-back transaction.