Covering Indexes With INCLUDE for Index-Only Scans

An index-only scan is the fastest read PostgreSQL will ever give you: it answers the query from the index alone and never touches the table. The trick to getting one on a query that returns more than the key columns is INCLUDE — and the trap is that it quietly stops working when autovacuum falls behind.

Most advice about Postgres indexing stops at "add an index on the column in your WHERE clause". That gets you an index scan: Postgres walks the B-tree to find the matching rows, then goes back to the heap — the actual table pages — to fetch the columns you selected. Those heap fetches are random I/O, and on a hot query they are the cost that matters. A covering index removes them. This post builds one with INCLUDE on PostgreSQL 17, confirms the index-only scan with EXPLAIN, and is honest about the one dependency that will bite you.

What an index-only scan actually is

Every index scan has two phases: find the rows in the index, then fetch the selected columns from the heap. Postgres can skip the second phase — an index-only scan — only when two conditions hold. First, every column the query touches (in the SELECT list, the WHERE clause, and any ORDER BY) is present in the index. Second, the row is known to be visible without consulting the heap, which is where the visibility map comes in. Get both right and the query is served entirely from index pages already sitting in shared buffers.

The obvious way to cover a query is a composite index on all the columns: (customer_id, status, total_amount). That works, but it makes status and total_amount part of the B-tree key. They get compared and sorted on every insert and update, they inflate every internal page of the tree, and they can only be appended after the columns you actually search on. INCLUDE gives you a cleaner tool.

Building the covering index with INCLUDE

The INCLUDE clause, available for B-tree indexes since PostgreSQL 11, appends non-key columns to the index. They are stored only in the leaf pages, not in the internal tree, and they play no part in searching, sorting or uniqueness — they are along for the ride purely so a scan can return them without a heap fetch. Take a typical orders table and a query that reads two columns for a given customer:

-- The hot query we want to serve from the index alone
SELECT status, total_amount
FROM   orders
WHERE  customer_id = 42;

-- Key column is what we search on; INCLUDE carries the payload
CREATE INDEX idx_orders_customer_covering
    ON orders (customer_id)
    INCLUDE (status, total_amount);

The B-tree is ordered by customer_id alone. status and total_amount ride in the leaf nodes. Because they are not key columns, you can still add a uniqueness constraint on the key without the payload columns interfering — CREATE UNIQUE INDEX ... (order_id) INCLUDE (status) enforces uniqueness on order_id only, which is exactly what you want and what a plain composite unique index cannot express.

Put the statement in a migration, not a psql session, so the change is reviewed in the same pull request as the code that depends on it. In production, add CONCURRENTLY so the build does not hold a lock that blocks writes for the duration:

CREATE INDEX CONCURRENTLY idx_orders_customer_covering
    ON orders (customer_id)
    INCLUDE (status, total_amount);

Confirming the index-only scan with EXPLAIN

Do not assume the planner used the index the way you intended — make it prove it. Run EXPLAIN (ANALYZE, BUFFERS) and read two things: the node type and the Heap Fetches line.

EXPLAIN (ANALYZE, BUFFERS)
SELECT status, total_amount
FROM   orders
WHERE  customer_id = 42;

-- Plan (values are illustrative of the shape, not a benchmark):
Index Only Scan using idx_orders_customer_covering on orders
    (actual time=0.024..0.031 rows=6 loops=1)
  Index Cond: (customer_id = 42)
  Heap Fetches: 0
  Buffers: shared hit=4

Two things confirm success. The node says Index Only Scan, not Index Scan — the planner believes it can answer from the index. And Heap Fetches: 0 confirms it actually did, on this run, without going back to the table. That second line is the one people miss. A plan can say "Index Only Scan" and still report thousands of heap fetches, because the node type is the planner's intent and heap fetches are the runtime reality. If Heap Fetches is high, the scan is index-only in name only, and you are paying for the random I/O you thought you had removed.

The visibility-map dependency nobody warns you about

Here is why heap fetches creep back in. An index entry does not, on its own, tell Postgres whether a row is visible to the current transaction — MVCC means a row can be dead, or newer than your snapshot. To skip the heap safely, Postgres consults the visibility map, a compact bitmap with one bit per heap page marking whether every tuple on that page is visible to all transactions. If the bit is set, the scan trusts the index and moves on. If it is not, the scan must visit the heap page to check — and that is a heap fetch.

The visibility map is maintained by VACUUM, which in normal operation means autovacuum. On a table taking a steady stream of inserts and updates, freshly written pages are not yet marked all-visible, so a covering index over hot, recently-written rows can still generate heavy heap fetches until autovacuum catches up. The covering index does not fail — it just degrades to something close to an ordinary index scan on exactly the rows you care about most.

The fix is operational, not structural. Keep autovacuum keeping pace on high-churn tables by tightening its thresholds so it runs sooner:

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_insert_scale_factor = 0.02
);

Then watch the outcome, not the setting. Re-run the EXPLAIN (ANALYZE, BUFFERS) under representative load and confirm Heap Fetches stays low. This is a case where you measure before and after rather than trust the plan — the same discipline that makes instrumenting your delivery metrics worthwhile applies to a query plan.

When INCLUDE earns its keep, and when a plain index is enough

A covering index is not free. Every included column is copied into the index leaf pages, so the index is larger on disk and in cache, and every update that changes an included column has more to write. On a table whose write path is already the bottleneck — the kind of hot endpoint where you are also thinking about making retries safe — that extra write amplification is a real cost to weigh, not an afterthought.

Reach for INCLUDE when a specific, high-frequency read returns a small, stable set of narrow columns for a given key, and the heap fetches show up as real cost in EXPLAIN. Do not reach for it when the query already returns a column that belongs in the key anyway — put that column in the key and let a plain composite index cover it. And do not add included columns speculatively: a wide, rarely-covered INCLUDE list is pure write tax. Make the index earn its place in a migration that goes through review, alongside the checks your pipeline already enforces, not as a hotfix someone runs by hand at two in the morning.

An index-only scan is the fastest read Postgres offers, but it is a read that depends on housekeeping you do not see. Build the covering index, prove it with Heap Fetches: 0, and keep autovacuum honest — or you will have paid for the index and kept the heap fetches.