What index anatomy forbids

Which combinations can no index give, and what does each escape cost?

What the anatomy forbids

You cannot have two clustered orders.
You cannot make an index free on the write path, only smaller, narrower or deferred.
You cannot get index-only scans without duplicating columns.
You cannot index an expression whose definition you cannot pin down.
You cannot prune with a zone map what you have not clustered.
You cannot use a partial index the planner cannot prove applies.

The escapes and their prices

Covering escapes the fetch and pays in duplication.
Partial escapes the maintenance and pays in a proof obligation.
Sparse escapes the space and pays in a required sort order.
Bitmap escapes the selectivity crossover and pays in update cost.
Skip scan escapes the leftmost rule and pays in leading-column cardinality.
Late binding escapes rewrite-on-move and pays a descent per lookup.

Every one of them relocates a cost; none removes one.

Case, resolvedConstructed case · Priya at Parcelo

A partial index that works only in the console

A partial index for pending pickups worked in the SQL console but not from the application, a covering index quietly became slow again, inserts into a UUID-keyed MySQL table were slow and the table was huge, and an (a, b, c) index still read a large range for a = 1 AND b > 5 AND c = 3. Where it started: Index anatomy →

The unused partial index. The ORM sends WHERE status = $1. The planner cannot prove $1 = 'pending' for every possible value, so it ignores the partial index. She changes the pending-work query to use the literal status = 'pending' (and adds a comment explaining why). The "find work" query drops from seconds to under a millisecond.
The covering index that decayed. The table is write-heavy and autovacuum was falling behind, so many pages were not marked all-visible and the "index-only" scan fetched table rows anyway. She tunes autovacuum for that table and verifies with EXPLAIN (ANALYZE) that "Heap Fetches" is near zero.
The UUID table. The billing service uses random UUIDv4 text as its InnoDB primary key. Each insert splits a random page, and the 36-byte key is copied into six secondary indexes. They move to a time-ordered UUIDv7 stored as 16 binary bytes. Inserts become append-like, and the table and its indexes shrink noticeably.
The composite order. For a = 1 AND b > 5 AND c = 3 she rebuilds the index as (a, c, b). The scan now reads one contiguous range and no longer filters on c entry by entry.

She also writes a short rule on the team wiki: after creating any index, check that it is chosen with the real query, and put a calendar reminder to drop indexes with zero scans.