Index Only Scan still hits the heap. What is it checking?

The index knows the values but not the whole visibility story

Consider this query and one possible index:

SELECT created_at, total_cents
FROM orders
WHERE merchant_id = :merchant
ORDER BY created_at DESC
LIMIT 100;

CREATE INDEX orders_recent_cover
ON orders (merchant_id, created_at DESC)
INCLUDE (total_cents);

The index stores enough information to locate matching entries, produce the required order, and return the selected fields. In PostgreSQL terminology, the INCLUDE column is payload. It is not another search key and does not affect the ordering. On paper, no heap access seems necessary.

But PostgreSQL permits multiple versions of a logical row to exist during concurrent transactions. A query must return versions visible to its own snapshot. A B-tree index entry does not, by itself, carry all the information needed to decide visibility for every transaction. If the database returned every matching index entry without checking, it could expose a row version that should not be visible or return an obsolete one. PostgreSQL's index-only scan documentation is explicit about this boundary.

The optimization is not “never visit the table.” It is “avoid table visits when the database can prove they are unnecessary.”

One bit per heap page changes the read path

PostgreSQL maintains a small visibility map for each heap relation. One of its bits records whether a particular heap page is all-visible, meaning every tuple on that page is known visible to all relevant snapshots. The index-only executor finds an index entry, checks the visibility map bit for the referenced heap page, and can return the value directly when that bit is set. If the bit is not set, it visits the heap and checks the tuple's visibility.

The map is conservative. A missing all-visible bit does not prove that the row is invisible. It means the shortcut cannot be trusted. Data changes clear the all-visible status of affected pages, while vacuum can set it again when the page qualifies. This is documented in PostgreSQL's visibility map internals.

That explains the age difference in our report. Last year's finalized orders are rarely modified. Many heap pages may be all-visible, so an index-only scan can avoid most heap access. Today's orders undergo payment confirmation, fulfillment updates, and refunds. The affected pages repeatedly lose the shortcut, and the executor visits their heap tuples despite holding all requested data in the index.

The plan name is an opportunity, not an I/O receipt

A useful measurement is:

EXPLAIN (ANALYZE, BUFFERS)
SELECT created_at, total_cents
FROM orders
WHERE merchant_id = 42
ORDER BY created_at DESC
LIMIT 100;

Look for the Index Only Scan node and its Heap Fetches counter. Compare hot and old partitions or time ranges, along with buffer activity and query frequency. A scan that makes 100 index visits and needs 90 heap fetches is not getting the same benefit as one that needs 2. Those values are illustrative, and page caching may still make heap fetches relatively cheap.

Also separate two causes. If the plan is an ordinary Index Scan, perhaps the index does not contain everything the query needs or the planner judged it cheaper. If it is Index Only Scan with many heap fetches, the visibility-map path is the immediate reason for those table visits. Adding total_cents twice will not change the visibility bits.

Vacuum activity can help restore all-visible pages, but it cannot promise that actively changing pages always qualify. Long-lived snapshots and ongoing modifications can delay cleanup or keep changing the visibility state. Running VACUUM FULL in production to solve a hot-read problem is not a routine tuning step. PostgreSQL distinguishes ordinary vacuum from the more disruptive full table rewrite in its maintenance guidance.

The extra index can also make updates more expensive

Every index duplicates some data and introduces maintenance work. PostgreSQL has an optimization called HOT, or heap-only tuple updates, that can avoid new index entries when updated columns are not referenced by the table's non-summarizing indexes and enough space is available on the old heap page. PostgreSQL 18 exempts summarizing indexes such as BRIN from that indexed-column restriction; the covering B-tree discussed here is not exempt. Changing a column stored as an index key or INCLUDE payload can remove that opportunity. PostgreSQL documents the prerequisites in its HOT storage notes.

This creates a real design tradeoff. Adding total_cents to the covering index might accelerate reads on stable orders, but if total_cents changes frequently, the index can also raise update cost and reduce HOT opportunities. The measure of success is the combined read and write workload, not an impressive Index Only Scan label.

For a frequently updated order dashboard, I would first inspect actual heap fetches, update patterns, vacuum progress, and the usefulness of the index to the endpoint. If the freshest data needs consistently low latency, a maintained projection or a narrower write model may be worth evaluating. That solution has a separate freshness and correction contract, so it should not be presented as free.

The most important lesson is that a database index participates in a concurrency model. It is not merely a sorted file of values. The engine must decide which committed history each query is entitled to observe, and the index alone does not always provide that proof.