Combining Indexes
When a query filters on two columns that each have their own index but no composite index covers both, the engine can use an index merge. It scans each index separately, then combines the results before fetching rows.
Merge Strategies
- Intersection is used for predicates joined by AND. The engine collects matching row identifiers from each index and keeps only those appearing in both sets.
- Union is used for predicates joined by OR. It gathers row identifiers from each index and removes duplicates.
- Sort union handles OR cases where row identifiers must be sorted before merging.
When It Helps And Hurts
Index merge avoids a full table scan when no single index covers the whole predicate. But combining two indexes plus fetching rows is often slower than one composite index that answers the query directly. Seeing a merge in a plan is a hint that a better composite index may be worth creating.
Key idea
Index merge scans two indexes and intersects or unions their row identifiers, but a single composite index usually outperforms it.