Scan Nodes

Every plan begins by getting rows out of a table, and Postgres has five ways to do it. Which one appears is not a rule about indexes; it is a price comparison, and the only variable that really moves it is how many rows the planner expects the scan to produce. At one row an index scan is unbeatable. At ten million it is the worst option available, and the same index is the reason why.

Recognizing these five nodes on sight is what makes the rest of a plan readable, because every join, sort and aggregate above them is priced from what they hand up. It is also the fastest way to read a plan backwards: the scan node tells you what the planner believed about selectivity, which is usually the thing that turned out to be wrong.

Seq Scan

A sequential scan reads every page of the relation in physical order and checks each row against whatever filter it carries. Nothing is skipped, including pages that hold no live rows at all. That sounds like the worst plan in the catalogue and it is frequently the best one, because sequential reads are the cheapest I/O a storage device offers and there is no per-row page lookup to pay. On inventory, 12,000 rows in 77 pages once Chapter 7's bloat is gone, it is the correct plan for essentially every query, and an index on that table would be read overhead with no payoff.

EXPLAIN SELECT * FROM orders WHERE customer_id = 88213;

 Index Scan using orders_customer_history_idx on orders
   (cost=0.56..87.42 rows=21 width=72)
   Index Cond: (customer_id = 88213)

EXPLAIN SELECT * FROM orders WHERE status = 'delivered';

 Seq Scan on orders  (cost=0.00..955000.00 rows=33512400 width=72)
   Filter: (status = 'delivered'::text)

Both queries filter orders and both columns are indexed. A sequential scan on a large table is an answer to a question about selectivity rather than a diagnosis, and the question is usually the interesting part. The first expects 21 rows out of 40 million and descends the index. The second expects 33.5 million, which is 84% of the table, and the planner declines the index without hesitation: 455,000 pages read sequentially plus 40 million rows examined comes to a cost of 955,000, while fetching 33.5 million rows one at a time through an index would cost several times that.

Index Scan

An index scan descends the tree to the first matching key, then walks forward along the leaf level, and for each entry it finds it goes to the heap and fetches that row. Two properties follow. The heap visits are in index order rather than physical order, so on a poorly correlated column they scatter across the table and every one of them can be a separate page read — which is exactly what random_page_cost is pricing. And the rows come out sorted by the index key, for free, so an ORDER BY that matches the index needs no sort node at all.

The cost therefore grows almost linearly with the number of rows returned, while a sequential scan's cost is fixed by the size of the table. Those two lines cross, and past roughly five to ten percent of a table the sequential scan wins. That crossover is where a wrong row estimate does the most damage, because it is the point at which the plan flips. It is not a constant either: it moves with random_page_cost, with the column's physical correlation, and with how much of the table the planner believes is cached.

Index Only Scan

When the index contains every column the query needs, Postgres can skip the heap — for the pages the visibility map marks all-visible, and only those. That bit belongs to Chapter 5's storage layer and to Chapter 7's vacuum, and nothing about the index or the query moves it. A page without it sends the scan to the heap regardless, and the node is still called Index Only Scan. The line that tells you the truth is Heap Fetches.

EXPLAIN (ANALYZE)
SELECT count(*) FROM orders
 WHERE customer_id = 88213 AND placed_at >= '2026-08-01';

 Index Only Scan using orders_customer_history_idx on orders
   (cost=0.56..24.18 rows=6 width=0)
   (actual time=0.033..0.412 rows=6.00 loops=1)
   Index Cond: ((customer_id = 88213) AND (placed_at >= '2026-08-01'))
   Heap Fetches: 6
   Index Searches: 1
   Buffers: shared hit=9 read=6

Read the two numbers against each other. Six heap fetches against six rows returned means the map helped with nothing, and read=6 on the buffer line is the confirmation: six heap pages, fetched one at a time. Neither the index nor the query is at fault here, so the reflex to add another column to the index changes nothing. Those pages have been written since the last vacuum, which puts the fix in Chapter 7.

Bitmap Index Scan and Bitmap Heap Scan

The bitmap pair is the middle ground, and it always appears as two nodes. The lower one, Bitmap Index Scan, walks the index and records the addresses of matching rows into a bitmap in memory without fetching anything. The upper one, Bitmap Heap Scan, then reads the heap in physical page order, visiting each page exactly once no matter how many matching rows it holds. Index ordering is lost, so an ORDER BY needs a real sort — that is the price. In exchange, a thousand matching rows spread over four hundred pages cost four hundred page reads instead of a thousand. This is also the only form that combines two indexes for one query, through a BitmapAnd or BitmapOr node sitting between them.

EXPLAIN (ANALYZE)
SELECT count(*) FROM delivery_events
 WHERE occurred_at >= now() - interval '3 days';

 Bitmap Heap Scan on delivery_events
   (actual time=812.004..9241.663 rows=11983402.00 loops=1)
   Recheck Cond: (occurred_at >= (now() - '3 days'::interval))
   Rows Removed by Index Recheck: 4271866
   Heap Blocks: exact=41208 lossy=118440
   Buffers: shared hit=63219 read=96429

The bitmap for twelve million rows did not fit in work_mem, so Postgres degraded part of it: instead of recording individual row addresses it started recording whole pages, which is what lossy=118440 counts. A lossy page has to have its original condition re-applied to every row on it, and Rows Removed by Index Recheck: 4271866 is the bill: four million rows read and re-tested that a precise bitmap would never have touched. Chapter 10 sets that memory properly. Two numbers on adjacent lines, one cause, and the setting to change is work_mem rather than the index.

Parallel Scans

A Gather node above a scan means background workers are splitting the work, each processing a slice and streaming rows back to the leader. Gather Merge is the ordered variant: each worker produces sorted output and the leader merges, preserving the order. Below the gather you see Parallel Seq Scan, Parallel Index Scan or Parallel Bitmap Heap Scan, and the row counts on those nodes are per-worker estimates rather than totals, which trips people up on first reading.

Parallelism is capped and conditional. max_parallel_workers_per_gather defaults to 2, so the usual shape is a leader plus two workers. Launching them is priced at parallel_setup_cost of 1,000 and every row shipped back costs parallel_tuple_cost of 0.1, so small scans never qualify: the coordination costs more than the scan. Several query shapes are excluded outright: anything that writes or locks rows, anything that might be suspended mid-execution such as a cursor or a PL/pgSQL FOR … IN loop, anything calling a function marked PARALLEL UNSAFE, and anything already running inside a parallel query. And a plan that asked for workers may not get them, because max_worker_processes and max_parallel_workers both default to 8 across the whole cluster; Workers Planned: 2 with Workers Launched: 0 is a busy server, not a broken setting.

The Others You Will Meet

Half a dozen more scan nodes turn up regularly and each one answers the same question — where did these rows come from. Function Scan is a set-returning function in FROM, typically generate_series or unnest. Values Scan is a literal VALUES list, which is what a multi-row INSERT looks like from inside. CTE Scan means a common table expression was materialized into a temporary result and is being read back, which is worth noticing because it means the optimizer could not push a predicate into it. Subquery Scan is a subquery that could not be flattened into the outer query. Tid Scan is a direct lookup by ctid, the physical address from Chapter 5.

Another two appear once a table is partitioned. Append concatenates the results from several partitions, and Merge Append does the same while preserving sort order across them. The number of children under an Append is the number of partitions the planner did not manage to eliminate, which makes it the fastest way to check whether partition pruning is working. Chapter 11 makes that a habit.