Why does PostgreSQL sometimes show Index Only Scan but still read the table?
An index does not store row visibility. PostgreSQL checks the visibility map, and for pages not marked all-visible it must fetch the heap row. EXPLAIN ANALYZE shows this as Heap Fetches, which VACUUM reduces.
Explain it simply
An index entry tells you "this value exists at this table location", but not whether the row is still visible to your transaction. Maybe it was deleted or updated by someone else. To answer without touching the table, PostgreSQL consults the visibility map, a tiny bitmap with one bit per table page: "every row on this page is visible to everyone".
If the bit is set, the index alone is enough. If not, PostgreSQL walks to the table page to check, and that is called a heap fetch.
Example
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id FROM orders WHERE customer_id BETWEEN 100 AND 200;
-- Index Only Scan using orders_cust_idx on orders
-- Heap Fetches: 48213 <- many table visits, scan is not really "only"
VACUUM orders;
-- run again
-- Heap Fetches: 0Tables with heavy updates keep clearing visibility bits, so index-only scans work best on mostly static or append-only data, with autovacuum keeping up.
What to say in the interview
"Index-only scans still depend on the visibility map. Pages not marked all-visible force heap fetches, which I see in EXPLAIN ANALYZE. Running VACUUM, or tuning autovacuum for that table, sets the bits and makes the scan truly index-only."
Common mistakes
- Adding INCLUDE columns and expecting speedup on a table that is never vacuumed.