Leftmost Prefix Rule

On the last page, we built one composite index for "My Orders": (user_id, status, created_at). It made that query read 20 rows instead of 500. We also saw a catch: it only helps queries that match its column order. This page explains exactly which queries it can and can't help.

The Problem: A New Query Ignores Our Index

Our food delivery app adds an admin dashboard. Support staff need every order that is still pending:

SELECT * FROM orders WHERE status = 'pending';

We already have an index that contains status, so this should be fast. But EXPLAIN ANALYZE shows a Seq Scan: the database reads all 10 million rows.

1Query: status = 'pending'
2Index: (user_id, status, created_at)
3Plan:  Seq Scan on orders   → full scan, index ignored

The index has the column, but the database can't use it. Here's why.

Why: How the Index Is Sorted

Remember the phone book: sorted by last name, then first name.

• Find "Sharma" → easy, they're all together.
• Find "Sharma, Rahul" → easy, inside the Sharma group.
• Find everyone named "Rahul" → useless. Rahuls are spread across every last name.

Our index works the same way. Look where the pending orders sit:

1(user_id, status,     created_at)
2(41,      delivered,  2026-10-25)
3(41,      pending,    2026-10-27)   ← pending
4(42,      delivered,  2026-10-20)
5(42,      pending,    2026-10-27)   ← pending
6(43,      cancelled,  2026-10-03)
7(43,      pending,    2026-10-26)   ← pending
8...                                   scattered across every user

status is only sorted inside each user_id. Across the whole index, pending orders are scattered everywhere, so there is no single spot to jump to.

That gives us the rule.

The Rule

An index on (A, B, C) can be used when the query filters on a prefix, meaning columns starting from the left with no gaps:

1(A)        ✓
2(A, B)     ✓
3(A, B, C)  ✓
4(B)        ✗   skips A
5(C)        ✗   skips A and B
6(B, C)     ✗   skips A
7(A, C)     ~   partly: uses A, then checks C on every entry for that A

For our index (user_id, status, created_at):

So how do we fix the admin dashboard?

Fixing Queries That Don't Match

Option 1: Add an index that starts with the missing column.

CREATE INDEX idx_orders_status_date
ON orders (status, created_at);

This serves status alone and status + created_at, such as "pending orders, oldest first".

Option 2: Reorder an existing index, if no other query needs the current order. Here, "My Orders" needs user_id first, so we can't.

Before adding an index, check how often the query runs. A rare admin report on a small table may be fine with a full scan. A query that runs thousands of times a second is not.

Two Things People Get Wrong

1. The order in the WHERE clause doesn't matter. The rule is about which columns the query uses, not how you write them. These two queries both use the index:

WHERE user_id = 42 AND status = 'delivered'
WHERE status = 'delivered' AND user_id = 42

2. The rule applies to ORDER BY too. Data is sorted by created_at only inside each (user_id, status) group. So WHERE user_id = 42 ORDER BY created_at still needs an extra sort, because status sits in between. WHERE user_id = 42 AND status = 'delivered' ORDER BY created_at does not.

A note on newer versions: PostgreSQL 18 added skip scan, which can sometimes use an index without its first column when that column has only a few distinct values. It helps in limited cases. Don't design around it; design indexes that match your queries.

Interview Angle

Common mistakes:

• Thinking "the column is in an index, so the query is indexed".
• Thinking the order of conditions in WHERE must match the index.

Summary

1Index (A, B, C)        → sorted by A, then B inside A, then C inside B
2Works for              → A | A+B | A+B+C
3Fails for              → B | C | B+C   (skips the leftmost column)
4Partial                → A+C   (gap at B)
5WHERE order            → doesn't matter, columns do
6ORDER BY               → also needs the columns before it fixed
7Fix                    → add an index that starts with the needed column

We now know which queries a composite index serves. Next, the ESR rule shows how to pick the best column order when a query mixes equality, sorting, and ranges.