Covering Indexes and Index-Only Scans
An Index Only Scan still touched the table heap once. The index was never the problem — the visibility map was.
A normal Index Scan uses the index only to find matching row locations, then goes to the table heap for the actual row data. If the index already stores every column a query needs — either because those columns are part of the indexed key, or added purely for this purpose via INCLUDE — PostgreSQL can, in principle, answer the query from the index alone, never touching the heap at all. That is an Index Only Scan, and it is genuinely faster: no random-access heap lookups per row, just a walk through the index structure.\n\nBut "in principle" carries a real asterisk. An Index Only Scan still has to confirm each row is visible to the current transaction — MVCC visibility cannot be determined from the index entry alone. PostgreSQL's visibility map tracks, per heap page, whether every row on it is known to be visible to everyone; only when a page is marked all-visible there can an Index Only Scan trust the index and skip the heap for that row entirely. A freshly inserted table has no pages marked all-visible yet, so even a perfectly-designed covering index still reports real, nonzero Heap Fetches — until VACUUM updates the map.
A Covering Index, and an Unexpected Heap Fetch
CREATE INDEX idx_covering_email ON idx_users3(email) INCLUDE (name, created_at);
EXPLAIN (ANALYZE, TIMING OFF)
SELECT name, created_at FROM idx_users3 WHERE email = 'user25000@example.com';
Index Only Scan using idx_covering_email on idx_users3 (actual rows=1.00 loops=1)
Index Cond: (email = 'user25000@example.com'::text)
Heap Fetches: 1
The plan already says "Index Only Scan" — the index structurally has everything this query needs, email as the search key and name/created_at riding along via INCLUDE. And yet "Heap Fetches: 1" means it touched the table heap anyway, for this one row. Structurally covered is not the same as visibility-confirmed.
VACUUM, and the Same Query Again
VACUUM idx_users3;
EXPLAIN (ANALYZE, TIMING OFF)
SELECT name, created_at FROM idx_users3 WHERE email = 'user25000@example.com';
Index Only Scan using idx_covering_email on idx_users3 (actual rows=1.00 loops=1)
Index Cond: (email = 'user25000@example.com'::text)
Heap Fetches: 0
Identical index, identical query — "Heap Fetches: 0" now. VACUUM updated the visibility map, marking the heap page this row lives on as all-visible, so the Index Only Scan can now trust the index entry completely and skip the heap.
Confirming the Index Is Actually Being Used
SELECT relname, idx_scan FROM pg_stat_user_indexes WHERE indexrelname = 'idx_covering_email';
idx_scan increments on every scan that used this index — a direct, cumulative confirmation independent of any single EXPLAIN run, useful for auditing which indexes a production workload actually exercises over time (the same view Lab 3.2.6 uses to find indexes that never get used at all).
INCLUDE columns
Extra columns attached to a B-tree index purely for their payload value, not as part of the searchable key. They add no sort order and cannot be used in an Index Cond, but their values are stored directly in the index, so a query needing only the indexed key columns plus the INCLUDE columns can be answered entirely from the index — a smaller, cheaper index than making every column part of the actual sort key.
Visibility map
A compact, per-relation bitmap tracking which heap pages are known to contain only rows visible to every current and future transaction (no pending updates or deletes still awaiting cleanup). An Index Only Scan consults this map before trusting an index entry alone; for any page not yet marked all-visible, it still has to fetch the actual heap row to check visibility, which is exactly what a nonzero Heap Fetches count reports. VACUUM is what updates the map.
📇 Build the Covering Index and Read the Unexpected Fetch
Create an index on email with name and created_at as INCLUDE columns, then run the query it should fully cover.
psql -U postgres -d beer_db -c "CREATE INDEX idx_covering_email ON idx_users3(email) INCLUDE (name, created_at);"psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT name, created_at FROM idx_users3 WHERE email = 'user25000@example.com';"student@lab:~$ psql -U postgres -d beer_db -c "CREATE INDEX idx_covering_email ON idx_users3(email) INCLUDE (name, created_at);" SET CREATE INDEX student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT name, created_at FROM idx_users3 WHERE email = 'user25000@example.com';" SET QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Index Only Scan using idx_covering_email on idx_users3 (cost=0.41..8.43 rows=1 width=17) (actual rows=1.00 loops=1) Index Cond: (email = 'user25000@example.com'::text) Heap Fetches: 1 Index Searches: 1 Buffers: shared hit=1 read=3 Planning: Buffers: shared hit=54 read=2 Planning Time: 21.516 ms Execution Time: 4.038 ms (9 rows)
🧹 Run VACUUM and Confirm the Real Cause
Run VACUUM against the table, then rerun the identical query to see whether Heap Fetches actually changes.
psql -U postgres -d beer_db -c "VACUUM idx_users3;"psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT name, created_at FROM idx_users3 WHERE email = 'user25000@example.com';"student@lab:~$ psql -U postgres -d beer_db -c "VACUUM idx_users3;" SET VACUUM student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN (ANALYZE, TIMING OFF) SELECT name, created_at FROM idx_users3 WHERE email = 'user25000@example.com';" SET QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Index Only Scan using idx_covering_email on idx_users3 (cost=0.41..4.43 rows=1 width=17) (actual rows=1.00 loops=1) Index Cond: (email = 'user25000@example.com'::text) Heap Fetches: 0 Index Searches: 1 Buffers: shared hit=4 Planning: Buffers: shared hit=90 Planning Time: 13.059 ms Execution Time: 5.371 ms (9 rows)
📊 Confirm Real Usage With pg_stat_user_indexes
Check the cumulative scan counter for this index directly, independent of any single EXPLAIN run.
psql -U postgres -d beer_db -c "SELECT relname, idx_scan FROM pg_stat_user_indexes WHERE indexrelname = 'idx_covering_email';"student@lab:~$ psql -U postgres -d beer_db -c "SELECT relname, idx_scan FROM pg_stat_user_indexes WHERE indexrelname = 'idx_covering_email';" SET relname | idx_scan ------------+---------- idx_users3 | 2 (1 row)
Lab 3.2.3 complete. Covering indexes and the real gate on Index Only Scans:\n\n\n Covering index built, INCLUDE columns : ✅ Index Only Scan chosen structurally\n Heap Fetches before VACUUM : ✅ 1 — visibility map not yet updated\n Heap Fetches after VACUUM : ✅ 0 — same index, same query\n pg_stat_user_indexes.idx_scan confirmed : ✅ 2, cumulative ground truth\n
Enable JavaScript to run the live terminal and track your progress.