All topics
6 / 66
queries & joins

EXPLAIN & Query Plans

Read what the optimizer actually does — scan types, join methods, row estimates — so you can target the real bottleneck.

EXPLAIN shows what the database actually decided to do with your query. Without it you are guessing — adding indexes hopefully and rewriting blindly.

A flight itinerary vs the actual flight. EXPLAIN is the plan; ANALYZE is the flight log.

Key Concepts

1
Read it inside out. The plan is a tree. The most indented operator runs first and feeds its rows to the one above:
2
    Hash Join  (cost=1240 rows=980)
      ->  Seq Scan on orders  (rows=2000000)
      ->  Hash
            ->  Index Scan on customers  (rows=1)
3
Read bottom up: find one customer by index, build a hash of it, then read all two million orders and match. The Seq Scan on two million rows is the cost here, and it is where to look first.
Seq Scan
4
The three things every plan tells you.How each table is reached. Index Scan or Index Seek jumps to matching rows. Seq Scan or Table Scan reads everything. A sequential scan on a large table where you expected a seek is the single most common finding.
Index ScanIndex SeekSeq ScanTable Scan
5
How tables are combined. Nested loops — for each row on one side, look up the other. Fast when the outer side is small, disastrous when it is not. Hash join — build a hash table from one side, probe with the other; the workhorse for large equality joins. Merge join — both sides already sorted, walk them together.
6
How many rows it expects. Every operator carries an estimate.
7
EXPLAIN ANALYZE is where the answer usually is. It runs the query and reports real times and real row counts beside the estimates:
EXPLAIN ANALYZE
8
    Seq Scan on orders  (rows=120) (actual rows=1840000)
9
The optimiser expected 120 rows and got 1.84 million. It chose nested loops because 120 lookups is cheap; 1.84 million is not. The plan is not "wrong" — it was built from a wrong number.
10
A large estimate-versus-actual gap almost always means one of two things: statistics are stale, so run the analyse command for that table; or the columns are correlated in a way the optimiser cannot see, such as city and postcode, where it multiplies two probabilities that are not independent.
11
The four signs worth hunting.
  - a sequential scan on a big table with a selective filter
  - a nested loop whose actual row count is large
  - a sort or hash that spilled to disk — the plan says so, and more working memory fixes it
  - estimated and actual rows differing by orders of magnitude
12
Then fix the one operator that dominates. Convert the scan into a seek with an index, or rewrite the predicate that defeated an existing one, or refresh the statistics, or reduce the rows entering an expensive join. Measure, fix the top cost, measure again.
13
What the interviewer is probing.1. "What do you look at first in a plan?" Probing: the method. Stalls: "The total cost." Moves up: the difference between estimated and actual rows — a large gap means bad statistics, and every decision above it is suspect.
14
2. "What does a sequential scan tell you?" Probing: interpretation. Stalls: "The query is slow." Moves up: not necessarily a problem — on a small table or a query touching most rows it is the right choice; it matters when you expected an index.
15
3. "Why is EXPLAIN ANALYZE different from EXPLAIN?" Probing: the distinction. Stalls: "More detail." Moves up: it actually runs the query and reports real timings and row counts, where EXPLAIN only shows the plan and estimates.
16
4. "What do you do when estimates are far off?" Probing: the fix. Stalls: "Rewrite the query." Moves up: update statistics first — stale or insufficient statistics are the usual cause, and no rewrite fixes a planner working from bad numbers.
Use cases:PlannerEXPLAINOptimization