Mastering Composite Index Cardinality: Ordering Columns for Query Planner Efficiency

Creating an index for every column in a slow query is one of the most common pitfalls in relational database administration. While individual single-column indexes may allow the query planner to execute bitmap index scans, they generate massive write amplification and consume valuable RAM.

The Leftmost Prefix Principle

A composite B-Tree index ordered as (tenant_id, status, created_at) operates conceptually like a multi-volume phone book. Lookups that specify exact matches on tenant_id and status can instantly traverse the tree to perform a laser-focused range scan on created_at.

However, if a query filters solely on status and created_at without supplying tenant_id, the query planner must either perform a full index skip-scan or fall back to an expensive sequential table scan.

The Golden Rule of Column Ordering

1. Exact Equality Filters First: Columns that are compared with = across your primary workload belong at the beginning of the index.
2. Range and Inequality Filters Second: Columns using >, <, BETWEEN, or LIKE 'prefix%' stop multi-column B-Tree progression, so they must follow equality fields.
3. Covering Columns in INCLUDE: Columns required only for the SELECT projection should be attached via INCLUDE (...) rather than added to the B-Tree search key itself.

Implementing concise composite indexes frequently reduces index storage footprints by 60% while simultaneously accelerating query response times by an order of magnitude.