A composite index on (country, age) isn’t two indexes — it’s one B-tree sorted
by country first, and by age within each country. That single fact decides
everything about which queries it can serve. (If the single-column story isn’t
solid yet, start with how an index finds a row.)
The leftmost-prefix rule
An index on (a, b) can serve a filter on a, or on a AND b — but not on b
alone. Walk three queries against an index on (country, age):
WHERE country = 'IN'— theINrows are one contiguous block; the index jumps straight to it. ✓WHERE country = 'IN' AND age > 30— jump to theINblock, and since ages are sorted inside that block,age > 30is a clean contiguous slice. The index serves the equality and the range in one shot. ✓WHERE age > 30— with no country pinned,agevalues are scattered across every country’s block; you’d have to visit them all and re-filter, which is no better than a scan. The index effectively can’t serve it. ✗
That’s the leftmost-prefix rule: an index on (a, b) serves a and a AND b,
never b by itself.
The real ordering rule: equality first, range last
“Put the most-queried column first” points the right way but isn’t the rule. The rule comes straight from the sort order:
- An
=predicate pins you to a single contiguous block, and everything after it is still fully sorted inside that block — so the next column’s range works. - A range predicate (
>,<,BETWEEN) spreads you across many values, and once you’re spread, any column after it is scattered within that spread and becomes unusable for seeking.
So (country, age) serves country = 'IN' AND age > 30 perfectly — equality pins
the block, range slices it. Flip it to (age, country) and the same query gets
much worse: age > 30 spreads you across dozens of ages, and country is
scattered inside every one. Same two columns, opposite performance, decided
entirely by order.
The heuristic
Order composite index columns as: equality columns first (most selective among them earliest as a tiebreaker), then a single range column, then any columns you only want along for the ride. And the mirror image is the number-one reason people say “the planner ignored my index”: the query filters on a column that isn’t a leftmost prefix of any index, so there’s nothing for the planner to seek into.