Index-only scan, and its heap fetches

An index-only scan answers a query from the index without reading the table, which it can do when every column the query needs is in the index. PostgreSQL indexes do not record whether a row is visible to the reading transaction, so the scan is only index-only where the visibility map says the whole heap page is visible to everyone. Where the map says nothing, the scan reads that heap page after all. The plan calls those reads heap fetches, and it counts them.

A plan node whose name overstates what happened

The thing that catches people is that the node is chosen by the planner and named before any of this is known. EXPLAIN without ANALYZE prints Index Only Scan whether the scan will touch the heap once or on every row, because the decision is a cost estimate based on pg_class.relallvisible, which vacuum refreshes and which is stale between passes. A plan that reads as optimal can be doing the work of an ordinary index scan plus the overhead of having hoped otherwise.

This produces a specific and repeatable incident. A table is loaded, vacuumed, and the query is fast. Writes resume, the visibility bits for the written pages are cleared, and nothing changes in the plan, the index or the statistics. The query gets slower in proportion to how scattered the writes were, and every artefact an operator normally looks at says the plan is unchanged. The only number that moved is the one that appears solely under EXPLAIN (ANALYZE).

It follows that an index-only scan is not a property of an index. It is a property of an index and a vacuum schedule together, which is why adding a covering column to an index sometimes produces nothing at all.

Reading the heap fetch counter

The same query, run either side of a vacuum, shows both states.

CREATE TABLE invoice (id int PRIMARY KEY, total numeric);
INSERT INTO invoice SELECT g, g FROM generate_series(1, 50000) g;
ANALYZE invoice;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
  SELECT count(*) FROM invoice WHERE id BETWEEN 1 AND 500;
VACUUM invoice;
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
  SELECT count(*) FROM invoice WHERE id BETWEEN 1 AND 500;

CREATE TABLE
INSERT 0 50000
ANALYZE
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   ->  Index Only Scan using invoice_pkey on invoice (actual rows=500 loops=1)
         Index Cond: ((id >= 1) AND (id <= 500))
         Heap Fetches: 500
(4 rows)

VACUUM
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   ->  Index Only Scan using invoice_pkey on invoice (actual rows=500 loops=1)
         Index Cond: ((id >= 1) AND (id <= 500))
         Heap Fetches: 0
(4 rows)

Before the vacuum the scan fetched one heap row for every row it returned, which is the worst case and is what a freshly loaded table always looks like. After it, none. The plan text either side is otherwise identical, which is the point. The capture above is from 14.24; later majors print the same two lines with extra decoration around them, and BUFFERS OFF is needed on 18 and later to keep the buffer counters out of the comparison.

Making the map keep up

A heap fetch count that stays high is a vacuum question rather than an index question, and the visibility map is the mechanism to understand before changing anything. For whether the index should exist in the shape it does, and what a covering column is worth against its write cost, the treatment is unused and missing indexes. If the plan itself changed rather than its cost, query plan regression is the other direction to search.