|
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.
|