-
Type: Improvement
-
Resolution: Won't Fix
-
Priority: Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
Query
Currently count operations that use the $ne operator appear to be ineligible to take advantage of the COUNT_SCAN stage. Such operations currently have the COUNT, FETCH, and IXSCAN stages although no filters are applied in the FETCH stage. The server should be able to generate a COUNT_SCAN plan if there are no benefits to the current implementation.
> db.version() 3.5.7 > > db.col.explain().count({x:{$ne:1}}) { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.col", "indexFilterSet" : false, "parsedQuery" : { "$nor" : [ { "x" : { "$eq" : 1 } } ] }, "winningPlan" : { "stage" : "COUNT", "inputStage" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "x" : 1 }, "indexName" : "x_1", "isMultiKey" : false, "multiKeyPaths" : { "x" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "x" : [ "[MinKey, 1.0)", "(1.0, MaxKey]" ] } } } }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "", "port" : 27037, "version" : "3.5.7", "gitVersion" : "79c2c2d340982da8b669cd7c3aa6b958dbf56263" }, "ok" : 1 } > > db.col.createIndex({x:1}) { "createdCollectionAutomatically" : true, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1 } > > db.col.explain().count({x:1}) { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.col", "indexFilterSet" : false, "parsedQuery" : { "x" : { "$eq" : 1 } }, "winningPlan" : { "stage" : "COUNT", "inputStage" : { "stage" : "COUNT_SCAN", "keyPattern" : { "x" : 1 }, "indexName" : "x_1", "isMultiKey" : false, "multiKeyPaths" : { "x" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "indexBounds" : { "startKey" : { "x" : 1 }, "startKeyInclusive" : true, "endKey" : { "x" : 1 }, "endKeyInclusive" : true } } }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "", "port" : 27037, "version" : "3.5.7", "gitVersion" : "79c2c2d340982da8b669cd7c3aa6b958dbf56263" }, "ok" : 1 }
- is related to
-
SERVER-27822 Count plans sometimes don't need a FETCH
- Closed