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

Leverage multiKeyPath information to prevent filtering in FETCH with negation operator

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major - P3
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: Backlog
    • Component/s: Querying
    • Labels:

      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

          Issue Links

            Activity

              People

              Assignee:
              backlog-query-optimization Backlog - Query Optimization
              Reporter:
              christopher.harris Christopher Harris
              Participants:
              Votes:
              0 Vote for this issue
              Watchers:
              10 Start watching this issue

                Dates

                Created:
                Updated: