The endpoint that powers our user dashboard was taking 180 ms at p95. The query plan showed an Index Scan using idx_orders_user_status. The index was there. The planner was using it. But the execution plan also showed 8,000 Heap Fetches for a query that returned 200 rows. Every time Postgres found a matching entry in the index, it jumped back to the heap to grab the columns the query actually needed. The index was not covering the query. It was merely starting it.

Adding more indexes was not the answer. The table already had six of them. The answer was the one index type that most teams skip because it sits in the shadow of composite indexes: the covering index created with INCLUDE.

This post is about how INCLUDE indexes work in Postgres, when they create a true Index Only Scan, the visibility map gotcha that breaks them, and the exact EXPLAIN output that tells you whether you have a covering index or a fake one.

What the planner is actually doing

When Postgres runs a query like this:

SELECT order_id, total, created_at
FROM orders
WHERE user_id = 42 AND status = 'shipped';

It has several strategies. A Seq Scan reads every page of the table. An Index Scan walks the B-tree index on (user_id, status) to find matching rows, then fetches each corresponding heap page to read order_id, total, and created_at. An Index Only Scan walks the index and never touches the heap at all, because every column the query needs is either in the index predicate or stored in the index leaf nodes.

Heap fetches are random I/O. Even if your index is in memory, jumping to the heap for every matching row means page lookups, buffer pin contention, and waiting on disk if the working set does not fit in RAM. For a query that returns 200 rows from an index with 10,000 matches, an Index Scan may touch 200 heap pages. An Index Only Scan touches zero.

The difference between 8 ms and 180 ms is often this single decision.

The composite index trap

The first instinct is to add the needed columns to the index key:

CREATE INDEX idx_orders_user_status_covering
ON orders (user_id, status, order_id, total, created_at);

This works. The planner can now satisfy the query entirely from the index. But it is wasteful. The three trailing columns (order_id, total, created_at) are not part of the search predicate. They are payload. By adding them to the key, you force the index to keep them sorted at every level of the B-tree. That increases index size, slows inserts and updates, and wastes space in interior nodes where the payload columns are stored redundantly.

Postgres 11 introduced INCLUDE for exactly this case:

CREATE INDEX idx_orders_user_status_include
ON orders (user_id, status)
INCLUDE (order_id, total, created_at);

Columns in the INCLUDE list are stored only in the leaf nodes of the index. They are not part of the sort order. They do not participate in the search. They exist purely as payload so that an Index Only Scan can return them without visiting the heap. Interior nodes stay small, insert overhead stays low, and the query still gets its columns.

Why INCLUDE is structurally different

In a composite index on (a, b, c), all three columns appear in every level of the index tree including the root and branch nodes. If c is a 16-byte timestamp that is never used for range filtering, it is still copied into every branch node, inflating the tree and increasing the depth. Deeper trees mean more page reads during the scan.

In an index on (a, b) INCLUDE (c), only a and b appear in branch nodes. c lives only at the leaves, appended to each tuple pointer. The branch nodes are smaller, the tree is flatter, and the payload columns are stored exactly once per row. For wide payload columns, the size difference is massive. We measured a 40% reduction in index size by moving four payload columns from the key to the INCLUDE list.

Even more important: because the payload columns are not in the key, an UPDATE that changes a payload column does not force a reorganization of the index structure. If total changes and it is in the key, the index tuple must be deleted and reinserted in the correct sort position. If total is in INCLUDE, the index tuple stays in place and only the payload is updated. This is a significant win for tables with high update rates on included columns.

The visibility map requirement

Here is the catch that breaks most first attempts at covering indexes. Postgres will only use an Index Only Scan if it can prove that every row on the relevant heap pages is visible to the current transaction. If a page contains a dead tuple from an uncommitted or recently committed transaction, Postgres must visit the heap to check visibility.

That visibility information is stored in the visibility map, a bitmap where each bit represents one heap page. When VACUUM runs, it marks pages as all-visible if every tuple on the page is visible to all transactions. Only then can an Index Only Scan skip the heap entirely for rows on that page.

If your visibility map is stale, your shiny INCLUDE index still produces heap fetches. The plan says Index Only Scan, but the execution says Heap Fetches: 7500. You fixed the structure but not the visibility.

