Must both columns appear in every query?

No. Whether a subset is useful depends on the access method, order and optimizer.

Is a composite index always unique?

No. Uniqueness must be required by the relevant index or constraint definition.

Can one replace two single-column indexes?

Sometimes, but the answer depends on the set of queries and their plans; the structures are not universally interchangeable.

AI Glossary / Data engineering and architecture / Intermediate

Composite index (database)

Definition

A composite index is a database index whose search key contains more than one column or expression. It can help the database locate rows using combinations of conditions. Its usefulness depends on the index type, column order, query, data distribution and database implementation; it does not automatically make every query on those columns faster.

What it means

A table might store millions of orders, while a frequent request asks for one customer's orders within a date range. An index organized around customer and date can provide a useful route to those records. The index is an additional access structure, separate from the business meaning of the data. A composite index can be unique or non-unique. Therefore, merely creating one does not necessarily prevent two rows from containing the same indexed values.

How it works

For a typical multicolumn B-tree, values are ordered by the first indexed column and then subsequent columns within matching earlier values. This makes the choice of leading columns significant. Actual optimizers can use several strategies, including scanning, combining indexes and, in some implementations, skip scanning. PostgreSQL 18 documents cases where skip scan helps with constraints on later columns. Consequently, “the first column is absent, so the index can never be used” is an unreliable absolute rule. Read the execution plan for the actual database and workload.

Three practical examples

An order system indexes CustomerID and OrderDate to support customer-specific history searches.
A monitoring platform considers an index on DeviceID and EventTime for investigating one device's recent events.
A training-data catalog searches records by DatasetID and Version. A suitable index can reduce lookup work without proving that the selected dataset is accurate or appropriate for an AI task.

Think of it this way

Think of a directory sorted by town and then surname. Finding a surname within a known town is straightforward, while finding that surname across all towns may require a different search strategy. The analogy captures ordered lookup but has limits: database planners use statistics and specialized access methods, and they may find shortcuts that a paper directory does not provide.

Worked example

Suppose an index contains the pairs (A, January 2), (A, January 8), (A, February 3), (B, January 4) and (B, February 7). A request for customer A's January orders targets the first two pairs. Customer equality and a date interval work together to describe a useful portion of the ordered index. A request for every customer's January orders has a different shape because matching dates are spread across customer groups. This five-row illustration explains arrangement, not a performance benchmark: on such a small table, simply reading all rows might be cheaper than using an index.

Illustrative case study

Fictional case: Cedar Repair notices that staff usually search for one machine's service records within a month. The team compares an index ordered by machine and service date with its existing configuration. It measures representative reads and the extra cost of inserting new records, then examines whether the planner actually chooses the candidate index. The fictional team keeps the choice conditional on measured workload behavior rather than assuming that adding more indexed columns always improves service.

Common misconceptions and mistakes

Do not equate a composite index with a composite primary key. One concerns access structure; the other concerns row identity and constraints. Do not assume a non-unique index enforces uniqueness. More indexes consume storage and add maintenance work when indexed data change. Column order is not governed by a single universal slogan: equality conditions, ranges, sorting, selectivity, index type and supported optimizer features all matter. An index that helps one query may have little value for another.

Business use

Businesses use composite indexes to support predictable, frequently used access patterns. Record the query's purpose, expected scale and update rate before choosing columns. Compare execution plans and realistic response times, and retain enough workload context to understand later changes. For AI applications, indexing can improve retrieval of structured records, but it is distinct from embedding search and does not measure semantic similarity. Revisit indexes when query patterns, data volumes or database versions change substantially.

Check your understanding

An index on CustomerID and OrderDate speeds up a customer-history query. Does that prove it is the best index for reports grouped only by date?

Answer: No. Those reports use a different access pattern and need their own measurement and plan review.

Related terms

Execution plan
Composite key
B-tree
query optimizer
unique index
selectivity
covering index

Learn next

Choose two real query patterns against the same table. Write their filters and ordering requirements, then compare candidate indexes using the database's plan explanation tools. Include insertion and update costs in the comparison rather than evaluating reads alone.

Sources

Primary reference: PostgreSQL 18 documentation, Multicolumn Indexes, https://www.postgresql.org/docs/18/indexes-multicolumn.html . Official page read for multicolumn access and skip-scan qualifications. Examples are original teaching scenarios, and database-specific behaviour should be checked for the implementation and version in use. Editorial review remains pending.

Previous: Componential analysis Next: Composite key