Indexes & Query Performance

Composite Indexes

A composite B-tree sorts lexicographically: later columns refine equal prefixes. Column order therefore defines searchable intervals and useful output order; equality predicates usually extend the prefix, while the first inequality commonly bounds contiguous scanning.

Permutations are different indexes.
Equality extends a contiguous prefix.
The first range is a boundary.
Sort support follows key order.
Most selective first is not a law.
Separate indexes are a different option.

Lexicographic permutations

(customer, status, placed):
(7, PAID, 09:00) (7, PAID, 10:00) (7, SHIPPED, 08:00) (8, PAID, 07:00)
Query customer=7 AND status=PAID AND placed>=09:30 → one interval starting at (7,PAID,09:30).

(status, customer, placed):
(PAID,7,09:00) (PAID,7,10:00) (PAID,8,07:00) (SHIPPED,7,08:00)
This leads efficiently by status, but customer-only rows are dispersed among every status.

Equality prefix, range, and order

CREATE INDEX orders_customer_status_placed_idx
  ON orders (customer_id, status, placed_at DESC);

SELECT order_id, placed_at
FROM orders
WHERE customer_id = 7
  AND status = 'PAID'
  AND placed_at >= TIMESTAMP '2026-05-01 00:00:00'
ORDER BY placed_at DESC
LIMIT 50;

How predicates map to `(customer_id, status, placed_at)`
Predicate	Typical B-tree consequence
customer = 7	Leading prefix; contiguous customer interval
customer = 7 AND status = PAID	Longer equality prefix
customer = 7 AND status = PAID AND placed >= t	Bounded ordered range
customer = 7 AND status > PAID AND placed = t	Status range bounds interval; placed may filter entries
status = PAID	No leading customer equality; full/skip scan may or may not win
customer = 7 ORDER BY status, placed	Remaining order is compatible