CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 4 of 5 · 2 min read

Covering indexes avoid touching the table at all

When an index holds every column a query needs, the table itself never has to be read.

Covering indexes avoid touching the table at all

Think of a library catalog card that lists not just a book's shelf location, but also its full title, author, and page count. A visitor who only needs those details never has to walk to the shelf at all.

In plain words: normally, an index lookup finds a matching row's location, and the database still has to read the actual table row to get any column that is not part of the index. A covering index includes every column a particular query needs, either as part of the sorted key or as extra stored data attached to each entry, so that query can be answered entirely from the index itself, called an index-only scan, without ever reading the table.

A worked example. An index on (customer_id, placed_on, order_id) can fully answer SELECT order_id, placed_on FROM orders WHERE customer_id = 42 as an index-only scan, since every selected and filtered column is already in the index.

The key lines.

CREATE INDEX idx_orders_covering ON orders (customer_id, placed_on, order_id);
SELECT order_id, placed_on FROM orders WHERE customer_id = 42;
-- index-only scan: every needed column is already in the index

Covering index, index-only scan

A covering index includes every column a specific query needs, letting that query run as an index-only scan, answered entirely from the index without reading the table.

Example: a catalog card listing a book's full details, not just its shelf location, lets a visitor skip the shelf entirely, the same way a covering index lets a query skip the table.

Remember this

A composite index's column order should put the most commonly filtered column first. A covering index goes one step further, adding extra columns so a specific query never touches the table at all.

Quick notes

  • A composite index sorts several columns together, in a fixed, declared order.
  • Only the leading, leftmost column, alone or with later columns in order, can be used efficiently.
  • A query filtering only on a later column, skipping the leading one, gets little benefit from the index.
  • A covering index includes every column a query needs, enabling an index-only scan that skips the table.
  • Column order should match the most common query patterns when a composite index is designed.