Index-Only Scans and the Visibility Map

An index-only scan reads the table. It reads it once per matching row whose heap page is not marked all-visible, which on a table taking ordinary write traffic is most of them. The plan node is not a promise that the heap was avoided — it is a statement that the heap could be avoided, and Heap Fetches in EXPLAIN (ANALYZE) is the count of times it was not.

The reason is one design decision: a B-tree entry stores the indexed values and a tuple identifier, and nothing about visibility. There is no xmin and no xmax in an index entry, so the index alone cannot tell you whether the row it points at exists as far as your snapshot is concerned. The visibility map — a separate fork of the table holding two bits per 8 kB heap page, one of which means "every row on this page is visible to every transaction, present and future" — is the way out. An index-only scan checks that bit for each candidate row. If it is set, the row is returned straight from the index. If it is clear, the executor reads the heap page and checks the row's xmin and xmax itself, which is exactly what a plain index scan does.

And the bit is set by one thing only: VACUUM. Every INSERT, UPDATE and DELETE clears it for the page it touches. So the speed of this query is a function of how long ago the table was vacuumed, which makes vacuum a query-performance feature rather than housekeeping.

The panel runs one query — SELECT customer_id, amount FROM orders WHERE customer_id BETWEEN $1 AND $2, served by CREATE INDEX ON orders (customer_id) INCLUDE (amount) — against a 20-million-row table of 312,500 heap pages. The slider that carries the lesson is minutes since the last vacuum. Drag it from 0 to 60 and watch Heap Fetches.

At minute 0 the answer is 0 heap fetches and the query is what the covering index promised. Twenty minutes and 1.2 million writes later, 97.9% of the pages this query needs have had their bit cleared and it does 9,760 heap fetches for 10,000 rows. Nothing about the plan changed. Nothing about the index changed. The table was written to.

The half-life of a visibility map

Set writes to 1,000 per second and watch the bar chart rather than any single number. It is not a gentle slope. Half the table's bits are gone after 3.6 minutes and 90% of them after 12, because writes land on random pages and the chance a given page has been missed after W writes to P pages is exp(-W/P). The 312,500 pages of this table are a small target for 60,000 writes a minute.

The consequence is that the useful question is never "is this table vacuumed?" but "what fraction of it is all-visible right now, and how long does that fraction survive?". A table taking 1,000 writes a second holds a useful map for about two minutes. Vacuum on the default settings visits it at best once a minute and realistically far less, so on a write-heavy table the honest expectation for an index-only scan is that most of its rows will be heap fetches most of the time. The feature is for tables that are predominantly read — the documentation says so in the phrase everybody skims, "for seldom-changing data there is a way around this problem."

Now the control that decides whether any of this matters: matching rows per heap page. At 1 — the query's rows scattered one to a page, which is what you get on any key uncorrelated with insertion order — 10,000 rows means 10,000 candidate pages, and at 20 minutes since the last vacuum 9,760 of them are read. Drag it to 64 and watch the wrong number stay still: Heap Fetches goes up, to 9,808, while heap pages read collapses from 9,760 to 154. The same 10,000 rows now live on 157 pages, so the executor asks the visibility question 10,000 times and is sent to the heap almost every time — but it is sent to the same page 64 times running, and 63 of those are a buffer-cache hit. The visibility map penalty you actually pay is proportional to how badly your index correlates with physical row order, which is why CLUSTER, append-ordered keys and partitioning by time all improve index-only scans without touching the visibility map at all.

Note also what the readouts do not say. Heap Fetches counts rows, not pages: the executor asks the visibility question once per index entry, and increments the counter every time the answer sends it to the heap, even when the previous entry sent it to the same page. With 64 matching rows per page you see 9,808 heap fetches from 154 page reads. That is why a large Heap Fetches number on a well-clustered index is much less alarming than the same number on a scattered one, and why you should read it alongside EXPLAIN (ANALYZE, BUFFERS)'s shared read count rather than on its own.

Why the planner picked a plan that no longer exists

The plan was not chosen against the visibility map. It was chosen against a number in a catalogue table.

When the planner costs an index-only scan it takes the number of heap pages the scan would fetch and multiplies it by 1 - allvisfrac, where allvisfrac is pg_class.relallvisible / relpages — a count written by the last VACUUM or ANALYZE and untouched between them. The map decays continuously; the catalogue does not. Ten minutes after a vacuum of this table, relallvisible still says 312,500 out of 312,500 while the real figure is 14.7%, so the planner discounts the heap access to zero and picks the index-only scan by a wide margin.

Leave minutes since the last vacuum at 20 and tick ANALYZE has run since. The estimate snaps to the truth — 10,000 expected heap fetches instead of 0 — and the cost readout stops lying. This is the mechanism behind the widely-reported oddity that VACUUM ANALYZE changes a plan when ANALYZE alone does not: ANALYZE refreshes the estimate, so the planner stops over-valuing the index-only scan; VACUUM refreshes the bits, so the index-only scan actually becomes cheap. They fix opposite halves and people run them together without noticing which one did the work.

