Seeing How A Query Runs
EXPLAIN asks the planner to show its chosen execution plan as a tree of operators without running the query, while EXPLAIN ANALYZE actually runs it and reports real timings and row counts.
Reading The Tree
- Plans are trees where each node feeds its parent, and you read from the innermost or bottom nodes upward.
- A sequential scan reads the whole table, while an index scan uses an index to find rows.
- Join nodes such as nested loop, hash join, and merge join show how two inputs are combined.
- Sort and aggregate nodes show extra work added after the data is gathered.
What To Look For
- Compare the estimated rows against the actual rows from ANALYZE. A large gap means stale statistics and likely a bad plan.
- A sequential scan on a big table with a selective filter often signals a missing index.
- An unexpected sort or a nested loop over many rows can be a performance red flag.
Using It Well
Look at the most expensive node first, since fixing the dominant cost usually matters most. Then ask whether an index, a rewrite, or fresher statistics would change the plan.
Key idea
A query plan is a tree of operators read from the leaves up, and the keys to reading it are spotting full scans, expensive joins, and large gaps between estimated and actual rows.