Plan versus reality
EXPLAIN shows the plan the planner chose and its cost estimates. EXPLAIN ANALYZE actually runs the query and reports the real timing and real row counts, which is what you need to diagnose slowness.
Reading the tree
The output is a tree of nodes you read from the inside out. Each node shows:
- An estimated cost and row count from the planner.
- The actual time and actual rows once executed.
- A loops count, since timing per node is per loop and must be multiplied.
What to look for
- A large gap between estimated rows and actual rows means stale statistics are misleading the planner. Run ANALYZE.
- A sequential scan on a big table where you expected an index scan suggests a missing or unusable index.
- A nested loop with huge loop counts can explode when the inner side is large.
- Rows removed by filter reveals work done reading rows that were then discarded.
Add the BUFFERS option to see how many pages came from cache versus disk, which often explains surprising timing.
Key idea
EXPLAIN ANALYZE runs the query and shows actual time, rows, and loops per node, so gaps between estimate and reality, unexpected sequential scans, and exploding nested loops point you straight at the fix.