Details
-
Improvement
-
Resolution: Fixed
-
Major - P3
-
None
-
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
Description
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.
Attachments
Issue Links
- is depended on by
-
SERVER-78467 Fix test failure of predicate push-down for aggregation_facet_unwind_passthrough
-
- Closed
-
- is related to
-
SERVER-72037 Allow $replace(With|Root) to participate more in $match optimizations
-
- Open
-