composite index
An index on several columns in a fixed order, which serves queries on a leading prefix of those columns.
In more detail
An index on (customer_id, ordered_on) helps WHERE customer_id = 3, and that filter sorted by ordered_on, but not a filter on ordered_on alone. Put the column you filter by equality first.
Related terms
- index — A separate, sorted structure a database keeps alongside a table so it can find matching rows without reading them all.
- composite key — A key made of two or more columns together, such as order and product on an order line.
- query planner — The part of the database that turns a SQL query into a plan, choosing among indexes, scans and join orders.
Taught in
- Indexes And What They Cost — PostgreSQL, Indexing & Relational Data Modeling
- Reading An EXPLAIN Plan — PostgreSQL, Indexing & Relational Data Modeling