· Development

Why a PostgreSQL Index Only Scan still reads the table: the visibility map and Heap Fetches

PostgreSQL index-only scan requirements, how the visibility map avoids MVCC heap checks, and INCLUDE covering index tradeoffs in PostgreSQL 18, plus diagnosis via Heap Fetches and relallvisible.

DevelopmentDatabasesPostgreSQL

Sometimes EXPLAIN ANALYZE prints Index Only Scan, and the Heap Fetches line right under it is far from zero. The name suggests that only the index gets read, but a PostgreSQL index-only scan will go to the heap, the table’s main storage, whenever it needs to. Whether it can skip the heap depends less on how the index is defined and more on the state of the table’s visibility map.

This article follows the documentation for PostgreSQL 18 (minor release 18.6), the newest stable release as of October 27, 2026. On that date PostgreSQL 19 was at RC 1 and did not yet appear in the release table.

The two basic requirements

The documentation lists two basic requirements for an index-only scan.

  1. The index type must support index-only scans. B-tree always does. GiST and SP-GiST support them for some operator classes but not others. Other index types have no support.
  2. The query must reference only columns stored in the index.

“Reference” in the second rule covers the WHERE clause, not just the output columns. Taking the documentation’s own example: for a table with columns x, y, and z and an index on (x, y), these two queries can use an index-only scan:

SELECT x, y FROM tab WHERE x = 'key';
SELECT x FROM tab WHERE x = 'key' AND y < 42;

These two cannot, because z is not in the index:

SELECT x, z FROM tab WHERE x = 'key';
SELECT x FROM tab WHERE x = 'key' AND z < 42;

Indexes carry no visibility information

Meeting both requirements is not the end of it. Every table scan in PostgreSQL has to check that each row it retrieves is visible to the query’s MVCC snapshot. That visibility information lives only in heap entries, not in index entries. The documentation points out that at first glance this seems to require a heap access for every row, and that this is indeed the case for rows that were modified recently. Which row version a snapshot sees is covered in READ COMMITTED and snapshots.

The visibility map is what reduces those checks. For each heap page, PostgreSQL records in a single bit whether every row on that page is old enough to be visible to all current and future transactions. After an index-only scan finds a candidate index entry, it checks the bit for the corresponding heap page.

Visibility map bit What the index-only scan does
Set (all-visible) The row is known to be visible, so it returns the value from the index without reading the heap
Not set It visits the heap entry to check visibility. In the documentation’s words, no performance advantage is gained over a standard index scan in this case

Even when the bit is set, the work is not free. The scan trades heap accesses for visibility map accesses. According to the documentation, the visibility map is four orders of magnitude smaller than the heap it describes, so it needs far less physical I/O and in most situations stays cached in memory. The documentation sums it up this way: an index-only scan is possible once the two basic requirements are met, but it is a win only if a significant fraction of the table’s heap pages have their all-visible bits set.

VACUUM sets the bits, data changes clear them

The visibility map keeps two bits per heap page. The first means the page is all-visible, in other words it holds no tuples that need to be vacuumed, and this is the bit index-only scans use. The second means every tuple on the page has been frozen. The map is stored as a separate fork next to the table file with a _vm suffix, and indexes do not have visibility maps.

The documentation (section 66.4) puts the update rule in one sentence: visibility map bits are only set by vacuum, but are cleared by any data-modifying operation on a page. The map is also conservative. When a bit is set, the condition is known to be true, but an unset bit does not mean the condition is false.

A few things follow from that rule. The points below are deductions from the documented rule, not sentences found in the documentation.

  • A table that was just bulk loaded may have few all-visible pages until a vacuum runs over it, and index-only scans may visit the heap often in the meantime.
  • Updates scattered across the table leave pages with cleared bits scattered across it too. Changing a single row on a page is enough to affect the whole page.
  • Even a HOT update, which avoids new index entries, still modifies the heap page, so by the documented wording it clears that page’s bit.

