Index Only Scan

An Index Only Scan returns data straight from the index without visiting the table heap, but only for pages the visibility map marks as all-visible. For other pages it must still fetch the heap row to check visibility (a 'heap fetch').

When every column the query needs is present in the index and the table is well-vacuumed, so most pages are all-visible.

The fastest read pattern when it works. Its advantage collapses if the table isn't vacuumed often enough: lots of 'Heap Fetches' means the visibility map is stale and the scan is secretly hitting the heap anyway.

How it works under the hood

An Index Only Scan is an Index Scan that tries to skip the heap fetch entirely. It can do that only when two conditions hold:

1. Every column the query needs is present in the index (a covering index).
2. The page the entry lives on is marked all-visible in the table's visibility map — meaning every tuple on it is visible to all transactions, so no per-row MVCC check is required.

When both hold, the answer comes straight from the index: no random heap I/O. When the second fails, the scan must still read the heap page to confirm visibility — that's a heap fetch, and it quietly erases the advantage.

Reading the numbers

Index Only Scan using idxonly_v on idxonly
  (cost=0.42..8331.68 rows=198663 width=0) (actual ... rows=150000 loops=1)
  Heap Fetches: 170500

Heap Fetches: 170500 is the tell. The plan calls itself "Index Only" but it hit the heap 170,500 times — because the visibility map was stale. That happens after lots of INSERT/UPDATE/DELETE without a VACUUM to re-mark pages all-visible.

A healthy index-only scan shows Heap Fetches: 0 (or a small number).

How to fix high heap fetches

• Vacuum the table: VACUUM (ANALYZE) your_table; rebuilds the visibility map immediately.

• Make autovacuum more aggressive on that table so the map stays fresh:

  ALTER TABLE your_table SET (autovacuum_vacuum_scale_factor = 0.02);
  

• For append-mostly tables, also consider lowering autovacuum_vacuum_insert_scale_factor so inserts trigger vacuums too.

Covering indexes with INCLUDE

To make a scan index-only without bloating the B-tree key, add non-key payload columns with INCLUDE:

CREATE INDEX ON orders (customer_id) INCLUDE (status, total);

Now SELECT status, total ... WHERE customer_id = $1 can be served index-only.

• Index Only Scan heap fetches — a stale visibility map is forcing heap visits (vacuum the table)

Paste a plan into the analyzer →

autovacuum_vacuum_scale_factor

How we tune these →

What are 'Heap Fetches' and why do they matter?

They count how often the scan had to read the table heap because the visibility map didn't mark a page all-visible. High heap fetches mean the table needs more aggressive (auto)vacuum — otherwise the 'index only' scan isn't really index-only.

• Index Scan
• Sequential Scan