-
Type:
Bug
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: Aggregation Framework
-
Query Optimization
-
ALL
A $redact stage can copy a portion of a subsequent $match before the $redact, but our current optimization strategy does not attempt to move the new, copied match further forward in the pipeline.
Original Description
// correct db.Account.explain().aggregate({$lookup:{"from" : "Opp", "as" : "Opp", "localField" : "Id", "foreignField" : "AccountId"}}, {$match:{ID_18__c:"001A0000008XXXXXXX"}}) { "stages" : [ { "$cursor" : { "query" : { "ID_18__c" : "001A0000008XXXXXXX" }, "queryPlanner" : { "plannerVersion" : 1, "namespace" : "source_staging.Account", "indexFilterSet" : false, "parsedQuery" : { "ID_18__c" : { "$eq" : "001A0000008XXXXXXX" } }, "winningPlan" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "ID_18__c" : 1 }, "indexName" : "ID_18__c_1", "isMultiKey" : false, "multiKeyPaths" : { "ID_18__c" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "ID_18__c" : [ "[\"001A0000008XXXXXXX\", \"001A0000008XXXXXXX\"]" ] } } }, "rejectedPlans" : [ ] } } }, { "$lookup" : { "from" : "Opp", "as" : "Opp", "localField" : "Id", "foreignField" : "AccountId" } } ], "ok" : 1 }
//correct db.Account.explain().aggregate( {$redact:"$$KEEP"},{$lookup:{"from" : "Opp", "as" : "Opp", "localField" : "Id", "foreignField" : "AccountId"}}, {$match:{ID_18__c:"001A0000008XXXXXXX"}}) { "stages" : [ { "$cursor" : { "query" : { "ID_18__c" : "001A0000008XXXXXXX" }, "queryPlanner" : { "plannerVersion" : 1, "namespace" : "source_staging.Account", "indexFilterSet" : false, "parsedQuery" : { "ID_18__c" : { "$eq" : "001A0000008XXXXXXX" } }, "winningPlan" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "ID_18__c" : 1 }, "indexName" : "ID_18__c_1", "isMultiKey" : false, "multiKeyPaths" : { "ID_18__c" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "ID_18__c" : [ "[\"001A0000008XXXXXXX\", \"001A0000008XXXXXXX\"]" ] } } }, "rejectedPlans" : [ ] } } }, { "$match" : { "ID_18__c" : "001A0000008XXXXXXX" } }, { "$redact" : "$$KEEP" }, { "$match" : { "ID_18__c" : "001A0000008XXXXXXX" } }, { "$lookup" : { "from" : "Opp", "as" : "Opp", "localField" : "Id", "foreignField" : "AccountId" } } ], "ok" : 1 }
// incorrect db.Account.explain().aggregate({$lookup:{"from" : "Opp", "as" : "Opp", "localField" : "Id", "foreignField" : "AccountId"}}, {$redact:"$$KEEP"}, {$match:{ID_18__c:"001A0000008XXXXXXX"}}) { "stages" : [ { "$cursor" : { "query" : { }, "queryPlanner" : { "plannerVersion" : 1, "namespace" : "source_staging.Account", "indexFilterSet" : false, "parsedQuery" : { }, "winningPlan" : { "stage" : "COLLSCAN", "direction" : "forward" }, "rejectedPlans" : [ ] } } }, { "$match" : { "ID_18__c" : "001A0000008XXXXXXX" } }, { "$lookup" : { "from" : "Opp", "as" : "Opp", "localField" : "Id", "foreignField" : "AccountId" } }, { "$match" : { "ID_18__c" : "001A0000008XXXXXXX" } }, { "$redact" : "$$KEEP" }, { "$match" : { "ID_18__c" : "001A0000008XXXXXXX" } } ], "ok" : 1 }
When the sequence $lookup is followed by $redact and then $match, even though $match gets moved ahead of $lookup, it's not sent to the query system.