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
}
- related to
-
SERVER-3173 Planner should use path-level multikey info to generate covered plans when possible
-
- Closed
-