Composite index
A composite index is a database index whose search key contains more than one column or expression.
In a B-tree on (a, b), a orders entries first and b breaks ties under the comparison rules. Reversing keys changes the ordering. SQL still needs ORDER BY for output order. [1]
Leading equalities and the first range bound a conventional B-tree lookup; later conditions can filter entries. A missing leading equality does not make every lookup impossible: SQLite and PostgreSQL 18 can choose skip scans across leading-key groups. Statistics and costs affect that choice. [2] [4]
Separate single-column indexes are different structures. PostgreSQL can combine row-location bitmaps but loses index ordering. SQLite does not generally intersect separate indexes for AND-connected terms. [5] [1]
UNIQUE(a, b) constrains combinations under the comparison rules, not each key individually. SQLite NOCASE folds ASCII only. NULL-bearing pairs may repeat, while GROUP BY groups NULLs together. PostgreSQL offers NULLS NOT DISTINCT. [3] [10] [11] [6]
Multiple key columns differ from included payload columns. Multicolumn GiST, GIN and BRIN have access-method-specific rules; B-tree ordering does not describe all of them. [4]
Relations
- related
- B-tree
Citations
- SQLite: Query Planning, multicolumn keys and search/sort
- SQLite: Query Optimizer Overview, skip-scan optimization
- SQLite: CREATE INDEX, key expressions and uniqueness
- PostgreSQL 18: Multicolumn Indexes, key versus payload and scan bounds
- PostgreSQL 18: Combining Multiple Indexes, bitmap ordering
- PostgreSQL 18: Unique Indexes, combinations and NULLS NOT DISTINCT
- SQLite: EXPLAIN QUERY PLAN, diagnostic output stability
- Selinger et al. (1979): Access Path Selection in a Relational Database Management System, SIGMOD, sections 4-5; DOI 10.1145/582095.582099
- Hesela: composite-index key order and native SQLite controls
- SQLite Datatypes: BINARY and ASCII-only NOCASE collation
- SQLite SELECT: NULL equality and collation in GROUP BY
- Hesela: composite UNIQUE, NULL grouping and native replay
Sources & caveats
- Primary-documentation synthesis. The linked five-query SQLite demonstration uses 400 constructed rows and tests answers independently of plan text; it is not a performance benchmark or PostgreSQL execution. [9]
- EXPLAIN QUERY PLAN output is diagnostic and can change between SQLite releases. [7]
- Historical context: System R retained alternatives for useful output orders and compared their costs with unordered access plus sorting. This 1979 design is not a claim about the internals of current SQLite or PostgreSQL. [8]
- A separate four-fixture, 48-insert SQLite control records full typed rows and exact errors; nullable UNIQUE acceptance differs from a GROUP BY duplicate audit. This is not a recovery or timing test. [12]