The practical failure this produces is not "the index-only scan is slower than it could be". It is that the planner rejected a Bitmap Heap Scan it should have taken. A bitmap scan collects the tuple identifiers first, sorts them by block number and reads the heap in ascending physical order, which the kernel and the storage layer both reward. An index-only scan walks the heap in index order, which on a scattered index means 9,760 random reads. With the all-visible discount applied to a stale relallvisible, the index-only scan is costed as though it does none of them, so it wins a comparison it would have lost. The log's alternative line tracks exactly this: it tells you when the two plans have converged on the same number of page reads, at which point the ordering is the only difference and the index-only scan is the worse of the two.

Where vacuuming harder stops working

Tick an old transaction is pinning the xmin horizon. All-visible goes to 0% and stays there at every position of the vacuum slider.

Vacuum may set the all-visible bit on a page only when every row on it is visible to every transaction that could still be running — the same test that decides whether a dead row version can be removed at all, described in MVCC and what VACUUM does not do. A single REPEATABLE READ session left open, a session that wrote once and then went idle in transaction, an abandoned prepared transaction, or an inactive replication slot holds that horizon still. While it does, vacuum runs on schedule, reports success, and marks nothing. Every index-only scan in the database quietly becomes an index scan, and no amount of autovacuum tuning changes it, because the problem is not throughput.

This is the version of the failure that is hardest to diagnose, because the plans are unchanged, the vacuum log lines look healthy, and the only symptom is that every covering-index query in the application got two to ten times slower at the same moment. The query to run is the one that finds the horizon-holder, not the one that measures the table.

Two more boundaries. First, freshly loaded data: a bulk COPY or INSERT ... SELECT sets no visibility map bits, so a table you just loaded and indexed has an all-visible fraction of zero and index-only scans on it are worthless until something vacuums it. That is what autovacuum_vacuum_insert_threshold, added in PostgreSQL 13 with a default of 1,000 rows plus 20% of the unfrozen part of the table, exists to trigger — and it is why "run VACUUM after a bulk load" is advice about query plans and not only about statistics.

Second, the index still has to be able to answer the query. An index-only scan is only considered when every column the query references is available from the index, and the planner is literal about it: SELECT x FROM tab WHERE x = 'key' AND z < 42 cannot use an index on (x) even though only x is returned. Adding payload with INCLUDE fixes that, at the cost of a fatter index — the documentation's warning is worth quoting because it is the whole trade-off in one sentence: "there is little point in including payload columns in an index unless the table changes slowly enough that an index-only scan is likely to not need to access the heap." A wide covering index on a write-heavy table buys you more index to maintain and no heap savings.

Checking it on a real system

Start with the plan, and always with BUFFERS, which is on by default with ANALYZE from PostgreSQL 18 and must be asked for explicitly before that:

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, amount FROM orders WHERE customer_id BETWEEN 100 AND 200;

Index Only Scan using orders_customer_amount_idx on orders
  (cost=0.56..412.30 rows=10000 width=12)
  (actual time=0.048..212.774 rows=10000 loops=1)
  Index Cond: ((customer_id >= 100) AND (customer_id <= 200))
  Heap Fetches: 9790
  Buffers: shared hit=812 read=9034

Three numbers, read together. Heap Fetches near rows means the visibility map is not helping at all. cost=…412.30 against 212 ms of actual time is the planner having applied a discount that reality did not honour. And shared read in the thousands on a plan whose whole premise was that it would not touch the heap tells you where the time went.

Then measure the map directly rather than inferring it. The catalogue gives you the planner's view, which is the stale one:

SELECT relname, relpages, relallvisible,
       round(100.0 * relallvisible / nullif(relpages,0), 1) AS planner_pct,
       last_vacuum, last_autovacuum
FROM pg_class c JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE relname = 'orders';

And the pg_visibility extension gives you the true one, read out of the map fork as it stands this second:

CREATE EXTENSION IF NOT EXISTS pg_visibility;
SELECT * FROM pg_visibility_map_summary('orders');
--  all_visible | all_frozen
-- -------------+------------
--        44125 |      31002

A large gap between planner_pct and all_visible / relpages is the diagnosis on its own: the planner is costing plans against a table that no longer exists. If the true number is low and last_autovacuum is recent, you are looking at the write rate and you should be tuning the vacuum frequency for this specific table. If the true number is zero and vacuum has been running, stop and check the horizon before anything else:

SELECT pid, state, backend_xid, backend_xmin,
       age(coalesce(backend_xmin, backend_xid)) AS held_age,
       now() - xact_start AS open_for
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL OR backend_xid IS NOT NULL
ORDER BY held_age DESC NULLS LAST;

SELECT slot_name, active, age(xmin) FROM pg_replication_slots;

What to do about it, in order of how often it is the right answer. Lower autovacuum_vacuum_scale_factor on the specific table so the map is refreshed often enough to matter — this is one of the few cases where a very low scale factor is justified by something other than bloat, and on an insert-mostly table autovacuum_vacuum_insert_scale_factor is the one to lower. Improve the correlation between the index and physical row order, by CLUSTER for a one-off, by partitioning for something that stays true, or by choosing an index whose leading column is already correlated with insertion order. Accept the heap fetches and drop the INCLUDE columns, because a narrower index that never achieves an index-only scan is often faster than a wide one that does not either. And only then consider whether the query wanted a bitmap scan all along.

Next: the other consumer that can hold the xmin horizon still for weeks without anyone noticing — replication slots and the WAL they retain.