-
Type: Improvement
-
Resolution: Fixed
-
Priority: Major - P3
-
Affects Version/s: None
-
Component/s: None
-
Query Optimization
-
Fully Compatible
-
v7.0
-
QO 2023-05-15, QO 2023-05-29, QO 2023-06-12, QO 2023-06-26, QO 2023-07-10, QO 2023-07-24
-
38
Via SERVER-34741, all of the following pipelines will have the $match predicate pulled ahead of the $group to leverage an index and minimize the amount of work performed:
//Match directly on single field group db.foo.aggregate([ { '$group': { _id: '$city', c: { '$sum': 1 } } }, { '$match': { _id: 'Austin' } } ]); //Match on directly single field nested group value db.foo.aggregate([ { '$group': { _id: { c: '$city' }, c: { '$sum': 1 } } }, { '$match': { '_id.c': 'Austin' } } ]); //Match directly on one field in a compound grouping db.foo.aggregate([ { '$group': { _id: { c: '$city', s: '$state' }, c: { '$sum': 1 } } }, { '$match': { '_id.c': 'Austin' } } ]); //Match on a renamed single field grouping db.foo.aggregate([ { '$group': { _id: '$city', c: { '$sum': 1 } } }, { '$project': { cityName: '$_id', c: 1 } }, { '$match': { cityName: 'Austin' } } ]);
Introducing a $project specifically when the there is a nested or compound grouping prevents the optimization from applying:
//Match on a renamed single field grouping, but with nested names db.foo.aggregate([ { '$group': { _id: { c: '$city' }, c: { '$sum': 1 } } }, { '$project': { cityName: '$_id.c' } }, { '$match': { cityName: 'Austin' } } ]); //Match on a renamed compound grouping (necessarily with nested names) db.foo.aggregate([ { '$group': { _id: { c: '$city', s: '$state' }, c: { '$sum': 1 } } }, { '$project': { cityName: '$_id.c' } }, { '$match': { cityName: 'Austin' } } ]);
This ticket will enhance our projection analysis to handle projections that rename nested field names from the grouping _id field.
- is depended on by
-
SERVER-78467 Fix test failure of predicate push-down for aggregation_facet_unwind_passthrough
- Closed
- is related to
-
SERVER-91102 Moving $match before $group is incorrect when predicate distinguishes equal values
- Closed
-
SERVER-91573 $match pushed before $addFields can lead to incorrect results
- Closed
-
SERVER-72037 Allow $replace(With|Root) to participate more in $match optimizations
- Closed