Check the visibility map state with:

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup + n_dead_tup > 0
    THEN ROUND(n_dead_tup::numeric / (n_live_tup + n_dead_tup) * 100, 2)
    ELSE 0
  END AS dead_pct
FROM pg_stat_user_tables
WHERE relname = 'orders';

If dead_pct is above 5%, your table needs more aggressive vacuuming before Index Only Scan will deliver. The visibility map is also frozen and set during VACUUM (not VACUUM FULL), so autovacuum must be keeping up.

You can also inspect the visibility map directly with the pg_visibility extension:

CREATE EXTENSION IF NOT EXISTS pg_visibility;

SELECT
  all_visible,
  count(*)
FROM pg_visibility_map('orders')
GROUP BY all_visible;

If a large percentage of pages show all_visible = false, heap fetches are inevitable regardless of how perfect your index is.

Seeing the difference in EXPLAIN ANALYZE

Before the covering index, the plan looks like this:

Index Scan using idx_orders_user_status on orders
  Index Cond: ((user_id = 42) AND (status = 'shipped'::text))
  Heap Fetches: 8243

Note Index Scan, not Index Only Scan. The planner needed the heap.

After creating the INCLUDE index, if visibility is clean, the plan becomes:

Index Only Scan using idx_orders_user_status_include on orders
  Index Cond: ((user_id = 42) AND (status = 'shipped'::text))
  Heap Fetches: 0

The presence of Heap Fetches: 0 is the only proof that matters. If you see Index Only Scan but Heap Fetches > 0, the visibility map is the problem, not the index design.

A complete before-and-after example

