Skip to content

CONCEPT Cited by 2 sources

Covering index

A covering index is a secondary index that contains every column the query projects — so the query can be answered entirely from the index, without the usual second walk into the clustered index (or heap) to fetch the row. In Postgres this shape is called an index-only scan; in MySQL / InnoDB it's a covering index. Same optimisation, two names.

Terminology mapping

Justin Gage canonicalises the cross-engine parity verbatim:

An [index only scan] ([covering index] in MySQL) refers to when the value your query is looking for is contained solely in the index, and thus doesn't require a table lookup. In PostgreSQL, not all index types support index-only scans.

— sources/2026-04-21-planetscale-how-do-database-indexes-work

Engine Name Exposed in
MySQL / InnoDB Covering index EXPLAIN's Extra: Using index
Postgres Index-only scan EXPLAIN's Index Only Scan node
SQL Server Covering index (explicit via INCLUDE) execution plan

The Postgres caveat — "not all index types support index-only scans" — reflects that Postgres B-tree indexes support it, but e.g. GIN / GiST indexes historically did not (GiST gained partial support later).

Why it matters: eliminate the second walk

On a clustered-index table, a normal secondary-index lookup does two B+tree walks:

  1. Walk the secondary index → find the primary key of each matching row.
  2. Walk the clustered index (the table itself) → fetch the full row to project the requested columns.

When the secondary index already contains all projected columns in its leaves, step 2 is skipped. The read path becomes one B+tree walk instead of two — particularly valuable for range scans that would otherwise do many random clustered-index fetches.

-- Not covering: index on email, query projects username
CREATE INDEX email_idx ON user(email);
SELECT username FROM user WHERE email = 'x@planetscale.com';
-- Walks email_idx → gets user_id → walks PK → fetches username.

-- Covering: composite index includes projected column
CREATE INDEX email_cover ON user(email, username);
SELECT username FROM user WHERE email = 'x@planetscale.com';
-- Walks email_cover → already has username → no PK walk.

In MySQL this shows up in EXPLAIN as Extra: Using index (see concepts/mysql-explain).

Trade-offs

The covering-index optimisation buys read speed at three costs:

  • Write latency — every UPDATE to a covered column must update the covering index too. If a non-key column is included only to cover projections, its writes now touch an extra B+tree.
  • Storage — the secondary index leaves are wider; the index uses more disk and more page-cache pressure.
  • Fragility — the covering property holds only if the query projects exactly the columns in the index. Adding a new projected column to the query (e.g. SELECT username, created_at) silently breaks the covering scan and reintroduces the clustered-index walk.

The usual guidance is to make an index covering only for well-known hot read paths whose projection list is stable, not by reflex.

Postgres specifics

Postgres added INCLUDE columns (non-key payload columns attached to a B-tree index) in PG11 for exactly this purpose:

CREATE INDEX email_cover ON users (email) INCLUDE (username);

Before INCLUDE, the same effect required adding the payload column as an additional key column — which wasted key-ordering bytes and restricted the index shape. Postgres also requires the visibility map to be up to date for an index-only scan to actually avoid heap fetches: when recent writes haven't been vacuumed, Postgres may still visit the heap to check row visibility, even on a covering index. This is the Postgres-specific limitation Gage alludes to.

Seen in

Last updated · 550 distilled / 1,221 read