Answering From The Index Alone
A normal index scan finds matching entries in the index, then fetches each row from the table to read its other columns. Those heap fetches are random and expensive. An index only scan avoids them: if the index already contains every column the query references, the engine answers from the index without touching the heap.
Covering Indexes
An index that includes all columns a query needs is a covering index for that query. You can extend an index to cover queries by adding columns, sometimes as nonkey included columns that ride along in the leaf without affecting key order.
- The query reads only the index, cutting random heap reads.
- Aggregations and lookups over the indexed columns get much faster.
- The tradeoff is a larger index and more write overhead to maintain it.
The Visibility Catch
In a multiversion engine the index lacks visibility information. The scan still must confirm a row is visible, which it does cheaply using the visibility map. If the page is all visible, no heap access is needed.
Key idea
An index only scan answers a query from a covering index without heap fetches, saving random reads, as long as a visibility check confirms the rows are all visible.