Covering indexes with `INCLUDE`: Index Only Scan

Capsule description

In the previous capsule you designed composites the planner uses. The plan shows Index Scan using idx_xxx, the buffers dropped, all good. But there's a hidden cost still there: after going down the index tree and finding the keys, PostgreSQL still has to go to the heap to fetch the columns the query requested in the SELECT.

SELECT id, title, price FROM books WHERE author_id = 42;

With an index on (author_id):

1. You go down the index tree with author_id = 42.
2. You get a list of TIDs (pointers to tuples in the heap).
3. For each TID, you read the heap page to get id, title, price.

That step 3 is the hidden cost. If author_id = 42 covers 5,000 rows, that's 5,000 visits to the heap (or several pages if they're grouped, but still significant).

Covering indexes eliminate that step. If the index contains all the columns the query needs (filters + projections), PostgreSQL can answer by reading only the index — without touching the heap. The plan changes from Index Scan to Index Only Scan. The difference in buffers read can be 10-100x.

This capsule teaches you to use INCLUDE (PostgreSQL 11+) to add "extra" columns to the index without making them keys, when an Index Only Scan actually applies (watch the visibility map), and the trade-offs to consider.

Concrete objective: you'll be able to convert an Index Scan with many buffers into a minimal Index Only Scan, validating with EXPLAIN (ANALYZE, BUFFERS).

Mental model: the index as a column cache

Think of an index as a mini-table. The key columns are the ones it uses to narrow (WHERE). But PostgreSQL lets you add "passenger" columns that ride along in the index without participating in the ordering:

Traditional index:            (author_id)
  → keys:    [42, 43, 44, ...]
  → TIDs:    [→heap, →heap, →heap, ...]

Covering index with INCLUDE:  (author_id) INCLUDE (title, price)
  → keys:    [42, 43, 44, ...]
  → payload: [(title="...", price=...), (title="...", price=...), ...]
  → TIDs:    [→heap, →heap, →heap, ...]

If the query requests SELECT id, title, price WHERE author_id = X:

• Without INCLUDE: goes down the tree, gets the TIDs, goes to the heap to fetch title, price. Index Scan.
• With INCLUDE: goes down the tree, reads title, price directly from the index. Index Only Scan. Doesn't touch the heap.

(id comes "for free" because PostgreSQL always includes the TID in each index entry, and for a simple PK the TID is essentially the id. But strictly: if you want id from a non-PK index, you should also include it.)

INCLUDE syntax

CREATE INDEX idx_books_author_covering
ON books(author_id)
INCLUDE (title, price);

• The columns in INCLUDE aren't part of the key (they don't influence the order or the search).
• They only ride along as "payload" so that queries can read them without going to the heap.
• There's no strict limit, but more columns = a bigger index.

INCLUDE vs putting the columns in the key

You might think: "I'll create a composite (author_id, title, price) and be done". It works, but:

• The index is now ordered by (author_id, title, price). If you never filter by title or price, that ordering is a waste.
• The index is bigger because the keys are longer tuples and participate in the tree's structure.
• Maintenance operations (rebalancing, page splits) are more expensive.

INCLUDE separates "what's used to narrow" from "what's returned". Cleaner, generally more efficient.

The visibility map: why an Index Only Scan sometimes still goes to the heap

PostgreSQL implements MVCC (Multi-Version Concurrency Control). Each tuple in the heap has visibility metadata: when it was inserted, when it was deleted (if applicable), which transactions can see it. That metadata lives in the heap, not in the index.

When the planner chooses Index Only Scan, it still needs to verify that each tuple "is visible to the current transaction". That verification, in the worst case, requires going to the heap.

Solution: PostgreSQL maintains a visibility map per table. It's a bitmap that marks, page by page, whether all the tuples on that page are visible to all transactions (no deleted or in-transition tuples).

• A page marked as "all visible" in the visibility map: the Index Only Scan can use the index without touching the heap.
• A page NOT marked: the Index Only Scan has to go to the heap to verify visibility.

The visibility map is updated by VACUUM (manual or automatic). A recently modified table (many INSERTs/UPDATEs) will have many unmarked pages until VACUUM runs.

How you verify it in EXPLAIN

Index Only Scan using idx_books_author_covering on books
  Index Cond: (author_id = 42)
  Heap Fetches: 0          ← zero! fresh visibility map
  Buffers: shared hit=12

Heap Fetches: 0 confirms that the query was answered 100% from the index. If you see Heap Fetches: 5000, it means the "Index Only" Scan had to go to the heap for 5,000 tuples — you lost the benefit.

