-
Type: Improvement
-
Resolution: Done
-
Priority: Major - P3
-
Affects Version/s: 3.3.9
-
Component/s: Aggregation Framework
-
Query Optimization
-
Fully Compatible
-
Query 2017-03-27, Query 2017-04-17, QO 2023-03-20
-
151
Aggregation pipeline sends down to query fields to return which are generated later in the pipeline (as result of "as" in $lookup).
Reproduce:
db.companies.explain().aggregate({$match:{_id:"14"}},{$graphLookup:{from:"companies",startWith:"$_id",connectFromField:"_id",connectToField:"parent",as:"tree",maxDepth:5, depthField:"order"}},{$group:{"_id":"$tree.country"}}) { "waitedMS" : NumberLong(0), "stages" : [ { "$cursor" : { "query" : { "_id" : "14" }, "fields" : { "tree.country" : 1, "_id" : 1 }, "queryPlanner" : { "plannerVersion" : 1, "namespace" : "d.companies", "indexFilterSet" : false, "parsedQuery" : { "_id" : { "$eq" : "14" } }, "winningPlan" : { "stage" : "PROJECTION", "transformBy" : { "tree.country" : 1, "_id" : 1 }, "inputStage" : { "stage" : "IDHACK" } }, "rejectedPlans" : [ ] } } }, { "$graphLookup" : { "from" : "companies", "as" : "tree", "connectToField" : "parent", "connectFromField" : "_id", "startWith" : "$_id", "depthField" : "order", "maxDepth" : NumberLong(5) } }, { "$group" : { "_id" : "$tree.country" } } ], "ok" : 1 }
Note "tree.country" which is coming from the lookup and not original document.
It appears that if project or group needs a field after lookup, there is no filtering out of fields that start with string passed as "as:" to $lookup and it ends up being pushed down into the query.
- is related to
-
SERVER-72020 [CQF] Make sure computed fields are not redundantly pulled from storage
- Backlog
-
SERVER-13703 Presence of extraneous $project causes a less efficient plan to be used
- Backlog