Covering indexes contain every column a specific query needs, so the database can answer it by reading the index alone and never touch the underlying table row. Normally, an index scan finds a matching entry, then follows a pointer to fetch the actual row for any column not stored in the index. A covering index removes that second step entirely — an index-only scan — one of the more reliable performance wins once a table is large enough for the difference to matter.
What changed in 2026
- INCLUDE clause usage kept growing as more developers learned it exists. It has been available in Postgres since version 11, but plenty of teams still default to widening the index key itself instead, which costs more than necessary.
- Visibility map monitoring got first-class dashboards. Several managed Postgres providers now surface the percentage of a table that is "all-visible," making it easier to see whether index-only scans are actually happening versus falling back to a heap fetch.
- MySQL's InnoDB covering-index behavior became easier to verify with clearer EXPLAIN output showing "Using index" versus "Using index condition," reducing guesswork about whether a query is actually covered.
What makes a scan index-only
Compare two nearly identical queries against orders(user_id, status, total, created_at) with an index on (user_id, status):
-- NOT covered: total is not in the index, so Postgres must fetch the row
SELECT total FROM orders WHERE user_id = 42 AND status = 'paid';
-- Covered: every selected and filtered column is in the index
SELECT user_id, status FROM orders WHERE user_id = 42 AND status = 'paid';
The second query can be answered from the index alone. The first needs one more step — a heap fetch — for every matching row, because total was never part of the index.
Building a covering index
The INCLUDE clause adds columns to the index for exactly this purpose, without making them part of the sortable key:
CREATE INDEX idx_orders_user_status
ON orders (user_id, status)
INCLUDE (total, created_at);
-- Now this query is fully covered:
SELECT total, created_at FROM orders
WHERE user_id = 42 AND status = 'paid';
INCLUDE columns live in the index's leaf pages but are not used for sorting or range comparisons — they exist purely so a covered query never needs to visit the table. That makes them cheaper than extending the key itself, since a wider key means a larger tree and slower comparisons at every level.
The catch: visibility and write cost
| Requirement |
What happens if it is missing |
| All selected columns present in the index (key or INCLUDE) |
Falls back to a normal index scan with a heap fetch per row |
| Postgres visibility map marks the relevant pages all-visible |
Falls back to a heap fetch even though the index technically covers the query |
| Table is not extremely write-heavy on the included columns |
Every UPDATE to an included column still has to update the index, same as any indexed column |
The visibility map point catches people off guard: Postgres uses MVCC, so a row's visibility cannot always be determined from the index alone — it may need to check the table, unless the visibility map already marked that page all-visible. A table with heavy recent writes and infrequent VACUUM can have a covering index that still triggers heap fetches simply because the visibility map has not caught up.
When to reach for one
Covering indexes pay off most for narrow, frequently run queries — an API endpoint hit thousands of times a minute, a hot path in a dashboard, a lookup inside a larger transaction. They are a form of query optimization that trades write cost and index size for read speed on exactly the queries you choose to cover, a better trade than blindly including every column in every index. A materialized view is a related but different tool for a similar goal — pre-computing an entire result rather than avoiding one row fetch per match.
FAQ
Is a covering index the same thing as a composite index?
Related but distinct. A composite index spans multiple columns for filtering and sorting; a covering index specifically contains every column a query needs so it never has to fetch the row. A composite index can happen to be covering, or not, depending on what a given query selects.
Do I need INCLUDE, or can I just add the columns to the index key?
INCLUDE is usually better when those columns are not used for filtering or sorting — it keeps the sortable key narrower and the tree structure smaller, which speeds up every lookup, not just the covered ones.
How do I check whether a query is actually getting an index-only scan?
Run EXPLAIN with ANALYZE and BUFFERS and look for "Index Only Scan," plus the "Heap Fetches" count — a high count means the visibility map is not keeping up despite the index technically covering the query.
Where to go next