Reading Query Execution Plans

Query Performance Fundamentals introduced the idea that a query's plan, not its SQL text, is what determines its speed. This article covers actually reading one.

A plan is a tree, executed bottom-up#

Aggregate  (SUM(amount) GROUP BY customer_id)  └── Hash Join  (orders.customer_id = customers.id)        ├── Seq Scan on orders      (rows=1,000,000)        └── Index Scan on customers (rows=50, using customers_pkey)

The leaves — the scans — run first, producing rows. Each parent node consumes its children's output and produces its own. The Aggregate at the top is the last thing that runs, even though it appears first when a plan is printed. Reading a plan top-down, as if it were a script executing in the order shown, is the single most common way to misread one — the same inside-out reading discipline Data Transformations with Pandas recommends for a chained Pandas expression applies here for the same reason: identify what runs first, then work outward to what consumes it.

The most useful number in the plan: estimated vs. actual rows#

EXPLAIN alone shows what the planner expected. EXPLAIN ANALYZE (or its equivalent) actually runs the query and shows what really happened at each node:

Seq Scan on orders  (cost=0.00..25000 rows=1,000,000)                     (actual rows=48,203)

A large gap between the estimated 1,000,000 and the actual 48,203 is the single strongest signal that something upstream of this node — usually stale statistics, sometimes a cardinality estimation limitation — misled the optimizer into choosing a worse plan than the data actually warranted. Cost-Based Optimization covers exactly how that estimate gets built and why it drifts.

The node types worth recognizing#

• Scan nodes (Seq Scan, Full Scan, Index Scan) — where rows enter the plan. A Seq Scan reads every row in the table; an Index Scan uses a B-tree index to find only the rows that might match, covered in depth in Index Theory.
• Join nodes (Hash Join, Nested Loop, Merge Join) — combine two inputs. Which algorithm the optimizer picked, and why, is the subject of Join Algorithms.
• Filter / Aggregate / Sort nodes — apply a WHERE, GROUP BY, or ORDER BY to whatever rows already reached that point in the tree.

Finding the node that actually dominates the total time#

A plan's total runtime is rarely spread evenly across every node — usually one or two nodes account for the overwhelming majority of it. EXPLAIN ANALYZE reports a time for each node; the practical skill is scanning for the single largest one rather than reading every line with equal attention:

Hash Join   (actual time=0.5..1204.3, rows=48,203)   <- dominates  Seq Scan on orders     (actual time=0.1..45.2)  Seq Scan on customers  (actual time=0.1..2.1)

Here, the join itself — not either input scan — is where the time actually goes; fixing this query means addressing the join, not the scans feeding it.

Common mistakes#

Reading the plan top-down as if it were a sequence of steps#

The node printed first is usually the last to execute. Trace from the leaves inward to understand execution order correctly.

Looking only at EXPLAIN, never EXPLAIN ANALYZE#

Plain EXPLAIN shows the optimizer's estimate, not reality. Without the ANALYZE variant's actual row counts and timings, a large estimate-vs-actual gap — the clearest sign of a stale-statistics problem — is invisible.

Treating every node as equally worth investigating#

A query with fifteen plan nodes usually has its entire runtime concentrated in one or two of them. Spending equal attention on all fifteen wastes time that would be better spent confirming which node actually dominates first.

Further reading#

• Using EXPLAIN — PostgreSQL documentation — the canonical reference for reading a real plan, including ANALYZE and its output formats.
• EXPLAIN — ClickHouse documentation — the equivalent for the columnar database used elsewhere on this site.
• Query plan — the general concept this article's tree diagrams represent.

See also#