An ordinary index scan does two things: find matching entries in the index, then fetch each matching row from the table to get the columns you actually selected. That second step is a random read per row, and it is usually the expensive part.
If the index already contains every column the query needs, the second step is unnecessary. The database reads the index and returns results without touching the table.
What changed in 2026
- Include-column support spread. More engines gained the ability to store extra columns in an index without making them part of the sort key.
- Covering index design became routine. Adding selected columns to an index moved from an advanced technique to a standard tuning step.
- Visibility interaction got better documented. The dependency on cleanup state became widely understood rather than a mysterious inconsistency.
- Write cost scrutiny increased. Teams became more careful about index width as write volumes grew.
What makes an index covering
The index must contain every column the query references — in the filter, in the select list, in any sort, and in any join condition.
Filtering on status and selecting id, name requires an index containing all three. An index on status alone can find the rows and must then fetch each one for name.
Two ways to include the extra columns:
As key columns, part of the index's sort order. Makes them usable for filtering and sorting, and enlarges every level of the index structure.
As included columns, stored only at the leaf level. Available for returning, not usable for filtering or ordering, and cheaper — the index's internal structure stays narrow.
Included columns are the right choice for anything you only need to return. Key columns for anything you filter or sort on.
| Query need |
Where the column belongs |
| Filtered on |
Key column |
| Sorted by |
Key column |
| Joined on |
Key column |
| Only returned |
Included column |
| Not referenced |
Not in the index |
The visibility catch
Under multi-version concurrency control, a row may exist in the index while being invisible to your transaction — deleted by a committed transaction, or created by one not yet committed. The index alone does not know.
Databases handle this with a structure tracking which pages contain only rows visible to everyone. If a page is marked all-visible, the index entry can be trusted without checking. If not, the table must be consulted after all, and the scan is no longer index-only.
The practical consequence: whether an index-only scan actually happens depends on cleanup state. A recently-updated table has many pages not marked all-visible, so the scan degrades toward a normal index scan.
Which means an index-only scan that worked in testing may not in production if the table is write-heavy and cleanup is behind. Rising update volume silently removes the optimisation — see MVCC explained and autovacuum tuning.
The write cost
Every column in an index is maintained on every write touching that row. A wide covering index makes reads fast and writes slower, permanently.
That trade is worth it for a query running constantly against a table written to occasionally. It is not worth it for a report running weekly against a hot table.
The discipline: add columns to an index because a specific frequent query needs them, and verify from the plan that the index-only scan actually happens. An index widened speculatively is pure write cost.
Common mistakes
- Including only filtered columns. Selected columns must be there too.
- Key columns where included would do. Enlarges the index structure unnecessarily.
- Assuming it works without checking the plan. Visibility state may prevent it.
- Widening indexes speculatively. Write cost with no read benefit.
SELECT * with a covering index. Guarantees a table fetch.- Ignoring cleanup on write-heavy tables. Silently disables the optimisation.
FAQ
How do I know if I got one?
The query plan names it explicitly. It will also report how many table fetches were still required — a non-zero count means visibility state is preventing a full index-only scan.
Does this work with partial indexes?
Yes, and it composes well — a partial covering index over a small subset of rows is both small and fast. See partial indexes.
Why did it stop working?
Almost always cleanup falling behind on a table that became write-heavy, leaving pages not marked all-visible.
Is there a limit on index width?
Engines have limits, and the practical constraint arrives first: a very wide index is large, slow to write, and consumes cache that other queries need.
Where to go next
For index fundamentals, read database indexing explained. For the visibility mechanism, MVCC explained, and for keeping cleanup current, autovacuum tuning.