SQL Composite Index Column Order

The leftmost-prefix rule — a composite index only helps queries that filter starting from its first column.

What & Why

A composite index is ordered lexicographically, so leading columns control which predicates can narrow its useful range. Column order should follow important query shapes, commonly equality columns before a range or ordering column.

See How It Works

BUSINESS QUESTION

Growth repeatedly filters one event name and then a created_at range.

id	user_id	event_name	created_at	properties
1001	101	signup	2024-01-15 09:12:00+00	{"source":"organic"}
1002	101	activated	2024-01-16 14:25:00+00	{"step":"workspace"}
1003	102	signup	2024-02-10 11:08:00+00	{"source":"paid"}
1004	103	report_viewed	2024-03-12 16:40:00+00	{"report":"retention"}

EXAMPLE QUERY

SELECT
  id,
  user_id,
  event_name,
  created_at
FROM growth.events
WHERE event_name = 'signup'
  AND created_at >= TIMESTAMPTZ '2024-01-01 00:00:00+00'
ORDER BY created_at DESC, id;

RESULT — signups in descending time order

id	user_id	event_name	created_at
1003	102	signup	2024-02-10 11:08:00+00
1001	101	signup	2024-01-15 09:12:00+00

The fixed predicate returns two signup rows and the requested ordering places event 1003 first.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice