You spend time creating what looks like the perfect index. It includes every column your query needs, nothing more, nothing less.
Naturally, you expect PostgreSQL to answer the query entirely from the index without accessing the table.
Then you run EXPLAIN ANALYZE and see something unexpected.
Heap Fetches: 1248
Then why Postgresql still reads from the table.The index already has all the needed data.
Then I figured out that the visibility map had a major role in index scan.Where it really helps to accelerate the performance. This introduced a new way to fetch data without spending time in heap table. A small cheatsheet comes along with VACUUM.
Why Isn't the Index Enough?
At first, index seems like it should contain everything.
Imagine an employee table:
CREATE TABLE employee (
id SERIAL PRIMARY KEY,
department TEXT,
salary NUMERIC
Suppose you frequently run:
SELECT salary
FROM employee
WHERE department = 'Sales';
To optimise, we can create index like this way:
CREATE INDEX idx_emp
ON employee(department)
INCLUDE (salary);
Now the index includes:
the searchable key: `department`, the additional column: `salary`
Since both columns are in the index, it seems reasonable that PostgreSQL should never need to read the table again.
But there is one important piece of information missing.
The Information an Index Doesn't Store
An index stores:
- Index value
- Pointer (ctid) to heap table
It doesn’t store whether the row is visible to your transaction.
Remember PostgreSQL's MVCC architecture. Every row in the heap contains transaction metadata :
- which transaction inserted it
- whether another transaction deleted or updated it
- whether that version is visible to the current transaction
All these datas exists only in the heap table. Where index intentionally doesn’t stores it.
So even if the index contains every column your query needs, PostgreSQL still has one unanswered question:
"Is this row actually visible to this transaction?"
Without knowing the answer, returning the row directly from the index could produce incorrect results.That is why PostgreSQL traditionally follows the index pointer back to the heap.
The Visibility Map: PostgreSQL's Cheat Sheet
Then comes the visibility map. Instead of tracking every row individually, it tracks entire heap pages. For every heap page, PostgreSQL stores just two bits.
One of those bits means:
"Every row on this page is visible to every transaction."
If that bit is set, PostgreSQL does not need to visit the heap.It already knows that every row referenced from that page is safe to return.The index becomes sufficient. It is really compact.A single byte describes four heap pages.That means one small visibility map page can track roughly 256 MB of table data.Since it is very small, Postgresql stores it in memory.Checking the visibility map is therefore much cheaper than performing thousands of random heap reads.
What Happens During an Index-Only Scan?
When PostgreSQL performs an index-only scan, the process looks roughly like this:
1. Read the matching entry from the index.
2. Check the Visibility Map.
3. If the heap page is marked all-visible, return the row directly from the index.
4. Otherwise, visit the heap to verify row visibility.
This clarifies many of us doubt, why still heap fetch happens even though Index-Only scan.
Heap Fetches: 542
The scan is still an index-only scan.Those heap fetches simply represent the pages that the Visibility Map could not certify.
Where VACUUM Fits In
Now comes the interesting connection.
Who marks a page as all-visible?
VACUUM does.During vacuuming, PostgreSQL examines heap pages.If every row on a page is visible to all transactions and there are no dead tuples left behind, VACUUM sets the page's all-visible bit inside the Visibility Map.From that moment on, index-only scans can safely skip that heap page.
And Who Clears Those Bits?
Every write operation.
PostgreSQL immediately clears the page's all-visible bit. Because the page now contains changes that are not guaranteed to be visible to everyone.Until VACUUM checks the page again, PostgreSQL cannot trust it.
Why Autovacuum Makes Read Queries Faster
This leads to a common misconception.Many people think VACUUM only exists to clean up dead tuples and reclaim disk space.And now you understand autovacuum also restores the all-visible bits inside the Visibility Map.Without those bits, index-only scans lose their advantage over time.
Imagine two identical databases.
Database A:
- autovacuum runs regularly
- most pages remain all-visible
Database B:
- autovacuum rarely finishes
- most all-visible bits stay cleared
Both databases have exactly the same indexes.Both execute the same query.
Yet Database A performs mostly index-only reads, while Database B repeatedly visits the heap.Nothing about the index changed.Nothing about the query changed.
Only the Visibility Map changed.
Tasks
There are two practical lessons you can apply immediately.
1. Build Covering Indexes
An index-only scan is only possible if the index includes **every column** your query needs.
Suppose you frequently run:
SELECT salary
FROM employee
WHERE department = 'Sales';
Instead of creating:
CREATE INDEX idx_emp
ON employee(department);
create:
CREATE INDEX idx_emp
ON employee(department)
INCLUDE (salary);
Here:
- `department` is the searchable key.
- `salary` is stored only as additional data.
The index can now satisfy the query without returning to the heap—provided the Visibility Map allows it.
Using `INCLUDE` is usually better than making `salary` part of the key:
CREATE INDEX idx_emp
ON employee(department, salary);
because `salary` isn't helping PostgreSQL search. It only needs to be returned. Keeping it as an included column avoids unnecessarily enlarging the B-tree's searchable key.
2. Keep Autovacuum Healthy
If you notice execution plans like:
Index Only Scan using idx_emp
Heap Fetches: 8531
do not immediately blame the index.Your covering index may be perfectly designed.
Instead, check whether autovacuum is keeping the Visibility Map up to date.A large number of heap fetches during an index-only scan often indicates that many heap pages are not marked all-visible, forcing PostgreSQL to verify row visibility from the table.In many cases, the problem isn't your indexing strategy.It's a stale Visibility Map.
An index-only scan is one of PostgreSQL's smartest optimizations, but the name can be slightly misleading. It is capable of answering queries using only the index, yet whether it actually does so depends on one small companion structure: the Visibility Map.That's why indexes and VACUUM are more closely related than they may seem. A well-designed covering index gives PostgreSQL the chance to avoid the heap, while a healthy autovacuum gives it the confidence to do so.In PostgreSQL, fast reads aren't just about building the right index, they're also about keeping the Visibility Map healthy.