Covering indexes: when you can skip the heap entirely

Every index lookup involves two steps: find the matching row locations in the index, then fetch the actual rows from the heap. That second step — the heap fetch — is the expensive one. It's random I/O, and for high-throughput queries it dominates your latency.

A covering index eliminates it entirely.

What makes an index "covering"

An index covers a query when it contains every column the query needs to return. The planner can answer the query using only the index, never touching the heap.

CREATE TABLE orders (
  id         bigint PRIMARY KEY,
  merchant_id bigint NOT NULL,
  status     text NOT NULL,
  amount     int NOT NULL,
  created_at timestamptz NOT NULL
);

For this query:

SELECT amount, created_at
FROM orders
WHERE merchant_id = 42 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

A standard index on (merchant_id, status) finds the rows but still has to fetch amount and created_at from the heap. A covering index includes them:

CREATE INDEX orders_covering
ON orders (merchant_id, status, created_at DESC)
INCLUDE (amount);

The INCLUDE clause (Postgres 11+) adds columns to the index leaf pages without affecting the sort order. The planner can now answer the query with an index-only scan.

Verifying it worked

EXPLAIN (ANALYZE, BUFFERS)
SELECT amount, created_at
FROM orders
WHERE merchant_id = 42 AND status = 'pending'
ORDER BY created_at DESC LIMIT 20;

-- Index Only Scan using orders_covering on orders
--   Heap Fetches: 0       ← this is what you want
--   Buffers: shared hit=3

Heap Fetches: 0 means the query never left the index. Buffers: shared hit=3 means three 8KB pages were read — for 20 rows, that's essentially free.

The storage trade

Covering indexes are not free. Adding columns to INCLUDE increases the size of every index leaf page. For a table with millions of rows and multi-column projections, the index can be 2–4× larger than a key-only index.

The rule of thumb: a covering index is worth it when the query runs frequently and the heap fetch is the bottleneck. Use pg_stat_user_indexes to check idx_scan and idx_tup_fetch to confirm the index is being used before over-indexing.

When it won't help

• Write-heavy tables. Every insert/update must maintain the index. Adding large INCLUDE columns increases write amplification significantly.
• Rows that don't fit in the index. text columns with variable-length values that exceed the index page size will fall back to heap fetches anyway (TOAST).
• Stale visibility maps. Postgres tracks which pages have no dead tuples in the visibility map. If the map is stale (run VACUUM), the planner adds heap fetches to verify row visibility — even on an otherwise perfect covering index.