Recall the shape of an index entry: a key plus a pointer to the row in the heap. Answering a query normally means following that pointer, and each follow is a random heap read — the most expensive step in the whole lookup (see how an index finds a row).
When the index already has everything
Consider SELECT age FROM users WHERE country = 'IN' with an index on
(country, age). The index entries already contain both country (to filter) and
age (to return). Once the B-tree walks to the IN block, it reads the age
values straight off the leaf pages — there’s no reason left to visit the heap.
An index that contains every column a query needs is a covering index, and the
scan it enables is an index-only scan. It removes the random heap reads
entirely — no pointer-chasing, no scattered I/O — which for a hot query is often a
~10× win. It’s why you’ll see people deliberately add columns to an index that
they never filter on (Postgres has INCLUDE (...) for exactly this): not to seek
by them, just so the index can answer the query alone.
The caveat: visibility lives in the heap
An index-only scan isn’t always free of the heap, even when the index covers the query — and this connects straight to MVCC. Row visibility (whether a given version is one your transaction’s snapshot is allowed to see) lives in the row versions in the heap, not in the index. The index doesn’t know if an entry points to a version you can see.
Postgres handles this with a visibility map: a compact bitmap marking pages
where all rows are visible to everyone. If the target page is flagged
all-visible, the scan trusts the index and skips the heap. If not, it must peek at
the heap to check visibility — and you’re paying for heap reads again. So a table
that’s been heavily updated and not yet vacuumed loses its index-only scans
until VACUUM refreshes the visibility map. The same VACUUM that reclaims old
row versions, showing up one layer higher.