Index design

Composite column order

The most consequential decision and the most often wrong.

A B-tree on (a, b, c) is sorted by a, then b within equal a, then c. This gives the leftmost prefix rule: the index serves WHERE a = ?, WHERE a = ? AND b = ?, and all three columns, but not WHERE b = ? alone.

Equality columns first, then the range or sort column, then the rest.

Once a range predicate (>, <, BETWEEN, LIKE 'x%') is used on a column, every later column in the index can only filter, not seek.

SELECT * FROM events
WHERE tenant_id = 42 AND created_at > now() - interval '7 days'
ORDER BY created_at DESC;