Some queries can be answered by reading only index pages, never touching the table's own data pages. What must be true for that access path to be possible, and how much I/O does it actually save compared with an index lookup that then fetches rows?
answer
- all referenced columns, not just select list
- second lookup disappears
- narrow entries, more per page, key order
- visibility check can drag the table back in
- wider index means slower writes
basics
~20 sEvery column the query touches, in the output and in the predicates, must exist in the index. Then the engine skips the per-row table fetch entirely, eliminating the random I/O and reading only narrow index leaf pages in order.
solid answer
~60 sThe condition is simple: the index must contain **every column the query references** in the select list, predicates, join conditions, grouping and ordering. If one column is missing, the engine has to go to the table for that column and the path degrades into an ordinary index scan with row fetches. The saving is the second lookup, and it is the expensive half. An ordinary index scan matching N rows can perform up to N random single-page fetches. An index-only scan drops all of them and reads only index leaf pages, which are narrower than rows, so more entries fit per page, and it reads them broadly in key order rather than randomly. For a range query the difference is commonly one to two orders of magnitude in page reads. Caveat: engines that store row visibility or version information only in the table may still need a per-row check outside the index. When most of the table is stable and that metadata is well maintained, the checks are largely skipped and the saving is real. When it is not, the path silently reverts to touching table pages.
code
text · 8 linesIndex Only Scan using orders_cust_date_idx on orders
Index Cond: customer_id = 42
Heap Fetches: 0 -- table never touched
-- add one unindexed column to the select list:
Index Scan using orders_cust_date_idx on orders
Index Cond: customer_id = 42
-> one table page fetch per matching rowgo deeper
State the rule: if the index already has every column the query asks for, the database never has to open the table. That skips the slow part.
Enumerate what counts as referenced (output, filter, join, order, group), and explain that the saving is the elimination of one random page fetch per matching row.
Add the verification step and the caveat: read the plan's fetch counter, and explain why a recently written table can defeat the path despite a correct index. Weigh the write and cache cost of a wider index.
Position it as a deliberate read-path investment for a small set of hot queries, budgeted against write amplification, index footprint in the buffer pool, and the maintenance regime that keeps the path effective.
## The three-path picture Recall the single-table access-path menu: read all table pages sequentially; use the index to locate rows and then fetch each row's page; or answer from the index alone. The third one exists only under a specific condition, and it removes the most expensive component of the second. ## The condition A query can be served index-only when the index carries **all columns the query needs**. "Needs" is broader than the select list. It includes: - output columns, - columns in the `WHERE` predicate, - join columns for this table, - columns used for grouping, ordering, and aggregation. If a query selects two columns and filters on a third, all three must be present. Miss one and the engine must reach the table for it, and the plan reverts to index scan plus row fetch. This is why apparently trivial changes, such as adding one more column to a select list, can quietly multiply a query's I/O. One subtlety: in engines whose primary storage is organised by the primary key, secondary index entries implicitly carry the primary-key columns, so those columns come along free and count as present. ## Where the saving comes from Compare the page-level work for a query matching N rows. **Index scan plus fetch:** descend the tree, a few pages; walk `L` leaf pages to gather N entries; then up to N table page fetches, scattered, at random-read cost. The last term dominates as N grows. **Index-only scan:** descend the tree; walk `L` leaf pages; done. Cost is roughly `L`, and `L` is small for two reasons. First, index entries hold only key columns plus a pointer, so they are far narrower than rows and pack more densely per page. Second, leaf pages are chained in key order, so walking a range is closer to a sequential read than a random one. So an index-only scan is not "a bit faster". It removes a term that grows with the number of matching rows and replaces it with nothing. For an aggregate over a large range, such as counting or summing over a date window, it is routinely the difference between milliseconds and seconds. A second, quieter saving is buffer-pool pressure. Reading only narrow index pages pulls far less data into memory than reading full rows, so more of the working set stays cached, benefiting other queries. ## The visibility caveat The path is not unconditionally free. Multi-version engines must decide whether a given row version is visible to the current transaction, and some of them keep that information only with the row itself, not in the index. Their solution is an auxiliary summary of which regions of the table are known to be all-visible; entries from those regions need no check, while entries elsewhere trigger a table page visit after all. Consequences worth knowing: - On a heavily and recently written table, an index-only scan can perform close to as much table I/O as an ordinary index scan, because few regions are marked clean. - Maintenance that refreshes that summary (background vacuum-style housekeeping) is what keeps the path fast, so this becomes an operational property, not just a schema one. - In engines that keep versions elsewhere, or that store the table itself index-organised, the mechanics differ, but the general lesson holds: verify from the actual plan and its counters that the table access really disappeared, rather than assuming. ## Costs on the other side of the ledger A wider index is a bigger index. More columns mean fewer entries per leaf page, hence more leaf pages to read and more pages to keep cached, plus more work on every insert, update and delete that touches those columns. The path is a trade of write and storage cost for read cost, not a free win, which is why it is applied to specific hot queries rather than universally. ## How to verify it in practice Read the plan. A plan node reporting an index-only or covering access, with a table-fetch counter of zero, confirms it. A non-zero fetch counter means the path is nominally chosen but is still visiting the table, which points at the visibility or housekeeping issue rather than at the index definition. Timing alone is not proof, since a fully cached table hides the difference until the data grows.
- A plan says the access path is index-only, yet the query is still slow and reports a high row-fetch count. What is going on?The path was chosen but is not actually avoiding the table. In multi-version engines the index does not record whether a row version is visible to the current transaction, so entries from table regions not yet marked all-visible force a visit to the row anyway. That happens on recently or heavily written tables, and the fix is operational: let the background housekeeping that maintains the visibility information catch up, rather than changing the index.
- Why not simply add every column to the index so all queries can be served this way?Because the index would approach the size of the table while remaining a second copy that every write must maintain. Wider entries mean fewer per leaf page, so scans of the index read more pages, and inserts, updates and deletes pay to update a large structure. The technique is targeted at specific high-frequency queries where the read saving justifies the write and storage cost.
saying these in an interview costs you the question
- Believing only the select-list columns must be in the index, ignoring predicate, join and ordering columns
- Assuming the path is chosen automatically whenever an index exists on the filtered column
- Claiming it always avoids table access, with no awareness of visibility or version checks
- Treating a wider index as free, ignoring write amplification and larger leaf-page counts
- Judging by wall-clock time on a fully cached small table instead of reading page or fetch counters