Databases
Composite index design: interview questions and how to answer them
A multi-column index is sorted by its first column, then the second within that, and so on — which is why column order determines which queries it can serve.
Written and reviewed by Sahil Srivastav
What it actually is
A composite index is a single B-tree whose keys are tuples. Entries are ordered by the first column; rows sharing a first-column value are then ordered by the second, and so on. That is the entire mechanism, and nearly every practical rule about composite indexes follows from it directly.
The consequence people memorise as "the leftmost prefix rule" is really just a property of the sort: the index is contiguous for a prefix of its columns. An index on (tenant_id, created_at, id) can serve a query on tenant_id, or on tenant_id plus created_at, but not on created_at alone — entries for a given timestamp are scattered across every tenant, so there is no range to seek.
The distinction that matters most for design is between seeking and filtering. Columns used for seeking narrow the range the database reads. Columns after a range predicate can only filter rows already read. Both appear in EXPLAIN as "index used", which is why a badly ordered index can look fine and still read a hundred times more than necessary.
Why it matters in production
Because column order is the difference between an index that answers a query in one seek and one that reads a large range and discards most of it. The two indexes contain exactly the same data and cost the same to maintain. One of them makes the endpoint fast and the other does not, and the plan output will claim both are being used.
It also decides how many indexes you need at all. A correctly ordered composite index frequently makes two or three single-column indexes redundant, which directly reduces write amplification. Teams that add one index per query end up maintaining five structures where two would have served — a cost paid on every insert and update, forever.
How it works
Equality columns first, then the range column
Columns compared with equality narrow the index to a contiguous block. Once you hit a column compared with a range — >, <, BETWEEN — the block is no longer contiguous for anything after it. So all equality predicates belong before the single range predicate. WHERE tenant_id = $1 AND created_at > $2 wants (tenant_id, created_at); reversed, the database must scan every row newer than the timestamp across all tenants.
The sort column comes after the equality columns
If the index is ordered by exactly what ORDER BY asks for, within the block the predicate selected, the database can return rows in order and stop at LIMIT without sorting anything. This is what makes a paginated query constant-cost. Getting it wrong means producing the whole matching set, sorting it, then discarding all but a page — the single most common cause of a list endpoint degrading as data grows.
Direction matters when you mix them
For a single column, the engine can read the index backwards, so DESC needs no special index. For multiple columns with mixed directions — ORDER BY created_at DESC, id ASC — a plain index cannot be read in that order in one direction, and you need the directions declared in the index itself.
Selectivity is a tiebreaker, not the rule
The common advice to put the most selective column first is a heuristic that only applies after the structural rules. Equality before range, and sort-supporting order, both outrank it — a highly selective column placed after a range predicate still cannot be used for seeking.
INCLUDE adds payload without affecting order
Columns in INCLUDE are stored in the leaf pages but are not part of the sort key. They cannot be used for seeking, but they can satisfy the query without a heap fetch, turning a plan into an index-only scan. This is the right tool when you need a column in the SELECT list but never in a predicate.
Implementing it
Derive the index from the query, in this order: all equality predicates, then the range predicate or the sort column, then anything needed only for output via INCLUDE. Write that down before creating anything, then confirm with EXPLAIN that there is no sort node and that rows removed by filter is small.
Check the rows-removed-by-filter number in EXPLAIN ANALYZE. A plan that uses your index and still discards most of what it read is telling you the column order is wrong, not that the index is missing.
Audit for redundancy after adding one. An index on (a) is fully covered by (a, b) and can be dropped; an index on (b) is not.
For keyset pagination, build the index to match the tuple comparison and the ordering exactly, including direction — (created_at DESC, id DESC) for WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC.
-- Query: one tenant’s recent orders, newest first, one page
SELECT * FROM orders
WHERE tenant_id = $1 AND created_at >= $2
ORDER BY created_at DESC
LIMIT 20;
-- Wrong order: range column first. The database scans every row newer than
-- $2 across ALL tenants, then filters by tenant, then sorts.
CREATE INDEX ON orders (created_at, tenant_id);
-- Right order: equality first, then the range/sort column. One seek, rows
-- already in order, stops at 20. No sort node in the plan.
CREATE INDEX ON orders (tenant_id, created_at DESC);Interview questions and how to answer them
Your query filters on tenant_id and orders by created_at. What index?
(tenant_id, created_at), with the direction matching the ORDER BY. The equality on tenant_id narrows to a contiguous block, and within that block entries are already ordered by created_at, so the database can read the page and stop — no sort, and work bounded by page size rather than by the tenant's total history.
Why does column order matter if both indexes contain the same columns?
Because the index is sorted by the tuple, left to right. Only a prefix of the columns is contiguous, so only a prefix can be used to seek. Columns after a range predicate can filter what was read but cannot narrow what is read. Same data, same maintenance cost, very different amount of I/O.
When would you use INCLUDE instead of adding the column to the key?
When the column is needed in the output but never in a predicate or an ORDER BY. Keeping it out of the key keeps the key narrower and the tree shallower, while still allowing an index-only scan. Putting it in the key would add sort structure you never use and cost on every write.
How can you tell the column order is wrong from EXPLAIN?
Look for a large "Rows Removed by Filter" under an index scan, or a Sort node above a scan that an index should have made unnecessary. Both mean the index was used and the order was wrong — the plan says "index scan" either way, which is why people stop looking too early.
Is it better to have one composite index or several single-column indexes?
Usually the composite, when the query uses the columns together. The engine can combine separate indexes via a bitmap scan, but that costs an extra step and loses the ordering benefit. Separate indexes are right when the columns are genuinely queried independently.
Answers that lose the round
- Assuming an index on (a, b) serves queries filtering on b alone
- Putting the range predicate before an equality predicate
- Ordering by selectivity before satisfying the structural rules
- Accepting "index used" in the plan without checking rows removed by filter
- Creating single-column indexes already covered by the leading column of a composite one
- Forgetting direction when the ORDER BY mixes ASC and DESC
- Adding output-only columns as key columns instead of using INCLUDE
FAQ
Does the optimiser reorder my index columns?
No. It can choose whether to use the index, and it can reorder the predicates in your WHERE clause, but the index's physical sort order is fixed at creation. That is why the ordering decision is yours and why it is permanent until you rebuild.
How many columns is too many in one index?
Each extra key column widens every entry, reducing fan-out and increasing size, so beyond three or four the returns diminish quickly. If you need more for output rather than filtering, move them to INCLUDE — payload columns do not widen the key.
Does this apply to MySQL as well as PostgreSQL?
The leftmost-prefix rule and the equality-before-range rule are properties of B-trees and apply to both. The differences are elsewhere: InnoDB tables are clustered on the primary key, so secondary indexes carry the primary key as their pointer, and MySQL lacks INCLUDE — you add the column to the key instead.
What about indexes for joins?
The join column on the inner side needs an index for a nested-loop join to be efficient, and the same ordering rules apply if the join is combined with filters. A missing index on a foreign key column is the usual reason a join degrades to a hash or sequential scan.