Index-Only Scan Eligibility Rules #

A covering index exists, the query selects only indexed columns, and the plan still shows Index Scan with heap access. Or it shows Index Only Scan with Heap Fetches: 4102110 — an index-only scan that touches the heap for nearly every row. Both are common, and each has a different cause among the three conditions an index-only scan must satisfy.

Designing covering indexes is covered in covering index design. This page is the checklist for why one is not used.

The Index Condition #

An index-only scan is possible when all three hold:

  1. Every column the query needs from the table comes from the index. That means columns in the SELECT list, WHERE, ORDER BY, GROUP BY, HAVING, join conditions and returned expressions — for the relation being scanned. Key columns and INCLUDE columns both count. A SELECT *, an unindexed column in a filter, or a column used only by a later join disqualifies it.
  2. The access method can return the indexed values. B-tree can. GiST can for some operator classes, SP-GiST for some, and GIN never can, because its entries are tokens rather than whole values. An index on an expression can satisfy a query that uses exactly that expression.
  3. The rows’ pages are marked all-visible. The executor checks the visibility map before trusting index-only data. Pages modified since the last vacuum are not marked, so their rows require a heap fetch to check visibility — reported as Heap Fetches.

Condition 3 is why index-only scans degrade on write-heavy tables and recover after VACUUM, the mechanism described in visibility map and index-only scan regressions.

The planner estimates the fraction of all-visible pages from pg_class.relallvisible and costs the expected heap fetches, so a table whose visibility map is stale may not even get an index-only plan.

Why is this not an Index Only Scan? Three checks in order. If any needed column is missing from the index, the plan uses a plain index scan. If the access method cannot return values, such as GIN, index-only scans are impossible. If the visibility map is stale, the plan may be an index-only scan but with many heap fetches. index-only scan not happening? a needed column is not in the index plain Index Scan add it as key or INCLUDE access method cannot return values no index-only scan GIN never can visibility map stale Heap Fetches high VACUUM the table all three must hold; the third one also degrades over time

Annotated EXPLAIN Evidence #

CREATE INDEX CONCURRENTLY orders_customer_placed_idx
  ON orders (customer_id, placed_at) INCLUDE (total);

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, placed_at, total
FROM orders WHERE customer_id = 4821 ORDER BY placed_at DESC LIMIT 20;

Eligible and healthy:

Limit  (actual time=0.03..0.04 rows=20 loops=1)
  ->  Index Only Scan Backward using orders_customer_placed_idx on orders
        (actual time=0.03..0.03 rows=20 loops=1)
        Index Cond: (customer_id = 4821)
        Heap Fetches: 0
        Buffers: shared hit=5
Execution Time: 0.06 ms

A column outside the index:

SELECT customer_id, placed_at, total, status FROM orders WHERE customer_id = 4821 …;
  ->  Index Scan Backward using orders_customer_placed_idx on orders  (actual rows=20 loops=1)
        Index Cond: (customer_id = 4821)
        Buffers: shared hit=24
-- status is not in the index: every row needs its heap tuple

Stale visibility map:

  ->  Index Only Scan Backward using orders_customer_placed_idx on orders
        (actual time=0.05..118.4 rows=20 loops=1)
        Index Cond: (customer_id = 4821)
        Heap Fetches: 18402
        Buffers: shared hit=18410 read=204
-- Heap Fetches ≫ rows returned: recently written pages are not marked all-visible
Three plans, three different causes Three plan fragments are annotated. Index Only Scan with Heap Fetches zero is the healthy case. A plain Index Scan on the same index means a needed column was missing. An Index Only Scan with heap fetches far above rows returned means the visibility map is stale. Index Only Scan … Heap Fetches: 0 healthy: no heap access Index Scan using the same index a needed column is missing Index Only Scan … Heap Fetches: 18402 visibility map stale Heap Fetches is the number of rows whose page was not all-visible

