Covering indexes: the cheap 10x that most schemas leave on the table

A lookup on an indexed column is supposed to be the easy case. The index exists, the planner uses it, EXPLAIN says Index Scan, and the query still takes 300ms to return 4,000 rows.

The index did its job. It found all 4,000 matching entries in a handful of page reads, because a B-tree is dense and sorted and that part is genuinely fast. Then the engine went and fetched 4,000 rows from the table, one at a time, at addresses scattered across the disk.

That second step is where the time went, and it is invisible in the plan node name. Index Scan and Index Only Scan differ by one word and by roughly an order of magnitude.

The pointer is not the row

An index entry stores the indexed values and a pointer to where the row lives. In Postgres that is a tuple identifier, in InnoDB a secondary index stores the primary key. Either way, anything the query selects that is not in the index requires following the pointer.

flowchart LR
    subgraph plain["Index Scan"]
        I1["Index leaf pages<br/>sequential, cached"] --> P1["4000 pointers"]
        P1 --> H1["4000 random reads<br/>into the heap"]
    end

    subgraph covering["Index Only Scan"]
        I2["Index leaf pages<br/>sequential, cached"] --> R2["Values read<br/>straight from the leaf"]
    end

    style H1 stroke:#ef4444,stroke-width:3px,color:#fff
    style R2 stroke:#4ade80,stroke-width:3px,color:#fff

Those random reads are the same access pattern that makes pointer chasing through a linked list slower than walking an array of the same length. The index scan itself is sequential and prefetch friendly. The heap fetches are not, and there is one of them per row.

Cache residency decides how badly this hurts. If the table is small enough to sit in the buffer pool, a heap fetch is a memory access and nobody notices. Once the table outgrows memory, each fetch becomes a storage read, and on network attached block storage that is a network round trip per row.

Making the index answer the whole question

The fix is to put everything the query needs into the index.

-- The query
SELECT status, total_amount
FROM orders
WHERE customer_id = ?;

-- Not covering: finds the rows, then fetches each one
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Covering: answers entirely from the index
CREATE INDEX idx_orders_customer_covering
    ON orders (customer_id) INCLUDE (status, total_amount);

The INCLUDE clause, available in Postgres 11 onwards, is the part worth understanding properly. Columns listed there are stored in the leaf pages only, not in the internal nodes that the tree uses for navigation.

That distinction matters more than it sounds. A B-tree’s depth is a function of how many entries fit in an internal page. Pack wide columns into the key and the entries get bigger, fewer fit per page, the tree grows a level, and every lookup pays an extra page read. Putting those columns in INCLUDE keeps the navigation structure narrow while still having the data available at the bottom, which is exactly the shape you want when the column is only ever selected and never searched on.

Use key columns for anything you filter, join or order by. Use INCLUDE for anything you only select.

InnoDB has no INCLUDE, so a covering index there means putting the columns in the key. It does give you one thing for free: every secondary index already contains the primary key, so an index on (customer_id) implicitly covers (customer_id, id). A query selecting only the primary key alongside an indexed column is already covered and most people do not realise it. MySQL reports this as Using index in the Extra column of EXPLAIN.

The Postgres catch that eats the gain

Here is the part that turns a clean win into a confusing one, and it took me an embarrassingly long time to internalise.

Postgres stores multiple versions of a row and decides which one your transaction can see using information stored in the row itself. The index does not carry that information. So even with a perfectly covering index, the engine has a problem: it can read the values from the index, but it cannot tell whether that row version is visible to you.

Its answer is the visibility map, a bitmap with a bit per heap page saying whether every row on that page is visible to all current transactions. If the bit is set, the index entry can be trusted and the heap is skipped. If not, the row must be fetched to check.

Vacuum is what sets those bits. Which means an index-only scan on a table that is being written to and not vacuumed promptly degrades back toward a regular index scan, quietly, with no plan change to signal it.

Index Only Scan using idx_orders_customer_covering on orders
  (actual time=0.03..142.88 rows=4210 loops=1)
  Heap Fetches: 3987

The plan node still says Index Only Scan. It fetched 3,987 rows from the heap anyway. That Heap Fetches line is the number to look at, and it should be near zero on a table where this optimisation matters. If it is not, the answer is vacuum tuning rather than more indexing: lower autovacuum_vacuum_scale_factor for that table so it gets visited more often.

I find this genuinely annoying as a design, and I also understand why it is the way it is. MVCC has to put visibility information somewhere, and putting it in every index would multiply write cost across every index on the table. The visibility map is the compromise, and the cost of that compromise lands on exactly the workload that most wants index-only scans: high write throughput tables.

What it actually costs

The word free in the usual framing of this technique is doing too much work, so let me be specific about the bill.

Every index is a second copy of the data it contains, maintained on every write. A covering index with three included columns is wider than a plain one, which means more leaf pages, which means more memory to keep it cached and more bytes written when the underlying row changes. Update one of the included columns and the index entry must be updated too, even though the column was never used for lookup.

Postgres has a specific interaction here worth knowing. Heap-only tuple updates, where an updated row can be written to the same page without touching any index, only apply when no indexed column changed. Adding a frequently updated column to a covering index can disable that optimisation and increase write amplification more than the index size alone suggests.

This is the same trade as always, just at a different layer. You are spending memory and update cost to buy read latency, which is the same triangle that decides whether a storage engine optimises reads, writes or space. Covering indexes sit firmly in the read corner and pay in the other two.

The practical version of that trade:

That last row is the one that decides whether this is worth doing. Covering a query that returns three rows saves three random reads and is not worth an index. Covering a query that returns four thousand saves four thousand, and that is where the order of magnitude comes from.

SELECT star makes all of this impossible

An index cannot cover a query that asks for every column, because that would mean duplicating the whole table.

This is the reason I have gradually stopped treating SELECT * as a style preference. It is a decision that forecloses an optimisation, and in ORM heavy codebases it is the default rather than a choice. Hibernate hydrating a full entity selects every mapped column, which means a covering index cannot help unless you project into a DTO instead.

// Selects every mapped column. No index can cover this.
List<Order> orders = orderRepository.findByCustomerId(customerId);

// Projects two columns. A covering index can answer it entirely.
public interface OrderSummary {
    String getStatus();
    BigDecimal getTotalAmount();
}

List<OrderSummary> summaries = orderRepository.findSummaryByCustomerId(customerId);

The second form is uglier and I would not write it everywhere. On the two or three endpoints carrying real traffic it converts a query that touches the table into one that never leaves the index, and that is a bigger win than most of the tuning people attempt first.

Where to look tomorrow

Take your slowest Index Scan node returning more than a few hundred rows and check what it selects beyond the indexed columns. If the answer is one or two narrow columns, an INCLUDE is a small change with a measurable effect.

Then check Heap Fetches on anything already running as an index-only scan. A number close to the row count means the optimisation is present in the plan and absent in practice, and no amount of further indexing will fix a vacuum problem.