juniorDatabases

What is a covering index and when does it help?

Updated Feb 20, 2026

Short answer

A covering index contains every column a query needs, so the database answers it from the index alone without ever reading the table. That removes the row lookups an ordinary index still requires, and on a query returning many rows it can be the difference between milliseconds and seconds. The cost is a wider index: more storage, and slower writes.

Deep explanation

An ordinary secondary index stores the indexed columns plus a pointer to the row. Answering a query means two steps: seek the index, then fetch each matching row for the remaining columns. That second step — the bookmark lookup — is random I/O, one per row.

SQL
-- index on (customer_id) only
select order_id, total from orders where customer_id = 42;
--  1. seek index -> 500 matching row pointers
--  2. 500 random row lookups to read order_id and total

Widen the index to include what the query selects and step 2 disappears:

SQL
create index idx_orders_covering
    on orders (customer_id)      -- key: what we filter/sort by
    include (order_id, total);   -- payload: what we select
-- now: one index range scan, sequential, no table access at all

EXPLAIN shows this as Index Only Scan in Postgres, or the absence of a Key Lookup in SQL Server.

Key columns versus included columns. Key columns are ordered and usable for filtering, joining, and sorting. Included columns are stored only at the leaf level as payload — they cannot be searched but they make the index covering without bloating the tree. Put filter and sort columns in the key; put selected-only columns in the INCLUDE.

When it pays. Queries returning many rows, where the lookups dominate. Hot queries you run constantly. Reporting queries touching a few columns of a wide table.

When it does not. Queries returning one or two rows — two lookups cost nothing. SELECT *, which can never be covered on a wide table. Write-heavy tables, where every additional index column must be maintained on every insert and update.

A Postgres caveat worth knowing: an index-only scan still consults the visibility map, and if the table has not been vacuumed recently it must visit the heap anyway. A covering index on a heavily updated table may not deliver the expected benefit until autovacuum catches up.

Real-world example

A dashboard query over 50 million orders:

SQL
select status, count(*)
from orders
where merchant_id = 9001 and created_at >= '2026-01-01'
group by status;

With an index on (merchant_id, created_at), the engine seeks 200,000 matching rows and then performs 200,000 random lookups just to read status — several seconds.

SQL
create index idx_orders_dash
    on orders (merchant_id, created_at) include (status);

Now it is a single range scan over a contiguous slice of the index, reading status from the leaf pages — typically tens of milliseconds. The query did not change; only what the index carries did.

Common mistakes

  • - Adding every column to the index key rather than using INCLUDE, which bloats the B-tree and slows every level of traversal.
  • - Trying to cover a `SELECT *` query, which is impossible on any wide table and usually signals the query should select fewer columns.
  • - Creating covering indexes on write-heavy tables without measuring the insert and update cost they add.
  • - Getting the key column order wrong — the leading column must be the one used for equality filtering, or the index cannot be seeked efficiently.
  • - Assuming an index-only scan in Postgres is guaranteed
  • it depends on the visibility map, so a recently-updated table may still hit the heap.
  • - Adding indexes speculatively rather than from actual `EXPLAIN` output on real queries.

Follow-up questions

  • What is the difference between key columns and included columns?
  • How do you tell whether an index is being used as a covering index?
  • What is the cost of adding one?
  • Why might an index-only scan still read the table in Postgres?

More Databases interview questions

View all →