Parameter · Query Tuning / Planner Method Configuration
enable_indexonlyscan
Enables the planner's use of index-only-scan plans.
- Default:
on - Type: bool
- Applies: Session (any user)
In plain words
If every column a query needs is in the index, PostgreSQL can skip reading the table, as long as the visibility map says the table page has no recent changes. That is an index-only scan, often the fastest way to read data. This switch turns it off for testing.
For DBAs and operators
The index does not generally carry enough MVCC visibility information to prove heap-tuple visibility, so the executor checks the visibility map. If the heap page is all-visible, the heap visibility check can be skipped; otherwise the executor visits the heap. EXPLAIN ANALYZE shows these visits as Heap Fetches: N. A high number means many relevant pages are not all-visible; investigate write activity and vacuum cadence rather than assuming a single fix. VACUUM normally sets all-visible bits when it can, and COPY FREEZE has special behavior for eligible newly created/truncated tables.
Seen on 18.6 for SELECT a FROM t WHERE a < 10: on a freshly loaded copy (pg_class.relallvisible = 0) the planner chose a Bitmap Heap Scan; after a plain VACUUM set all pages all-visible, it chose an Index Only Scan. On the vacuumed test table (all 2,273 pages all-visible) the Index Only Scan costs 42.8; with enable_indexonlyscan = off it goes back to the bitmap scan at 2,340.
enable_indexscan must also be on for index-only scans to be considered. Covering indexes (INCLUDE (…)) exist to make more queries eligible. For tables that are insert-only, autovacuum's insert threshold (autovacuum_vacuum_insert_threshold) is what keeps the visibility map current.
How to change it
Any user can change it for their session with SET.
See it in context
Scan methods Free space map and visibility map B-tree indexes Index types
Related parameters
enable_indexscan enable_bitmapscan autovacuum_vacuum_insert_threshold random_page_cost