You built a covering index — every column the query needs is in the index, so Postgres shouldn't have to touch the table at all. EXPLAIN confirms it: Index Only Scan. You declare victory. Then production load testing shows the query is still doing heap I/O, and EXPLAIN (ANALYZE, BUFFERS) shows a line you didn't expect:
Index Only Scan using orders_customer_created_idx on orders
(actual time=0.31..42.88 rows=9820 loops=1)
Heap Fetches: 9820
Buffers: shared hit=201 read=8714
Heap Fetches: 9820 on a scan that returned 9820 rows means every single row still went to the heap. The index-only scan optimization didn't fire for a single tuple. The index has the right columns. The planner chose the right node type. So what failed?
What index-only scans actually require
An index entry doesn't carry MVCC visibility information — no xmin, no xmax, nothing to tell a scan whether the row version it points to is visible to your snapshot. Normally that's why every index scan has to visit the heap: to check visibility, not just to fetch columns that aren't in the index.
The index-only scan optimization skips that heap visit by using the visibility map (VM) instead — a compact bitmap, one bit per heap page, that's set when every tuple on that page is known to be visible to all transactions (all-visible) and, since PG9.6, a second bit per page for all-frozen. If the VM bit for a given page is set, Postgres trusts it and skips the heap entirely for rows on that page. If the bit is not set, it has to fall back to the heap to check visibility — that's a "heap fetch," and it happens per-tuple, on the page that isn't marked all-visible.
Your covering index controls whether the scan can avoid the heap for column data. The visibility map controls whether it can avoid the heap for visibility. Both have to cooperate. Most people building a covering index only think about the first one.
Why the VM bit wasn't set
The all-visible bit on a heap page is cleared by any write to that page and only gets set back by VACUUM — specifically, by a vacuum pass that confirms every live tuple on the page is visible to all currently-running transactions, and no dead tuples remain that a concurrent transaction might still need. There are three common reasons it's missing in practice:
- Autovacuum hasn't run recently enough. On a high-write table, pages are getting dirtied by inserts/updates/deletes faster than autovacuum revisits them. Check
last_autovacuum/last_vacuuminpg_stat_user_tables— if it's hours old on a table that takes writes every second, the VM is stale across most of the table by definition. - Long-running transactions are holding back the horizon. Vacuum can't mark a page all-visible if there's an older open transaction (or snapshot, e.g. a
REPEATABLE READsession, a replication slot, or a stuckidle in transactionconnection) that might still need to see an old row version. Checkpg_stat_activityfor anything with a very oldxact_start, andpg_replication_slotsfor a slot that isn't advancing — either one pins the vacuum horizon for the whole cluster, not just one table. - The table is append-mostly and freshly written. Freshly inserted pages are never all-visible until a vacuum pass runs after the inserting transaction commits and is old enough that no snapshot predates it. A table that's constantly appending new partitions or new rows at the tail will permanently have a "hot" region with a low VM hit rate, no matter how often you vacuum, simply because new pages are always being created faster than vacuum revisits the newest ones.
Confirming the diagnosis
You don't have to guess — pg_visibility (contrib extension) shows the actual bitmap state per page:
CREATE EXTENSION IF NOT EXISTS pg_visibility;
SELECT all_visible, all_frozen, count(*)
FROM pg_visibility_map('orders')
GROUP BY 1, 2
ORDER BY 3 DESC;
If a large fraction of pages show all_visible = false, that's your heap-fetch rate, independent of anything about the index. Cross-reference with pg_stat_user_tables.last_autovacuum and pg_stat_activity for long transactions before changing anything about the index itself — tuning the index further won't move this number.
What actually fixes it
Rule of thumb:
Heap Fetchesclose to your row count means the visibility map, not the index, is the bottleneck. Checkpg_visibilityandpg_stat_activitybefore touching the index definition — a wider or more covering index can't fix a vacuum problem.
The index-only scan is really two optimizations wearing one name: skip the heap for column data, skip the heap for visibility. Most tuning advice only covers the first. The second one is a vacuum problem in disguise, and it's the one that actually shows up as Heap Fetches in your plan.