Covering index
A covering index supplies the data values needed by a particular query from index entries, without base-table access to obtain those values.
Coverage depends on the query, not just the index name. Adding an output column or a filter on a value absent from the index can remove coverage. SQLite illustrates this by appending output columns to a multi-column index. [1]
PostgreSQL can carry non-key payload with INCLUDE. In UNIQUE (x) INCLUDE (y), uniqueness applies to x alone; y is not a search key. Wide payload duplicates storage and can exceed the index tuple size limit. B-tree deduplication is unavailable when an index has a non-key column. [3]
Coverage does not promise zero heap fetches. PostgreSQL may visit the heap to check visibility when its all-visible map bit is clear. The planner must also support and choose the access path. Partial-index membership can establish a predicate without storing its column. [2]
Covering describes available query values, not a checksum, a content-verification operation or a measured speedup. The linked analysis separates these questions. [4]
Relations
Citations
- SQLite Query Planning: covering indexes
- PostgreSQL 18: Index-Only Scans and Covering Indexes
- PostgreSQL 18: CREATE INDEX, INCLUDE
- Hesela: PostgreSQL TOAST and index read coverage
Sources & caveats
- Primary-source synthesis; PostgreSQL INCLUDE and visibility behavior scoped to version 18 documentation, separate from the analysis's earlier PostgreSQL 16.15 experiments. No benchmark or new PostgreSQL execution.