Here is a realistic schema and workload:

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  user_id bigint NOT NULL,
  status text NOT NULL,
  order_id uuid NOT NULL,
  total numeric(12,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

-- Populate with 1 million rows
INSERT INTO orders (user_id, status, order_id, total)
SELECT
  (random() * 100000)::bigint,
  (ARRAY['pending','shipped','delivered','cancelled'])[1 + (random() * 3)::int],
  gen_random_uuid(),
  (random() * 500)::numeric(12,2)
FROM generate_series(1, 1000000);

-- Old composite index that covers but bloats
CREATE INDEX idx_old_composite
ON orders (user_id, status, order_id, total, created_at);

Run the dashboard query:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT order_id, total, created_at
FROM orders
WHERE user_id = 4242 AND status = 'shipped';

You will likely see an Index Only Scan on idx_old_composite with Heap Fetches: 0, but the index is huge and slow to maintain. Now try the INCLUDE version:

DROP INDEX idx_old_composite;

CREATE INDEX idx_orders_user_status_include
ON orders (user_id, status)
INCLUDE (order_id, total, created_at);

VACUUM ANALYZE orders;

Run the same EXPLAIN. You should see Index Only Scan on the new index, same zero heap fetches, and a smaller index size:

SELECT
  indexrelname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index
JOIN pg_class ON pg_class.oid = pg_index.indexrelid
WHERE indrelid = 'orders'::regclass;

On our test dataset, the composite index was 142 MB. The INCLUDE index was 89 MB. That is a 37% reduction in index size for the same query performance.

When to use INCLUDE vs composite keys

Use INCLUDE when:

• The extra columns are only needed for projection (SELECT list), not filtering, sorting, or grouping.
• The query returns many rows and heap fetches dominate latency.
• You have a stable access pattern where a few queries drive most of the read load.

Use a regular composite key (no INCLUDE) when:

• The extra columns are used in ORDER BY or range conditions. INCLUDE columns are not sorted and cannot support an ordered scan.
• You need the index to enforce uniqueness on a combination of columns. INCLUDE columns are ignored for uniqueness.
• The column is tiny (boolean, smallint) and the structural savings of INCLUDE are negligible.

Never use INCLUDE for columns that change frequently unless you have checked that HOT updates are working on the table. An UPDATE to an included column still writes a new index tuple at the leaf, just like any index. The savings come from avoiding branch node churn, not from making the column free to update.

Monitoring heap fetches in production

The single most useful metrics are already in pg_stat_user_tables:

SELECT
  relname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_tables
WHERE relname = 'orders';

idx_tup_read is the number of index entries examined. idx_tup_fetch is the number of heap rows fetched. If idx_tup_fetch is close to idx_tup_read, your indexes are not covering. In a well-tuned read-heavy workload, idx_tup_fetch should be significantly lower than idx_tup_read because many scans should be Index Only.

For per-index statistics:

SELECT
  indexrelname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_tup_fetch DESC;

An index with millions of idx_tup_fetch visits is a candidate for INCLUDE extension. Find the queries that use it with pg_stat_statements (if enabled), check their SELECT lists, and add the needed columns to INCLUDE.

The write cost trade-off

Nothing is free. Every column in INCLUDE adds to the index leaf size and increases write amplification. When you insert a row, every index on the table gets a new tuple. When you update an included column, every covering index that includes it gets a new leaf tuple. If you have four covering indexes and you update a column included in all four, that update writes four index tuples.

Measure the impact before shipping to production. Use pg_stat_user_indexes to track index growth after adding INCLUDE. If insert latency rises by more than 10%, the column may not be worth including, or you may have over-indexed the table.

As a rule of thumb, keep the payload under 200 bytes per row in the INCLUDE list. Wide payloads (jsonb blobs, long text) defeat the purpose. The index leaf pages fill faster, the tree gets taller, and the scan reverts to memory pressure. If you truly need a huge column in an Index Only Scan, consider whether the table should be narrower or whether the column belongs in a separate table.

The three-step tuning workflow

When a query is slow and the planner already uses an index, use this workflow before adding more hardware:

1. Confirm the scan type. If the plan shows Index Scan (not Index Only Scan), the query needs columns that are not in the index. Check the SELECT list and WHERE conditions.

2. Check heap fetches. Even with Index Only Scan, nonzero Heap Fetches means the visibility map is stale. Run VACUUM or tighten autovacuum_vacuum_scale_factor for the table until the map is current.

3. Add INCLUDE for payload columns. Move non-predicate columns from the key to INCLUDE. Re-run EXPLAIN ANALYZE. Verify Heap Fetches: 0. Verify index size did not explode. Verify insert and update latency stayed flat.

If step three increases write latency too much, the table may be write-bound and read-bound in equal measure. In that case, a materialized view or a read replica with a different index strategy is a better architectural fit than a larger index on the primary.

Common mistakes

Assuming all Index Only Scans are zero-cost. They are not. Index Only Scan means the planner avoided the heap, but if the index is huge and not in shared_buffers, it still reads pages from disk. Buffers: shared read=4500 in EXPLAIN is the truth.

Including columns just in case. Do not add INCLUDE (created_at, updated_at, metadata, notes) because some future query might need them. Every included column increases index maintenance and page pressure. Add only the columns proven by query logs.

Ignoring the visibility map on replicas. If you route reads to a replica, the replica has its own visibility map. A primary that vacuums aggressively does not update the replica’s map. If replica queries show heap fetches, vacuum the replica independently or accept that covering indexes have limited effect on stale replicas.

Using INCLUDE for ORDER BY columns. The planner cannot use an INCLUDE column to avoid a sort. If your query is ORDER BY created_at DESC and created_at is only in INCLUDE, the planner will fetch heap rows or add a separate sort step.

The practical takeaway

Covering indexes with INCLUDE are the most underused optimization in Postgres because they do not show up in pg_stat_statements as a missing index warning. The planner already has an index. It just is not complete. The symptom is slow queries with fast-looking plans and unexplained heap fetches.

Before your next performance review, run this on your top five tables:

SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  CASE WHEN idx_tup_read > 0
    THEN ROUND(idx_tup_fetch::numeric / idx_tup_read * 100, 2)
    ELSE 0
  END AS fetch_ratio
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_tup_fetch DESC
LIMIT 20;

Any index with a fetch_ratio above 80% is a prime candidate. Find the queries, check the SELECT lists, and consider whether INCLUDE can turn heap-bound index scans into true index-only scans. The latency drop is immediate, the disk savings are real, and the queries need no changes.

A note from Yojji

Query performance work that looks at the difference between Index Scan and Index Only Scan, that measures heap fetches instead of just wall-clock time, and that understands the visibility map as a prerequisite for covering indexes, is the kind of deep database craft that most product teams skip. It is also exactly the work that keeps production fast as data grows.

Yojji is an international custom software development company founded in 2016, with offices across Europe, the US, and the UK. Their senior engineering teams build scalable backend systems and performance-tune production databases as part of their full-cycle delivery practice, from product discovery through cloud deployment.