Scan methods

For DBAs and operators

Seq Scan

Reads blocks 0…N in order and evaluates the Filter on every visible tuple. Cheap per page and served by a read stream (PostgreSQL 17+) that combines neighbouring blocks into one read of up to io_combine_limit; since PostgreSQL 18 those reads are issued ahead with asynchronous I/O. Large scans use a small ring buffer so they do not evict the whole cache, and with synchronize_seqscans a second scan of the same big table joins the first one mid-way.

Index Scan

Descends the B-tree once per search (Index Searches in PostgreSQL 18 EXPLAIN), walks matching leaf entries in key order and fetches each heap tuple by TID to check visibility. Output is sorted by the index key, which can save a Sort. Cost grows with random heap reads unless the table is physically correlated with the index.

Index Only Scan

Possible when every referenced column can be returned by the index (including payload columns supplied with INCLUDE, where the access method supports index-only scans). For each entry it checks the page's bit in the visibility map: an all-visible heap page needs no per-tuple heap visibility check; otherwise the heap is visited. Watch Heap Fetches: a high number means many referenced pages are not all-visible, which can be normal on frequently modified data or can justify reviewing vacuum cadence.

Index Only Scan using orders_pkey on orders (actual rows=999.00 loops=1)
  Index Cond: (id < 1000)
  Heap Fetches: 0
  Index Searches: 1
  Buffers: shared hit=4 read=2

Bitmap Index Scan + Bitmap Heap Scan

The index scan builds an in-memory TID bitmap (one entry per heap page, with per-tuple bits). Several bitmaps can be combined with BitmapAnd / BitmapOr, e.g. for a = 1 OR b = 2. The heap scan then visits pages in physical order, each at most once, and can prefetch them (effective_io_concurrency). If the bitmap would exceed work_mem, pages become lossy: only "something matches on this page" is remembered, so every tuple there must be rechecked.

SET work_mem = '64kB';
Bitmap Heap Scan on orders (actual rows=203374.00 loops=1)
  Recheck Cond: ((created >= '2024-03-01'::date) AND (created <= '2024-04-30'::date))
  Rows Removed by Index Recheck: 393133
  Heap Blocks: exact=967 lossy=3641
  ->  Bitmap Index Scan on orders_created_idx (actual rows=203374.00 loops=1)

TID Scan and TID Range Scan

WHERE ctid = '(5,2)' fetches block 5, line pointer 2 directly; ctid >= '(1000,0)' AND ctid < '(2000,0)' (TID Range Scan, PostgreSQL 14+) reads a block range, which is handy for batching big deletes or updates. A ctid changes when a row is updated or the table is rewritten, so never store it.

Diagnosing

• Rows Removed by Filter large on a Seq Scan of a big table → missing or unusable index (function on the column, type mismatch, leading wildcard LIKE '%x').
• enable_seqscan = off etc. do not forbid a method: since PostgreSQL 18 the planner counts disabled nodes and prefers plans with fewer of them, but still uses a disabled method when there is no alternative (EXPLAIN then shows Disabled: true). Use them in a session to compare plans, never globally.
• pg_stat_user_tables.seq_scan / idx_scan and pg_stat_user_indexes.idx_scan show which methods are used in practice.