[DOCS-11728] count() does not need a continuous range of keys in the index Created: 22/May/18  Updated: 30/Oct/23  Resolved: 03/Mar/20

Status: Closed
Project: Documentation
Component/s: manual, Server
Affects Version/s: 3.6.4
Fix Version/s: Server_Docs_20231030

Type: Bug Priority: Major - P3
Reporter: Daniel Coupal Assignee: Unassigned
Resolution: Gone away Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Participants:
Days since reply: 5 years, 38 weeks, 1 day ago
Epic Link: DOCSP-1769

 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)
		}
	}
}



 Comments   
Comment by Daniel Coupal [ 22/May/18 ]

The same applies to the following query, the first in the group of 3, no FETCH is done:

db.collection.find( { a: 5, b: { $in: [ 1, 2, 3 ] } } ).count()

replset:PRIMARY> db.movies.explain("executionStats").count({"imdb.rating": 5, countries: {$in: ["Canada", "USA"]} })
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "mflix.movies",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"imdb.rating" : {
						"$eq" : 5
					}
				},
				{
					"countries" : {
						"$in" : [
							"Canada",
							"USA"
						]
					}
				}
			]
		},
		"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, 5.0]"
					],
					"countries" : [
						"[\"Canada\", \"Canada\"]",
						"[\"USA\", \"USA\"]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 0,
		"executionTimeMillis" : 6,
		"totalKeysExamined" : 361,
		"totalDocsExamined" : 0,
		"executionStages" : {
			"stage" : "COUNT",
			"nReturned" : 0,
			"executionTimeMillisEstimate" : 0,
			"works" : 361,
			"advanced" : 0,
			"needTime" : 360,
			"needYield" : 0,
			"saveState" : 2,
			"restoreState" : 2,
			"isEOF" : 1,
			"invalidates" : 0,
			"nCounted" : 336,
			"nSkipped" : 0,
			"inputStage" : {
				"stage" : "IXSCAN",
				"nReturned" : 336,
				"executionTimeMillisEstimate" : 0,
				"works" : 361,
				"advanced" : 336,
				"needTime" : 24,
				"needYield" : 0,
				"saveState" : 2,
				"restoreState" : 2,
				"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, 5.0]"
					],
					"countries" : [
						"[\"Canada\", \"Canada\"]",
						"[\"USA\", \"USA\"]"
					]
				},
				"keysExamined" : 361,
				"seeks" : 2,
				"dupsTested" : 359,
				"dupsDropped" : 23,
				"seenInvalidated" : 0
			}
		}
	},
	"serverInfo" : {
		"host" : "Daniels-MacBook-Pro-3.local",
		"port" : 27017,
		"version" : "3.6.4",
		"gitVersion" : "d0181a711f7e7f39e60b5aeb1dc7097bf6ae5856"
	},
	"ok" : 1,
	"operationTime" : Timestamp(1527014467, 1),
	"$clusterTime" : {
		"clusterTime" : Timestamp(1527014467, 1),
		"signature" : {
			"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
			"keyId" : NumberLong(0)
		}
	}
}

Generated at Thu Feb 08 08:03:31 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.