Chapter 11 · Index-Aware Logical and Physical Design
Composite Indexes and Column Order
Design composite indexes deliberately by matching leading columns, equality and range predicates, sort requirements, and common access patterns.
Learning outcomes
Composite indexes contain multiple key columns in a defined order. That order is not cosmetic: it determines how the index can be searched, how ranges behave, whether ordering can be reused, and which query patterns benefit. Good composite design starts from actual predicates rather than from column popularity.
Understand the leading-column principle.
Order equality, range, and sort columns deliberately.
Design one composite index to support several compatible queries when possible.
Avoid redundant overlapping indexes.
Composite index structure
Suppose:
CREATE INDEX ix_work_order_asset_openedON work_order(asset_id, opened_at);Conceptually, entries are sorted first by asset_id and then by opened_at within each asset.
Leading-column principle
This index is naturally useful for:
WHERE asset_id = ?and:
WHERE asset_id = ? AND opened_at >= ?It may not be equally useful for:
WHERE opened_at >= ?because opened_at is not the leading column.
Equality before range
A common heuristic is to place equality predicates before range predicates:
WHERE technician_id = ? AND started_at >= ? AND started_at < ?Index:
(technician_id, started_at)lets the engine seek to one technician and then scan only the relevant time range.
Why range columns often come later
Once the engine begins scanning a range on one index key, subsequent key columns may be less useful for narrowing the search, depending on DBMS and predicate form.
Ordering support
Query:
SELECT *FROM work_orderWHERE asset_id = ?ORDER BY opened_at DESCLIMIT 20;A suitable index can avoid an explicit sort and stop after the newest 20 rows.
Mixed sort directions
Some engines support indexes with explicit ASC/DESC directions. Whether direction matters depends on engine capabilities and query ordering. Inspect the plan rather than relying on assumptions.
Multiple equality columns
For:
WHERE tenant_id = ? AND customer_id = ? AND status_code = ?several column orders may support the exact query. Choose order by broader workload reuse, selectivity, tenant isolation, and range/sort needs.
Tenant-first indexes
Multi-tenant systems often place tenant_id first:
(tenant_id, customer_id, opened_at)This keeps tenant-scoped data adjacent and supports tenant-bound access patterns.
Composite uniqueness
Indexes can enforce scoped uniqueness:
UNIQUE (tenant_id, username)The column order may also matter for query reuse.
Prefix reuse
An index on:
(asset_id, status_code, opened_at)can often support queries filtering:
asset_idasset_id + status_codeasset_id + status_code + opened_at rangeThis means a separate index on asset_id may be redundant, though write/query details can still justify both in special cases.
Before adding an index, compare it with existing left-prefix indexes to avoid unnecessary duplication.
But prefix reuse is not universal magic
If the workload heavily filters only status_code, an index beginning with asset_id may not help. Design around actual query shapes.
Three-query WorkshopHub example
Q1:WHERE asset_id = ?ORDER BY opened_at DESCQ2:WHERE asset_id = ? AND status_code = 'open'ORDER BY opened_at DESCQ3:WHERE status_code = 'open'ORDER BY opened_at DESCOne index may support Q1 and Q2 well, but Q3 has a different leading predicate. Decide whether Q3 is important enough for another index or partial strategy.
IN predicates
Predicates such as:
status_code IN ('open','blocked')may behave like multiple equality probes. Optimizer behavior depends on engine and statistics.
Composite index and joins
For a table scoped by tenant and referenced by parent ID:
(tenant_id, asset_id)may simultaneously support a composite foreign key and tenant-scoped joins.
Index width matters
Adding many key columns creates larger index entries, fewer entries per page, more I/O, and more write cost. Do not turn every composite index into a copy of the table.
Functional ordering versus selectivity slogans
“Put the most selective column first” is too simplistic. For composite indexes, query predicate structure and left-prefix usability often matter more than raw single-column selectivity.
WorkshopHub composite candidates
| Access pattern | Candidate |
|---|---|
| Asset history newest first | (asset_id, opened_at) |
| Technician assignments by time | (technician_id, started_at) |
| Part usage by work order and part | (work_order_id, part_id) |
| Tenant-scoped asset lookup | (tenant_id, asset_id) |
Practice: choose column order
Access pattern
WHERE customer_id = ? AND opened_at >= ?ORDER BY opened_at DESCWhich index order is more natural: (opened_at, customer_id) or (customer_id, opened_at)?
Review answer
(customer_id, opened_at) is usually more natural because the engine can first seek to one customer, then scan the requested time range in order. The reverse order may scan many customers' rows for the date range.
Summary and next lesson
Composite index design is about ordered access paths. Leading columns, equality predicates, ranges, sort requirements, tenant scope, and overlap with existing indexes all matter. The next lesson extends the toolkit with covering, partial, expression, and specialized indexes.
References
- PostgreSQL documentation on multicolumn indexes.
- Markus Winand, SQL Performance Explained.
- Use The Index, Luke! material on concatenated indexes.