How to fix a high Heap Fetches:

VACUUM books;

That refreshes the visibility map. Recapture the plan: probably Heap Fetches: 0.

In production, autovacuum does this automatically with some frequency. But after a bulk insert or large update, a manual VACUUM before measuring is worthwhile.

When covering indexes win the most

Three situations where INCLUDE gives the greatest benefit:

1. "List of items with limited fields" endpoints

SELECT id, title, price FROM books WHERE author_id = 42;

A typical catalog endpoint: filters by something, returns a few columns. If the filter returns 1,000+ rows, avoiding 1,000 heap visits is noticeable.

2. Counts and aggregations

SELECT COUNT(*) FROM orders WHERE customer_id = 42;

With (customer_id) (without INCLUDE), an Index Only Scan returns the count reading only the index. Even faster than going to the heap to count.

3. EXISTS / validation queries

SELECT 1 FROM users WHERE email = 'foo@bar.com' LIMIT 1;

If you only need to know whether it exists, an Index Only Scan tells you without touching the heap.

When it's NOT worth it

• If you do SELECT * or return many columns: the INCLUDE grows and stops being efficient.
• If the table changes a lot (write-heavy): the visibility map goes stale fast and the benefit is lost.
• If the filter returns few rows (dozens): the buffer savings are marginal vs the cost of maintaining a bigger index.

Heuristic: consider INCLUDE when a frequently-read endpoint returns N rows but only needs 2-4 extra columns. If it needs "all the columns", the INCLUDE doesn't scale.

Worked example: going from Index Scan to Index Only Scan

You'll set up the case, see the initial plan, add INCLUDE, see the difference in buffers.

Setup

DROP TABLE IF EXISTS demo_books;
CREATE TABLE demo_books (
    id            BIGSERIAL PRIMARY KEY,
    title         TEXT NOT NULL,
    author_id     INTEGER NOT NULL,
    price         NUMERIC(10, 2) NOT NULL,
    pages         INTEGER NOT NULL,
    isbn          TEXT,
    description   TEXT
);

INSERT INTO demo_books (title, author_id, price, pages, isbn, description)
SELECT
    'Book ' || g,
    (random() * 1000)::INTEGER + 1,
    (random() * 100)::NUMERIC(10, 2),
    (random() * 800)::INTEGER + 50,
    'ISBN-' || lpad(g::TEXT, 13, '0'),
    repeat('Lorem ipsum ', 50)  -- large column so it weighs
FROM generate_series(1, 200000) g;

ANALYZE demo_books;

You have 200,000 books with a long description field (which takes up space in the heap).

Initial plan: Index Scan

Create a traditional index on author_id:

CREATE INDEX idx_books_author ON demo_books(author_id);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, price FROM demo_books WHERE author_id = 42;

Expected output:

Index Scan using idx_books_author on demo_books
  Index Cond: (author_id = 42)
  Buffers: shared hit=205 read=3
Planning Time: 0.234 ms
Execution Time: 1.823 ms

200+ buffers read. The query returns ~200 rows (1000 authors / 200k books = ~200 books per author). Each row requires visiting the corresponding heap page.

Improved plan: Index Only Scan with INCLUDE

DROP INDEX idx_books_author;
CREATE INDEX idx_books_author_covering
ON demo_books(author_id)
INCLUDE (title, price);

ANALYZE demo_books;
VACUUM demo_books;  -- refresh the visibility map

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, price FROM demo_books WHERE author_id = 42;

Expected output:

Index Only Scan using idx_books_author_covering on demo_books
  Index Cond: (author_id = 42)
  Heap Fetches: 0
  Buffers: shared hit=4
Planning Time: 0.245 ms
Execution Time: 0.312 ms

Comparison:

Key point: the buffers dropped 52x. On a much larger table or with concurrent queries, that difference translates into less cache contention, less IO if the data doesn't fit in RAM, and lower latencies.

Verification: what happens if you do NOT VACUUM?

To show the visibility map's effect:

-- Without a prior VACUUM, after an INSERT
INSERT INTO demo_books (title, author_id, price, pages, isbn, description)
SELECT 'New book', 42, 50, 300, 'ISBN-NEW', 'desc'
FROM generate_series(1, 50);

-- Do NOT run VACUUM here

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, price FROM demo_books WHERE author_id = 42;

Expected output:

Index Only Scan using idx_books_author_covering on demo_books
  Index Cond: (author_id = 42)
  Heap Fetches: 50         ← it had to go to the heap for the new ones
  Buffers: shared hit=...  ← more buffers than the ideal case

