Covering Index
A covering index includes every column a query needs, letting the engine answer from the index alone and skip the table heap entirely, turning random reads into sequential ones.
Definition
A covering index contains all columns referenced by a query — filter columns, join keys, and selected values. The executor can then satisfy the query from index entries only, avoiding heap or clustered-index lookups per row. Databases report this as an index-only scan or covering plan.
Context and scope
Applied to hot read paths: dashboard aggregates, existence checks, API list endpoints. PostgreSQL heap fetches degrade the benefit until the visibility map is fresh; MySQL InnoDB secondary indexes cover automatically against the clustered key.
Common misconceptions
- A covering index makes writes free — every added column widens the index, increasing write amplification and cache pressure
- Any index that matches the WHERE covers the query — coverage requires the SELECT list too, not just filters
- Index-only scans never touch the table — in PostgreSQL, stale visibility maps cause heap checks per row
Anti-patterns
- Adding many marginal columns to cover one rare query and bloating every insert and update
- Selecting a computed expression the index cannot store and losing index-only plans
- Assuming EXPLAIN shows index-only when visibility-map churn forces heap fetches in production
Related concepts
Related examples
Authoritative source
https://www.postgresql.org/docs/current/indexes-index-only-scans.html
What it is
A normal index finds rows, then the engine fetches each full row from the heap. A covering index answers the whole query from the sorted, compact index structure: filters, returned columns, and ordering all come from one place. Random point lookups disappear from the plan.
How it works
The optimizer notices every required column exists in the index and chooses an index-only scan. PostgreSQL additionally consults the visibility map: pages where all rows are visible to all transactions skip the heap check, so vacuum freshness directly controls the speedup. InnoDB secondary indexes implicitly carry the primary key, widening coverage for free.
When to use it
Repetitive read-heavy queries with narrow column sets: counters, existence probes, ranked lists, and join projections. Combine with partial indexes to cover a filtered subset, keeping size proportional to the hot slice of data.
Pitfalls
Wide covering indexes slow every insert and update of indexed columns and inflate buffer pool usage. Extra columns can help one query and starve others sharing the cache. Revisit after schema changes: a dropped column silently invalidates coverage, and the plan quietly regresses to heap scans.