Insert-only tables get vacuumed as well. The routine vacuuming documentation (section 24.1.6) says autovacuum runs when the number of tuples inserted since the last vacuum exceeds the insert threshold, defined as:

vacuum insert threshold = vacuum base insert threshold
                        + vacuum insert scale factor * number of tuples * percent of table not frozen

The base part is autovacuum_vacuum_insert_threshold, the scale factor is autovacuum_vacuum_insert_scale_factor, the number of tuples is pg_class.reltuples, and the fraction not frozen is 1 - pg_class.relallfrozen / pg_class.relpages. The documentation says such vacuums may allow portions of the table to be marked all-visible. It does not say this setting exists for the sake of index-only scans, so connecting the two is an interpretation that combines the two rules above.

Covering indexes with INCLUDE

A covering index is one designed to hold every column a frequently run query needs. To carry columns that are not part of the search key, use the INCLUDE clause. With an orders table, for example:

-- A query that runs often
SELECT created_at, total FROM orders WHERE customer_id = 42;

-- customer_id is the only search key; created_at and total are payload
CREATE INDEX orders_customer_id_inc
    ON orders (customer_id) INCLUDE (created_at, total);

The CREATE INDEX documentation describes INCLUDE (non-key) columns like this:

  • They cannot be used in an index scan search qualification. The index above does not help a search on WHERE created_at > ... alone.
  • They are disregarded by any uniqueness or exclusion constraint the index enforces. With CREATE UNIQUE INDEX ... (x) INCLUDE (y), uniqueness applies to x alone, not to the combination of x and y. INCLUDE can also be attached to UNIQUE and PRIMARY KEY constraints.
  • Because they are not search keys, they may be of a data type the index could not otherwise handle.
  • Currently only B-tree, GiST, and SP-GiST support them.
  • Expressions are not allowed, because they cannot be used in index-only scans.
  • An index can have up to 32 columns, INCLUDE columns included (this limit can be changed when building PostgreSQL).

Before INCLUDE existed, payload columns were appended as ordinary key columns, as in (x, y). That approach cannot enforce uniqueness on the key columns alone. The documentation also mentions that B-tree suffix truncation removes non-key columns from upper levels, and notes that declaring payload columns explicitly as non-key columns reliably keeps the tuples in upper levels small.

The costs the documentation names

The documentation advises being conservative about adding non-key payload columns, especially wide ones, for two reasons.

  • If an index tuple exceeds the maximum size allowed for the index type, data insertion will fail.
  • Non-key columns duplicate data from the table and bloat the index, which can slow down searches.

Then comes a sentence that ties back to the visibility map: there is little point in including payload columns unless the table changes slowly enough that an index-only scan is likely to not need to access the heap. One way to read this is that a modified row produces a new row version and may need its index entries rewritten, so wide INCLUDE columns on a frequently changing table can add cost without much benefit.

If you plan to build such an index on a live table, the CREATE INDEX CONCURRENTLY article covers how to do it without blocking writes, and what that costs.

Expression indexes and partial indexes

In principle, index-only scans can work with expression indexes, but the documentation says the planner is currently not very smart about such cases. The planner treats a query as a candidate for an index-only scan only when all the columns the query needs are available from the index. In SELECT f(x) FROM tab WHERE f(x) < 1;, the column x itself is not in the index, so the query is not a candidate. The documented workaround is to add the underlying column with INCLUDE:

CREATE INDEX tab_f_x ON tab (f(x)) INCLUDE (x);

Partial indexes have an exception in the other direction. With a partial index built on WHERE success, the success column is not stored in the index, yet every entry in it has success = true, so a query with WHERE subject = ... AND success can still use an index-only scan. According to the documentation, PostgreSQL 9.6 and later recognize this case.

Diagnosis: check the planner’s choice and runtime heap visits separately

The key here is not to mix two questions. Whether the planner chooses an index-only scan and how often the execution actually visited the heap are confirmed by different evidence.

1) Did the planner choose it? EXPLAIN

