What Is a Covering Index?

Quick answer
A covering index contains all the columns a query reads, not just the ones it filters on, so the database can answer from the index alone.
That access path is called an index-only scan: it avoids the extra lookup into the table for each matching row.
In PostgreSQL you add the extra columns with INCLUDE; in MySQL and SQL Server you add them to the index’s column list.
The win is largest for queries that read a few columns from many rows, where the per-row table lookup dominates.
It costs more index size and write overhead, so add covering columns to an index you already need rather than creating new indexes everywhere.

What is a covering index?

It is an index that includes every column the query touches. A normal index stores the indexed columns plus a pointer back to the row, so a query that needs other columns has to follow that pointer — one table lookup per matching row. If the index already contains those other columns, no pointer is needed and the query can be answered from the index structure alone. That is a covering index, and the access path it enables is an index-only scan.

The difference is not subtle at scale. A query matching ten thousand rows with an ordinary index performs ten thousand row lookups, which are random reads; with full coverage it reads the index range and stops. The saving is the point of the technique, and it is why the columns a query selects matter to index design and not only the columns it filters on.

How to build one

PostgreSQL separates the two roles: the key columns — what you filter or sort by — and the included columns, declared with INCLUDE. That keeps the included columns out of the index’s sort structure, so they cost space but do not change how the index is searched. MySQL and SQL Server instead put all columns in the index definition, which affects both searching and size, so their order matters more carefully.

Add inclusion to an index you already need for the filter. Creating a fresh covering index purely so one query can avoid a lookup is usually a poor trade on a write-heavy table; extending an existing index with the one or two columns the hot query needs is usually a good one. Check the plan afterwards to confirm the index-only scan is actually happening, because the planner may prefer the ordinary path anyway.

-- PostgreSQL: key columns filter and sort; INCLUDE covers the select list.
CREATE INDEX idx_orders_customer_created
  ON orders (customer_id, created_at DESC)
  INCLUDE (total, status);

-- Every column this query reads is now in the index.
EXPLAIN (ANALYZE, BUFFERS)
SELECT created_at, total, status
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

-- Look for 'Index Only Scan' in the output.
-- In PostgreSQL, a high number under 'Heap Fetches' means
-- the visibility map is stale and VACUUM is needed.

Covering index versus ordinary index versus none

Compare three plans for one query that reads a handful of columns from many rows. With no index, the planner scans the table and reads everything. With an ordinary index, it walks the index for the matching keys and then performs a table lookup per row. With a covering index, it walks the index and answers directly. Each step removes a class of I/O, and the last one removes the random reads that usually dominate.

The trap is cost. The included columns duplicate data, so the index is larger, and each insert or update has to maintain it. On a table with heavy write traffic, adding coverage to several indexes slows writes measurably. The discipline is to cover the one or two queries that run most often on the largest tables, and to accept a lookup for the rest.

When an index-only scan does not materialise

In PostgreSQL the mechanism relies on a visibility map: only pages known to contain no dead tuples can be answered from the index alone. If the table is stale, the scan silently falls back to fetching from the heap, which shows up as a high heap-fetch count in the plan. Running VACUUM properly is therefore part of making covering indexes pay off, which is an easy thing to miss when tuning.

Three other reasons are common: the query selects a column you did not include, the WHERE clause is not selective enough for the planner to prefer the index, or a type mismatch (comparing text to an integer, for example) prevents the index being used for the filter at all. All three are visible in the plan, and all three look like “the covering index did not help” until you read it.

Where this bites vibecoders
Generated queries often do SELECT * on a list endpoint, which no index can cover, so the covering-index option never arises. Tightening the select list to the columns the UI actually renders is a prerequisite for covering those queries, and it is a useful change on its own because it reduces the bytes crossing the network as well. The assistant will happily add an index if asked, but it will not notice that the column list makes one impossible.

Where AI coding assistants get this wrong

Selecting every column and then expecting an index-only scan.
Creating a new index for coverage instead of extending one that is already needed.
Forgetting that included columns cost write throughput and storage.
Assuming the covering index is used without checking the plan.
Ignoring the visibility-map requirement and never running maintenance.
Covering low-value queries on write-heavy tables at the expense of writes.

Checklist

Reduce the select list to the columns the query actually needs.
Extend the index the query already filters on with INCLUDE for the rest.
Confirm the plan reports an index-only scan.
Check for heap fetches and run maintenance if they are high.
Measure the write cost on a write-heavy table before rolling out.
Keep coverage to the highest-value queries, not every query.

FAQ

What is the difference between a composite and a covering index?

A composite index has multiple key columns; a covering index contains all the columns the query reads. They overlap but are not the same: an index can be composite without covering the select list, and in PostgreSQL it can cover via INCLUDE columns that are not part of the key. The question to ask is whether the query can be answered from the index alone.

Does a covering index always make a query faster?

No. It removes row lookups, which is a large win when many rows are read, but it also makes the index bigger and can make scanning it more expensive for very wide coverage. On a small result set the saving may be negligible, and the added write cost may not be worth it. Check the plan and the timing rather than assuming.

Why is my index-only scan still slow?

Check the heap fetches. In PostgreSQL, if the visibility map is stale the scan still touches the table, which removes most of the benefit; regular vacuuming fixes it. Also confirm the filter is selective enough for the index to be chosen and that the column types match, since a type mismatch prevents index use entirely.

Should I add INCLUDE columns to every index?

No. Add them where a specific frequent query reads a small number of extra columns from many rows — that is where the win is. Blanket coverage inflates every index and slows every write. Treat each inclusion as a decision backed by a plan and a measurement on the query it is meant to help.