Scan types: Seq, Index, Bitmap, Index-Only

EXPLAIN shows Seq Scan on your 10M-row table even though an index exists on the filter column. The planner is not broken — it calculated that a sequential scan is cheaper for this selectivity. Understanding when and why the planner picks each scan type is what separates guessing from diagnosing.

Reading the cost line

Every plan node prints:

(cost=startup..total rows=N width=B)

• startup — cost paid before the first row can be emitted. Zero for Seq Scan; non-zero for Sort (must read everything first).
• total — cost to run the node to completion.
• rows — estimated rows emitted by this node.
• width — average row width in bytes.

Cost is in arbitrary units where 1.0 ≈ one sequential page fetch. Only ratios matter; the absolute numbers are meaningless without context. With ANALYZE, every node also gets (actual time=startup..total rows=N loops=L) — real wall time in milliseconds.

Interpretation rule: if the node is inside a Nested Loop’s inner side, divide actual time by loops to get per-execution cost.

The four scan types

Seq Scan

Cost formula: relpages × seq_page_cost + reltuples × cpu_tuple_cost. Default seq_page_cost = 1.0, cpu_tuple_cost = 0.01. For a 5,000-page table: 5000 × 1.0 + 1M × 0.01 = 15,000 units. Optimal when the predicate is unselective (most rows match) — reading the whole heap sequentially is faster than hopping randomly to each matching row.

Index Scan

Cost grows with random_page_cost × matching_rows. Default random_page_cost = 4.0 (HDD-era). For 100 matching rows: 100 × 4.0 = 400 — much cheaper than the 15,000 Seq Scan. For 100,000 matching rows: 100,000 × 4.0 = 400,000 — Seq Scan wins. The crossover is roughly when the predicate matches 5–15% of the table.

SSD tuning note: on NVMe SSDs, random reads are only 1.5–2× slower than sequential — not 4×. Set random_page_cost = 1.1 in postgresql.conf for SSD systems. The planner will then prefer Index Scan for a much larger fraction of queries.

Bitmap Heap Scan

A two-step process: first a Bitmap Index Scan builds an in-memory bitmap of all matching TIDs from the index; then Bitmap Heap Scan sorts the TIDs by physical page address and reads the heap in order. This converts scattered random reads into a sequential heap traversal. It wins when many rows match but they are scattered across the heap — the middle ground between Index Scan and Seq Scan.

The bitmap can be OR-ed across multiple indexes (BitmapOr node) — useful when an OR predicate has an index per column.

Index Only Scan

Reads only the index leaf pages — no heap fetch — when two conditions are both true: (1) all columns needed by the query are stored in the index (either as key columns or via INCLUDE), and (2) the Visibility Map confirms the page is “all-visible” (every tuple visible to all transactions). When the VM bit is clear, Postgres must fall back to a heap fetch for that page. In EXPLAIN ANALYZE output, Heap Fetches: N reveals how often this happened — high N means the VM is stale and VACUUM has not run recently.

BUFFERS: the I/O picture

EXPLAIN (ANALYZE, BUFFERS) adds buffer accounting per node:

Seq Scan on orders  (actual time=0.1..450 rows=1000000 loops=1)
  Buffers: shared hit=4800 read=200

• shared hit — page found in Postgres’s shared_buffers cache (no disk I/O)
• read — page fetched from OS or disk
• dirtied — page modified by this node
• written — page flushed to disk

High read with low hit means the working set is larger than shared_buffers, or the query is touching cold data. This diagnoses “is this slow because of CPU or I/O”: high reads = I/O-bound; low reads but high actual time = CPU-bound (often a poor join or heavy sort).