Covering Indexes Explained Independently of Any Framework

1. How a regular index works and why a lookup becomes necessary

A classic secondary index stores the indexed column values together with a reference to the complete row, either directly as a physical row address or, with a clustered primary key, as the value of that primary key. When the database finds matching entries via the index, the index itself initially contains only the indexed columns and the reference, not the other columns requested by the query.

If additional columns are needed that are not part of the index, the database has to additionally fetch the complete row from the table for every match found. This second access is called a key lookup or bookmark lookup and, with an index residing on a different physical structure than the table itself, means another, often random, I/O access per match.

2. Why lookup costs add up considerably with many matches

For a query with few matches, an additional lookup per row barely matters, because the absolute number of extra accesses stays small. For a query that finds thousands or millions of rows via the index and performs a separate lookup for each of them, these many small, often randomly scattered accesses across the disk add up to considerable overall load that partly eats into the theoretical benefit of the index.

This is exactly where the optimizer's cost estimate kicks in: past a certain estimated match count, a full table scan can appear cheaper than an index scan with many individual lookups despite an available index, because a sequential scan is more I/O-efficient than many scattered individual accesses. This behavior is often mistakenly interpreted as proof that the index is broken or unsuitable, even though it is a rational cost decision.

3. The core principle of a covering index

A covering index solves this problem by additionally containing, alongside the actual filter and sort columns, every column a given query needs beyond that. When the database finds matching entries in the index, every requested value is already available in the index itself, so no additional access to the table is needed at all. This pattern is called an index-only scan and is usually noticeably faster than an index scan followed by individual lookups.

The important distinction here is between key columns, which determine the sort order of the index and are used for range filters or sorting, and additional, non-key columns that are merely carried along to avoid lookups, without themselves influencing sort order. Many database systems offer an explicit INCLUDE clause for this.

-- Query requiring a lookup with only a partially matching index
CREATE INDEX idx_orders_customer ON orders (customer_id);

SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 4711;
-- The index finds the row, order_date/total_amount need
-- to be fetched via a lookup from the table

-- Covering index: contains every needed column
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (order_date, total_amount);
-- The same query is now executed as an index-only scan

4. Practical example: spotting an index-only scan in the execution plan

The difference shows up clearly in the execution plan: without a covering index, a separate step for table access appears alongside the index access, often explicitly labeled a key lookup or bookmark lookup, connected via a nested-loop join with the index access. With a suitable covering index, this second step disappears entirely, and the plan shows only a single access, frequently labeled explicitly as an index-only scan or covering-index scan.

When analyzing an existing, slow execution plan, it therefore pays to look specifically for whether an additional lookup step follows an index seek or index scan. If that is the case and the lookup makes up a considerable share of total cost, extending the index with the missing columns is usually the most obvious and effective optimization.

EXPLAIN
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 4711;

-- Without a covering index (excerpt):
--   -> Index Lookup on orders using idx_orders_customer
--   -> Key Lookup on orders (fetches order_date, total_amount)

-- With a covering index (excerpt):
--   -> Covering Index Scan on idx_orders_customer_covering
--      (no additional table-access step remains)

5. The trade-off: larger indexes against fewer lookups

Every additional column in a covering index physically enlarges that index, tying up more disk space and more memory in the buffer pool or page cache. A larger index also means more data that has to be additionally written and inserted in sorted order into the index tree on every insert, update, or delete affecting the indexed or included columns, which raises write load.

This trade-off is not a blanket argument against covering indexes, but a balancing act that should be judged per query pattern: for a very frequently executed, read-heavy query with many matches, the lookup savings usually clearly justify the extra storage and write overhead. For a rarely executed query with few matches, the extra storage is often not worth it.

6. INCLUDE columns vs. additional key columns

A column carried along via INCLUDE differs technically from an additional key column, because it is not part of the sorted tree structure of the index, but is only stored at the leaf nodes. That reduces overhead compared to a full-fledged additional key column, because neither the tree height nor the sort logic is affected by the extra column, but at the same time an INCLUDE column cannot be used for range filters or sorting.

Systems without an explicit INCLUDE clause achieve the same effect by simply adding the needed columns as additional, lower-priority key columns to the index. The effect on the index-only scan is functionally similar, but the write overhead tends to be somewhat higher, because these columns then also influence sort order and thus the structure of the tree.

7. Why SELECT * almost always defeats a covering index

A covering index can only take effect if the set of requested columns is actually known and bounded. A query with SELECT * implicitly requests every column of the table, which would require a covering index to contain every column of the table to be effective, negating its original size advantage over the table itself. In practice, SELECT * therefore almost always means the optimizer falls back to a classic lookup or a full table scan.

Explicitly listing the actually needed columns is therefore not just generally good style, but a direct prerequisite for a covering index to be effective at all. This discipline in column selection is a simple but often underrated lever, especially for frequently executed, performance-critical queries.

8. Maintenance overhead: keeping covering indexes aligned with changing queries

A covering index is tightly bound to a specific query pattern. If the application changes and a previously frequently used query suddenly requests an additional column not contained in the index, the index-only scan falls back to a lookup again, without this being immediately noticeable unless execution plans are reviewed regularly. Covering indexes therefore need more ongoing maintenance than generic indexes on individual filter columns.

In practice, it works well to create covering indexes deliberately for a small number of particularly frequent, performance-critical queries and to review regularly, for instance after larger application changes, whether they still apply. For rarely used or constantly changing queries, on the other hand, the maintenance effort of a tailored covering index rarely pays off.

9. A practical decision rule for when to use one

A covering index is most worthwhile for queries that are executed frequently, need a manageable, stable set of columns, and currently show a noticeable lookup share of total cost in the execution plan. If any of these three factors is missing, especially a high execution frequency, the extra storage and maintenance effort is usually not justified.

Before making a decision, it pays to look at the current execution plan of the affected query with real production data, because only there does it become visible how large the lookup share actually is. An optimization without this evidence remains speculative and can, in the worst case, increase write load without measurably speeding up the read path.

10. Summary

11. FAQ: Covering Indexes: Key Facts at a Glance