Heap access, the visibility map and MVCC storage
Visibility and index-only scans
The idea in 30 seconds
An index does not know whether a row version is visible to your transaction; only the table does. So even an index-only scan must check the table, unless the visibility map says the whole page is visible to everyone. Vacuum sets those bits and every change clears them, which is why the same query can read 277 pages one minute and 138,000 the next.
Helps to know first: How a table is stored on disk, B-tree indexes
On this page
Indexes do not know who can see a row
Every row version carries the transactions that created and replaced it (xmin and xmax, from How a table is stored on disk). Whether you can see a version depends on those and on your transaction’s snapshot. That information lives only in the table. An index entry says “key 42 is at (17,3)” and nothing about whether that version is still alive for you.
So an ordinary index scan always visits the table: once to fetch the row, and to check that it is visible.
The visibility map
For each table page, PostgreSQL keeps one bit that means “every row on this page is visible to every transaction, now and in future”. These bits are the visibility map, a small separate file next to the table.
An index-only scan uses it. For each index entry, it checks the bit for that row’s page:
- Bit set: the row is certainly visible, and the index already has the needed columns. No table visit.
- Bit not set: it has to visit the table to check. That visit is reported as a heap fetch.
Vacuum sets the bits when it finds pages with nothing left to clean up. Any change to a page clears its bit until the next vacuum.
The same query, three times
PredictAn index-only scan after an update
A one-million-row table, freshly vacuumed. SELECT count(*) FROM t WHERE id <= 100000 uses an index-only scan and reads 277 pages. Then all 100,000 of those rows are updated. Roughly how many pages does the same count read now?
138,379 pages, with 168,846 heap fetches. Our measurements, PostgreSQL 18.3:
| State | All-visible pages | Time | Pages read | Heap fetches |
|---|---|---|---|---|
| Freshly vacuumed | 4,425 of 4,425 | 6.5 ms | 277 | 0 |
| After updating 100,000 rows | 3,981 | 40 ms | 138,379 | 168,846 |
After VACUUM | 4,868 of 4,868 | 5.9 ms | 550 | 0 |
The update cleared the bits on every page it touched, so the “index-only” scan had to check the table for each entry. There were more heap fetches than rows because the index briefly held entries for both the old and new version of every updated row, and each had to be checked. After vacuum, every page was all-visible again and heap fetches dropped to 0. The index read twice as many pages as before (550), because it still held the extra entries; the table had also grown, from 4,425 to 4,868 pages, to hold the new versions.
When EXPLAIN shows Index Only Scan with a large Heap Fetches number, the plan is fine; the table just needs vacuuming. On busy tables autovacuum handles it, and how quickly it does is the subject of Vacuum, autovacuum and bloat.
You can see the map with the pg_visibility extension:
CREATE EXTENSION pg_visibility;
SELECT all_visible, all_frozen FROM pg_visibility_map_summary('t');Check yourself
Why can’t an index entry alone tell PostgreSQL whether a row is visible to a query?
Show answer
Visibility depends on the creating and replacing transactions stored with each row version in the table. Index entries do not store that information.
What does
Heap Fetches: 168846on an index-only scan mean?Show answer
The scan had to visit the table 168,846 times because those entries pointed at pages not marked all-visible in the visibility map, so their visibility had to be checked in the table.
What makes heap fetches go back to zero?
Show answer
Vacuum: it removes dead row versions and sets the all-visible bit on pages where every row is visible to everyone.
Finished reading?
Saved in this browser only.
Sources
Where each claim on this page comes from.
- 1.PostgreSQL documentation: 11.9 Index-Only Scans and Covering IndexesPrimary· §11.9 Index-Only Scans and Covering Indexes
Visibility information is stored only in heap entries; an index-only scan checks the visibility map bit for the row's page and must visit the heap when it is not set.
- 2.PostgreSQL documentation: 66.4 Visibility MapPrimary· §66.4 Visibility Map
The visibility map keeps two bits per heap page, all-visible and all-frozen, in a separate fork; indexes have none.
- 3.PostgreSQL documentation: 24.1 Routine VacuumingPrimary· §24.1.4 Updating the Visibility Map
Vacuum maintains the visibility map, which lets later vacuums skip pages and lets queries be answered from the index alone.
- 4.PostgreSQL documentation: F.36 pg_visibilityPrimary· F.36 pg_visibility
pg_visibility_map_summary counts all-visible and all-frozen pages of a table.
Go further
What to read next, and why it is worth the time.
- 1.PostgreSQL documentation: 11.9 Index-Only Scans and Covering IndexesPrimary· §11.9
Spells out every condition an index-only scan needs, including the one most people forget.
Applies to PostgreSQL 18. Draft: not yet fact-checked end to end.