Composite Indexes Explained: Why Column Order Is the Whole Game

#postgres #database #sql #backend

Composite Indexes Explained: Why Column Order Is the Whole Game

Why won't the database use your index? You created it on (user_id, created_at). Your query filters on created_at alone. You run EXPLAIN. Full table scan. The index is right there, doing nothing.

Because column order in a composite index isn't a suggestion. It's the whole mechanism. Get it wrong and your index might as well not exist.

I won't rehash what an index is or how B+ trees work internally. This post is about the one thing that trips people up most: which column goes first, and why it matters.

⚡ Think of it as sort order, not a "rule"

A composite index on (a, b, c) stores rows sorted by a first, then by b within groups of equal a values, then by c within each (a, b) group. Like a phone book sorted by last name, then first name.

This sort order is the whole explanation. You don't need to memorise a "leftmost prefix rule" as some separate law. Just think about what a sorted structure lets you do:

Searching on a alone? The matching rows are contiguous. Works.
Searching on (a, b)? Within the a group, b is sorted. Still contiguous. Works.
Searching on b alone? The b values are scattered across different a groups. Not contiguous.

That last case is the problem. And it's exactly why your (user_id, created_at) index can't help a query that only filters on created_at. The created_at values aren't sorted globally. They're sorted within each user.

But "can't use" isn't quite accurate anymore. MySQL 8.0.13 introduced Index Skip Scan — the optimiser iterates over each distinct value of the skipped column and does a range lookup for each. PostgreSQL 18 (September 2025) added native B-tree skip scan too. Both only kick in when the skipped column has low cardinality (a few hundred distinct values, not thousands). So the index isn't completely useless for a prefix-skipping query. It's just way less efficient than having the right column first.

🛠️ Equality first, range second

Once you understand sort order, column ordering strategy falls out naturally.

An equality predicate (WHERE status = 'shipped') narrows you to a contiguous group. The next column in the index is sorted within that group, so the database can keep navigating. But a range predicate (WHERE created_at > '2025-01-01') breaks contiguity for everything after it. Columns beyond the range column can only be filtered row-by-row, not searched through the index structure.

So: put equality columns before range columns. Always.

-- BAD: range column first breaks it for status
CREATE INDEX bad_idx ON orders (created_at, status);
SELECT * FROM orders
 WHERE created_at > '2025-01-01'
   AND status = 'shipped';
-- created_at range means status is just a post-filter

-- GOOD: equality column first, then range
CREATE INDEX good_idx ON orders (status, created_at);
SELECT * FROM orders
 WHERE status = 'shipped'
   AND created_at > '2025-01-01';
-- Navigates to status='shipped', then range-scans created_at within it

And this same logic explains when a composite index can serve ORDER BY without a separate sort step.

-- Index on (status, created_at) serves both filter and sort
CREATE INDEX idx_status_date ON orders (status, created_at);

SELECT * FROM orders
 WHERE status = 'shipped'
 ORDER BY created_at DESC
 LIMIT 20;
-- Navigates to status='shipped', walks backward. No filesort.

Mixed sort directions (ORDER BY a ASC, b DESC) need an index defined with matching directions. PostgreSQL has supported per-column direction since 8.3, MySQL since 8.0.

🎯 One composite versus several singles

You might think: just create a single-column index on each column and let the database figure it out.

PostgreSQL can actually do this. Bitmap index scans combine multiple single-column indexes by building in-memory bitmaps and ANDing or ORing them. But there are costs. You lose index ordering (a separate sort is needed for ORDER BY). Each extra index scan adds latency. And heap fetches come back in physical order, not logical. The PostgreSQL docs explicitly say a multicolumn index is "typically more efficient" than combining singles. It's like a single smart load balancer versus three dumb ones needing coordination.

MySQL's index merge does something similar but is even more limited.

So a single composite index almost always wins for multi-column queries. The trade-off is write cost. Every INSERT, UPDATE, and DELETE maintains all your indexes. More write amplification, more WAL traffic, bigger buffer pool footprint.

Spotting redundant indexes: if you have an index on (a, b) and another on (a, b, c), the shorter one is usually droppable. Any query that uses (a, b) can also use the (a, b, c) index. Audit your index list for prefixes.

-- These two indexes exist:
CREATE INDEX idx_ab ON orders (status, created_at);
CREATE INDEX idx_abc ON orders (status, created_at, customer_id);

-- idx_ab is redundant. idx_abc handles all its queries
-- Drop it to save write overhead
DROP INDEX idx_ab;

The one exception: the shorter index is significantly narrower (fewer bytes per entry = more entries per page), and you run high-volume queries on just those columns. In practice this is rare. Like an API gateway routing requests through unnecessary middleware, redundant indexes add write overhead for no read benefit.

📌 Key takeaways

Column order in a composite index follows directly from the sort order. The leftmost column is sorted globally, the second is sorted within groups of the first.
Queries skipping the leftmost column aren't completely blocked (skip scan exists in MySQL 8.0.13+ and PostgreSQL 18+) but are far less efficient. Put the column you actually filter on first.
Equality columns go before range columns. A range predicate breaks contiguity for everything after it.
One composite index beats multiple single-column indexes for multi-column queries. But each index costs writes.
If one index's columns are a prefix of another, you probably have a redundant index. Drop it.