The 50 new rows aren't on pages marked as "all visible", so the Index Only Scan had to verify them in the heap. After VACUUM, it goes back to Heap Fetches: 0.

The costs of INCLUDE: the honest trade-off

INCLUDE isn't free. The costs:

1. Index size on disk

Each index entry now carries the INCLUDE columns. If description (long text) is in INCLUDE, the index becomes huge — potentially larger than the original table.

How to measure:

SELECT
  pg_size_pretty(pg_relation_size('demo_books'))         AS table_size,
  pg_size_pretty(pg_indexes_size('demo_books'))          AS all_indexes,
  pg_size_pretty(pg_relation_size('idx_books_author_covering')) AS this_index;

2. Write cost

Each INSERT/UPDATE/DELETE has to update the index. More columns in INCLUDE = more data to write per operation.

If a column in INCLUDE changes (UPDATE demo_books SET price = ... WHERE id = ...), the index has to be updated. Without INCLUDE (an index only on author_id), a change to price doesn't touch the index.

On write-heavy tables with an INCLUDE of mutable columns, the cost can exceed the benefit of the Index Only Scan.

3. Cache pollution

A bigger index takes up more space in shared_buffers and the OS page cache. It reduces the amount of other data that fits in cache.

Practical rules for INCLUDE

• Yes: 1-3 small columns (numbers, short strings, booleans) that are returned together frequently.
• Consider: 4-5 columns if the endpoint is very frequent and the buffer savings make up for it.
• No: large TEXT/BYTEA columns, columns that change with every UPDATE, "all the columns just in case".

Why does this matter in real work?

1. Listing endpoints become 5-50x faster.

GET /products?category=X that returns 50 products with 4 fields each is the golden case for an Index Only Scan. It goes from 200ms to 5ms without changing the application code.

2. You reduce pressure on shared_buffers.

Each Index Scan that avoids going to the heap is one fewer page to read. In an API with 1,000 sustained RPS, that difference shows in the cache hit ratio and in the server's CPU.

3. COUNT(*) over a subset becomes viable.

SELECT COUNT(*) FROM events WHERE user_id = X with an index on (user_id) can be an Index Only Scan and answer in milliseconds. Without it, seq scan + filter, seconds.

4. You know one of the few cases where VACUUM directly affects latency.

After a bulk insert, the visibility map is dirty. Your read endpoint starts taking longer. The cause isn't "the index broke", it's "the Index Only Scan is no longer 'only', it's going to the heap". You know that a VACUUM fixes it.

Traps and common mistakes

Mistake 1 (conceptual): assuming Index Only Scan always avoids the heap

Symptom: you see Index Only Scan in the plan, you assume it's maximally optimized.

Why it's sometimes wrong: Heap Fetches > 0 means that even though it chose Index Only Scan, it had to go to the heap due to a dirty visibility map. The benefit is lost proportionally.

How to detect: look at Heap Fetches in the plan. If it's high vs actual rows, the visibility map is stale.

How to fix it: VACUUM table; and recapture the plan. If it's still high after VACUUM, there's constant write activity dirtying the VM faster than autovacuum can clean it. Consider a scheduled manual VACUUM, or tuning autovacuum (module 7).

Mistake 2 (practical): putting INCLUDE with large TEXT columns

Symptom: you add description (5KB average text) to INCLUDE, the index becomes 4x larger than the table, the writes become 3x slower.

Why it happens: each index entry now carries the full text. Multiplied by the number of rows, it's huge.

How to detect: pg_indexes_size much larger than the table's pg_relation_size.

How to fix it: remove the large columns from INCLUDE. If your endpoint really needs them, go back to the traditional Index Scan with a heap visit. It's the right trade-off when you return heavy payloads.

Mistake 3 (conceptual): putting the INCLUDE columns in the composite's key

Symptom: you create (author_id, title, price) to cover the query.

Why it's sub-optimal:

• It works (the index contains those columns), but the order includes title and price in the tree, taking up space and increasing the cost of inserts.
• The planner only leverages title, price for the order if you filter by them. If you only return them, the order is a waste.

How to fix it: use INCLUDE. The columns ride along as payload without participating in the order.

-- Sub-optimal
CREATE INDEX ON books(author_id, title, price);

-- Better
CREATE INDEX ON books(author_id) INCLUDE (title, price);

Mistake 4 (conceptual): expecting Index Only Scan with SELECT *

Symptom: your query is SELECT * FROM books WHERE author_id = 42, you create a covering index with an INCLUDE of 5 columns, and it's still an Index Scan.

