[SERVER-29247] COUNT_SCAN not used for partial indexes Created: 17/May/17  Updated: 18/Aug/23  Resolved: 19/May/17

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 3.4.4
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Andre Spiegel Assignee: Backlog - Query Team (Inactive)
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-28889 Partial index shouldn't do fetch on c... Closed
Related
is related to SERVER-23808 Implement Index Choice based on Parti... Backlog
is related to SERVER-28889 Partial index shouldn't do fetch on c... Closed
Assigned Teams:
Query
Operating System: ALL
Participants:
Case:

 Description   

Performing a count on a collection with a partial index leads to IXSCAN/FETCH/COUNT, rather than the more efficient COUNT_SCAN. By contrast, a count on a regular index does lead to a COUNT_SCAN.

For example:

db.coll.createIndex({"value":1}, {"partialFilterExpression":{"flag":true}})
 
db.coll.explain(true).count({"flag":true,"value":10})
...
		"winningPlan" : {
			"stage" : "COUNT",
			"inputStage" : {
				"stage" : "FETCH",
				"filter" : {
					"flag" : {
						"$eq" : true
					}
				},
				"inputStage" : {
					"stage" : "IXSCAN",
					"keyPattern" : {
						"value" : 1
					},
					"indexName" : "value_1",
					"isMultiKey" : false,
					"multiKeyPaths" : {
						"value" : [ ]
					},
					"isUnique" : false,
					"isSparse" : false,
					"isPartial" : true,
					"indexVersion" : 2,
					"direction" : "forward",
					"indexBounds" : {
						"value" : [
							"[10.0, 10.0]"
						]
					}
				}
			}
		},
...

Here is the same example with a compound index on (flag, value):

db.coll.createIndex({"flag":1, "value":1})
 
db.coll.explain(true).count({"flag":true,"value":10})
...
		"winningPlan" : {
			"stage" : "COUNT",
			"inputStage" : {
				"stage" : "COUNT_SCAN",
				"keyPattern" : {
					"flag" : 1,
					"value" : 1
				},
				"indexName" : "flag_1_value_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"flag" : [ ],
					"value" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"indexBounds" : {
					"startKey" : {
						"flag" : true,
						"value" : 10
					},
					"startKeyInclusive" : true,
					"endKey" : {
						"flag" : true,
						"value" : 10
					},
					"endKeyInclusive" : true
				}
			}
		},		

This issue is related to, but distinct from, other cases where partial indexes are not utilized to the full extent, such as SERVER-23808, SERVER-28889.



 Comments   
Comment by Ian Whalen (Inactive) [ 19/May/17 ]

After discussing amongst the Query team we believe that this really is due to the same underlying improvement request as recorded in SERVER-28889.

Generated at Thu Feb 08 04:20:18 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.