跪拜 Guibai
← All articles
SQL · MySQL · PostgreSQL

Covering Indexes: What They Actually Save You, and the MySQL vs. PostgreSQL Gotchas

By ENCI ·
Read original on juejin.cn ↗ Google Translate ↗ Alt translation

Slow queries that scan few rows but take hundreds of milliseconds are usually dying on random I/O from lookups. Knowing how to design and verify a covering index, and where PostgreSQL's behavior diverges from MySQL's, can cut that latency without adding storage or write overhead blindly.

Summary

InnoDB secondary indexes store only the indexed columns plus the primary key, not the whole row. When a query returns columns outside the index, each row requires a second B+ tree traversal back to the clustered index, a lookup that turns into random I/O on cold data. A covering index avoids that by putting every filter, sort, and return column into the same index, so the executor never touches the table data.

The key checks are filter columns following the leftmost prefix, sort columns aligned with the index order, and every SELECT column present in the index. SELECT * is the most common killer. MySQL's EXPLAIN shows Using index for true covering; Using index condition means index pushdown but still requires a lookup, a value developers often misread as covering.

PostgreSQL has its own trap: Index Only Scan only skips the heap when the visibility map marks pages all-visible. After bulk loads or updates, Heap Fetches can still equal the row count unless VACUUM runs. PG secondary indexes also omit the primary key, so to cover an id column you must explicitly add it via INCLUDE. The article ends with a symptom-to-fix table and three rules for when not to force a covering index.

Takeaways
— A covering index is not a special index type; it is any index that happens to contain the filter, sort, and return columns for a specific query.
— The three checks are: filter columns follow the leftmost prefix, ORDER BY columns follow equality columns in index order, and every SELECT column is in the index.
— SELECT * is the top reason a covering index silently stops covering when a new column is added later.
— In MySQL EXPLAIN, only a standalone `Using index` means true covering; `Using index condition` (ICP) still requires a lookup.
— InnoDB secondary indexes implicitly carry the primary key, so long primary keys like UUID make every secondary index wider.
— PostgreSQL's Index Only Scan can still do heap lookups when the visibility map is not marked all-visible; run VACUUM and check Heap Fetches.
— PostgreSQL secondary indexes do not include the primary key, so you must explicitly add it with INCLUDE (id, ...) to cover an id column.
— INCLUDE columns in PG participate only as payload; they cannot be used for WHERE, ORDER BY, or unique constraints.
— Design covering indexes backward from slow query log entries, then confirm with EXPLAIN, and re-check write performance after adding columns.
— Skip covering indexes when return columns are too many, the filter selectivity is low, or write patterns are heavy and read patterns unstable.
— Redundant indexes follow the leftmost prefix rule; `(user_id, created_at)` is fully covered by `(user_id, created_at, amount, status)` and can be dropped.
Conclusions

The distinction between `Using index` and `Using index condition` is a common source of overconfidence about covering; the article makes the test simple: look for the exact phrase.

PostgreSQL's visibility map requirement means a freshly loaded table can show an Index Only Scan plan while still performing lookups, so execution plan names alone mislead.

The recommendation to start from slow queries rather than table creation flips the common habit of pre-emptively indexing wide columns, which often just bloats writes.

The trade framework, random read swapped for sequential storage, gives a concrete way to decide based on read/write ratio rather than on index aesthetics.

Concepts & terms
Covering Index
An index that contains every column a query needs for filtering, sorting, and returning, allowing the database to produce results without reading the table rows.
Lookup (Table Lookup)
The step where a secondary index gives primary keys and the database then traverses the clustered index to fetch the full row. Each lookup is a random read on cold data.
Index Condition Pushdown (ICP)
A MySQL optimization that pushes filter conditions down to the storage engine to reduce rows read from the index, but the engine still has to fetch the full row from the clustered index, so it is not a covering read.
Visibility Map
A PostgreSQL data structure that marks pages where all rows are visible to every transaction, letting an Index Only Scan avoid checking the heap table for visibility.
Index Only Scan
A PostgreSQL execution plan node that reads only the index to produce results. It can still fall back to heap lookups when the visibility map is not all-visible.
INCLUDE Clause (PostgreSQL)
Introduced in PG 11, it adds columns as payload in the index leaf without making them part of the key, so they can satisfy the RETURN list without contributing to search or sort.
Leftmost Prefix
The rule that an index on `(a, b, c)` can be used for queries filtering on `a`, on `a` and `b`, or on `a`, `b`, and `c`, but not when the leftmost column is missing.
Redundant Index
An index that has the same leftmost prefix as another index, so it contributes no additional coverage while still adding write and storage overhead.
From the discussion
Featured comments
卷土的土

Even if it's covered, you still need to check the heap access count.

See top comments, translated →
Source: juejin.cn ↗ Google Translate ↗ Backup ↗