Composite indexes cover more than one column in a single index structure, sorted by the leading column, then by the next column within each group, and so on down the list. The mechanics are simple once you see them; the part that trips people up is that column order is not cosmetic. The same three columns, indexed in a different order, can turn a fast query into a sequential scan with nothing else changing.
What changed in 2026
- Index skip scan support arrived in more engines. Postgres 18 and recent MySQL versions can now use a composite index even when a query skips its leading column, in some cases — a limited exception to the traditional left-prefix rule, not a replacement for good column ordering.
- Redundant-index detection tools got better.
pg_stat_user_indexes dashboards in managed providers now flag single-column indexes made obsolete by an existing composite index automatically.
- More query plan visualizers highlight index-column usage directly — showing exactly which columns of a composite index a given plan actually used, rather than just "index used: yes."
What a composite index actually stores
Think of a composite index on (status, created_at) as a sorted list: every row grouped by status first, and within each status group, sorted by created_at. That structure directly serves a query filtering on status alone, and one filtering on status plus ordering or range-filtering by created_at, since within any one status the rows are already in created_at order. It does not serve a query filtering on created_at alone, since the index is not sorted by created_at globally, only within each status group.
The column order rule
The working rule: equality-filtered columns first, in roughly descending order of selectivity, then the one column you range-filter or sort by last.
-- Query: WHERE hotel_id = 42 AND status = 'confirmed' AND check_in_date > '2026-08-01'
-- Correct order: both equality columns first, range column last
CREATE INDEX idx_bookings_hotel_status_checkin
ON bookings (hotel_id, status, check_in_date);
-- Wrong order for the same query — range column placed too early
CREATE INDEX idx_bookings_wrong
ON bookings (check_in_date, hotel_id, status);
-- This can only use check_in_date as a range scan; hotel_id and status
-- cannot narrow the scan the way they could in the first version.
The columns after the first range predicate in the index stop contributing to narrowing the scan — they can still be checked, but only row by row, not through the sorted structure.
A worked example
Suppose bookings filters most often by hotel_id and status, then sorts by check_in_date:
| Index |
Query it fully serves |
Query it partially serves |
(hotel_id, status, check_in_date) |
WHERE hotel_id = ? AND status = ? sorted by check_in_date |
WHERE hotel_id = ? alone (uses only the leading column) |
(status, hotel_id, check_in_date) |
Same query, different column written first — equally valid if status is more selective |
Queries filtering only by hotel_id (skips the leading column) |
(check_in_date, hotel_id, status) |
Range queries on check_in_date alone |
Nothing that also needs to filter tightly by hotel_id first |
The first two rows are both defensible depending on which column eliminates more rows on your actual data; the third is very likely wrong for this workload.
When a composite index replaces several single-column indexes
A single composite index on (hotel_id, status, check_in_date) already serves any query that filters on hotel_id alone, since that is the leading column — a separate single-column index on just hotel_id is redundant and only adds write cost. This is one of the most common wins available: audit pg_stat_user_indexes for indexes with a leading column duplicated by a wider composite index, and drop them. It shrinks the query optimization surface the planner has to reason about, cutting write overhead with no read-side downside.
Common mistakes
Ordering columns by how they appear in SELECT, not WHERE. Column order should be driven entirely by how the query filters and sorts, not by any other convention.
One composite index per query, without checking for overlap. Before adding a fourth narrow composite index, check whether an existing one already covers the new query as a left-prefix.
Forgetting that write cost scales with every index. A composite index still costs a write on every insert and update, same as a single-column one — consolidating three indexes into one composite index is a genuine improvement, not a free additional index.
FAQ
Does column order in a composite index matter for ORDER BY too?
Yes — put the sort column last, after your equality-filter columns; matching sort direction (ascending or descending) can avoid an extra sort step.
How many columns should a composite index have?
There is no fixed limit, but each extra column adds diminishing returns and more write overhead. Three or four columns covering your most common filter pattern is typical; wider than that usually signals the query needs rethinking.
Is a composite index the same as a covering index?
No, though they can overlap. A composite index is about which columns form the sortable key; a covering index is about including enough columns that the query never fetches the full row.
Where to go next