Index-Only Scans, Explained With Real Query Plans

There is a special kind of fast in PostgreSQL where a query answers itself entirely from an index and never reads the table at all. It shows up in EXPLAIN as Index Only Scan, and when you first see one replace a regular Index Scan on a hot query, the latency drop can feel like cheating.

It is not cheating, and it is not automatic. Index-only scans depend on a piece of bookkeeping most people never think about: the visibility map. If you want them reliably, you have to understand what the database is actually deciding when it picks between reading just the index and reading the index plus the table.

What a normal index scan really does

A regular index scan is a two-step dance. First, walk the B-tree to find matching entries. Second, for each match, follow the pointer to the actual row in the table heap to read the columns you asked for and to check whether that row version is visible to your transaction.

That second step is the expensive one. Each heap fetch is potentially a random page read, and there can be one per matching row.

CREATE TABLE events (
  id          bigserial PRIMARY KEY,
  user_id     bigint NOT NULL,
  event_type  text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);
 
CREATE INDEX idx_events_user ON events (user_id);
 
EXPLAIN ANALYZE
SELECT user_id FROM events WHERE user_id = 1234;

                            QUERY PLAN
------------------------------------------------------------------
 Index Scan using idx_events_user on events
   (cost=0.43..412.55 rows=210 width=8)
   Index Cond: (user_id = 1234)
   actual time=0.028..1.984 rows=204 loops=1
 Planning Time: 0.121 ms
 Execution Time: 2.041 ms

Notice the irony: we only selected user_id, which is already in the index, yet Postgres still went to the heap. Why? Because the index entry alone cannot tell whether a given row version is visible under MVCC. The heap visit is partly about visibility, not just data.

Enter the index-only scan

If the index contains every column the query needs, Postgres can skip the data retrieval from the heap. But it still has the visibility problem. The solution is the visibility map: a compact bitmap, one or two bits per heap page, recording whether all tuples on that page are visible to all transactions.

When the index entry points to a page that the visibility map marks all-visible, Postgres trusts the index and skips the heap entirely. That is an index-only scan. To make our query qualify, the index needs to actually cover it:

-- A covering index: the query's columns all live in the index
CREATE INDEX idx_events_user_covering
  ON events (user_id, created_at);
 
EXPLAIN ANALYZE
SELECT user_id, created_at
FROM events
WHERE user_id = 1234;

                            QUERY PLAN
------------------------------------------------------------------
 Index Only Scan using idx_events_user_covering on events
   (cost=0.43..28.31 rows=210 width=16)
   Index Cond: (user_id = 1234)
   Heap Fetches: 0
   actual time=0.021..0.214 rows=204 loops=1
 Planning Time: 0.140 ms
 Execution Time: 0.258 ms

Two things changed. The node is now Index Only Scan, and there is a new line: Heap Fetches: 0. That zero is the whole game. We answered the query without a single heap visit, and execution time dropped roughly tenfold.

The Heap Fetches line is the truth-teller

Heap Fetches is non-negotiable reading. An index-only scan that still reports thousands of heap fetches is barely an index-only scan at all. That happens when the visibility map is stale, typically right after a burst of writes, because pages that have been modified are not yet marked all-visible.

 Index Only Scan using idx_events_user_covering on events
   Index Cond: (user_id = 1234)
   Heap Fetches: 198       <-- visibility map is stale; still hitting the heap

The visibility map is maintained by VACUUM. Until vacuum (or autovacuum) processes recently-changed pages, those pages are not marked all-visible, and any index-only scan touching them falls back to heap fetches for safety.

Covering indexes with INCLUDE

Sometimes you want extra columns available for index-only scans without making them part of the searchable key. PostgreSQL supports INCLUDE for exactly this: the included columns are stored in the index leaves but do not participate in ordering or uniqueness.

-- email is searchable; full_name and signup_source ride along
-- only so reads can be satisfied without touching the heap
CREATE INDEX idx_users_email_covering
  ON users (email) INCLUDE (full_name, signup_source);
 
EXPLAIN ANALYZE
SELECT full_name, signup_source
FROM users
WHERE email = '[email protected]';

This keeps the key narrow (good for the B-tree's structure and for uniqueness semantics) while still letting reads avoid the heap. The official CREATE INDEX documentation describes INCLUDE and the trade-offs in detail, and the broader index-only scans notes explain the visibility-map dependency.

When index-only scans will not help you

They are not a universal win. A few honest limits:

• Wide result sets. If you select many columns, your covering index becomes nearly as wide as the table, costing storage and slowing writes. Past a point, just read the heap.
• Write-heavy tables. Constant churn keeps the visibility map perpetually stale, so the scans keep falling back to heap fetches. The benefit is largest on read-mostly tables.
• Expression or partial mismatches. If the query needs a column or expression the index does not carry, you are back to a regular index scan.

Markus Winand's Use The Index, Luke frames covering indexes well: they are a deliberate trade of write cost and storage for read speed, justified by query frequency, not applied everywhere.

Reading the plan like a pro

Your checklist when chasing an index-only scan:

1. Confirm the node says Index Only Scan, not Index Scan.
2. Confirm Heap Fetches: 0, or close to it. A high number means the optimization is not actually firing.
3. If heap fetches are high on a read-mostly table, run VACUUM and re-check. If they stay high on a write-heavy table, accept that this table may not be a good candidate.
4. Make sure the covering index is worth its write and storage cost for how often the query runs.

Takeaways

• An index-only scan answers a query straight from the index, skipping the heap entirely, but only when the index covers every column the query needs.
• Visibility under MVCC normally forces a heap visit; the visibility map is what lets Postgres skip it for all-visible pages.
• Heap Fetches: 0 in EXPLAIN ANALYZE is the signal the optimization is truly working; a high count usually means autovacuum is behind.
• Use INCLUDE to add payload columns to an index without bloating its key.
• The technique shines on read-mostly tables and narrow result sets; it offers little on write-heavy churn or wide selects.