Index-only scan architecture, in depth

Two questions every index lookup answers

Any index scan has to answer two questions for each matching entry. First, what are the column values? Second, is this row version visible to my snapshot? An ordinary index scan answers both by following the entry's pointer to the table row. In PostgreSQL that pointer is a TID, a heap block number plus an item offset, and following it is usually a random page read.

A covering index answers the first question: if every column the query reads is stored in the index, the values are already in hand. The second question is the hard one. Under MVCC, an index points at row versions, and several versions of a row can coexist, some committed, some deleted, some from transactions still running. PostgreSQL's index entries carry no transaction information at all, so the index alone cannot say whether an entry is visible. Something else has to.

The visibility map

PostgreSQL keeps a visibility map for every table: a small fork with bits per heap page. The all-visible bit says every tuple on that page is visible to all current and future transactions. The all-frozen bit, used by anti-wraparound vacuum, says every tuple is also frozen. Because the map holds a couple of bits per 8 KB page, it is orders of magnitude smaller than the table and usually stays in cache.

Two rules govern the all-visible bit. In normal operation only VACUUM sets it (loading a new or truncated table with COPY ... FREEZE is the main exception), and only after confirming that every tuple on the page is committed and older than the oldest snapshot any session could still hold. Any change to the page clears it: an insert, an update (including a HOT update that stays on the same page) or a delete. So the bit describes pages, not rows, and one new row on a page is enough to lose the shortcut for every other row on it.

The executor loop is short. Values always come from the index; the heap is consulted only to answer the visibility question.

# PostgreSQL index-only scan, simplified
for entry in index.scan(conditions):          # walk matching leaf entries
    page = entry.tid.block
    if not visibility_map.all_visible(page):  # one bit, usually cached
        tuple = heap.fetch(entry.tid)         # random I/O: a "heap fetch"
        heap_fetches += 1
        if not tuple.visible_to(snapshot):
            continue                          # dead or not yet committed for us
    emit(entry.values)                        # columns come from the index

EXPLAIN ANALYZE reports the counter as Heap Fetches. It is the single most important number for this plan: zero means the scan was truly index-only, and a value close to the row count means you paid for an index-only plan and got an index scan.

What the planner needs before it will choose the plan

The planner considers an index-only scan only when several conditions hold, and the PostgreSQL documentation is explicit about each of them.

• The index type must return values. B-tree indexes always can. GiST and SP-GiST can for some operator classes. GIN cannot, because an entry holds only part of the original value, and hash and BRIN cannot either.
• Every column the query references must be in the index. That includes columns in the select list, WHERE, ORDER BY and join conditions.
• Expressions are a known blind spot. With an index on f(x) and a query that only needs f(x), the planner still wants column x itself and concludes an index-only scan is impossible. The documented workaround is to add x as an included column.
• Partial index predicates are fine. A partial index WHERE success can serve an index-only scan even though success is not stored, because every entry already satisfies the predicate and nothing needs rechecking.

Costing matters as much as eligibility. The planner reads pg_class.relallvisible, the count of all-visible pages recorded by the last VACUUM or ANALYZE, and estimates how many heap fetches the scan will need. A table that is mostly not all-visible makes the index-only plan look little better than an index scan, and the planner may choose something else entirely. Stale statistics push it the other way: the map may have improved, but the estimate has not.

Covering with INCLUDE

Before INCLUDE, covering a query meant adding its columns as extra key columns, which widens every inner page, changes the sort order and can break a unique constraint's meaning. The INCLUDE clause stores non-key payload columns in the leaf entries only. Suffix truncation removes them from upper B-tree levels, so they never guide the search and do not bloat the inner pages. A unique index stays unique on its key columns while carrying payload.

-- Uniqueness on (email); name and plan ride along for index-only reads
CREATE UNIQUE INDEX CONCURRENTLY users_email_cov
    ON users (email) INCLUDE (display_name, plan);

Currently B-tree, GiST and SP-GiST support included columns, and expressions are not allowed as included columns. Be conservative with width. Payload duplicates table data, every write to those columns now writes the index too, updates to an included column can no longer be HOT updates, and an entry that exceeds the index tuple size limit makes the insert fail outright. Include narrow, frequently read columns; never include a large text or JSON column just to win one plan.

How InnoDB does the same job

MySQL's InnoDB reaches covering reads from the other direction. Tables are clustered on the primary key, and every secondary index entry stores the primary key columns as its row pointer. A query that reads only secondary-index columns plus the primary key is covered, and EXPLAIN shows Using index in the Extra column.

InnoDB also lacks per-entry visibility in secondary indexes, so it has its own page-level shortcut. Each secondary index page records the largest transaction id that modified it. If that id is older than everything the reader's snapshot might consider in progress, every entry on the page is visible and the read is served from the index. Otherwise InnoDB looks up the clustered index record, and possibly its undo history, to reconstruct the correct version. Secondary entries are delete-marked rather than changed in place, and purge removes them later. The analogy to PostgreSQL is direct: a recently modified page loses the shortcut, and long-running transactions that delay purge keep it lost.

