Visibility Map Not Updated (Index-Only Scan Heap Fetches)

Scenario

A DBA creates a covering index to enable index-only scans and eliminate heap access for a high-frequency query. After deploying, EXPLAIN ANALYZE still shows Heap Fetches: 180000 in the Index Only Scan node. The index exists and covers all needed columns, but the query is still accessing the heap on nearly every row.

How to Identify

Conditions:

• EXPLAIN ANALYZE shows Index Only Scan with high Heap Fetches count
• Heap Fetches is close to or equal to actual rows — defeating the purpose of index-only scan
• Table was recently bulk-loaded or has many updates since last VACUUM
• pg_statio_user_tables.heap_blks_read is high despite queries only needing indexed columns

Analysis Steps

-- 1. Run EXPLAIN and inspect Heap Fetches
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT customer_id, count(*)
FROM   orders
GROUP  BY customer_id;
-- Look for: "Heap Fetches: N" in Index Only Scan node
-- If N ≈ actual rows → visibility map is not set → heap is being checked

-- 2. Check when VACUUM last ran (VACUUM sets visibility map bits, ANALYZE does not)
SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM   pg_stat_user_tables
WHERE  relname = 'orders';
-- If last_vacuum is NULL or old after a bulk load → visibility map is stale

-- 3. Check pg_visibility for page visibility (requires extension)
-- CREATE EXTENSION pg_visibility;
-- SELECT count(*) FROM pg_visibility('orders') WHERE all_visible = false;

Pitfalls

• ANALYZE does NOT set visibility map bits — only VACUUM does. Running ANALYZE after a bulk load is not sufficient to enable index-only scans.
• After bulk loads, always run VACUUM ANALYZE (not just ANALYZE) to set visibility map bits and update statistics in one pass.
• The planner’s estimate of Heap Fetches can be wildly optimistic if the visibility map is stale — the plan looks correct but actual I/O is much higher.
• Disabling autovacuum on bulk-load tables is common but leaves the visibility map permanently stale until manually vacuumed.

Resolution Approach

Run VACUUM (not just ANALYZE) after bulk loads. VACUUM marks pages as all-visible in the visibility map, allowing index-only scans to skip heap access entirely. VACUUM ANALYZE does both in one pass.