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

Leverage multiKeyPath information to prevent filtering in FETCH with negation operator

    • Type: Icon: Improvement Improvement
    • Resolution: Unresolved
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Querying
    • Query Optimization

      Traditionally when an operation such as $ne is applied to a field in a multikey index, appropriate index bounds may be applied but a filter in the FETCH stage is required to confirm if the entire document satisfies the condition. It seems that it should be possible to avoid this check starting in 3.4 if the field with the operator is not an array (based on the multiKeyPath knowledge).

      Projection added below to emphasize the potential improvements, but the filter check happens regardless.

      diff:PRIMARY> db.version()
      3.4.3
      diff:PRIMARY> 
      diff:PRIMARY> db.collection.drop()
      true
      diff:PRIMARY> 
      diff:PRIMARY> db.collection.createIndex({x:1,y:1})
      {
      	"createdCollectionAutomatically" : true,
      	"numIndexesBefore" : 1,
      	"numIndexesAfter" : 2,
      	"ok" : 1
      }
      diff:PRIMARY> 
      diff:PRIMARY> 
      diff:PRIMARY> 
      diff:PRIMARY> db.collection.explain().find({x:{$ne:1}},{_id:0, x:1})
      {
      	"queryPlanner" : {
      		"plannerVersion" : 1,
      		"namespace" : "test.collection",
      		"indexFilterSet" : false,
      		"parsedQuery" : {
      			"$nor" : [
      				{
      					"x" : {
      						"$eq" : 1
      					}
      				}
      			]
      		},
      		"winningPlan" : {
      			"stage" : "PROJECTION",
      			"transformBy" : {
      				"_id" : 0,
      				"x" : 1
      			},
      			"inputStage" : {
      				"stage" : "IXSCAN",
      				"keyPattern" : {
      					"x" : 1,
      					"y" : 1
      				},
      				"indexName" : "x_1_y_1",
      				"isMultiKey" : false,
      				"multiKeyPaths" : {
      					"x" : [ ],
      					"y" : [ ]
      				},
      				"isUnique" : false,
      				"isSparse" : false,
      				"isPartial" : false,
      				"indexVersion" : 2,
      				"direction" : "forward",
      				"indexBounds" : {
      					"x" : [
      						"[MinKey, 1.0)",
      						"(1.0, MaxKey]"
      					],
      					"y" : [
      						"[MinKey, MaxKey]"
      					]
      				}
      			}
      		},
      		"rejectedPlans" : [ ]
      	},
      	"serverInfo" : {
      		"host" : "Chriss-MacBook-Pro.local",
      		"port" : 30001,
      		"version" : "3.4.3",
      		"gitVersion" : "f07437fb5a6cca07c10bafa78365456eb1d6d5e1"
      	},
      	"ok" : 1
      }
      diff:PRIMARY> 
      diff:PRIMARY> 
      diff:PRIMARY> db.collection.insert({x:1, y:[1,100]})
      WriteResult({ "nInserted" : 1 })
      diff:PRIMARY> 
      diff:PRIMARY> 
      diff:PRIMARY> db.collection.explain().find({x:{$ne:1}},{_id:0, x:1})
      {
      	"queryPlanner" : {
      		"plannerVersion" : 1,
      		"namespace" : "test.collection",
      		"indexFilterSet" : false,
      		"parsedQuery" : {
      			"$nor" : [
      				{
      					"x" : {
      						"$eq" : 1
      					}
      				}
      			]
      		},
      		"winningPlan" : {
      			"stage" : "PROJECTION",
      			"transformBy" : {
      				"_id" : 0,
      				"x" : 1
      			},
      			"inputStage" : {
      				"stage" : "FETCH",
      				"filter" : {
      					"$nor" : [
      						{
      							"x" : {
      								"$eq" : 1
      							}
      						}
      					]
      				},
      				"inputStage" : {
      					"stage" : "IXSCAN",
      					"keyPattern" : {
      						"x" : 1,
      						"y" : 1
      					},
      					"indexName" : "x_1_y_1",
      					"isMultiKey" : true,
      					"multiKeyPaths" : {
      						"x" : [ ],
      						"y" : [
      							"y"
      						]
      					},
      					"isUnique" : false,
      					"isSparse" : false,
      					"isPartial" : false,
      					"indexVersion" : 2,
      					"direction" : "forward",
      					"indexBounds" : {
      						"x" : [
      							"[MinKey, 1.0)",
      							"(1.0, MaxKey]"
      						],
      						"y" : [
      							"[MinKey, MaxKey]"
      						]
      					}
      				}
      			}
      		},
      		"rejectedPlans" : [ ]
      	},
      	"serverInfo" : {
      		"host" : "Chriss-MacBook-Pro.local",
      		"port" : 30001,
      		"version" : "3.4.3",
      		"gitVersion" : "f07437fb5a6cca07c10bafa78365456eb1d6d5e1"
      	},
      	"ok" : 1
      }
      

            Assignee:
            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Reporter:
            christopher.harris@mongodb.com Chris Harris
            Votes:
            0 Vote for this issue
            Watchers:
            10 Start watching this issue

              Created:
              Updated: