Index-only scans feel like cheating the first time they work. PostgreSQL answers from the index, avoids visiting the heap for every row, and a query that used to touch a large table suddenly becomes cheap.
Then production changes the mood. The plan still says Index Only Scan, but runtime is worse than expected and the plan shows thousands or millions of heap fetches. The team asks the obvious question: if this is index-only, why is it reading the table?
The answer is that index-only scans depend on truth outside the index. PostgreSQL must know whether the tuple is visible to the current transaction. If the visibility map cannot prove that every tuple on a heap page is visible, PostgreSQL has to check the heap.
The visibility map is the hidden feature
PostgreSQL tracks whether heap pages are all-visible. VACUUM sets those bits after it confirms there are no dead tuples on the page that matter to active transactions. If the bit is set, an index-only scan can skip the heap for rows on that page.
If autovacuum is lagging, if a table has heavy churn, or if old transactions keep dead rows alive, the visibility map becomes less useful and heap fetches come back.
Use EXPLAIN to check whether it is really cheap
In the output, look for Heap Fetches. A small number is normal. A large number means the plan name is better than the actual behavior.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, status
FROM orders
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 100;Cover the query, not just the filter
An index-only scan needs the columns required by the query. That includes filters, ordering, and selected columns. PostgreSQL supports INCLUDE columns for values that should be available from the index but do not need to be part of the search key.
CREATE INDEX CONCURRENTLY orders_tenant_created_cover_idx
ON orders (tenant_id, created_at DESC)
INCLUDE (status, total_cents);When index-only scans disappoint
- The table is update-heavy and visibility map coverage stays low.
- Autovacuum cannot keep up with dead tuples.
- Long transactions prevent cleanup.
- The index does not include the selected columns.
- The query returns too many rows for index access to be cheap.
The practical standard
The best PostgreSQL performance work is boring in the right way. Name the failure mode, capture the before plan or metric, make one change, and compare the exact same signal afterward. Anything else is just a more confident guess.