-
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.