Why it happens: an Index Only Scan is only chosen if all the columns the query projects are in the index (key or INCLUDE). SELECT * projects all the table's columns. Unless your INCLUDE covers them all (impractical), it always goes to Index Scan.

How to fix it: avoid SELECT * in critical endpoints. List the columns explicitly. It's good general practice too — SELECT *s can break when someone adds columns to the table.

Mistake 5 (conceptual): not updating the plan after VACUUM

Symptom: you measure the plan right after a bulk insert, you see a high Heap Fetches, you conclude that covering doesn't work.

Why it's wrong: without VACUUM, the visibility map is dirty. Wait for autovacuum or force a manual VACUUM and re-measure.

How to fix it: after large loads, VACUUM table; and capture the plan. In real production, autovacuum runs periodically and the constant scenario is Heap Fetches: 0 for relatively stable tables.

Mistake 6 (conceptual): assuming INCLUDE helps for columns used in WHERE

Symptom: you create (author_id) INCLUDE (status) and queries with WHERE author_id = X AND status = 'active' don't fully leverage it.

Why it happens: the columns in INCLUDE don't participate in the Index Cond. The index uses only author_id to narrow; status = 'active' is applied as a Filter after reading from the index (even if it's in INCLUDE).

How to fix it: if the column participates in the filter, it goes in the key of the composite, not in INCLUDE: (author_id, status). INCLUDE is only for columns that are returned, not filtered.

Exercises

Exercise 1: predict when INCLUDE helps

For each query, decide whether adding INCLUDE helps significantly. Justify.

1. SELECT id, title FROM books WHERE author_id = 42 (returns ~200 rows)
2. SELECT * FROM books WHERE id = 42 (returns 1 row)
3. SELECT COUNT(*) FROM orders WHERE customer_id = 42
4. SELECT id, title, description FROM books WHERE author_id = 42 (description is 5KB)
5. SELECT id, title, price FROM books WHERE author_id = 42 (returns 5 rows)

Exercise 2: convert Index Scan into Index Only Scan

You have:

SELECT id, status, total FROM orders WHERE customer_id = 42;

Current plan:

Index Scan using idx_orders_customer on orders
  Index Cond: (customer_id = 42)
  Buffers: shared hit=412

Design the covering index. Capture the plan afterward.

DROP INDEX idx_orders_customer;
CREATE INDEX idx_orders_customer_covering
ON orders(customer_id)
INCLUDE (status, total);

ANALYZE orders;
VACUUM orders;

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

Index Only Scan using idx_orders_customer_covering on orders
  Index Cond: (customer_id = 42)
  Heap Fetches: 0
  Buffers: shared hit=12

Exercise 3: diagnose a high Heap Fetches

Your plan shows:

Index Only Scan using idx_books_covering on books
  Index Cond: (author_id = 42)
  Heap Fetches: 4500
  Buffers: shared hit=...
Execution Time: 25ms

You have the right covering index, but Heap Fetches: 4500. What's happening? How do you fix it?

VACUUM books;

Index Only Scan using idx_books_covering on books
  Index Cond: (author_id = 42)
  Heap Fetches: 0
  Buffers: shared hit=...  ← much less
Execution Time: 1ms

Exercise 4: compare INCLUDE vs columns in the key

You have two options for the query SELECT id, title, price FROM books WHERE author_id = 42 ORDER BY title LIMIT 10;

• Option A: CREATE INDEX ON books(author_id, title, price);
• Option B: CREATE INDEX ON books(author_id, title) INCLUDE (price);

Which is better? Why?

Limit
  ->  Index Only Scan using idx_books_author_title on books
        Index Cond: (author_id = 42)
        Heap Fetches: 0

Exercise 5: measure the cost of INCLUDE

Take a table in your environment with enough data. Create two indexes on the same column:

CREATE INDEX idx_simple ON table(column);
CREATE INDEX idx_covering ON table(column) INCLUDE (col_a, col_b, col_c);

Measure:

1. Disk size of each index (pg_relation_size).
2. The time of an INSERT with a script that inserts 10,000 rows, repeated with each index active alone (drop the other).
3. Comment on the difference.

SELECT
  'idx_simple' AS name,
  pg_size_pretty(pg_relation_size('idx_simple')) AS size
UNION ALL
SELECT 'idx_covering', pg_size_pretty(pg_relation_size('idx_covering'));

name          | size
--------------+-------
idx_simple    | 4 MB
idx_covering  | 18 MB

-- Only idx_simple active
DROP INDEX idx_covering;
\timing on
INSERT INTO table SELECT generate_series, ...;
-- Time: 850 ms

