Building Effective Covering Indexes: Eliminating Heap Fetches with INCLUDE #

When query latency spikes despite an existing index, EXPLAIN ANALYZE often exposes the root cause in a single line: Heap Fetches: N. That counter records how many times the planner traversed the index tree but then had to perform an extra random I/O round-trip to the heap to fetch a column that was not stored in the index. Driving that number to zero is the central objective of covering index design, and it requires matching the index definition precisely to the query’s column projection.

Why the Planner Falls Back to a Heap Fetch #

The planner switches from an Index Only Scan to a standard Index Scan — or degrades an Index Only Scan by issuing heap fetches — whenever either of two conditions holds:

  1. Missing projected columns. A column in SELECT, ORDER BY, or a computed expression is not stored anywhere in the index. The planner has no choice but to retrieve the full heap tuple.
  2. Stale visibility map. Even when every column is present in the index, the planner must verify tuple visibility for MVCC. If the visibility map page bit for a heap block is not set (meaning VACUUM has not confirmed all tuples on that page are visible to all transactions), the planner fetches the heap tuple anyway to check the transaction ID.

Both conditions can coexist on the same table, and both appear as Heap Fetches in the plan output. Understanding sequential vs index scans gives broader context on when the planner chooses index access paths at all; this page focuses on converting an Index Scan into a true Index Only Scan.

Annotated EXPLAIN Evidence: Spotting the Problem #

The plan below is for a simple status-and-region filter query on an orders table. The existing index covers (status, region) but nothing else.

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, customer_email, created_at
FROM orders
WHERE status = 'shipped' AND region = 'US';
Index Scan using idx_orders_status_region on orders
  (cost=0.43..12.55 rows=100 width=64)            -- cost: unitless planner metric
  (actual time=0.080..1.240 rows=100 loops=1)
  Index Cond: ((status = 'shipped') AND (region = 'US'))
  Heap Fetches: 100                               -- ← every row required a heap visit
  Buffers: shared hit=102 read=14                 -- 14 heap blocks read from disk

Three signals confirm a covering index gap:

The index filtered correctly on status and region, but order_id, customer_email, and created_at were absent from the index, forcing those 14 heap block reads.

Index Only Scan vs Index Scan — heap fetch comparison Left side shows Index Scan: query goes to B-tree index then must also fetch heap pages. Right side shows Index Only Scan: query reads B-tree leaf pages and visibility map only, never touching heap pages. Heap Fetches drops from N to 0. Index Scan (before) Query B-tree index Heap pages (random I/O) Heap Fetches: N (one per result row) Index Only Scan (after) Query B-tree + INCLUDE Visibility map (in-memory check) Heap Fetches: 0 heap never touched

Step-by-Step Resolution Workflow #

Step 1 — Capture the baseline plan #

Always start with the full BUFFERS output so you have numbers to compare against after the change.

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, customer_email, created_at
FROM orders
WHERE status = 'shipped' AND region = 'US';
-- Record: Heap Fetches, actual time, Buffers: shared read

Step 2 — Classify every column in the query #

List the columns used by SELECT, WHERE, and ORDER BY, then sort them into two groups:

Group Role Index placement
status, region Filter predicates Key columns — appear in (col1, col2)
order_id, customer_email, created_at Payload (projection only) INCLUDE (col3, col4, col5)

Key columns drive B-tree structure and predicate selectivity. INCLUDE columns are stored only in leaf pages; they satisfy projection without inflating the internal nodes.

Step 3 — Create the covering index #

CREATE INDEX CONCURRENTLY idx_orders_covering
  ON orders (status, region)
  INCLUDE (order_id, customer_email, created_at);
-- CONCURRENTLY avoids an ACCESS EXCLUSIVE lock on the table

Do not promote payload columns to key position. Adding order_id as a third key column would widen every internal B-tree node and bloat the index without improving predicate selectivity.

Step 4 — Run VACUUM to refresh the visibility map #

VACUUM ANALYZE orders;
-- Updates the visibility map so the planner can skip heap fetches
-- ANALYZE refreshes statistics for the new index

Without this step, Heap Fetches may remain non-zero because the visibility map still contains un-set bits from earlier writes, even though the index now holds every column.

