Covering Index

An ordinary index tells the engine where the rows are; it must then fetch each one from the table, which is a random read per row. A covering index contains the answer, so the table is never touched.

For a query returning many rows, this can be an order-of-magnitude difference — the difference between one sequential index range scan and ten thousand random page fetches.

Constructing one: the columns used for filtering and ordering come first, in the order the query needs them, and the columns merely being selected are appended — either as trailing key columns or, where the engine supports it, as INCLUDE columns which live in the leaf pages without affecting the index order or size as much.

The costs are the usual index costs, amplified: a wider index is larger, slower to maintain, and adds more to every write touching those columns. So it is a targeted optimisation for a specific hot query, justified by a plan showing the table fetches dominating — not something to apply broadly.

In PostgreSQL, note that an index-only scan still consults the visibility map, so it delivers its full benefit only on a well-vacuumed table.