Plan Versus Reality
Plain EXPLAIN shows the planner's chosen plan and its cost estimates. EXPLAIN ANALYZE actually executes the query and reports real timings and row counts, so you see what happened, not what was guessed.
The Numbers That Matter
- Estimated rows versus actual rows reveals how good the planner's statistics are. A large gap often means stale statistics or a bad estimate.
- Actual time appears as startup time and total time per node, with loops showing how many times the node ran.
- Total time per node times loops tells you where the wall clock went.
Reading the Tree
Plans are trees that execute from the leaves upward. Indentation marks child nodes. A scan feeds a join, which feeds a sort or aggregate at the top. Find the node with the largest contribution to total time and work there first.
Common Smells
- Actual rows far above the estimate, which can flip a good plan into a bad one.
- A sequential scan over a large table when a filter is selective.
- A node whose loops count is surprisingly high.
Key idea
EXPLAIN ANALYZE executes the query and shows real timings and row counts, so compare estimated to actual rows and target the costliest node.