-
Type:
Task
-
Resolution: Works as Designed
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
Query Execution
-
None
-
None
-
None
-
None
-
None
-
None
-
None
For a $match-$group with a rooted $or
[{$match: {$or: [{a: 1}, {a: 2}]}},
{$group: {_id: null, total: {$sum: "$a"}}}]
over a non-multikey index a:1, the SBE plan includes a nested loop join that appears to be unnecessary, even when the queryPlan does not include a fetch.
db.c.drop();
db.c.insert({a:1});
db.c.createIndex({a:1});
db.c.explain().aggregate([{$match: {$or: [{a: 1}, {a: 2}]}}, {$group: {_id: null, total: {$sum: "$a"}}}]).queryPlanner.winningPlan.slotBasedPlan.stages
...
[3] group [] [s9 = aggDoubleDoubleSum(s7)] spillSlots[s8] mergingExprs[aggMergeDoubleDoubleSums(s8)]
[1] nlj inner [] [s4, s5]
left
[1] project [s4 = getField(s2, "l"), s5 = getField(s2, "h")]
[1] unwind s2 = outField, s3 = outIndex, s1 = inField !_preserveNullAndEmptyArrays
[1] limit 1ll
[1] coscan
right
[1] ixseek seekKeyLow = s4 seekKeyHigh = s5 [s6 = recordId] [s7 = 0] @"45972ce2-5bb4-4a47-8592-51d3a6fb0262" @"a_1" forward
Replacing the $or with $and removes the nlj.
db.c.explain().aggregate([{$match: {$and: [{a: 1}, {a: 2}]}}, {$group: {_id: null, total: {$sum: "$a"}}}]).queryPlanner.winningPlan.slotBasedPlan.stages
...
[3] group [] [s4 = aggDoubleDoubleSum(s2)] spillSlots[s3] mergingExprs[aggMergeDoubleDoubleSums(s3)]
[1] ixscan_generic IndexBounds("field #0['a']: ") [s1 = recordId] [s2 = 0] @"45972ce2-5bb4-4a47-8592-51d3a6fb0262" @"a_1" forward
Both queries have query plan GROUP, PROJECTION_COVERED, IXSCAN.