Visibility Map and Index-Only Scan Regressions #

An index-only scan is a promise the planner makes conditionally: if the heap page holding a matching row is known to contain nothing but rows visible to everyone, the executor can answer from the index alone. The register of which pages qualify is the visibility map, and it is maintained by vacuum. Between vacuums, write traffic erases entries, and the scan silently degrades — with the plan text unchanged.

This is the regression that looks like nothing changed, because in the plan nothing did. The maintenance background is in autovacuum and plan stability.

The Condition That Triggers It #

The visibility map holds two bits per heap page. The one that matters here is all-visible: set by vacuum when every tuple on the page is visible to all transactions, cleared by any insert, update, or delete touching the page.

An Index Only Scan therefore proceeds entry by entry:

  1. Read the index entry and extract the tuple pointer.
  2. Check the visibility map for that heap page.
  3. If all-visible, return the values straight from the index.
  4. Otherwise read the heap tuple to test visibility — and count a heap fetch.

The planner estimates how many step-4 detours will be needed from pg_class.relallvisible divided by relpages, which is refreshed by vacuum and analyze. So a table that was 99% all-visible when last analyzed will be costed as almost free, even if today it is 20% all-visible.

Why an index-only scan reaches the heap Index entries flow into a visibility map check. Entries whose page is marked all-visible return directly from the index. Entries whose page bit was cleared by recent writes divert to the heap and increment the Heap Fetches counter. index entries 8,412 matched visibility map is the page all-visible? yes no answer from the index no heap I/O read the heap tuple random page read + visibility test Heap Fetches += 1 every write to a page clears its bit — only VACUUM sets it again

Annotated EXPLAIN Evidence #

Two captures of the identical query, forty minutes apart:

-- Right after VACUUM: the promise is kept
Index Only Scan using orders_customer_total_idx on orders  (actual time=0.03..2.11 rows=8412 loops=1)
  Index Cond: (customer_id = 4471)
  Heap Fetches: 0
  Buffers: shared hit=34                 -- index pages only
Execution Time: 2.18 ms

-- After 40 minutes of update traffic on the same rows
Index Only Scan using orders_customer_total_idx on orders  (actual time=0.04..38.62 rows=8412 loops=1)
  Index Cond: (customer_id = 4471)
  Heap Fetches: 7891                     -- 94% of rows needed the heap
  Buffers: shared hit=1204 read=6702     -- random heap reads dominate
Execution Time: 38.71 ms

Node type, index, and Index Cond are byte-for-byte identical. Only Heap Fetches and the buffer profile moved. That combination — same plan, same rows, 17× the time, heap fetches near the row count — is the unambiguous fingerprint of visibility-map lag rather than of bloat, statistics, or a plan flip.

Measure the underlying coverage directly:

CREATE EXTENSION IF NOT EXISTS pg_visibility;

SELECT relpages,
       (SELECT count(*) FROM pg_visibility_map('orders') WHERE all_visible) AS all_visible_pages
  FROM pg_class WHERE relname = 'orders';
-- relpages | all_visible_pages
--  1841204 |            412880       -- 22% coverage: index-only scans cannot work here

Step-by-Step Resolution #

  1. Confirm the diagnosis by comparing Heap Fetches with the returned row count. A ratio near 1.0 means the index-only path delivered nothing.

  2. Measure coverage with pg_visibility as above, or via the planner’s own view of it:

    SELECT relname, relpages, relallvisible,
       round(100.0 * relallvisible / NULLIF(relpages, 0), 1) AS pct_all_visible
  FROM pg_class WHERE relname = 'orders';

  3. Vacuum the table and re-measure to prove causation:

    VACUUM (VERBOSE, ANALYZE) orders;

    ANALYZE matters here too: it refreshes relallvisible, which is what the planner costs against.

  4. Make the coverage durable by vacuuming that table more often:

    ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_cost_limit   = 2000
);

  5. Remove snapshot holders. Vacuum cannot mark a page all-visible while an older transaction might still need an earlier version, so a long-running session caps your coverage no matter how often vacuum runs.

  6. Reduce the write pressure on the covered columns where possible. An update that touches only non-indexed columns can still be a HOT update, avoiding index churn — but it still clears the page’s all-visible bit, so nothing removes the need for vacuum.

  7. Re-verify the win on the original query: Heap Fetches: 0 and a buffer count back in the tens.

