← The Query Eye

Module 1: Reading the Plan

How to read an EXPLAIN plan — and why a Seq Scan is not a verdict.

How to read a plan

An EXPLAIN shows the planner's intended strategy as a tree of operators (scans, joins, sorts, aggregates). EXPLAIN ANALYZE actually runs the query and adds real numbers — so you can compare what the planner guessed with what happened.

Index Scan using orders_customer_id_idx on orders
  (cost=0.43..8.45 rows=12 width=64)
  (actual time=0.018..0.022 rows=11 loops=1)
  • operator — Index Scan, Seq Scan, Nested Loop, Hash Join, Sort, Aggregate.
  • cost=start..total — the planner's estimate in arbitrary units. Useful for comparing plans, not a time.
  • rows / width — estimated rows out, and average row size in bytes.
  • actual time / rows / loops — from ANALYZE only: real time, real rows, and how many times this node ran.
The most reliable danger sign is estimated `rows` vs `actual rows` off by orders of magnitude — the planner was lying to itself and probably picked the wrong plan. You can only see this with EXPLAIN ANALYZE. A plain EXPLAIN shows estimates only.

Read the tree bottom-up and inside-out: the deepest, most-indented nodes run first and feed their parents. loops=N on an inner node means it ran N times — multiply its per-loop cost by N to get the real work.

Ship, Flag, Escalate — and the Seq Scan myth

Same three calls as every Reviewer's Eye course, tuned for plans:

  • Ship — the plan is appropriate for the data. An index scan, a tiny-table seq scan, a nested loop over a small outer.
  • Flag — a definite performance bug: a selective query seq-scanning a huge table, a nested loop over two large unindexed inputs, deep OFFSET.
  • Escalate — the verdict depends on facts you can't see here: the real table size, whether stats are current (last_analyze), the live data distribution. Route it to someone who can run EXPLAIN ANALYZE on production.
The single biggest myth in plan review: "Seq Scan = bad." It is not. A sequential scan is the optimal plan when the table is small, or when the query returns a large fraction of the rows — an index would just add random I/O the planner correctly refuses. An index on a 40-row table will be ignored. Don't flag a seq scan on reflex.
Only flag a Seq Scan when all three hold: the table is large, the predicate is selective (returns a small fraction), and no usable index exists. Miss any one and the seq scan is probably correct.
AI assistants and naive linters love to "optimize" by adding an index to every seq scan and calling every full read "slow". Your job is to overrule them when the scan is already optimal — that false alarm is as costly as missing a real one.

Warm-up below: four plans, two of them safe — including a seq scan that's perfectly fine. See where your eye sits.

Drill — Reading the Plan

Judge each artifact. You are scored on calibration: catches vs false alarms.

CARD 1 / 4catches 0/0false alarms 0/0
Postgres · EXPLAIN ANALYZEQuery Plan Review
EXPLAIN ANALYZE » "Look up a country by its ISO code. Is this Seq Scan a problem?"
SELECT name FROM countries WHERE iso_code = 'JP'; Seq Scan on countries  (cost=0.00..4.30 rows=1 width=20)  (actual time=0.012..0.020 rows=1 loops=1)  Filter: (iso_code = 'JP'::text)Planning Time: 0.08 msExecution Time: 0.03 ms

Capstone: the Capstone: Calibration Exam (16 cards) from The Query Eye unlocks once modules are complete.