The Slow Query Log

Index-only scans that aren't: the visibility map's role in a plan you can't explain

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:

  1. 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_vacuum in pg_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.
  2. 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 READ session, a replication slot, or a stuck idle in transaction connection) that might still need to see an old row version. Check pg_stat_activity for anything with a very old xact_start, and pg_replication_slots for a slot that isn't advancing — either one pins the vacuum horizon for the whole cluster, not just one table.
  3. 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 Fetches close to your row count means the visibility map, not the index, is the bottleneck. Check pg_visibility and pg_stat_activity before 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.