Column order in a composite index

SQL, indexing and PostgreSQL Mid-level Python 3.13+ PostgreSQL 17+ Reviewed By Best Latest Apps

Pick the fix

The hottest query is WHERE tenant_id = $1 AND status = 'open' ORDER BY created_at DESC LIMIT 20. Best single index?

Options
Hint 1 of 1

Equality columns first; what comes last?

Choose an answer, then open Show answer below to check it.

An index on (customer, day). Filtering on the customer follows one path to that customer's leaf pages; filtering on the day alone has to read every leaf page, because days are sorted only within each customer.

Plan shape checked with EXPLAIN on PostgreSQL 17; page counts are a toy table

Text version

WHERE customer_id = 7 (Index Scan):

  1. Root: c7–c9
  2. Inner: c7
  3. Leaf: c7 Mon, c7 Tue, c7 Wed
  4. Table: H3, H6

WHERE created_at > … (Index Scan):

  1. Root: not used
  2. Inner: not used
  3. Leaf: c7 Mon, c7 Tue, c7 Wed
  4. Table: H1
Pages read
ScenarioPlanIndex pagesTable pages
WHERE customer_id = 7Index Scan52
WHERE created_at > …Index Scan31
Show answer

Answer: A (tenant_id, status, created_at DESC)

Correct. Two equality columns narrow to one slice, already sorted by created_at, so LIMIT stops after 20 entries.

A B-tree is sorted by its columns left to right. With the equality columns first, all of one tenant's open orders sit together, already sorted by created_at, so the query reads 20 entries and stops.

Why not the others?
  • B (created_at, tenant_id, status). Range or sort column first: the scan walks all tenants.
  • C (status). Low selectivity, and the sort still happens.
  • D Three single-column indexes. Can be combined with bitmaps, but not for an ordered LIMIT scan.

30-second answer

A composite B-tree is sorted by the first column, then by the second within each first value, like a phone book by last name then first name. It serves filters on customer_id alone, customer_id plus created_at, and WHERE customer_id = ? ORDER BY created_at with no sort. It can't seek on created_at alone. Put equality columns first and the range or sort column last.

Practise saying it · 30 s. No recording, just a timer

How do you order columns in a composite index?

Say your answer out loud, then check which of these points you covered:

  • Sorted by first, then second
  • Leftmost prefix rule
  • Equality first, range/sort last
  • Check with EXPLAIN

Deep dive

WHERE customer_id = 7 AND created_at > now() - interval '7 days' is one contiguous slice of the index.

WHERE created_at > ... alone would have to scan the whole index; PostgreSQL 18 can sometimes skip-scan, but don't design for it.

Selectivity of the first column matters less than whether every query filters on it.

One well-ordered composite index can replace several single-column indexes.

Leftmost prefix in action Ran on PostgreSQL 17.11
SQL · PostgreSQL 17
SET max_parallel_workers_per_gather = 0;
CREATE TABLE orders (
  id bigint PRIMARY KEY,
  customer_id int,
  created_at timestamptz
);
INSERT INTO orders
SELECT g, g % 1000, now() - g * interval '1 minute'
FROM generate_series(1, 100000) g;
CREATE INDEX orders_cust_created ON orders (customer_id, created_at);
VACUUM ANALYZE orders;
EXPLAIN (COSTS OFF)
SELECT * FROM orders WHERE customer_id = 7
ORDER BY created_at DESC LIMIT 5;
EXPLAIN (COSTS OFF)
SELECT * FROM orders WHERE created_at > now() - interval '5 minutes';

Common mistakes

  • Putting the range column first, so the equality filter can't narrow the scan.
  • Creating (a, b) and (a) both: the first already serves queries on a.
  • Assuming the planner reorders index columns.

Senior take & follow-ups

Use the phone-book analogy, then the rule: equality first, range or sort last, and check with EXPLAIN.

Follow-up questions

Think of your answer first, then open the hint.

  1. Does (a, b) help ORDER BY a, b DESC?

    Show hint

    Not as a pure ordered scan; you need matching sort directions, e.g. (a, b DESC).

  2. Why not index (created_at, customer_id)?

    Show hint

    Recent-orders-for-one-customer would scan every customer's recent rows.

Related questions

Suggest a fix if something here is wrong or out of date.