Covering Index

DatabasesMiddb

The Question

What is a covering index?

What a Strong Answer Covers

• Index-only scan
• no heap lookup.

Senior-Level Answer

A covering index is an index that contains every column referenced in a query — in the SELECT list, WHERE clause, ORDER BY, and JOIN conditions — so the database engine can satisfy the entire query by reading the index alone, without accessing the underlying table (heap) pages. This access pattern is called an **index-only scan**.

To understand why this matters, consider how a standard B-tree index works. The index stores the indexed columns plus the row's physical location (heap pointer). For a query like `SELECT name, email FROM users WHERE email = 'x@y.com'`, if only `email` is indexed, the engine finds the matching rows in the index, then follows heap pointers to fetch `name` from the table pages — two I/O operations per row.

If you create `CREATE INDEX idx ON users(email, name)`, the index now contains both `email` and `name`. The engine finds the matching `email` entry in the index and reads `name` directly from the index leaf node — zero heap I/O. For high-cardinality filtered queries returning many rows, this difference is dramatic.

**When to use covering indexes:** - High-frequency read queries with stable column sets (dashboards, hot API endpoints) - Queries on large tables where heap I/O is the bottleneck - Sorting: if the covering index matches the ORDER BY, you avoid a sort step - COUNT/aggregate queries over filtered sets

**Trade-offs:** - Write overhead: every INSERT, UPDATE, or DELETE must update the index as well as the heap. Wider indexes mean more index maintenance. - Storage: covering indexes can be large — you're duplicating data into the index - Column order matters: the leftmost columns must match the WHERE clause for the index to be used at all. The covering columns (SELECT list) typically go at the end.

In PostgreSQL, `EXPLAIN (ANALYZE, BUFFERS)` shows `Index Only Scan` and `Heap Fetches = 0` when a covering index is fully effective. In MySQL InnoDB, secondary indexes already include the primary key, so covering is simpler to achieve — include all needed non-PK columns.

A practical pattern: identify your slowest N queries, check their execution plans for `Seq Scan` or `Heap Fetches > 0`, and add covering indexes targeting the specific column set of each hot query.

What Separates a 2/3 from a 3/3

Common Mistakes

• Defining covering index as just 'an index on multiple columns' — the key distinction is that it covers the entire query without heap access.
• Ignoring column order — putting SELECT columns before WHERE columns defeats the index for filtering.
• Not mentioning write overhead — covering indexes are not free.
• Forgetting to mention EXPLAIN/query plan verification to confirm the index is actually used.

Follow-Up Questions

• How does column order in a composite index affect whether it can be used as a covering index? — The leading columns must match the WHERE/JOIN predicates for the index to be selected; covering columns go at the end.
• What does 'Heap Fetches' in a PostgreSQL EXPLAIN output tell you? — Nonzero Heap Fetches means the index-only scan had to visit the table for some rows — usually because the visibility map is not up to date (VACUUM needed).
• How would you identify which queries in production would benefit from covering indexes? — pg_stat_statements for slow queries, EXPLAIN ANALYZE for seq scans and high heap fetches, look for hot read paths with stable column sets.
• When would you explicitly NOT add a covering index despite a slow query? — Write-heavy tables where index maintenance cost outweighs read gain; tables with frequent bulk updates; when the query is infrequent enough that the storage/write cost is not justified.

Related Questions

• ACID
• Indexes
• Clustered vs Non-clustered
• Isolation Levels
• Normalization