How do composite indexes and the leftmost prefix rule work?

Column order in a composite index is not cosmetic. Think of the index as sorted first by the first column, then by the second within equal first values. A query must provide the leading columns to navigate that order.

CREATE INDEX idx_events_user_type_time
  ON events (user_id, event_type, created_at DESC);

SELECT id, created_at
FROM events
WHERE user_id = 42
  AND event_type = 'login'
ORDER BY created_at DESC
LIMIT 20;

Ordering rules of thumb

• Put equality-filtered columns first, then range or sort columns
• The index can serve user_id, or user_id plus event_type, or all three
• A query filtering only on event_type generally cannot use this index
• A range condition on an earlier column limits use of later columns for ordering
• A covering index with INCLUDE columns can avoid table lookups

For a query that filters by user_id and sorts by created_at, an index on (user_id, created_at) is a better fit than one on (created_at, user_id). Match the index order to the query's equality and ordering needs.