Composite indexes in PostgreSQL: choosing column order Basic

🎮 Try it

• The slowing query: choose the column order for WHERE + ORDER BY.
• Choose an index type: composite, partial, expression, covering, GIN, BRIN.

Quick summary

An index on (a, b, c) orders keys like a phone book: by a, within each a by b, within each (a, b) by c. The index can only narrow the scanned range when the conditions run contiguously from the leftmost column.

Rule	Meaning
Equality columns go first	a = ?, b = ? produce one contiguous range
Range or ORDER BY column goes last	The first column with a range is the last one that still narrows the scan
Leading column missing	PG17 and earlier: almost certainly a Seq Scan. PG18: skip scan is possible (see below)
Middle column missing	The index is still usable, it just scans more

The PostgreSQL docs state it precisely: equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will always be used to limit the portion of the index that is scanned.

Constraints on columns to the right of these columns are checked in the index, so they save visits to the table proper, but they do not necessarily reduce the portion of the index that has to be scanned.

A fair experiment: three queries, same shape

Table activity_demo with 200,000 rows, 100 tenants, 2 statuses, one index:

CREATE INDEX idx_esr ON activity_demo (tenant_id, status, created_at DESC);

All three queries have ORDER BY created_at DESC LIMIT 10, each was run 3 times on a real table, and the numbers are from the third (warm) run:

-- A: all three columns
SELECT id, created_at FROM activity_demo
WHERE tenant_id = 42 AND status = 'open' AND created_at >= '2026-01-02'
ORDER BY created_at DESC LIMIT 10;

-- B: missing tenant_id (leading column)
SELECT id, created_at FROM activity_demo
WHERE status = 'open' AND created_at >= '2026-01-02'
ORDER BY created_at DESC LIMIT 10;

-- C: missing status (middle column)
SELECT id, created_at FROM activity_demo
WHERE tenant_id = 42 AND created_at >= '2026-01-02'
ORDER BY created_at DESC LIMIT 10;

Query	Plan	Buffers	Time	Matching rows
A: all 3 columns	Index Scan, no Sort	13	0.012 ms	10 (stops at LIMIT)
C: missing middle column	Bitmap Index + Heap Scan, top-N Sort	586	0.30 ms	1,136
B: missing leading column	Parallel Seq Scan, top-N Sort	1,927	5.8 ms	56,801

Buffers are a more stable measure than time at the microsecond scale. Note that B does not read less because the index is useless here: this query has 56,801 matching rows and no range to jump to.

Same index, three ways of touching key ranges

Enlarge diagram

tenant 41 · 42 · closed · 42 · open · tenant 43

◀ Prev▶ PlayNext ▶↺ RestartStep 1/4

The index sorts keys by (tenant_id, status, created_at): ranges sit side by side, like last name then first name in a phone book.

Why those numbers

A: tenant_id = 42 and status = 'open' are equality, created_at >= ... is a range at the end. Together they form one contiguous range. Because created_at DESC is already in the index, ORDER BY ... LIMIT 10 reads the first 10 entries and stops. No Sort.

Limit (actual time=0.006..0.008 rows=10 loops=1)
  ->  Index Scan using idx_esr on activity_demo (actual rows=10 loops=1)
        Index Cond: ((tenant_id = 42) AND (status = 'open') AND (created_at >= '2026-01-02 ...'))
        Buffers: shared hit=13

C: status is dropped, so tenant 42’s range includes both closed and open. The index scans every entry of tenant 42 (2,000 entries), checks created_at inside the index, and keeps 1,136 entries that match.

1,136 is the number of matching rows, not the number of entries scanned. Then it visits the heap and runs a Sort.

Limit
  ->  Sort (Sort Key: created_at DESC, top-N heapsort)
        ->  Bitmap Heap Scan on activity_demo (actual rows=1136) Heap Blocks: exact=573
              ->  Bitmap Index Scan on idx_esr
                    Index Cond: ((tenant_id = 42) AND (created_at >= '2026-01-02 ...'))
                    Buffers: shared hit=13

B: without tenant_id, open rows are spread across every tenant. The planner picks a Parallel Seq Scan: it discards 143,199 rows, then runs a top-N sort.

Equality first, range last

A range column placed in the middle stops the columns after it from narrowing the scan. Experiment: 200,000 rows, status = 'urgent' is only about 1%, and the query fetches all urgent rows of tenant 42 since noon (no LIMIT):

WHERE tenant_id = 42 AND status = 'urgent' AND created_at >= '2026-01-01 12:00'

Index	Order	Buffers (Index Only Scan, warm)
(tenant_id, status, created_at)	equality, equality, range	4
(tenant_id, created_at, status)	equality, range, equality	11

The second index has to walk through every status within the time range and filter status inside the index. The demo table is small, so the gap is only 2.7x; for a tenant with millions of rows, the gap grows with the size of the range.

It also shows why status (2-3 values) still goes before created_at. The mantra “put the column with the most distinct values first” is not a composite index rule: cardinality is a weak heuristic; what decides is equality vs range and the real WHERE shape.

PostgreSQL 18: skip scan

PG18 adds skip scan for B-trees: the index can be used when the leading column is missing, by repeating the search for each value of that column. This is verified from the PG18 release notes and docs (not run in this article’s demo, which uses PG17).

The docs state the condition clearly: it is only effective when the omitted column has few distinct values, so the planner can skip most of the index.

With thousands of tenant_id values, the whole index still has to be scanned and the planner usually picks a seq scan. The conclusion stands: put the column you always filter on first; do not count on skip scan to replace a correct index.

When to use / when not to

Use a composite index when	Think twice when
Many queries filter by equality on the same column group, plus a range/sort	More than 3 columns: the docs say this is rarely helpful unless the workload is extremely fixed
You want ORDER BY ... LIMIT read straight from the index, without Sort	Only one rare query uses a different leading column: a separate index is cheaper than lengthening a large one
You want an Index Only Scan (add INCLUDE, see covering index)	Write-heavy table: each index costs writes and storage

Redundant indexes: (tenant_id) is a prefix of (tenant_id, status, created_at), so it can usually be dropped, since the longer index also serves queries filtering only on tenant_id. Measure with pg_stat_user_indexes before dropping.

Real-world example

Multi-tenant dashboard, 50 million rows

The activity table has two query shapes, both served by the index (tenant_id, status, created_at DESC):

Query	Shape	How the index serves it
“Open activities of tenant X, newest first”	Equality on tenant_id + status, sort by created_at DESC	Reads exactly 10 entries
“All recent activities of tenant X”	Missing status (shape C above)	Scans tenant X’s range but does not fall back to Seq Scan

If an internal report appears that filters status across all tenants, do not change this index. Add a separate one, typically a partial index WHERE status = 'open'.

Trade-offs

Pros

• One index serves many queries when the conditions match a left prefix
• Dropping a middle column still uses the index part in front of it
• Once the leading columns are fixed by equality, ORDER BY on the next column reads from the index with no Sort

Cons

• Missing leading column: PG17 and earlier almost certainly Seq Scan; PG18 only helps when the leading column has few values
• Each column order is optimal for one family of query shapes; two very different families need two indexes
• Long indexes cost storage and writes; many-column indexes are often a sign the design needs revisiting

Common mistakes

Common pitfall

Ordering columns by cardinality or by a gut feeling of “important columns”. The correct rule: list the columns with equality, put them first, put the range or sort column last. Then check each real query shape with EXPLAIN (ANALYZE, BUFFERS): the leading column must be one every query has.

Checklist for designing a composite index

1. List the real WHERE and ORDER BY shapes of the query group to serve.
2. The leading column is one every query filters on; do not count on PG18 skip scan instead of the right order.
3. Equality columns first; the range or ORDER BY column last. Do not order by cardinality.
4. Check each shape with EXPLAIN (ANALYZE, BUFFERS): expect an Index Cond with all columns and no Sort when there is ORDER BY ... LIMIT.
5. More than 3 columns, or a rare query with a different leading column: consider a separate index (or a partial index) instead of lengthening this one.
6. Find redundant indexes that are prefixes of longer ones; check pg_stat_user_indexes before dropping.

Summary

Key takeaways

• A composite index orders keys left to right; narrowing the scan needs contiguous conditions from the leading column
• Equality first, range or sort last. A range column in the middle leaves the columns after it only filtering inside the index
• Same index, same query shape, warm: missing middle column reads 586 buffers, missing leading column 1,927, all columns 13
• PG18 has skip scan for a low-cardinality leading column, but a high-cardinality one (tenant_id) still needs the right index
• Do not choose order by cardinality; choose by the WHERE and ORDER BY shapes of real queries

Self-check

1. Why does query A have no Sort node while query C does?
2. Where does the index (tenant_id, created_at, status) fall short of (tenant_id, status, created_at) when status = 'urgent'?
3. Which case does PG18 skip scan rescue, and which does it not?
4. Why is the 1,136 in plan C not the number of entries scanned?

References

• PostgreSQL 17 — Multicolumn Indexes
• PostgreSQL 18 — Multicolumn Indexes
• PostgreSQL 17 — Indexes and ORDER BY
• PostgreSQL 17 — Using EXPLAIN