-- Only idx_covering active
DROP INDEX idx_simple;
CREATE INDEX idx_covering ON table(column) INCLUDE (col_a, col_b, col_c);
\timing on
INSERT INTO table SELECT generate_series, ...;
-- Time: 1320 ms

Exercise 6: apply it to a real endpoint

Take an endpoint from your app that returns a list filtered by one column and projects 2-4 columns. Follow:

1. Capture the initial plan.
2. Design a covering index with INCLUDE.
3. ANALYZE and VACUUM the table.
4. Capture the plan afterward.
5. Compare Buffers and Execution Time.
6. Measure the index size before and after.
7. Decide: is it worth it or is the write cost prohibitive?

## Endpoint: GET /products?category=X

**SQL query emitted:**
```sql
SELECT id, name, price, in_stock
FROM products
WHERE category_id = $1;

Index Scan using idx_products_category on products
  Index Cond: (category_id = 5)
  Buffers: shared hit=1240 read=80
Execution Time: 18ms

DROP INDEX idx_products_category;
CREATE INDEX idx_products_category_covering
ON products(category_id)
INCLUDE (name, price, in_stock);

ANALYZE products;
VACUUM products;

Index Only Scan using idx_products_category_covering on products
  Index Cond: (category_id = 5)
  Heap Fetches: 0
  Buffers: shared hit=42
Execution Time: 1.2ms

</details>

---

## Summary and next step

In this capsule you learned:

- A **covering index** with `INCLUDE` allows answering the query without touching the heap, via `Index Only Scan`.
- `INCLUDE (col1, col2)` adds columns as **payload**, without participating in the order or the filtering.
- The **visibility map** controls when an `Index Only Scan` avoids going to the heap. `Heap Fetches: 0` confirms zero visits.
- After a bulk insert/update, `VACUUM table;` refreshes the visibility map and restores the benefit.
- **Costs**: a larger index on disk, slower writes, cache pollution. It's only justified if the endpoint is frequent and the buffer savings are significant.
- **Usage rules:** 1-3 small columns in INCLUDE, no large TEXT columns, not on write-heavy tables with mutable columns.

Before moving on, you should be able to:

- Distinguish `Index Scan` from `Index Only Scan` in a plan.
- Design a covering index for a specific endpoint, deciding what goes in the key and what goes in INCLUDE.
- Diagnose `Heap Fetches > 0` and resolve it with `VACUUM`.
- Compute the reads vs writes trade-off before applying INCLUDE.

**Next capsule — Partial indexes.** Up to here we've indexed the whole table. What if you only care about 5% of the rows (the active ones, the pending ones, the non-deleted ones)? A partial index indexes only the relevant subset: 20x smaller, 20x faster to maintain, focused queries. It's the tool for tables with soft delete, a skewed status enum, or multi-tenancy. You'll learn the `WHERE` syntax and the most common trap: exact predicate matching (a partial index is only used if the query includes its condition).

---

## Resources

1. [Markus Winand — Use The Index, Luke! — "Index-Only Scan"](https://use-the-index-luke.com/sql/clustering/index-only-scan-covering-index) — a visual explanation of covering indexes and why an `Index Only Scan` is the holy grail of read queries.
2. [PostgreSQL Documentation — Index-Only Scans and Covering Indexes](https://www.postgresql.org/docs/16/indexes-index-only-scans.html) — the official chapter. Covers the visibility map, INCLUDE, and edge cases.
3. [PostgreSQL Documentation — CREATE INDEX (INCLUDE syntax)](https://www.postgresql.org/docs/16/sql-createindex.html) — the exact syntax reference.
4. [Hubert "depesz" Lubaczewski — "Waiting for PostgreSQL 11 – Indexes with INCLUDE columns"](https://www.depesz.com/2018/03/27/waiting-for-postgresql-11-indexes-with-include-columns/) — the article from when INCLUDE was introduced in PG11. Illustrative use cases.
5. [PostgreSQL Wiki — Index Maintenance — Visibility Map](https://wiki.postgresql.org/wiki/Index_Maintenance#Heap_Fetches) — queries to diagnose a high Heap Fetches and VACUUM plans.
6. [Bruce Momjian — "MVCC in PostgreSQL" (slides)](https://momjian.us/main/presentations/internals.html) — a section on the visibility map and why it exists (it's not an implementation detail, it's central to MVCC).
7. [Tomas Vondra — "When to use covering indexes"](https://www.2ndquadrant.com/en/blog/index-only-scans-and-covering-indexes/) — an analysis of trade-offs and real benchmarks.

---

*Module 3 — Database Performance & Query Tuning Guide*