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

Consider allowing COUNT_SCAN plans when the $ne operator is present

    • Type: Icon: Improvement Improvement
    • Resolution: Won't Fix
    • Priority: Icon: Major - P3 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
      }
      

            Assignee:
            backlog-server-query Backlog - Query Team (Inactive)
            Reporter:
            christopher.harris@mongodb.com Chris Harris
            Votes:
            0 Vote for this issue
            Watchers:
            10 Start watching this issue

              Created:
              Updated:
              Resolved: