記録 · Entry

PostgreSQL composite indexes: which column goes first

A multicolumn B-tree sorts by its first column, then its second. Why (a, b) and (b, a) are different indexes, and the rule that decides the order.

10 min read

PostgreSQLDatabasesIndexingPerformanceSQL

Two indexes on the same two columns:

CREATE INDEX ON orders (status, created_at);
CREATE INDEX ON orders (created_at, status);

They hold the same data, cost the same to maintain, and are not interchangeable. One of them makes the most common query on an orders table fast, and the other makes the planner ignore it and scan half the table. Which is which depends on the query, and the way to reason about it fits in one picture.

#An index is a phone book

A B-tree index on (last_name, first_name) is a phone book. Sorted by surname, and within each surname, by first name.

Finding everyone called Khan is a single contiguous run of pages. Open to K, read until the surnames change. Finding "Khan, Numan" is the same run narrowed further, because within Khan the first names are sorted too.

Finding everyone called Numan, regardless of surname, is a different job. The Numans are scattered through the book, one under every surname that has one, and the sort order gives you nothing. You read the whole book.

A multicolumn index is that book. The first column decides the primary order. The second column is only sorted within runs of equal first-column values. The third is only sorted within runs of equal first and second. Every property of composite indexes follows from that, and most of the wrong ones come from forgetting it.

#The leftmost prefix rule

An index on (a, b, c) can efficiently serve a query that filters on a, on a and b, or on a, b and c. It cannot efficiently serve a query that filters only on b, or only on c, or on b and c. Those columns are not in a useful order unless a is pinned.

Postgres will still sometimes use the index for a query on b alone. It can scan the entire index rather than the entire table, which is a win if the index is much narrower than the table. And since PostgreSQL 18 the planner can perform a skip scan, iterating over each distinct value of a and probing for b within it, which is effective exactly when a has few distinct values and useless when it has millions. Neither of these is the same as the index doing its job. Both are the database making the best of an index that was built in the wrong order.

The rule that follows: a column you filter on alone needs to be the first column of some index. An index on (status, created_at) does not cover WHERE created_at > $1 by itself.

#Equality first, then the range, then the sort

Here is the query that every application with an orders table runs somewhere:

SELECT *
FROM orders
WHERE tenant_id = $1
  AND status = 'paid'
  AND created_at >= $2
ORDER BY created_at DESC
LIMIT 50;

Two equality conditions, one range condition, one sort. The index that serves it perfectly is:

CREATE INDEX orders_tenant_status_created_idx
  ON orders (tenant_id, status, created_at);

Walk through it as the phone book. Pin tenant_id, which lands on one contiguous run. Pin status inside that, which narrows to a smaller contiguous run. Inside that run, rows are sorted by created_at, so the range condition is a single seek to $2 and a scan forward, and the ORDER BY is already satisfied because the scan produces rows in created_at order. With LIMIT 50 the scan reads fifty index entries and stops.

Now put the range column first:

CREATE INDEX ON orders (created_at, tenant_id, status);

Seek to $2 and scan forward, fine. But within the run of rows after $2, the entries are sorted by created_at, and tenant_id values are scattered across it in whatever order the timestamps happened to fall. Every row after $2 has to be visited and checked. If the date range covers a million orders and the tenant has a thousand of them, that is a million index entries read to return fifty rows.

The general rule, and it is the one thing to take from this post: columns compared with equality go first, then the single column compared with a range, then columns that only appear in ORDER BY. After a range condition, no later column is in a usable order, so only one range column can benefit and it has to come last among the filters.

#Selectivity is not the ordering rule

The advice that gets repeated most is "put the most selective column first". It is not wrong so much as it is answering a different question.

For the query above, both tenant_id and status are equality conditions, and the index has to pin both before it reaches the range. Whether the index is (tenant_id, status, created_at) or (status, tenant_id, created_at) makes no difference to that query. Either way the planner descends to the one run where both are pinned, and the size of that run is the same in both orders. Selectivity of the combination is what determines how much gets read, and combining is commutative.

Where the order of the equality columns matters is for the other queries. An index on (tenant_id, status, created_at) also serves WHERE tenant_id = $1 alone, and WHERE tenant_id = $1 AND status = $2 alone, because those are leftmost prefixes. It does not serve WHERE status = 'paid' on its own across all tenants. So put first the equality column that appears most often by itself, which in a multi-tenant application is almost always the tenant. That is a decision about which prefixes you want for free, not about cardinality.

#Getting the sort for free, and when you do not

An index can satisfy an ORDER BY without a sort step only when the sort column comes immediately after the equality columns and the index order matches. A B-tree can be read in either direction, so (tenant_id, created_at) serves both ORDER BY created_at ASC and ORDER BY created_at DESC.

What it cannot do is mix directions. A query that wants ORDER BY created_at DESC, id ASC cannot be served by (created_at, id) read backwards, because reading backwards flips both. You have to declare the direction in the index:

CREATE INDEX ON orders (tenant_id, created_at DESC, id ASC);

That index reads forward and produces exactly that order. It reads backward and produces created_at ASC, id DESC. Any other combination gets a sort.

This is the detail that decides whether keyset pagination is fast, because keyset queries are precisely a range condition on a composite key followed by an ORDER BY on the same columns. The index has to match the comparison and the sort, in the same direction, or every page after the first sorts.

#When the range column is also the thing you sort by

The pattern above works out cleanly because the range and the sort are the same column. When they are not, you have to choose.

WHERE tenant_id = $1 AND created_at >= $2
ORDER BY total DESC
LIMIT 50;

Index (tenant_id, created_at) finds the range quickly and then has to read every matching row to sort by total, because after the range condition nothing is in total order. Index (tenant_id, total) produces rows in the right order and can stop after fifty, but has to check every row's created_at on the way, which means visiting rows it will discard.

Which wins depends on the data. If the date range is narrow, index the range and sort the small result. If the date range is wide and the limit is small, index the sort and filter as you go. The planner will estimate this for you, and EXPLAIN will show you which it chose and why. There is no index that does both without a scan, and the honest answer is sometimes two indexes.

#Read the plan, not the schema

The test for whether an index has the right column order is a single line in EXPLAIN (ANALYZE, BUFFERS):

Index Scan using orders_tenant_status_created_idx on orders
  Index Cond: ((tenant_id = 42) AND (status = 'paid') AND (created_at >= '2026-08-01'))

Every condition appears in Index Cond. The index is doing all the work and the heap is only visited for rows that will be returned.

Contrast:

Index Scan using orders_created_idx on orders
  Index Cond: (created_at >= '2026-08-01')
  Filter: ((tenant_id = 42) AND (status = 'paid'))
  Rows Removed by Filter: 981204

Filter means the condition was checked after the row was fetched. Rows Removed by Filter is the count of rows that were read and thrown away. That number is the cost of the wrong column order, and it grows with the table.

#Covering the query with INCLUDE

Once the index finds the right rows, Postgres still has to visit the heap to fetch any column not in the index. If the query only needs a few columns, put them in the index as payload:

CREATE INDEX ON orders (tenant_id, status, created_at)
  INCLUDE (total, customer_id);

INCLUDE columns are stored in the leaf entries but take no part in the ordering, so they do not change any of the reasoning above. They let the scan return total and customer_id without touching the table at all, an index-only scan, which for a hot list query can be the difference between reading fifty pages and reading five thousand.

Index-only scans depend on the visibility map, so on a table with heavy update churn the planner may still visit the heap to check row visibility. EXPLAIN reports this as Heap Fetches, and a high count means vacuum is behind, not that the index is wrong.

#A partial index instead of a status column

When most queries filter on the same value of a low-cardinality column, consider leaving that column out of the key and putting it in a WHERE on the index instead:

CREATE INDEX ON orders (tenant_id, created_at)
  WHERE status = 'pending';

The index only contains pending orders. It is smaller than a full index on (tenant_id, status, created_at) by the share of orders that are not pending, which over time is nearly all of them, and the planner uses it for any query whose WHERE clause implies status = 'pending'. This is the same shape that makes a job queue in Postgres fast, and it works for the same reason: the index tracks the live rows, not the history.

#Every index is paid for on write

An index that is never used still costs an insert into it for every row written, and an update to it whenever an indexed column changes. Five composite indexes on a hot table can double or triple the write cost of every insert, before counting the write amplification that random keys add on top.

So build indexes for the queries you have, in the column order those queries need, and drop the ones pg_stat_user_indexes says nobody reads. An index on (status, created_at) that turns out to be used for nothing but an occasional admin report is not free because it is small. It is a tax on every order the system ever takes.

#The one-line summary

A composite index is sorted by its first column, then within that by its second. Put equality columns first, the one range column next, the sort columns last, and check EXPLAIN for a Filter line to see whether you got it right.

#References

Related entries