Covering Indexes: Turning One Random Read Per Row Into Zero

A composite index on (tenant_id, created_at) and a query that filters on exactly those two columns. The planner picks the index, the row count drops to a fraction of the table, and the query is fast.

It is still doing one random heap read per matching row, and that is usually the entire remaining cost.

On a large table those heap reads are where the p99 goes, because they are random and each one is a potential miss in shared buffers. The fix is not a better index, it is putting the rest of the row inside the index so the heap is never touched. When you cannot do that, the second-best lever is not visiting rows you are about to skip, and pagination is the case where teams skip the most. It is also worth knowing that an index the planner refuses to use is a write-only index, which is the failure mode described in why the planner rejects your index.

The second read nobody budgets for

In Postgres the heap is a separate structure. An index entry holds a key and a pointer to a physical location, not the row itself. So an index scan is a two-step operation for every match: walk the index in order, then go to the heap page the pointer names.

  index (tenant_id, created_at)          heap
  +--------------------------+          +------------------+
  | t42  2026-08-09  -> (7,18)| ----->   | page 7  row 18    |
  | t42  2026-08-09  -> (9,4) | ----->   | page 9  row 4     |
  | t42  2026-08-09  -> (2,71)| ----->   | page 2  row 71    |
  | t42  2026-08-09  -> (6,15)| ----->   | page 6  row 15    |
  +--------------------------+          +------------------+

  the index is in order, the heap pointers are not

Those pointers are not in any useful order. With a LIMIT of 50 the cost is 50 random reads, which is fine. With a reporting query that matches 250,000 rows it is 250,000 random reads, and the index was never the problem.

250,000 matching rows

  heap pages in shared buffers    250,000 x ~1.5 us  =   0.4 s
  heap pages not in buffers       250,000 x ~500 us  = 125 s

  the same 250,000 index entries, read sequentially = ~0.2 s

The second line is the one that matters. A query can be fast in staging and unusable in production for no reason other than whether the working set happens to fit in memory, and the difference is three orders of magnitude.

INCLUDE: columns you carry but cannot search

The fix is to put the columns you need to read in the index itself, so the heap is not needed at all.

CREATE INDEX orders_tenant_created_idx
    ON orders (tenant_id, created_at)
    INCLUDE (status, total_cents, customer_id);

INCLUDE is not part of the key. The index is still sorted by tenant_id and then created_at. The extra columns ride along in the leaf, positioned after the key, and are not considered when the planner decides whether it can use the index for a search or for an ordering.

That separation is the reason INCLUDE exists rather than just widening the key:

key columns      searched, ordered, can be unique, must be reasonably small
INCLUDE columns  carried only, no search, no sort, no uniqueness

If you put status in the key instead, it becomes a third sort column. An index on (tenant_id, created_at, status) can still provide created_at order after an equality on tenant_id, because status follows the ordering column. The difference is that status participates in key comparisons, pivot tuples, operator-class requirements, and uniqueness. INCLUDE is the accurate choice when the column is payload only; it is not required to preserve this particular ordering.

The size difference is worth knowing before you commit to it.

per index entry (8 KB pages, 8 byte item pointer)

  (tenant_id, created_at)
    tid 8 + tenant 8 + created 8                          = 24 bytes

  + INCLUDE (status, total_cents)
    tid 8 + tenant 8 + created 8 + status 2 + total 8   = 34 -> 40 bytes

50,000,000 rows

  key only        50M x 24 B = 1.2 GB
  with INCLUDE    50M x 40 B = 2.0 GB

You are paying 800 MB to remove 250,000 random reads. On a table that does not fit in memory that is an easy trade. On a table where everything is already cached it is a waste, which is why this is a decision that belongs next to the buffer pool size and not in a schema review.

The visibility map is the real gate

Once the needed columns are in the index, the planner can propose an Index Only Scan. It does not always follow, and the reason is not the index.

