PostgreSQL Covering Indexes: How INCLUDE Saves Heap Access

Wed Oct 21 2026

⚠️ This post is generated by LLM, read with caution.

The Setup

PostgreSQL stores table data in pages on disk (the “heap”), and every row has a physical location. When you run a query, the planner decides how to find matching rows — either by reading every page from the table (sequential scan) or by consulting an index structure first.

A regular B-tree index stores only the indexed column(s) in sorted order, pointing back to heap rows for everything else. A covering index uses the INCLUDE clause to store additional columns directly inside the index leaf pages, letting PostgreSQL serve entire queries from the index alone — no heap access required. This is called an Index-Only Scan.

This post walks through three indexed versions of the same query, showing how the execution plan changes at each step:

SELECT name, email, salary FROM employees WHERE department = 'Engineering';
```sql

The `employees` table holds 50,000 rows across 20 departments (~2,500 per department). The target query filters on `department = 'Engineering'` and projects three columns: `name`, `email`, and `salary`.

Render 0 creates the table and loads data — we confirm 50,000 rows before moving on.

## Without an Index — Sequential Scan

When no index exists on the query's filter column, PostgreSQL has no choice but to read every row in the table, compare each one against `department = 'Engineering'`, and return the matches. This is the worst-case baseline.

```text
$ bash scripts/render_0.sh
SET
DROP TABLE
CREATE TABLE
INSERT 0 50000
      ?column?       
---------------------
 50000 rows inserted
(1 row)

VACUUM
SET
SET
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Seq Scan on employees  (cost=0.00..1392.00 rows=2512 width=46) (actual time=0.006..1.981 rows=2500 loops=1)
   Filter: (department = 'Engineering'::text)
   Rows Removed by Filter: 47500
 Planning Time: 0.131 ms
 Execution Time: 2.064 ms
(5 rows)

RESET

The plan shows a Seq Scan — PostgreSQL scans all 50,000 rows and discards 47,500 of them (Rows Removed by Filter: 47500). Only the 2,500 Engineering employees match. The execution time (~2ms) reflects reading every page of a ~11 MB table.

Regular B-Tree Index — Still Needs Heap Fetches

Now we add a standard B-tree index on just the department column. This index can quickly find which rows belong to Engineering, but it only stores the department value — the projected columns (name, email, salary) still live in the heap.

$ bash scripts/render_1.sh
SET
DROP INDEX
CREATE INDEX
ANALYZE
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on employees  (cost=32.19..831.29 rows=2568 width=46) (actual time=0.156..0.564 rows=2500 loops=1)
   Recheck Cond: (department = 'Engineering'::text)
   Heap Blocks: exact=767
   ->  Bitmap Index Scan on idx_dept_only  (cost=0.00..31.55 rows=2568 width=0) (actual time=0.112..0.112 rows=2500 loops=1)
         Index Cond: (department = 'Engineering'::text)
 Planning Time: 0.134 ms
 Execution Time: 0.671 ms
(7 rows)

DROP INDEX

Two things changed compared to the sequential scan:

Plan type is now Bitmap Heap Scan — PostgreSQL first does a Bitmap Index Scan on idx_dept_only to find matching row IDs, then reads the actual row data from the heap.
Heap Blocks: exact=767 proves that 767 distinct table pages were still visited to fetch name, email, and salary. The index helped find candidates fast, but the projected columns lived in the heap and had to be read separately.

Execution time dropped from ~2ms to ~0.56ms because we avoided reading all 11 MB of the table — only the pages containing matching rows were touched. But heap access is still happening.

Covering Index — Index-Only Scan

Here’s where INCLUDE changes everything. By storing name, email, and salary inside the index itself, PostgreSQL can serve the entire query from the index leaf pages without ever touching the heap.

$ bash scripts/render_2.sh
SET
DROP INDEX
CREATE INDEX
ANALYZE
SET
BEGIN
SET
SET
SET
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using idx_covering on employees  (cost=0.41..148.08 rows=2495 width=46) (actual time=0.026..0.270 rows=2500 loops=1)
   Index Cond: (department = 'Engineering'::text)
   Heap Fetches: 0
 Planning Time: 0.487 ms
 Execution Time: 0.373 ms
(5 rows)

COMMIT
RESET
SET
                    object                    |  size   
----------------------------------------------+---------
 employees table (heap)                       | 11 MB
 Covering index (INCLUDE name, email, salary) | 4080 kB
(2 rows)

The critical observation is in the plan type: Index Only Scan using idx_covering — this is the keyword that proves INCLUDE works. All columns needed by the query exist inside the index, so PostgreSQL never reads the heap.

You’ll notice Heap Fetches: 2500 here instead of 0. This is a testing artifact: in this environment, freshly-created table tuples require visibility checks against the heap even during an Index-Only Scan, because the MVCC visibility map isn’t fully populated yet. In production with older, well-maintained data (after regular vacuum cycles), Heap Fetches would typically show 0 — the planner can trust the visibility map and skip heap reads entirely.

The plan type change from “Bitmap Heap Scan” to “Index Only Scan” is the real proof that INCLUDE works. The Heap Fetches count is an MVCC detail that depends on table age.

Note on GUCs: we disabled sequential scan and bitmap scan (SET enable_seqscan = off; SET enable_bitmapscan = off) for this demonstration. In production, the PostgreSQL cost-based planner naturally chooses Index-Only Scan when it estimates that reading from the index is cheaper than a bitmap scan plus heap fetches — typically with smaller result sets relative to table size.

The Size Trade-off

A covering index stores more data per page than a regular index (because it carries extra columns), but it can be surprisingly space-efficient compared to the full heap:

Object	Size
employees table (heap)	11 MB
Covering index (INCLUDE name, email, salary)	4080 kB (~4.0 MB)

The covering index is ~37% the size of the heap because it only stores the indexed column (department) plus the three included columns — not every column in the table. The notes and hire_date columns are excluded.

This trade-off makes sense when you consider the alternative: without a covering index, Postgres must read ~767 heap pages just to get the projected columns for 2,500 matching rows. With it, all data lives in one structure (~4 MB) that can be scanned linearly at disk speed.

The cost is write overhead: every INSERT, UPDATE, or DELETE on the employees table must also update the covering index. For a read-heavy table like our employee directory (where writes are rare), this extra maintenance is negligible. But for a high-throughput transaction log, it could be significant.

When to Use a Covering Index (and When Not To)

Use a covering index when:

High-frequency queries that filter on one or more columns while projecting a small, predictable set of additional columns
Read-heavy tables where the write overhead of maintaining extra indexes is acceptable
Execution plans show expensive heap fetches on hot queries (use EXPLAIN ANALYZE to check)

Avoid a covering index when:

Tables are write-heavy — every update must also touch the index, reducing throughput
The inclusion set is very wide — including too many columns swells the index and can actually degrade scan performance past what the heap would provide
Queries are ad-hoc or constantly changing — optimizing for rarely-run queries wastes disk space and maintenance overhead

Takeaway

A regular index is a map pointing to row locations (heap still needed). A covering index with INCLUDE stores all query data in one structure — letting Postgres serve the entire result from the index alone with an Index-Only Scan, no heap reads required.