Coverage decay between vacuum cycles A sawtooth line shows the percentage of all-visible pages dropping steadily as writes accumulate and jumping back up at each vacuum. A shaded band marks the region below which index-only scans stop paying off. time → all-visible % below here, Heap Fetches dominates VACUUM VACUUM VACUUM VACUUM longer gap → deeper decay

Before and After #

-- BEFORE: 22% of pages all-visible
Index Only Scan  Heap Fetches: 7891   Buffers: shared hit=1204 read=6702
Execution Time: 38.71 ms

-- AFTER: VACUUM ANALYZE, 97% of pages all-visible
Index Only Scan  Heap Fetches: 0      Buffers: shared hit=34
Execution Time:  2.18 ms

The index was never the problem — its size, density, and definition are unchanged. Only the fraction of heap pages carrying the all-visible bit moved, which is why rebuilding the index would have accomplished nothing.

Common Pitfalls #

Rebuilding the index in response. Bloat and visibility are different failures with different repairs. Diagnostic signal: healthy avg_leaf_density from pgstatindex alongside high Heap Fetches. Fix: vacuum, do not reindex.

Adding more columns to make the index “more covering”. Coverage of the target list is a separate condition from page visibility. Diagnostic signal: Heap Fetches still high after widening the index. Fix: address vacuum frequency.

Reading Heap Fetches on a test system. A quiet database has near-perfect coverage, so the regression is invisible outside production write traffic. Diagnostic signal: cannot reproduce a production slowdown locally. Fix: measure relallvisible on the real system.

Overlooking snapshot holders. They cap achievable coverage regardless of vacuum settings. Diagnostic signal: coverage that never recovers after a manual vacuum. Fix: locate the long transaction or stale replication slot and clear it.

Comparing coverage across replicas. The visibility map is part of the relation and ships through replication, but a replica’s own long-running queries hold back snapshots on the primary through hot_standby_feedback, capping coverage everywhere. Diagnostic signal: coverage that recovers only when a reporting replica is idle. Fix: bound query duration on the replica, or accept the trade and disable the feedback knowingly.

Assuming an append-only table stays covered. Inserts clear the all-visible bit on the page they land on, so a heavily appended table has a permanently uncovered tail — exactly the range that recent-data queries read. Diagnostic signal: Heap Fetches concentrated on queries filtering for recent rows. Fix: vacuum frequently enough that the tail catches up, which on older versions means scheduling it explicitly for insert-only tables.

Telling visibility lag from index bloat A slow index-only scan branches on the leaf density measurement: healthy density with high heap fetches means visibility lag, while low density means bloat. slow Index Only Scan density ≈ 90% density < 50% visibility map lag vacuum more often index bloat REINDEX CONCURRENTLY

Frequently Asked Questions #

What exactly does Heap Fetches count? Index entries whose heap page was not marked all-visible, forcing a heap read to test visibility. Zero means a genuinely index-only scan; a count near the row count means the scan performed like an ordinary index scan plus a visibility check.

Why does the plan still say Index Only Scan? Because the node describes what the executor may attempt, not what it avoided. The planner estimates the fetches from relallvisible, which reflects the state at the last vacuum or analyze, while the actual number depends on write traffic since then.

Do HOT updates keep the visibility map fresh? No. A heap-only tuple update avoids new index entries, which helps in other ways, but it still writes a new version onto the page and clears the all-visible bit. Only vacuum can set it again.