MVCC means a row can have several versions. To know whether the version in the heap is the one this transaction should see, the database generally has to look at the heap, which is what an index-only scan is trying to avoid.

The visibility map is the shortcut. It is a separate structure with one bit per heap page, and the bit is set when every row version on that page is visible to every transaction. If the bit is set, the index entry can be trusted on its own. If it is not set, the heap has to be visited to check.

EXPLAIN (ANALYZE, BUFFERS)
SELECT tenant_id, created_at, status, total_cents
FROM orders
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 50;

 Index Only Scan using orders_tenant_created_idx on orders
   (cost=0.42..8.61 rows=50 width=28) (actual time=0.031..0.089 rows=50 loops=1)
   Heap Fetches: 50

Index Only Scan in the plan and Heap Fetches: 50 means every one of those fifty reads still happened. The plan is technically correct and the optimisation did nothing.

The fix is not a new index. It is making the visibility map current, which means vacuuming:

VACUUM orders;
-- or, for a table you cannot lock out:
VACUUM (ANALYZE) orders;

After a vacuum you should see the fetches drop towards zero. If they do not, something is preventing the visibility map from advancing, and that something is more interesting than the index.

The usual causes:

• A long-running transaction or replication horizon. An old snapshot can prevent VACUUM from removing dead row versions that remain visible to it. That delays pages containing those versions from becoming all-visible; merely reading a clean page does not clear its visibility bit or make every page the transaction touched ineligible.
• Replication slots. An unreleased slot on a replica does exactly the same thing, at a distance, for as long as it exists.
• Churn above what autovacuum can clear. A table updated several times per row per day will never be all-visible between autovacuum passes unless the scale factor is tuned for it.
• A prepared transaction. Rare, and just as permanent.

The last point generalises. An index-only scan is only a win on tables that are read-heavy relative to their write rate, because the benefit depends entirely on the visibility map being warm. On a table where every row is updated hourly, you are paying for the INCLUDE columns on every write and getting heap fetches on every read.

HOT updates are the hidden cost

This is the part that turns a good index into a slow table.

Postgres has an optimisation called a HOT update. When a row is updated and no indexed column changed, Postgres can write the new version onto the same page and mark the old one dead, leaving the index untouched entirely.

indexed column changed   -> new index entry, old one dead, page split likely
no indexed column changed -> HOT update, same page, no index work

INCLUDE columns are indexed columns in this sense. If you add total_cents to an index via INCLUDE, then every update to total_cents is now an update to an indexed column, and every one of those updates loses the HOT optimisation.

For a table where total_cents changes on nearly every row, this is the worst possible choice.

orders table, 50M rows, updated ~1.2x per day

  no INCLUDE (total_cents)
    HOT updates succeed      table stays near 50M rows

  INCLUDE (total_cents)
    HOT updates impossible   dead tuples accumulate until vacuum,
                             new pages written for every update

The bloat then slows down the very queries you built the covering index for, because the heap is now bigger and the index entries are not helping. You have traded a bounded random read for an unbounded write amplification.

The rule that follows is simple. Include columns in an index only if they are stable relative to the table’s write rate. A created_at or a status that changes once in a lifecycle is a good candidate. A running total, a view count, a last_seen_at stamped on every request, or any column touched on every update is not, no matter how often you read it.

last_seen_at is the specific trap here, because it is an obvious thing to include and it is updated on exactly the requests that make the table hot.

The size limit, and why large values do not belong

B-tree entries have a maximum size, and INCLUDE columns count against it. A btree tuple is limited to roughly a third of a page, which on 8 KB pages is about 2,700 bytes including overhead. Push past that and the index build or the insert fails with a row too large error.

The reflex is usually to include the description, the address, or the metadata document because the query selects them. That fails for two reasons. It may exceed the tuple limit, and even when it does not, it pushes the index past the point where the planner considers it a reasonable way to answer a query.