Step-by-Step Resolution #

  1. List every column the query touches for that table — including WHERE, ORDER BY and join conditions, not just the select list.

  2. Compare with the index definition.

    SELECT indexdef FROM pg_indexes WHERE indexname = 'orders_customer_placed_idx';

  3. Add missing columns. Columns used in conditions or ordering must be key columns; columns only returned can be INCLUDE columns, which keep the key small — the trade-off in INCLUDE columns vs composite keys.

  4. Check the access method. For GIN, an index-only scan is impossible; a B-tree on the same column, or a different query shape, is the alternative.

  5. Check visibility.

    SELECT relallvisible, relpages, last_vacuum, last_autovacuum
FROM pg_class c JOIN pg_stat_user_tables t ON t.relid = c.oid
WHERE relname = 'orders';

    A low relallvisible ÷ relpages ratio means most pages are not marked. VACUUM orders; updates it.

  6. Keep the ratio high on hot tables by tuning autovacuum for them — see per-table autovacuum settings for hot tables.

  7. Re-check Heap Fetches after vacuum; it should be close to zero for a stable table.

Cost of each failure mode A healthy index-only scan reads 5 buffers. The same query with one column outside the index reads 24 buffers through a plain index scan. With a stale visibility map the index-only scan reads 18,610 buffers. After vacuum it returns to 5 buffers. index-only, fresh vacuum 5 buffers extra column, Index Scan 24 buffers stale visibility map 18,610 buffers after VACUUM 5 buffers the same index can be excellent or useless depending on vacuum state

Before and After #

-- BEFORE: SELECT adds status, which is not in the index
Index Scan Backward using orders_customer_placed_idx  Buffers: shared hit=24        Execution Time: 0.14 ms

-- AFTER: INCLUDE (total, status)
Index Only Scan Backward using orders_customer_placed_idx  Heap Fetches: 0  Buffers: shared hit=5   Execution Time: 0.06 ms

When an index-only scan is not worth chasing #

Every column added to an index makes it larger, slower to maintain and more likely to be rewritten on updates. For a query returning twenty rows, the difference between five buffers and twenty-four is invisible to users. Index-only scans pay off when the row count is large — aggregates over millions of rows, range scans feeding joins, and counts — because there the heap fetches dominate. A useful rule: chase index-only scans for queries whose returned or scanned row counts are in the thousands or more, and accept plain index scans for point lookups.

There is also a subtlety with count(*). PostgreSQL can answer SELECT count(*) FROM t with an index-only scan of the smallest index, but it must still read every index entry and check visibility, so the query is proportional to the table size. It is faster than a sequential scan, not instant, and its speed depends entirely on how much of the visibility map is set.

Index-only scans on aggregates and joins #

The clearest wins come from queries that never return the rows they read. SELECT count(*) FROM orders WHERE customer_id = $1, SELECT max(placed_at) …, and semi-joins used only to test existence all read many index entries and need no table columns at all — provided every condition is in the index. Those are worth designing covering indexes for even when the result is a single number, because the alternative reads one heap page per matching row. The same applies to the inner side of a nested loop: an index-only probe avoids a heap page per outer row, which at high loop counts is the difference between a fast plan and an I/O-bound one.

Common Pitfalls #

Forgetting filter and join columns. They must be in the index too. Diagnostic signal: plain index scan despite a covering select list. Fix: add them, as keys if they are used in conditions.

SELECT * in ORMs. It references every column. Diagnostic signal: no index-only scans anywhere in an application. Fix: narrow projections for hot queries.

Expecting index-only scans from GIN. Not supported. Diagnostic signal: Bitmap Heap Scan always present for JSONB or full-text queries. Fix: different index type or accept heap access.

Vacuum starvation on hot tables. Heap fetches climb between vacuums. Diagnostic signal: query time rising through the day, dropping after autovacuum. Fix: per-table autovacuum tuning.

Frequently Asked Questions #

Why is PostgreSQL not using an index-only scan with my covering index? #

Some column the query needs is not in the index, the access method cannot return values, or the table’s visibility map is stale enough that the planner expects heap fetches for most rows.

What does Heap Fetches mean in an Index Only Scan? #

It counts rows whose heap page was not marked all-visible, so the executor had to read the heap tuple to check visibility. A high count means the table needs vacuuming more often.

Can GIN indexes support index-only scans? #

No. GIN stores keys extracted from values rather than the values themselves, so it cannot return the original column data required for an index-only scan.

Up: Covering Index Design