-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: 8.0.6
-
Component/s: None
-
Query Optimization
-
None
-
None
-
None
-
None
-
None
-
None
-
None
The query planner does not consider combining together clauses from different $or selectors in a cartesian product style, even when this would produce significantly more efficient index scans at the cost of having more individual index scans.
Steps to reproduce:
db.a.createIndex({ a: 1, b: 1, c: 1 }); let filter = { $and: [ { $or: [ { a: 0, }, { a: 1, }, ], }, { $or: [ { b: 0, c: { $gt: 0 }, }, { b: { $gt: 0 } }, ], }, ], }; db.a.explain().find(filter).sort({ b: 1 });
This produces two index scans, both only constrained on the a field, merged using a SORT_MERGE.
A more efficient way would be to combine the two $or selectors using a cartesian product to produce four index scans, like the following query does with the same index:
let filter = { $or: [ { a: 0, b: 0, c: { $gt: 0 }, }, { a: 1, b: 0, c: { $gt: 0 }, }, { a: 0, b: { $gt: 0 } }, { a: 1, b: { $gt: 0 } }, ], }; db.a.explain().find(filter).sort({ b: 1 });
Note that the two queries are semantically equivalent, but all the index scans made by the latter are constrained on both the a and b fields, and two are additionally constrained on the c field, potentially scanning a significantly lower number of documents.