Details
-
Improvement
-
Status: Backlog
-
Major - P3
-
Resolution: Unresolved
-
None
-
None
-
Query Optimization
Description
Compare these two queries and their plans:
db.full.explain().count({a:{$eq:1}})
|
{
|
"queryPlanner" : {
|
"plannerVersion" : 1,
|
"namespace" : "test.full",
|
"indexFilterSet" : false,
|
"parsedQuery" : {
|
"a" : {
|
"$eq" : 1
|
}
|
},
|
"winningPlan" : {
|
"stage" : "COUNT",
|
"inputStage" : {
|
"stage" : "COUNT_SCAN",
|
"keyPattern" : {
|
"a" : 1
|
},
|
"indexName" : "a_1",
|
"isMultiKey" : false,
|
"multiKeyPaths" : {
|
"a" : [ ]
|
},
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : false,
|
"indexVersion" : 2,
|
"indexBounds" : {
|
"startKey" : {
|
"a" : 1
|
},
|
"startKeyInclusive" : true,
|
"endKey" : {
|
"a" : 1
|
},
|
"endKeyInclusive" : true
|
}
|
}
|
},
|
"rejectedPlans" : [ ]
|
},
|
"serverInfo" : {
|
"host" : "Asyas-MacBook-Pro.local",
|
"port" : 27017,
|
"version" : "3.6.4",
|
"gitVersion" : "d0181a711f7e7f39e60b5aeb1dc7097bf6ae5856"
|
},
|
"ok" : 1
|
}
|
With equality on "a" we get best possible plan.
But adding a "redundant" condition on "a" that by itself cannot be fulfilled using the same index, we now revert to the "worse" plan with additional FETCH:
test@127.0.0.1:27017(3.6.4) > db.full.explain().count({a:{$exists:true,$eq:1}})
|
{
|
"queryPlanner" : {
|
"plannerVersion" : 1,
|
"namespace" : "test.full",
|
"indexFilterSet" : false,
|
"parsedQuery" : {
|
"$and" : [
|
{
|
"a" : {
|
"$eq" : 1
|
}
|
},
|
{
|
"a" : {
|
"$exists" : true
|
}
|
}
|
]
|
},
|
"winningPlan" : {
|
"stage" : "COUNT",
|
"inputStage" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"a" : {
|
"$exists" : true
|
}
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"a" : 1
|
},
|
"indexName" : "a_1",
|
"isMultiKey" : false,
|
"multiKeyPaths" : {
|
"a" : [ ]
|
},
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : false,
|
"indexVersion" : 2,
|
"direction" : "forward",
|
"indexBounds" : {
|
"a" : [
|
"[1.0, 1.0]"
|
]
|
}
|
}
|
}
|
},
|
"rejectedPlans" : [ ]
|
},
|
"serverInfo" : {
|
"host" : "Asyas-MacBook-Pro.local",
|
"port" : 27017,
|
"version" : "3.6.4",
|
"gitVersion" : "d0181a711f7e7f39e60b5aeb1dc7097bf6ae5856"
|
},
|
"ok" : 1
|
}
|
This feels conceptually maybe related to SERVER-12281 (and even SERVER-34119) but seemed like it might be an easier fix on its own.
Same thing happens if $eq:1 is and'ed with $ne:null for instance.