PostgreSQL Covering Indexes: Eliminate Heap Fetches with INCLUDE

2026-04-19

A covering index in PostgreSQL contains all columns a query needs, allowing the planner to satisfy the query entirely from the index without visiting the heap table. Using the INCLUDE clause (PostgreSQL 11+), you can add non-searchable columns to a B-tree index, enabling index-only scans that eliminate heap fetches and dramatically reduce I/O.

The Problem

Every standard B-tree index lookup in PostgreSQL follows a two-step process: first, the index is scanned to find matching row pointers (TIDs), then the heap table is fetched to retrieve the actual row data. That second step — the heap fetch — is the bottleneck. For queries that return many rows, heap fetches dominate the execution time because they access random pages scattered across the table.

Consider a common dashboard query:

SELECT customer_name, customer_email
FROM customers
WHERE customer_id BETWEEN 1000 AND 2000;

With a standard index on customer_id, PostgreSQL finds the 1,000 matching TIDs in the index, then performs 1,000 heap fetches to retrieve customer_name and customer_email. Each heap fetch is a random I/O operation — on a cold cache with spinning disks, this means 1,000 random reads. Even with SSDs and the buffer cache, it is significantly slower than reading the data directly from the index.

An index-only scan avoids the heap entirely. If all the columns the query needs (customer_id, customer_name, customer_email) exist in the index, PostgreSQL reads everything from the compact, ordered index structure. No random heap access. No wasted I/O on columns you do not need.

Before PostgreSQL 11, achieving an index-only scan required a composite index on all columns: CREATE INDEX ON customers (customer_id, customer_name, customer_email). This works, but it has a cost — the composite index is sorted on all three columns, which means PostgreSQL maintains the sort order for customer_name and customer_email even though no query ever searches or sorts on them. This wastes CPU during inserts and updates, and the index is larger than necessary.

The INCLUDE clause solves this. It adds columns to the index leaf pages without including them in the index's sort key. The index is sorted only on the key columns, but the included columns are stored alongside them, available for index-only scans. The planner can search efficiently on the key columns and return the included columns without a heap visit.

The gap between an index scan with heap fetches and an index-only scan widens as tables grow. On a 100-million-row table, eliminating heap fetches can reduce query time from hundreds of milliseconds to single-digit milliseconds — a 10-100x improvement.

How to Detect It

Identify queries that are doing index scans with heap fetches by checking EXPLAIN ANALYZE output:

-- Look for "Index Scan" (not "Index Only Scan") with high heap fetches
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_name, customer_email
FROM customers
WHERE customer_id BETWEEN 1000 AND 2000;

In the output, look for these patterns:

Index Scan using idx_customers_id on customers — the index found the rows, but the heap was visited for each one
Heap Fetches: 1000 — confirms 1,000 trips to the heap table
Buffers: shared hit=1234 — high buffer count from random heap access

An Index Only Scan with Heap Fetches: 0 is the goal.

You can also identify covering index candidates system-wide by finding index scans that consistently trigger heap fetches:

-- Find indexes where index-only scans are rarely used
-- (high idx_scan but low idx_tup_fetch relative to idx_tup_read)
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan > 100
ORDER BY idx_tup_fetch DESC
LIMIT 20;

Tables with high idx_tup_fetch values are performing many heap fetches. Cross-reference these with your most frequent queries to identify covering index opportunities.

Check the visibility map coverage — index-only scans require pages to be marked all-visible, which depends on vacuum:

-- Check visibility map coverage for a table
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    last_autovacuum,
    last_vacuum
FROM pg_stat_user_tables
WHERE relname = 'customers';

If last_autovacuum is stale and there are many dead tuples, even a covering index will fall back to heap fetches because pages are not marked all-visible.

How myDBA.dev Shows It

myDBA.dev's Index Advisor identifies covering index opportunities by analyzing query patterns alongside existing indexes. When a query consistently performs index scans with high heap fetch counts, the advisor flags it as a candidate for INCLUDE columns. The recommendation includes the specific columns to add and the expected reduction in heap fetches.

Index Advisor highlighting covering index opportunities with INCLUDE column recommendations

The Queries page provides direct visibility into index-only scan efficiency. Clicking on a query reveals its execution plan, where you can see whether the planner chose an Index Scan (with heap fetches) or an Index Only Scan (without). The buffer statistics quantify the I/O difference — comparing buffer reads before and after adding a covering index shows the exact improvement.

Queries page showing index-only scan performance with heap fetch elimination

How to Fix It

Create a covering index using the INCLUDE clause to add the non-key columns your query needs:

-- Covering index: search on customer_id, include name and email for index-only scan
CREATE INDEX CONCURRENTLY idx_customers_covering
    ON customers (customer_id)
    INCLUDE (customer_name, customer_email);

The key column (customer_id) is the search key — PostgreSQL uses it for WHERE, ORDER BY, and join conditions. The included columns (customer_name, customer_email) are stored in the index leaf pages but are not part of the sort order. They exist solely to satisfy index-only scans.

After creating the index, verify the improvement:

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_name, customer_email
FROM customers
WHERE customer_id BETWEEN 1000 AND 2000;

You should now see Index Only Scan with Heap Fetches: 0 instead of Index Scan with heap fetches. Buffer reads will drop significantly.

INCLUDE vs Composite Index

Use INCLUDE when the additional columns are only needed for retrieval, not for searching or sorting:

-- INCLUDE: customer_name is retrieved but never searched
CREATE INDEX ON orders (order_date)
    INCLUDE (customer_name, order_total);

-- Composite: both columns are used in WHERE or ORDER BY
CREATE INDEX ON orders (customer_id, order_date);

A composite index on (order_date, customer_name, order_total) would also enable an index-only scan, but it would be larger (it maintains a three-column sort key) and would not help queries that filter only on order_date and customer_name in a different order. INCLUDE columns do not affect index key ordering, so the index remains optimally sorted for the search pattern.

Practical Example: Dashboard Query

A reporting dashboard that shows recent orders with customer details:

-- The query
SELECT order_id, customer_name, order_total, order_status
FROM orders
WHERE created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

-- The covering index
CREATE INDEX CONCURRENTLY idx_orders_recent_covering
    ON orders (created_at DESC)
    INCLUDE (order_id, customer_name, order_total, order_status);

This single index handles the WHERE filter, the ORDER BY, the LIMIT, and returns all selected columns — entirely from the index. No heap access, no random I/O.

Ensure Vacuum Runs Frequently

Index-only scans depend on the visibility map. PostgreSQL can skip the heap fetch only for pages marked "all-visible" — meaning every tuple on the page is visible to all transactions. Vacuum maintains the visibility map. If vacuum falls behind, pages are not marked all-visible, and the planner falls back to regular index scans with heap fetches even when a covering index exists.

-- Check visibility map coverage
SELECT
    c.relname,
    pg_stat_get_live_tuples(c.oid) AS live_tuples,
    pg_stat_get_dead_tuples(c.oid) AS dead_tuples
FROM pg_class c
WHERE c.relname = 'orders';

If dead tuples are accumulating, tune autovacuum to run more frequently on that table.

How to Prevent It

Adopt a systematic approach to covering indexes as part of query design. When writing a new query that selects specific columns from an indexed table, ask: "Can I add these columns to the index with INCLUDE to eliminate heap fetches?"

Keep covering indexes focused. Do not add every column in the table to the INCLUDE list — this creates a bloated index that approaches the table size and defeats the purpose. Include only the columns that your most frequent queries select. If different queries need different columns, create targeted covering indexes for each high-frequency pattern rather than one massive index.

Monitor heap fetch counts over time in myDBA.dev. A query that shows Heap Fetches: 0 today may start showing heap fetches tomorrow if vacuum falls behind or if a new column is added to the SELECT list. Track the idx_tup_fetch metric on your most important indexes — a sudden increase signals that an index-only scan has regressed to a regular index scan.

Review covering indexes when query patterns change. Application refactors that add columns to SELECT clauses will break index-only scans silently — the query still works, but it falls back to heap fetches. Periodic EXPLAIN ANALYZE reviews on your top queries catch these regressions before they accumulate into a performance problem.

FAQ

What is a covering index in Postgres?

A covering index is an index that contains all columns a query needs, enabling PostgreSQL to satisfy the query entirely from the index without accessing the heap table. Since PostgreSQL 11, the INCLUDE clause lets you add non-key columns to a B-tree index specifically for this purpose. The included columns are stored in the index leaf pages but are not part of the sort key.

When should I use a covering index?

Use covering indexes for frequently executed queries that select a small number of columns from a table with a supporting index. The best candidates are queries that already use an index scan but perform many heap fetches — dashboard queries, lookup queries, and reporting queries where you know the exact columns needed. Avoid covering indexes when the query selects many columns or when the table is rarely vacuumed.

What are the three types of indexing in PostgreSQL?

PostgreSQL supports multiple index types: B-tree (the default, for equality and range queries), Hash (for equality-only), GiST (for geometric and full-text data), GIN (for arrays, JSONB, and full-text search), BRIN (for physically ordered data), and SP-GiST (for partitioned search spaces). Covering indexes with INCLUDE are a feature of B-tree indexes specifically.

How do I create a composite index in PostgreSQL?

A composite index includes multiple columns in the index key: CREATE INDEX ON table (col1, col2, col3). All columns are part of the sort order. This differs from a covering index with INCLUDE, where only the key columns are sorted and included columns are just stored for retrieval. Use composite indexes when multiple columns appear in WHERE or ORDER BY; use INCLUDE when extra columns are only in the SELECT list.