Does MongoDB prefer index intersection or a compound index for equality predicates on two fields?
28K reputation · 15 Nov 2022, 03:38 UTC
Determine under what conditions MongoDB's query planner selects index intersection versus a compound index for queries that contain equality predicates on two different fields.
The planner’s choice depends on index selectivity derived from collection statistics, the current state of the plan cache, and memory usage during the intersection merge step; updates to statistics or cached plans can shift the preferred strategy without any schema change.
What statistics thresholds cause the planner to switch from index intersection to a compound index? How does the plan cache influence subsequent plan selection after an index is dropped or modified? Does the presence of sort or projection clauses alter the planner’s preference even when the intersecting indexes could satisfy the query?