-
Type:
Improvement
-
Resolution: Fixed
-
Priority:
Major - P3
-
Affects Version/s: None
-
Component/s: None
-
Labels:
-
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
-
-
- Closed
-