The leading-column rule and composite index design

A team creates an index on (workspace_id, status, created_at) for a dashboard. Queries filter by workspace_id alone — fast. Queries filter by workspace_id and status — still fast. A new analytics query filters by status alone — sequential scan, 8 seconds. The index exists and is correct. The query is wrong for this index. The difference is the leading-column rule.

B-tree internals: why order matters

Postgres’s default index type is a B-tree (balanced search tree). Each internal node is a disk page (8KB) containing a sorted list of keys and pointers to child pages. Leaf nodes contain the actual key values and pointers (TIDs) to the heap rows.

A B-tree on a single column (a) is sorted globally by a. To answer WHERE a = 42, Postgres walks the tree from the root, taking at most ~4 hops for a 100M-row table (B-tree depth ≈ 4 levels with fanout ~200–400 keys per page), then reads the matching leaf entries.

A multi-column B-tree on (a, b, c) is sorted primarily by a, then by b within equal a values, then by c within equal (a, b) pairs. It accelerates:

• Queries filtering on a alone: scan the leading subtree.
• Queries filtering on a and b: narrow further.
• Queries filtering on a, b, c: narrowest possible, best case.
• Queries with a plus a range on b (e.g., WHERE a = 1 AND b > 100): still uses the index.
• Queries with a plus ORDER BY b: the index already provides that sort order.

It does NOT accelerate:

• Queries filtering only on b, only on c, or only on b, c — the index provides no ordering entry point for these; a full scan of the index is required, which is almost always worse than a seq scan.

Designing composite indexes for real query patterns

The leading-column rule means composite index design follows the queries, not the table. The discipline:

1. List the top-N hot queries for a table.
2. Group them by which column is always present in the filter.
3. For each group, design a composite with that always-present column as the leading column, then add secondary columns in decreasing selectivity order.
4. Use ORDER BY columns as trailing key columns — they serve both the filter and the sort without an extra Sort step.

Example: a dashboard query WHERE workspace_id = $1 AND status = 'pending' ORDER BY created_at DESC LIMIT 50. The always-present column is workspace_id (every query is tenant-scoped). The secondary filter is status. The sort is created_at DESC.

Best composite: (workspace_id, created_at DESC) WHERE status = 'pending' — the leading column matches the always-present filter; the sort column is second; the partial WHERE clause is handled separately (covered in lesson 03). An index (workspace_id, status, created_at) would also work but is a different shape.

One composite vs two single-column indexes

A composite (a, b) is one physical structure. It costs roughly 30–50% more than (a) alone in size and write overhead. Two single-column indexes (a) and (b) cost roughly the sum of their sizes and write overheads separately.

A composite wins when the dominant query always filters by the leading column. Two single-column indexes win when queries filter by a often AND by b often, never together — in that case no composite serves both, and the planner can combine them via a Bitmap Index Scan.

Senior rule: design the composite for the dominant query first. Add a second index only if a separate hot query needs a different leading column and the Bitmap And plan is not fast enough.