Large values are not just discouraged in a covering index, they are a design error. They belong in the heap, or in TOAST, and the query that needs both the small columns and the large one is a query that needs the heap.

If a query genuinely reads one huge column for two rows, the right answer is usually a second narrow query. A list endpoint that returns identifiers, then a detail endpoint that hydrates the body, avoids the covering index entirely and keeps the list query fast.

Writing projections instead of SELECT *

A covering index is useless to SELECT *, and SELECT * is what an ORM emits by default when it materialises an entity.

// SELECT * -> every column, so no index can cover it
var orders = await db.Orders
    .Where(o => o.TenantId == tenantId)
    .OrderByDescending(o => o.CreatedAt)
    .Take(50)
    .ToListAsync();

Projecting to a specific shape changes the columns the driver asks for, which is what makes the index eligible.

// three columns, all in the index -> index-only scan becomes possible
var rows = await db.Orders
    .AsNoTracking()
    .Where(o => o.TenantId == tenantId)
    .OrderByDescending(o => o.CreatedAt)
    .Take(50)
    .Select(o => new OrderSummary(o.Id, o.Status, o.TotalCents))
    .ToListAsync();

AsNoTracking() matters for a different reason than index selection. Without it, EF Core materialises full entities and maintains an identity map, which for a read-only list is work you are doing for nothing. With a projection to a non-entity type there is no identity map entry to create, so it is a throughput decision rather than a correctness one.

The habit worth forming is projecting on every read path that returns a list, and asking whether the projected columns are all present in some index. If a projection is wide and nothing covers it, the honest options are to narrow the projection or to accept the heap reads deliberately. Deciding which is better is easier than discovering it in a load test.

A production-ready architecture

  query needs columns C
       |
       v
  +-----------------------------+
  | is C a subset of some      |
  | (key + INCLUDE) index?     |
  +-----------------------------+
     | no -> heap reads are expected, index for the filter only
     | yes
     v
  +-----------------------------+
  | is every C column stable    |
  | relative to write rate?    |
  +-----------------------------+
     | no -> INCLUDE breaks HOT updates, table bloats, revert it
     | yes
     v
  +-----------------------------+
  | visibility map warm?        |
  | EXPLAIN ... Heap Fetches    |
  +-----------------------------+
     | no -> long txn, slot, or vacuum lag, not an index problem
     | yes
     v
  index-only scan, zero heap reads

A delivery checklist:

1. Read Heap Fetches in EXPLAIN (ANALYZE, BUFFERS). An Index Only Scan without it tells you nothing.
2. Vacuum and re-measure before concluding the index is not covering.
3. If fetches stay high, find the transaction or the slot holding back the visibility map before touching the index.
4. Keep INCLUDE columns to the minimum that satisfies the query, and re-derive the list from a measured plan rather than from the model.
5. Never include a column updated on most rows, or a last_seen_at-style stamp. You will break HOT updates and bloat the table.
6. Put columns in INCLUDE rather than the key unless you also need to search or order on them.
7. Use a partial covering index when the read is confined to a small fraction of the table, and make sure the query implies the predicate.
8. Project specific columns on every list read instead of materialising entities.
9. Watch index size against the memory you gave the buffer pool. A covering index that doubles in size can evict the heap pages it was meant to avoid reading.
10. Re-check after autovacuum tuning. Heap fetches on a read-heavy table should trend to zero, and if they do not, the write path is outrunning the vacuum.

Failure stories worth testing

Read Heap Fetches on a covering index immediately after a bulk update

Fill the table with updates and run the query before any vacuum. Then vacuum and run it again. The difference between the two numbers is the entire visibility map story in one measurement, and it is usually larger than anyone expects.

Add a frequently updated column to INCLUDE and watch the table grow

Pick a column that changes on most rows, add it to a covering index, and run the same update volume. Compare the ratio of dead tuples to live tuples and the physical table size against the run before. This is the test that turns HOT updates from a fact into a number.

