A Perfect Covering Index That Still Hits the Heap
Your team builds a composite index specifically to enable an index-only scan:
CREATE INDEX idx_orders_covering
ON orders (customer_id, status, total_cents);
The query selects only customer_id, status, and total_cents,
filtered on customer_id — every column it needs is in the index.
The table was bulk-loaded overnight (2 million new rows) and has
never been vacuumed since. EXPLAIN ANALYZE shows Index Only Scan
in the plan, but also reports Heap Fetches: 1,842,000 — nearly one
heap fetch per row the scan touches, even though the index alone
contains every column the query needs.
Why does Postgres still visit the heap here, despite a fully covering index existing?
The visibility map isn't updated yet — it needs VACUUM.
Having every needed column in the index is necessary for an index-only scan, but not sufficient. Postgres uses MVCC: a row version's visibility (is it committed and visible to this transaction's snapshot, or was it deleted/updated by a transaction this snapshot shouldn't see?) is tracked in the heap, not the index. An index entry alone doesn't tell the scanner whether that row version is currently visible — normally that check requires visiting the heap page.
Postgres has a shortcut for exactly this: the visibility map, one
bit per heap page, marking pages where every row is known to be
visible to all transactions (no pending deletes/updates to worry
about). When a page is marked all-visible, an index-only scan can
trust the index entry alone and skip the heap page entirely. That
bit only gets set by VACUUM (or autovacuum) — it is not maintained
automatically as rows are inserted.
A bulk load of 2 million new rows writes fresh heap pages whose
visibility-map bits are unset by default; nothing has told Postgres
yet that those pages are safely all-visible. So even though the
index has everything the query needs, the scanner still falls back
to checking the heap for every one of those newly-loaded rows —
exactly what Heap Fetches: 1,842,000 is reporting. The index-only
scan is real and does skip re-reading indexed column values from
the heap, but the visibility check itself still costs a heap access
until the visibility map catches up.
The fix is exactly what's missing: run VACUUM orders (or wait for
autovacuum to trigger, or lower the table's autovacuum thresholds if
this pattern — bulk load followed by immediate heavy read traffic —
recurs) so the visibility map is populated. After that, the same
query's index-only scan should show Heap Fetches: 0 (or close to
it), and its execution time should drop accordingly — this is also
exactly the same class of "statistics/metadata haven't caught up
with a bulk load" issue as a stale planner-statistics problem, just
hitting a different piece of Postgres bookkeeping.
Share this question