composite-index · shipping · also Multicolumn index, Multi-column index, Compound index

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

  1. SQLite: Query Planning, multicolumn keys and search/sort (accessed 2026-11-18)
  2. SQLite: Query Optimizer Overview, skip-scan optimization (accessed 2026-11-18)
  3. SQLite: CREATE INDEX, key expressions and uniqueness (accessed 2026-11-18)
  4. PostgreSQL 18: Multicolumn Indexes, key versus payload and scan bounds (accessed 2026-11-18)
  5. PostgreSQL 18: Combining Multiple Indexes, bitmap ordering (accessed 2026-11-18)
  6. PostgreSQL 18: Unique Indexes, combinations and NULLS NOT DISTINCT (accessed 2026-11-18)
  7. SQLite: EXPLAIN QUERY PLAN, diagnostic output stability (accessed 2026-11-18)
  8. Selinger et al. (1979): Access Path Selection in a Relational Database Management System, SIGMOD, sections 4-5; DOI 10.1145/582095.582099 (accessed 2026-11-18)
  9. Hesela: composite-index key order and native SQLite controls (accessed 2026-11-18)
  10. SQLite Datatypes: BINARY and ASCII-only NOCASE collation (accessed 2026-11-18)
  11. SQLite SELECT: NULL equality and collation in GROUP BY (accessed 2026-11-18)
  12. Hesela: composite UNIQUE, NULL grouping and native replay (accessed 2026-11-18)
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]

JSON: /data/terms/composite-index.json

Published by Hesela team · Updated

Report a correction · Editorial standards · Reuse guidance

Cite this page
Hesela team. Composite index. Hesela. 2026-11-18. https://hesela.dev/terms/composite-index/