Open a transaction and leave it idle, then re-measure the index-only scan

The heap fetches should climb to match the row count. Close the transaction and vacuum, and they collapse. Nothing about the index changed, which is the point.

Compare SELECT * with a three-column projection on the same predicate

Both use the same index. Only the projection can be covered. The gap between the two timings is the value of a covering index, isolated from every other variable.

Push a large text value into INCLUDE and watch the index build fail

Use deliberately incompressible payloads of increasing size and observe when the deployed PostgreSQL build rejects an index tuple. A literal “4 KB text value always fails” is not portable: page size, data type storage, and compression affect the stored datum. The design lesson is to keep payload columns narrow and test the actual type distribution.

Common mistakes

Mistake	What actually happens	Better decision
Reading Index Only Scan as proof of zero heap reads	Fetches still happen whenever the visibility map bit is clear	Always read Heap Fetches from EXPLAIN ANALYZE
Including a column updated on most rows	HOT updates stop, dead tuples accumulate, the table bloats	Keep frequently updated columns out of any index
Including last_seen_at because it seems useful	The column changes on exactly the requests that make the table hot	Read it from the heap, it is not stable enough
Putting INCLUDE columns in the key instead	They become sort columns and the index stops serving the ordering	Key for search and sort, INCLUDE for everything else
Covering SELECT *	No index can cover every column, so the work is wasted	Project the columns the query returns
Widening the key to carry extra columns	Extra sort keys break the ordering the index exists to provide	INCLUDE exists for this, use it
Vacuuming and calling the index broken if fetches remain	Something else is holding the visibility map stale	Find the long transaction or the slot
Adding INCLUDE to a write-heavy table for a read win	Write amplification exceeds the saved reads and evicts heap pages	Only include columns stable relative to the write rate
Assuming a read-heavy table is automatically fine	Churn above vacuum throughput keeps the map cold	Tune autovacuum for the table, then re-measure
Covering a query that also needs a large body column	The tuple exceeds the index limit or the index gets too big to be chosen	Two queries, or accept the heap read deliberately

The complete story in one minute

An index entry is a key and a physical pointer, not a row. So a selective index scan still walks to the heap once per matching row, and those pointers are in no useful order. With a limit of fifty that is a non-issue. With a quarter of a million matching rows it is either a third of a second or two minutes, depending entirely on whether those pages happen to be in the buffer pool, which is why the same query is fast in staging and slow in production.

INCLUDE puts columns needed by the result into non-key payload without making them search keys or part of uniqueness. The cost is workload-specific index growth, loss of B-tree deduplication for indexes with non-key columns, and extra write work. The planner chooses an index-only path only when its estimate—including all-visible coverage—beats alternatives.

What gates heap avoidance is the visibility map. Writes clear an all-visible bit for the affected page; VACUUM can set it after every tuple is known visible to all current and future transactions. EXPLAIN (ANALYZE, BUFFERS) reports fallback visits as Heap Fetches, which must be read relative to rows returned—a few dozen heap fetches across hundreds of thousands of index results can still be an excellent outcome. Long snapshot horizons and vacuum lag can delay cleanup; frequently modified pages also lose their bits immediately by design.

The cost nobody measures is the write side. INCLUDE columns are indexed columns, so updating one prevents a HOT update and forces a new index entry. An index covering a running total or a last_seen_at stamp will bloat the table faster than it saves reads, and the bloat then slows the reads the index was built for. Include only columns that are stable relative to the write rate, and take the rest from the heap. And project specific columns rather than materialising entities, because an index that cannot cover SELECT * is an index you paid for and did not use.

Technical references

• PostgreSQL index-only scans and covering indexes
• PostgreSQL visibility map
• PostgreSQL CREATE INDEX and INCLUDE
• PostgreSQL HOT row updates
• PostgreSQL VACUUM