Back to roadmap
Module 6 · Relational Data at ScaleDay 05325 min

Query Plans and Execution

Read the plan; the plan is the truth.

← PreviousNext →
Day 053

Query Plans and Execution

Nested loop
note
Hash join
note
Merge join
note
Memory hook

Query Plans and Execution: read the plan

Mental model

shape data so reads and writes stay honest

Design lens

Stats too stale → bad plans.

Recall anchors
OperatorsJoinsStats

Why it matters

The query planner converts SQL into a tree of physical operators. Reading these trees lets you reason about why a query is slow, faster than guessing.

Deep dive

Joins: nested loop (small inputs), hash join (large unsorted), merge join (sorted inputs).

Slow patterns: seq scan on big table, sort spill, bad estimates, missing index.

ANALYZE updates stats; planner depends on them.

Demo / scenario

Slow report: filter + join + group.

  1. EXPLAIN ANALYZE shows seq scan on big table.
  2. Add (filter) index — plan switches to index scan.
  3. Wrong row estimate → bad join order; ANALYZE table.
  4. Now hash join with smaller side first.

Tradeoffs

  • Stats too stale → bad plans.
  • Stats too aggressive → write overhead.
  • Sometimes you must hint or rewrite the query.

Diagram

Nested loop
Hash join
Merge join
Join algorithms.

Mind map

Check yourself

Loading quiz…

Sources & further reading

← PreviousNext →