Sequential, Index, Bitmap, Index-Only, TID, and Parallel Scan Behavior

Learning outcomes

A scan node is where the plan touches base-table data. ServiceHub sees sequential scans labeled “bad,” index scans labeled “good,” and a mysterious bitmap scan blamed for rechecks. Those labels are too shallow. Scan choice depends on how many rows/pages are expected, whether ordering matters, whether heap visibility can be proven from the visibility map, and whether the work can be divided across processes.

1. Build a scan-shape dataset

DROP TABLE IF EXISTS app.ch11_scan_lab;CREATE TABLE app.ch11_scan_lab (    id bigint PRIMARY KEY,    tenant_id integer NOT NULL,    status text NOT NULL,    priority smallint NOT NULL,    created_at timestamptz NOT NULL,    payload text NOT NULL);INSERT INTO app.ch11_scan_labSELECT g,       1 + (g % 100),       CASE g % 10 WHEN 0 THEN 'rare' WHEN 1 THEN 'queued' WHEN 2 THEN 'assigned' ELSE 'done' END,       1 + (g % 5),       timestamptz '2026-01-01 00:00+00' + g * interval '5 seconds',       repeat(md5(g::text), 5)FROM generate_series(1,350000) AS g;CREATE INDEX ch11_scan_tenant_idx ON app.ch11_scan_lab (tenant_id);CREATE INDEX ch11_scan_status_priority_idx ON app.ch11_scan_lab (status, priority);CREATE INDEX ch11_scan_cover_idx ON app.ch11_scan_lab (tenant_id, created_at DESC) INCLUDE (status, priority);ANALYZE app.ch11_scan_lab;

2. Sequential scan: throughput over selectivity

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)SELECT count(*)FROM app.ch11_scan_labWHERE status <> 'rare';

Because most rows satisfy the predicate, reading a large portion of the heap sequentially can be cheaper than following many index entries back to heap pages. The planner estimates this tradeoff from relation size, selectivity, caching assumptions, and cost parameters.

3. Plain index scan: a few tuples or useful order

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)SELECT id, created_at, statusFROM app.ch11_scan_labWHERE tenant_id = 42ORDER BY created_at DESCLIMIT 20;

The covering index matches tenant equality and timestamp order. The plan might use Index Only Scan if all selected columns are in the index and enough heap pages are all-visible; otherwise it may use an ordinary index scan. That distinction is about MVCC visibility, not simply column coverage.

4. Bitmap scans: collect tuple locations, then visit heap pages

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)SELECT id, tenant_id, status, priorityFROM app.ch11_scan_labWHERE status = 'queued'  AND priority IN (1,2,3);

A Bitmap Index Scan builds tuple-location information; Bitmap Heap Scan visits the needed heap blocks in physical order. Heap Blocks: exact=... means the bitmap retained exact tuple offsets for those pages. Under memory pressure it can become lossy at page granularity, in which case PostgreSQL must recheck candidate tuples from those pages.

BEGIN;SET LOCAL work_mem = '64kB';EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)SELECT id, payloadFROM app.ch11_scan_labWHERE status IN ('queued','assigned');ROLLBACK;

If the output includes Heap Blocks: lossy=... and Rows Removed by Index Recheck, you have observed bitmap compression causing page-level candidates. If it remains exact, the local bitmap fit in memory or the planner selected a different strategy. Do not fake a lossy result.

5. Index-only scans depend on the visibility map

Indexes do not carry enough MVCC information to decide visibility for arbitrary heap tuples. PostgreSQL can skip heap visits when the visibility map says a heap page is all-visible. This is why a query whose columns are all covered can still show nonzero Heap Fetches.

VACUUM (ANALYZE) app.ch11_scan_lab;EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)SELECT tenant_id, created_at, status, priorityFROM app.ch11_scan_labWHERE tenant_id = 42ORDER BY created_at DESCLIMIT 100;

Expect an index-only path to be plausible, with Heap Fetches showing how often the visibility map could not prove visibility. Immediately modify some matching rows and run the query again before vacuum to see why heap fetches can rise.

UPDATE app.ch11_scan_labSET priority = CASE priority WHEN 5 THEN 1 ELSE priority + 1 ENDWHERE tenant_id = 42;EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)SELECT tenant_id, created_at, status, priorityFROM app.ch11_scan_labWHERE tenant_id = 42ORDER BY created_at DESCLIMIT 100;VACUUM (ANALYZE) app.ch11_scan_lab;

6. TID/CTID scans are physical diagnostics, not identifiers

ctid identifies the current physical tuple location as (block, item). It can change after an update because PostgreSQL writes a new tuple version; table rewrites can change locations much more broadly. PostgreSQL can use a TID Scan when a query directly constrains ctid.

SELECT ctid, id, tenant_id, statusFROM app.ch11_scan_labWHERE id = 12345;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)SELECT ctid, id, tenant_id, statusFROM app.ch11_scan_labWHERE ctid = '(0,1)'::tid;

The literal (0,1) is only an example. Use the actual value from the first query. A TID lookup proves how PostgreSQL can directly address a physical tuple location; it does not make ctid a durable key.

7. Parallel scans split work, but not every plan is parallel-safe

A parallel plan has a leader and workers coordinated by Gather or Gather Merge. Parallel sequential scans divide heap blocks among cooperating processes. In a parallel bitmap heap scan the leader builds the bitmap while heap blocks are shared. Parallel B-tree index/index-only scans distribute index blocks among participants.

BEGIN;SET LOCAL max_parallel_workers_per_gather = 4;SET LOCAL min_parallel_table_scan_size = 0;SET LOCAL parallel_setup_cost = 0;SET LOCAL parallel_tuple_cost = 0;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)SELECT sum(length(payload))FROM app.ch11_scan_labWHERE priority >= 1;ROLLBACK;

These forced-low thresholds are a teaching experiment, not a production recommendation. If the plan still is not parallel, inspect function parallel safety, relation size, worker availability, transaction context, and planner estimates rather than assuming parallelism is broken.

BEGIN;SET LOCAL max_parallel_workers_per_gather = 4;SET LOCAL min_parallel_table_scan_size = 0;SET LOCAL parallel_setup_cost = 0;SET LOCAL parallel_tuple_cost = 0;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)SELECT tenant_id, created_at, idFROM app.ch11_scan_labWHERE tenant_id BETWEEN 1 AND 100ORDER BY created_at DESC, id DESCLIMIT 10000;ROLLBACK;

8. Production judgment and cleanup

When a scan surprises you, compare estimated versus actual cardinality first. Then check required columns, ordering, index predicates/operator classes, visibility-map state, buffer evidence, work_mem for bitmap pressure, parallel eligibility, and competing indexes. Do not globally disable sequential scans or force index scans because one plan looked unfamiliar.

DROP TABLE IF EXISTS app.ch11_scan_lab;

Authoritative references

Planner and executor behavior is version-sensitive and workload-sensitive. Verify the exact PostgreSQL major, statistics state, server settings, indexes, and data distribution before generalizing any plan shown in this lesson.

• PostgreSQL 18 — Using EXPLAIN
• PostgreSQL 18 — Index-Only Scans and Covering Indexes
• PostgreSQL 18 — Parallel Plans
• PostgreSQL 18 — Index Scanning Interface
• PostgreSQL 18 — Routine Vacuuming / Visibility Map
• PostgreSQL 18 — Resource Consumption / work_mem