The leading-column rule and composite index design

B-tree internals: why order matters

Before you can design a composite index correctly, you need to understand what “sorted by (a, b, c)” actually means on disk — because the physical layout is what makes the rule inescapable, not an arbitrary Postgres decision.

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.

Together these four steps mean you are building the index around the access pattern, not the schema. Skip step 2 and you will have a composite that helps some queries and silently fails others — the leading-column violation is almost never obvious until you read an EXPLAIN plan.

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.