Why an Index Only Scan Still Read the Heap

The optimisation was textbook: add a covering index so the query reads only the index and never touches the table, and EXPLAIN obligingly printed Index Only Scan. The buffers told a different story. The query still fetched heap pages, nearly as many as before, and the latency barely moved. The plan name was correct and the behaviour was not, because Index Only Scan is a promise the visibility map has to keep, and after the night's bulk load, the map kept none of it.

I have lost on-call weeks to this exact shape of slow burn, so the order below is the order I fix it in.

This is the quiet half of MVCC, the part where the index knows the key but not whether the row is alive to your snapshot, and the visibility map is the bit that answers that question cheaply. When the bit says unknown, the engine checks the heap, and the index only scan is index only in name.

This was Postgres 16.3, a covering index added for a hot aggregate, and a nightly bulk upsert that reset the visibility map every evening.

What the visibility map actually stores

For each heap page, the visibility map holds a bit saying whether every tuple on that page is visible to all current and future transactions, which in practice means all tuples on the page are committed and no live transaction can still see an older version. When the bit is set, an index only scan can return the index entry without visiting the heap, because there is no visibility question left to answer.

The bit is set by vacuum, which is the only process that can prove a page all visible, by confirming no dead or in doubt tuples remain. So the visibility map is vacuum's published opinion about the heap, and an index only scan is a reader that trusts it.

Why the heap is still read

When the bit is not set, the index only scan must visit the heap page to check tuple visibility against the snapshot, which is the same work a plain index scan does, plus the index. So the scan degenerates, and the degenerate case is what the bulk load produced.

An update or insert marks the affected page not all visible, because the new tuple's fate is undecided and older versions may still be visible to some snapshot. A bulk upsert touching most pages therefore clears most bits, and until vacuum rewalks the table and resets them, every index only scan pays the heap visit. The nightly job cleared the map at two in the morning, the morning queries read the heap, and the vacuum that would have restored the bits ran too late or too slowly to matter.

This is the same MVCC machinery as vacuum, dead tuples and wraparound, where vacuum is the housekeeping that makes the fast paths possible, and its absence is the tax.

Seeing it

The plan's output names the degenerate case directly. EXPLAIN ANALYZE prints, alongside Index Only Scan, the counter Heap Fetches. A scan that returns a million rows with a million heap fetches is not index only in effect, and the ratio of heap fetches to rows is the visibility map's report card.

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Watch Heap Fetches and the shared buffers hit for heap pages. After a vacuum, rerun and watch the fetches collapse toward zero. The before and after is the visibility map's entire contribution, made visible.

You can also query the map's opinion per table, the fraction of pages marked all visible, which tells you whether the table is in a state where index only scans can pay off at all, and which makes the nightly reset obvious as a sawtooth across the day.

Why the heap fetches still cost

It is worth naming why a heap fetch under an index only scan is not free even when the page is cached. The fetch is a buffer lookup plus a visibility check per tuple, and for wide tables the heap pages are large and cold relative to the slim index, so the cache that held the index does not hold the heap, and the fetch becomes physical IO. The index was added to avoid the heap precisely because the heap is the expensive thing, and the degenerate scan reintroduces it row by row.

The fix, in order

Let vacuum keep up with the writes. The map is restored by vacuum, so a table that is bulk written needs vacuum to follow the bulk load, with settings that let autovacuum trigger on the load's scale rather than on a percentage tuned for the table's old size. On very large tables the percentage based trigger fires too late, and the absolute thresholds exist for exactly this.

Vacuum the pages you wrote, in the job. A bulk job that issues a targeted vacuum over the range it touched, or relies on the autovacuum that the load triggers, restores the bits before the morning readers arrive. The ordering matters: the load, then the map restore, then the fast queries, and a pipeline that skips the middle step ships the degenerate case to production every day.

Question the covering index on a write heavy table. A covering index only pays when the visibility map is mostly set, which requires the table's write pattern to leave pages settled. On a table that is rewritten nightly, the index only scan may never be the fast path, and the honest design is a plain index plus a heap the cache can hold, or moving the aggregate off the hot path, because the optimisation's precondition is absent.

The rule

Index Only Scan is conditional on the visibility map, and the map is vacuum's opinion, reset by every write and restored only by vacuum. Read Heap Fetches to see whether the condition holds, keep vacuum aligned with the write pattern, and treat a covering index on a table that is rewritten nightly as an optimisation whose precondition is missing.

The plan name is a promise. The buffers are the truth. The gap between them is the visibility map, and it is the housekeeping, once again, that decides whether the fast path exists. The write side of the same MVCC tax is the index amplification post, and the vacuum side is the wraparound post in this series.

Related reading: What an Index Actually Does, and the Query It Cannot Help explores a connected angle.

Published on debugly.dev by Rohit Bhadani | Founder of Krova Cloud