Step 5 — Verify the plan #

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, customer_email, created_at
FROM orders
WHERE status = 'shipped' AND region = 'US';

Expected output after tuning:

Index Only Scan using idx_orders_covering on orders
  (cost=0.43..8.10 rows=100 width=64)
  (actual time=0.031..0.280 rows=100 loops=1)
  Index Cond: ((status = 'shipped') AND (region = 'US'))
  Heap Fetches: 0                               -- ← resolved
  Buffers: shared hit=12                        -- all satisfied from buffer cache

Step 6 — Drop superseded indexes #

DROP INDEX CONCURRENTLY idx_orders_status_region;
-- The old narrow index is now redundant; remove it to reduce write overhead

Before / After Plan Comparison #

-- BEFORE
Index Scan using idx_orders_status_region  actual time=0.080..1.240  Heap Fetches: 100

-- AFTER
Index Only Scan using idx_orders_covering  actual time=0.031..0.280  Heap Fetches: 0

The node type changed from Index Scan to Index Only Scan, actual time dropped by approximately 4×, and Buffers: shared read disappeared entirely.

Scenario: Sort Order With Descending Key #

A sort predicate requires the sort column to be a key column, not an INCLUDE column — the B-tree must be built in the desired sort order to avoid a separate Sort node. Compare:

SELECT product_id, price, stock_count
FROM inventory
WHERE category_id = 5
ORDER BY updated_at DESC
LIMIT 10;

Incorrect index (sort column in INCLUDE):

-- Wrong: updated_at in INCLUDE cannot drive the B-tree sort order
CREATE INDEX idx_inv_wrong
  ON inventory (category_id)
  INCLUDE (updated_at, product_id, price, stock_count);

The plan will show a Sort node and likely Heap Fetches because updated_at is not a key column.

Correct index (sort column as key):

CREATE INDEX CONCURRENTLY idx_inv_covering
  ON inventory (category_id, updated_at DESC)
  INCLUDE (product_id, price, stock_count);

With this definition, the planner can perform an Index Only Scan Backward on (category_id, updated_at DESC) and read product_id, price, and stock_count from leaf pages — no Sort node, no heap access. The B-tree index optimization guide covers sort-key ordering in more depth.

Common Pitfalls #

Putting payload columns in key position. Adding customer_email as a third key column bloats every B-tree internal node and increases index size by far more than using INCLUDE. Reserve key position for columns that appear in WHERE or ORDER BY.

Skipping VACUUM after index creation. The visibility map is not updated by CREATE INDEX. Running EXPLAIN ANALYZE immediately after adding the index but before VACUUM can still show Heap Fetches > 0, leading to a false conclusion that the index is wrong.

Over-widening the INCLUDE list. Each additional INCLUDE column increases leaf-page size, write amplification, and cache pressure. Include only the columns that appear in the specific query’s SELECT list — not every column on the table.

Leaving the old narrow index in place. A superseded index still receives every INSERT, UPDATE, and DELETE. Drop it with DROP INDEX CONCURRENTLY after confirming the covering index is used.

Frequently Asked Questions #

How do I force PostgreSQL to use an Index Only Scan? #

Ensure all SELECT, WHERE, and ORDER BY columns are covered by the index — key columns for filtering and sorting, INCLUDE columns for projection. Then run VACUUM to mark heap pages as all-visible, and verify Heap Fetches: 0 in EXPLAIN ANALYZE. If fetches persist after vacuuming, check pg_stat_user_tables.n_dead_tup to confirm autovacuum is keeping pace with the write load.

Does INCLUDE affect index maintenance overhead? #

INCLUDE columns are stored only in index leaf pages, not in internal B-tree nodes. This slightly increases leaf-page storage and write amplification per row change, but keeps tree traversal fast. The overhead is much smaller than promoting those same columns to key position, which would inflate every level of the tree.

When should I avoid covering indexes? #

Avoid them on tables where very high UPDATE or DELETE frequency keeps the visibility map dirty, because heap fetches will persist even with a correctly structured index. Also avoid them when the projected columns are very wide (approaching 20 % of total row width), where index bloat and write amplification outweigh the read benefit. In those cases, a partial index that excludes inactive rows can reduce both index size and visibility-map churn — see partial index implementation for that approach.