Question
Does MongoDB prefer index intersection or a compound index for equality predicates on two fields?
Sora Byte
0 reputation · 15 Nov 2022, 03:38 UTC
60.8K views
Loading…
0 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?
33275 reputation · 15 Nov 2022, 11:28 UTC
When a query contains equality predicates on two fields, MongoDB’s query planner will choose a compound index that covers both fields if one exists. Only in the absence of such a compound index will it consider index intersection (using two separate single‑field indexes), and even then only if the planner estimates that intersection is cheaper than a collection scan.
mongodb{field1: 1, field2: 1} (or any permutation) is present, explain() shows an IXSCAN stage on that index and no intersection stage.A compound index lets the planner satisfy both equality predicates with a single index scan, minimizing I/O and avoiding the extra cost of merging two result sets. Intersection requires two separate scans plus an INTERSECTION (or AND_SORT) stage, which is typically more expensive unless each single‑field index is extremely selective and the compound index would be large or poorly selective.
db.collection.explain('executionStats').find({field1: , field2: }).stages array:
IXSCAN on an index that includes both field1 and field2, the planner prefers the compound index.IXSCAN stages followed by an INTERSECTION (or AND_SORT) stage, index intersection is being used.executionStats.totalKeysExamined and totalDocsExamined between the two plans to observe the efficiency difference..hint({field1:1, field2:1}) and re‑run the explain to see if execution time improves.Missing diagnostic detail that could change the recommendation: Please provide the explain() output (or at least the stages and execution stats) for your specific query so we can confirm which plan MongoDB is actually selecting.
Use comments to ask for clarification. Post a solution as an answer.
33,275 reputation · 15 Nov 2022, 08:59 UTC
On the first sub-question: there are no documented selectivity or statistics thresholds that trigger a switch. MongoDB does not maintain RDBMS-style histograms; the multi-planner races candidate plans over a trial period and keeps the one with the fewest work units, so the choice is empirical per query shape rather than threshold-based.
A related constraint: index intersection cannot produce sorted output. If the query includes a sort that no single candidate index satisfies, the planner will not use intersection to serve it and will favor a compound index whose key order follows equality-then-sort (the ESR rule), avoiding a blocking in-memory sort.
Verification on your release (planner internals are version-dependent and not fully documented): run explain('executionStats') — intersection appears as AND_SORTED or AND_HASH stages over multiple IXSCANs, while a compound index shows one IXSCAN with bounds on both fields. Also note $or queries use index union across branches, not intersection.