Lesson 16 · Query performance

Scans & joins

The building blocks a plan is made of — and when the planner picks each.

Your win: name the scan types and join strategies you'll see in EXPLAIN, and explain what makes the planner choose one over another.

Scan strategies — how rows are read

Scan	What it does	Chosen when
Seq Scan	read every page of the heap	small table, or the query wants most rows
Index Scan	walk the index, heap-fetch each match	few, selective rows
Index-Only Scan	answer from the index, no heap fetch	covering index (Lesson 12) + all-visible pages
Bitmap Index+Heap Scan	collect matching page ids into a bitmap, read pages in physical order	medium selectivity; can combine several indexes

The bitmap scan is the clever middle ground: too many rows for a per-row Index Scan, too few for a full Seq Scan — so it gathers the page list first, then reads sequentially.¹

Join strategies — how two inputs combine

Join	How	Chosen when
Nested Loop	for each outer row, look up matches in the inner	one side small, or the inner side indexed (cheap to start)
Hash Join	build a hash table of one side, probe with the other	both sides large, equality join
Merge Join	walk two sorted inputs in lockstep	both inputs already sorted (e.g. by index)

It's all driven by estimated rows The planner picks scans and joins from its row estimates (Lesson 14). A Nested Loop is great over a few rows but terrible over millions; a Hash Join is the opposite. That's why a bad row estimate (stale stats) produces a bad join choice — and why the estimate-vs-actual gap in EXPLAIN (Lesson 15) is the first thing to check.

Anchor A selective query — WHERE resource_path = $1 AND status = $2 — becomes an Index Scan (or Bitmap) on the resource_path index. A join emails ⋈ email_recipients on the indexed email_id is a Nested Loop when few emails match, a Hash Join over a large batch. You'll see exactly which in the plan when you /query-db → EXPLAIN ANALYZE a real query.

PostgreSQL — Using EXPLAIN + pgMustard glossary

The docs show each node type in real plans; pgMustard's glossary explains when the planner prefers each scan and join.

→ postgresql.org/docs — Using EXPLAIN
→ pgMustard — EXPLAIN glossary

Check yourself (from memory)

Q1. A Bitmap scan is chosen for…

Too many rows for a per-row Index Scan, too few for a Seq Scan — it gathers page ids into a bitmap, then reads in physical order.

Q2. A Hash Join is typically used when…

Build a hash of one side, probe with the other — good for large equality joins. Sorted inputs favour Merge; a small side favours Nested Loop.

Q3. A Nested Loop is cheap when…

Few outer rows and an indexed inner lookup = cheap. Over millions of loops it becomes very slow.

Name the scan types and join strategies, and when the planner picks each.

recall, then click to reveal

SCANS — Seq Scan (whole heap; small tables or most-of-table); Index Scan (walk index + heap-fetch; few selective rows); Index-Only Scan (covering index, no heap fetch); Bitmap Index+Heap Scan (medium selectivity — bitmap of matching pages, read in physical order; can combine indexes). JOINS — Nested Loop (one side small or inner indexed; cheap start); Hash Join (both large, equality — hash one, probe with the other); Merge Join (both inputs sorted). The planner chooses from its estimated row counts (Lesson 14) — so a bad estimate makes a bad choice.

Want to see why a Nested Loop "blew up" in a real plan (high loops × per-loop cost)? Ask me with the EXPLAIN output.

1. PostgreSQL — Using EXPLAIN; pgMustard — EXPLAIN glossary.