Plain EXPLAIN does not run the query; it only shows the plan. An Index Only Scan using ... node in the plan means the planner picked that method, not that execution skipped the heap. The planner works from estimates such as pg_class.relallvisible, and it may pick a different plan even when a covering index exists.

2) Did execution visit the heap? EXPLAIN ANALYZE

EXPLAIN ANALYZE actually runs the query. The documentation warns that the output is discarded but every other side effect of the statement happens as usual, and suggests running data-modifying statements between BEGIN and ROLLBACK. In PostgreSQL 18, ANALYZE turns on BUFFERS automatically. Here is the output shown in the Using EXPLAIN documentation (section 14.1):

 Index Only Scan using tenk1_four_unique1_idx on tenk1  (cost=0.29..6.90 rows=1 width=8) (actual time=0.006..0.007 rows=1.00 loops=1)
   Index Cond: ((four >= 1) AND (four <= 3) AND (unique1 = 42))
   Heap Fetches: 0
   Index Searches: 3
   Buffers: shared hit=7

It is natural to read Heap Fetches as the number of times the index-only scan visited the heap during this execution. However, the two EXPLAIN documentation pages checked for this article only show the line in an example and do not define it, so that reading is a deduction from the node name and the visibility map explanation. If the value is large relative to the rows returned, the scan likely ran into many pages that were not all-visible. The value can change from run to run, so it is safer not to draw conclusions from a single result.

3) Table state: pg_class and the statistics views

SELECT relname,
       relpages,
       relallvisible,
       round(100.0 * relallvisible / nullif(relpages, 0), 1) AS all_visible_pct
FROM pg_class
WHERE oid = 'orders'::regclass;

In the documentation’s words, relpages and relallvisible are “only an estimate used by the planner.” They are updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX, so changes made after the last update are not reflected.

SELECT relname,
       n_ins_since_vacuum,
       n_dead_tup,
       last_vacuum,
       last_autovacuum
FROM pg_stat_all_tables
WHERE relid = 'orders'::regclass;

n_ins_since_vacuum is the estimated number of rows inserted since the table was last vacuumed, and last_autovacuum is the last time the table was vacuumed by the autovacuum daemon. At the index level, pg_stat_all_indexes offers idx_tup_read (index entries returned by scans on the index) and idx_tup_fetch (live table rows fetched by simple index scans using the index). The documentation explains that the latter can be smaller when heap fetches are avoided by an index-only scan. Dead or not-yet-committed rows and bitmap scans also affect the gap, though, so these numbers alone cannot pin down the cause.

4) The visibility map as it is now: pg_visibility

To look at the current visibility map rather than an estimate, use the pg_visibility module.

CREATE EXTENSION pg_visibility;
SELECT * FROM pg_visibility_map_summary('orders');

pg_visibility_map_summary returns the number of all-visible pages and all-frozen pages according to the visibility map. For per-block output, use pg_visibility_map('orders'). By default these functions can be executed only by superusers and roles with privileges of the pg_stat_scan_tables role. The same module’s pg_truncate_visibility_map clears the map when corruption is suspected, and until the next VACUUM rescans every page, queries treat the map as all zeroes. Diagnosis has no use for it.

Limits and caveats

  • A covering index does not guarantee an index-only scan. The planner may choose another plan, and even when it chooses one, pages that are not all-visible still send it to the heap.
  • Results depend on how the table changes and when vacuum runs. The same query can show different Heap Fetches right after a vacuum and right after a large batch of updates.
  • relallvisible and the statistics view values are estimates, and pg_visibility shows only the state at the moment you run it.
  • Running VACUUM manually, changing autovacuum settings, and adding wide INCLUDE columns all affect I/O, storage, and write cost. This article does not recommend any particular change. Before making one, check it against your actual workload and the documentation for your version.
  • This article is based on the PostgreSQL 18 documentation. For example, ANALYZE enabling BUFFERS automatically and the relallfrozen column were confirmed in this version’s documentation, so check the documentation for your version if you run an older one.

References

Links and content were checked on October 27, 2026.

Back to the blog