Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-28454

Make more of an effort to move $match stages through $redact stages

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

            Assignee:
            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Reporter:
            asya.kamsky@mongodb.com Asya Kamsky
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated: