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

Leverage multiKeyPath information to prevent filtering in FETCH with negation operator

    XMLWordPrintableJSON

Details

    • Icon: Improvement Improvement
    • Resolution: Unresolved
    • Icon: Major - P3 Major - P3
    • None
    • None
    • Querying
    • Query Optimization

    Description

      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
      }
      

      Attachments

        Activity

          People

            backlog-query-optimization Backlog - Query Optimization
            christopher.harris@mongodb.com Chris Harris
            Votes:
            0 Vote for this issue
            Watchers:
            10 Start watching this issue

            Dates

              Created:
              Updated: