Column order in a multicolumn index

A phone book sorted twice

A phone book is sorted by surname, then by first name within each surname. Finding “Rao, Asha” is quick. Finding everyone called “Asha” means reading the whole book, because the Ashas are scattered under every surname.

A multicolumn index works the same way. An index on (customer_id, ordered_at) is sorted by customer, and within each customer by date. One customer’s orders from one quarter are a single short run of entries.

Equality first, range last

The rule from the documentation: equality conditions on the leading columns, plus a range on the next column, decide how much of the index is read. Conditions on columns after that are still checked inside the index, but no longer narrow it. So:

1. Columns compared with = first.
2. Then the one column used for a range (<, >, BETWEEN) or for ORDER BY.
3. Anything after that only filters.

Skipping the first column

Until PostgreSQL 18, a query that did not constrain an index’s first column could only use the index by reading all of it. PostgreSQL 18 added skip scan: it can run one search per distinct value of the leading column, as if you had written status = 'paid', then status = 'shipped', and so on.

On the same two million orders, counting one day’s orders (2,670 rows) without mentioning status:

Skip scan made (status, ordered_at) nearly as good as a dedicated index, because it only had a handful of statuses to skip through. With 20,000 customers in front, there were too many to skip, and forcing the index made it read 7,666 pages in one long pass. Skip scan rescues an index with a low-cardinality first column; it does not make column order stop mattering.

Check yourself

1. Why did (ordered_at, customer_id) read 70 times more pages for one customer’s quarter?

   Show answer

   The index is sorted by date first, so one quarter’s entries from every customer sit together and must all be read to find one customer’s. With customer_id first, that customer’s entries are together and already in date order.

   I could explain this without looking

2. What order would you choose for a query WHERE tenant_id = $1 AND status = $2 AND created_at > $3?

   Show answer

   (tenant_id, status, created_at): both equality columns first, then the range column.

   I could explain this without looking

3. When does PostgreSQL 18’s skip scan help, and when does it not?

   Show answer

   When the query skips an index’s leading column and that column has few distinct values, so one search per value is cheap. With many distinct values, as with 20,000 customers, it is not worth it.

   I could explain this without looking