What is a covering index? | Interview Question

Q: What is a covering index?

**Target Audience:** Beginners who know what an index is and keep hearing "make it a covering index", plus candidates who need to prove it with a query plan. **Difficulty:** Medium **Engines:** SQLite 3.45.0 through sql.js 1.10.2 (the runnable examples, plans from `EXPLAIN QUERY PLAN`) and PostgreSQL 16.15 (the Index Only Scan example). SQL Server and MySQL are described from their documentation and were not run here. > **How to read this doc:** Each idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every plan and result shown was produced by running it; the **Verified** section quotes each example output in full. ## 1. Why This Even Matters — A Story First A library has a card catalogue sorted by author. You want to know the *titles* of every book by one author. With an ordinary catalogue card that says only "author, shelf number", you find the right cards quickly, then walk to the shelves once

The problem

Question presented to candidate: "What is a covering index? Give me an example of a query that a covering index helps, and explain why it is faster than an ordinary index on the same column."

What a strong answer should cover:

A covering index is not a special index type: it is an index that contains every column a particular query needs (filter, join, sort and select list), so the engine answers that query from the index alone and never visits the table rows
Why it is faster: an ordinary index finds the matching keys, then does one extra lookup per row into the table; a covering index removes that lookup, which on a large table is the random I/O that dominates the cost
How to build one: put the filter columns in the key in the right order, then add the selected columns either as trailing key columns or as non-key payload with INCLUDE (PostgreSQL 11+, SQL Server); SQLite and MySQL have no INCLUDE, so you append them to the key
How to prove it: SQLite says USING COVERING INDEX in EXPLAIN QUERY PLAN, PostgreSQL says Index Only Scan (and Heap Fetches), MySQL says Using index in the Extra column
Engine details: in SQLite and InnoDB the row id or primary key is stored in every secondary index, so it is covered for free; in PostgreSQL an Index Only Scan still checks the visibility map and may fetch heap pages
Trade-offs: a wider index is bigger, slower to write and needs maintaining; cover the few hot queries, not every query

Code / implementation expected: Optional — an index, the query it covers, and the plan line that proves the table was not read.

Schema used by the examples:

covering-index

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who know what an index is and keep hearing "make it a covering index", plus candidates who need to prove it with a query plan. Difficulty: Medium Engines: SQLite 3.45.0 through sql.js 1.10.2 (the runnable examples, plans from EXPLAIN QUERY PLAN) and PostgreSQL 16.15 (the Index Only Scan example). SQL Server and MySQL are described from their documentation and were not run here.

How to read this doc: Each idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every plan and result shown was produced by running it; the Verified section quotes each example output in full.

1. Why This Even Matters — A Story First

A library has a card catalogue sorted by author. You want to know the titles of every book by one author. With an ordinary catalogue card th

The solution is hidden so you can try first

14 min read · 10 sections

Run the code

Edit the query and run it against the tables for this question.

SQLSQLite: the same query before and after the index covers it

SQLSQLite: the row id is in every index, so some queries are covered for free

SQLSQLite: the covered query returns the normal result

PostgreSQL: Index Only Scan with Heap Fetches: 0 on 100000 rows

-- 100000 generated orders: 5000 customers with 20 orders each
CREATE TABLE big_orders WITH (autovacuum_enabled = false) AS
SELECT g AS id,
       g % 5000 AS customer_id,
       (g % 997) + 0.5 AS amount,
       repeat('x', 80) AS note
FROM generate_series(1, 100000) AS g;
CREATE INDEX big_orders_customer_amount_idx ON big_orders (customer_id, amount);
VACUUM ANALYZE big_orders;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT customer_id, amount FROM big_orders WHERE customer_id = 42;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.