Uploaded image for project: 'Documentation'
  1. Documentation
  2. DOCS-11728

count() does not need a continuous range of keys in the index

    XMLWordPrintableJSON

Details

    Description

      In the following section: https://docs.mongodb.com/manual/reference/method/db.collection.count/#db.collection.count

      we state:

      If, however, the query can use an index but the query predicates do not access a single contiguous range of index keys or the query also contains conditions on fields outside the index, then in addition to using the index, MongoDB must also read the documents to return the count.
      ...

      db.collection.find( { a: { $gt: 5 }, b: 5 } ).count()
      

      However, the following ``count()`` does not fetch any document:

      replset:PRIMARY> db.movies.explain("executionStats").count({"imdb.rating":{$gt:5}, countries: "USA" })
      {
      	"queryPlanner" : {
      		"plannerVersion" : 1,
      		"namespace" : "mflix.movies",
      		"indexFilterSet" : false,
      		"parsedQuery" : {
      			"$and" : [
      				{
      					"countries" : {
      						"$eq" : "USA"
      					}
      				},
      				{
      					"imdb.rating" : {
      						"$gt" : 5
      					}
      				}
      			]
      		},
      		"winningPlan" : {
      			"stage" : "COUNT",
      			"inputStage" : {
      				"stage" : "IXSCAN",
      				"keyPattern" : {
      					"imdb.rating" : 1,
      					"countries" : 1
      				},
      				"indexName" : "imdb.rating_1_countries_1",
      				"isMultiKey" : true,
      				"multiKeyPaths" : {
      					"imdb.rating" : [ ],
      					"countries" : [
      						"countries"
      					]
      				},
      				"isUnique" : false,
      				"isSparse" : false,
      				"isPartial" : false,
      				"indexVersion" : 2,
      				"direction" : "forward",
      				"indexBounds" : {
      					"imdb.rating" : [
      						"(5.0, inf.0]"
      					],
      					"countries" : [
      						"[\"USA\", \"USA\"]"
      					]
      				}
      			}
      		},
      		"rejectedPlans" : [ ]
      	},
      	"executionStats" : {
      		"executionSuccess" : true,
      		"nReturned" : 0,
      		"executionTimeMillis" : 22,
      		"totalKeysExamined" : 20392,
      		"totalDocsExamined" : 0,
      		"executionStages" : {
      			"stage" : "COUNT",
      			"nReturned" : 0,
      			"executionTimeMillisEstimate" : 20,
      			"works" : 20392,
      			"advanced" : 0,
      			"needTime" : 20391,
      			"needYield" : 0,
      			"saveState" : 159,
      			"restoreState" : 159,
      			"isEOF" : 1,
      			"invalidates" : 0,
      			"nCounted" : 20305,
      			"nSkipped" : 0,
      			"inputStage" : {
      				"stage" : "IXSCAN",
      				"nReturned" : 20305,
      				"executionTimeMillisEstimate" : 10,
      				"works" : 20392,
      				"advanced" : 20305,
      				"needTime" : 86,
      				"needYield" : 0,
      				"saveState" : 159,
      				"restoreState" : 159,
      				"isEOF" : 1,
      				"invalidates" : 0,
      				"keyPattern" : {
      					"imdb.rating" : 1,
      					"countries" : 1
      				},
      				"indexName" : "imdb.rating_1_countries_1",
      				"isMultiKey" : true,
      				"multiKeyPaths" : {
      					"imdb.rating" : [ ],
      					"countries" : [
      						"countries"
      					]
      				},
      				"isUnique" : false,
      				"isSparse" : false,
      				"isPartial" : false,
      				"indexVersion" : 2,
      				"direction" : "forward",
      				"indexBounds" : {
      					"imdb.rating" : [
      						"(5.0, inf.0]"
      					],
      					"countries" : [
      						"[\"USA\", \"USA\"]"
      					]
      				},
      				"keysExamined" : 20392,
      				"seeks" : 87,
      				"dupsTested" : 20305,
      				"dupsDropped" : 0,
      				"seenInvalidated" : 0
      			}
      		}
      	},
      	"serverInfo" : {
      		"host" : "Daniels-MacBook-Pro-3.local",
      		"port" : 27017,
      		"version" : "3.6.4",
      		"gitVersion" : "d0181a711f7e7f39e60b5aeb1dc7097bf6ae5856"
      	},
      	"ok" : 1,
      	"operationTime" : Timestamp(1527013617, 1),
      	"$clusterTime" : {
      		"clusterTime" : Timestamp(1527013617, 1),
      		"signature" : {
      			"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
      			"keyId" : NumberLong(0)
      		}
      	}
      }
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            daniel.coupal@mongodb.com Daniel Coupal
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:
              5 years, 38 weeks, 1 day ago