Worked example: the recent-data trap

A multi-tenant events table receives a steady stream of inserts. A dashboard asks how many events of each kind a tenant produced in the last seven days.

CREATE TABLE events (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id  int         NOT NULL,
    created_at timestamptz NOT NULL,
    kind       text        NOT NULL,
    payload    jsonb
);

CREATE INDEX CONCURRENTLY events_tenant_time_kind
    ON events (tenant_id, created_at) INCLUDE (kind);

EXPLAIN (ANALYZE, BUFFERS)
SELECT kind, count(*)
FROM events
WHERE tenant_id = 42 AND created_at >= now() - interval '7 days'
GROUP BY kind;

The plan is an Index Only Scan on the new index, yet Heap Fetches is close to the number of rows returned. The index is right. The data is the problem: the last seven days live on the most recently written heap pages, and with the default settings autovacuum has not visited them since they were filled, so their all-visible bits were never set. The query reads exactly the part of the table the map knows least about.

Before PostgreSQL 13 an insert-only table was vacuumed mainly for wraparound, because there were no dead tuples to trigger it. PostgreSQL 13 added insert-driven autovacuum, controlled by autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor. The scale factor is a fraction of the table, which on a large table means a long wait. Tune it per table so vacuum follows the insert front closely:

ALTER TABLE events SET (
    autovacuum_vacuum_insert_scale_factor = 0.01,
    autovacuum_vacuum_scale_factor        = 0.02
);

-- Check how much of the table the map covers
SELECT relpages, relallvisible,
       round(100.0 * relallvisible / greatest(relpages, 1), 1) AS pct_all_visible
FROM pg_class WHERE relname = 'events';

-- Exact counts, if the extension is available
CREATE EXTENSION IF NOT EXISTS pg_visibility;
SELECT * FROM pg_visibility_map_summary('events');

After vacuum catches up, everything older than the last pass becomes all-visible and heap fetches fall to the rows on the few pages written since. The very newest pages will always need fetches; the goal is to make that a thin slice, not the whole range. If the dashboard can tolerate it, querying up to the start of the current hour instead of now() shrinks the slice further.

Operating index-only scans

Treat the visibility map as part of the index's health and monitor it. This small check flags tables whose hot plans depend on index-only scans but whose map coverage is slipping, and shows whether inserts are outrunning vacuum.

import psycopg

WATCH = ["events", "users"]
SQL = '''
SELECT c.relname, c.relpages, c.relallvisible,
       s.n_ins_since_vacuum, s.n_dead_tup, s.last_autovacuum
FROM pg_class c JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relname = ANY(%s)
'''

with psycopg.connect("dbname=app") as conn:
    for name, pages, allvis, ins, dead, last in conn.execute(SQL, (WATCH,)):
        pct = 100.0 * allvis / max(pages, 1)
        if pct < 90:
            print(f"WARN {name}: {pct:.1f}% all-visible, "
                  f"{ins} inserts since vacuum, {dead} dead, last autovacuum {last}")

• Capture plans in production. Use auto_explain with log_analyze on slow statements so Heap Fetches shows up where the real data lives, not on a freshly vacuumed staging copy.
• Watch the xmin horizon. VACUUM cannot mark a page all-visible while any snapshot could still see an older state. Long-running transactions, abandoned sessions idle in transaction, stale replication slots and hot_standby_feedback from a replica running long reports all hold the horizon back. Bits stop being set across the whole cluster, not just one table.
• Check after bulk changes. A large update or backfill clears bits on every page it touches. Run VACUUM on the table afterwards instead of waiting for autovacuum, then ANALYZE so the planner sees the new relallvisible.

Failure modes

• Index-only in name only. The plan node is right and heap fetches are high, usually on hot, recent or frequently updated pages.
• Plan flips. relallvisible drops after a busy period, the estimated cost rises and the planner switches to a different plan mid-day.
• One extra column. A developer adds a column to the select list, the index no longer covers it, and the query quietly becomes a normal index scan. Add plan checks to the tests for queries that depend on coverage.
• Write amplification. Wide INCLUDE lists make every write heavier and turn HOT updates into full index updates. The read win can cost more than it saves.
• Replica surprises. A standby can only use the visibility map that recovery has replayed from the primary, so coverage there follows vacuum on the primary.

Trade-offs

What to do next

1. List the five most frequent read queries on your largest tables and note which ones could be covered.
2. Run them with EXPLAIN (ANALYZE, BUFFERS) on production-shaped data and record Heap Fetches against rows returned.
3. Compute relallvisible / relpages for those tables; anything under about 90 percent deserves a look at autovacuum settings and the xmin horizon.
4. For insert-heavy tables on PostgreSQL 13 or later, lower autovacuum_vacuum_insert_scale_factor per table and confirm heap fetches fall.
5. Prefer narrow INCLUDE columns over extra key columns, and measure write latency before and after.
6. Add a regression check that fails if a coverage-dependent query stops producing an index-only plan.