-
Type: Improvement
-
Resolution: Unresolved
-
Priority: Major - P3
-
None
-
Affects Version/s: None
-
Component/s: Querying
-
Query Optimization
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.
- is related to
-
SERVER-81792 Optimize $exists and $ne: null in the boolean expression simplifier
- Backlog
-
SERVER-75079 Simplify boolean expressions before feeding them to the optimizer
- Closed