Covering Indexes: What They Actually Save You, and the MySQL vs. PostgreSQL Gotchas
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.
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.
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.
Even if it's covered, you still need to check the heap access count.