[SERVER-27822] Count plans sometimes don't need a FETCH Created: 26/Jan/17  Updated: 15/Nov/21  Resolved: 13/Jun/17

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: 3.5.9

Type: Bug Priority: Major - P3
Reporter: Asya Kamsky Assignee: Anne Lim
Resolution: Done Votes: 0
Labels: neweng
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
is duplicated by SERVER-31122 Count by regex match on index is not ... Closed
is duplicated by SERVER-17266 count command performance optimizatio... Backlog
Related
related to SERVER-29300 Consider allowing COUNT_SCAN plans wh... Closed
is related to SERVER-17148 Remove plans do not need a FETCH stage Backlog
Backwards Compatibility: Minor Change
Operating System: ALL
Sprint: Query 2017-06-19
Participants:
Case:

 Description   

Not sure why this is happening, but given index on username:1 match by username and group on username correctly uses covered index, but grouping on constant does not:

aggregate { aggregate: "t1", pipeline: [ { $match: { username: /^a/ } }, { $group: { _id: "$username", count: { $sum: 1.0 } } } ], cursor: {} } planSummary: IXSCAN { username: 1.0 } keysExamined:2 docsExamined:0 cursorExhausted:1 numYields:0 nreturned:1
aggregate { aggregate: "t1", pipeline: [ { $match: { username: /^a/ } }, { $group: { _id: 1.0, count: { $sum: 1.0 } } } ], cursor: {} } planSummary: IXSCAN { username: 1.0 } keysExamined:2 docsExamined:1 cursorExhausted:1 numYields:0 nreturned:1



 Comments   
Comment by Kyle Suarez [ 23/Jan/18 ]

Hi ZOXEXIVO, yes – the regular count command also takes advantage of this optimization.

Comment by Ivan Artemov [ 23/Jan/18 ]

Fixed for non aggregation queries ?

Comment by Githook User [ 13/Jun/17 ]

Author:

{u'name': u'Anne Lim', u'email': u'anne.lim@mongodb.com'}

Message: SERVER-27822: Count plans sometimes don't need a FETCH
Branch: master
https://github.com/mongodb/mongo/commit/5f7b0c8e234209ba4fbcc70bd05afae9e6d58afa

Comment by David Storch [ 17/Feb/17 ]

The latter aggregation is semantically equivalent to the following count operation:

> db.c.explain().count({username: /^a/});
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.c",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"username" : {
				"$regex" : "^a"
			}
		},
		"winningPlan" : {
			"stage" : "COUNT",
			"inputStage" : {
				"stage" : "FETCH",
				"inputStage" : {
					"stage" : "IXSCAN",
					"keyPattern" : {
						"username" : 1
					},
					"indexName" : "username_1",
					"isMultiKey" : false,
					"multiKeyPaths" : {
						"username" : [ ]
					},
					"isUnique" : false,
					"isSparse" : false,
					"isPartial" : false,
					"indexVersion" : 2,
					"direction" : "forward",
					"indexBounds" : {
						"username" : [
							"[\"a\", \"b\")",
							"[/^a/, /^a/]"
						]
					}
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "dstorch",
		"port" : 27017,
		"version" : "0.0.0",
		"gitVersion" : "unknown"
	},
	"ok" : 1
}

The explain shows that the count plan also has an unnecessary FETCH stage. Since the aggregation system uses the planner's count path for this query, these two issues have the same root cause. The issue is already tracked here: SERVER-17148. But since SERVER-17148 is kind of an odd umbrella ticket for multiple optimizations, I'll leave this ticket open and mark them as related.

Comment by Asya Kamsky [ 26/Jan/17 ]

Plans for two:

MongoDB Enterprise alt4:PRIMARY> db.t1.explain().aggregate([ {$match:{username:/^a/}}, {$group:{_id:"$username",count:{$sum:1}}}])
{
	"stages" : [
		{
			"$cursor" : {
				"query" : {
					"username" : /^a/
				},
				"fields" : {
					"username" : 1,
					"_id" : 0
				},
				"queryPlanner" : {
					"plannerVersion" : 1,
					"namespace" : "test.t1",
					"indexFilterSet" : false,
					"parsedQuery" : {
						"username" : /^a/
					},
					"winningPlan" : {
						"stage" : "PROJECTION",
						"transformBy" : {
							"username" : 1,
							"_id" : 0
						},
						"inputStage" : {
							"stage" : "IXSCAN",
							"keyPattern" : {
								"username" : 1
							},
							"indexName" : "username_1",
							"isMultiKey" : false,
							"multiKeyPaths" : {
								"username" : [ ]
							},
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 2,
							"direction" : "forward",
							"indexBounds" : {
								"username" : [
									"[\"a\", \"b\")",
									"[/^a/, /^a/]"
								]
							}
						}
					},
					"rejectedPlans" : [ ]
				}
			}
		},
		{
			"$group" : {
				"_id" : "$username",
				"count" : {
					"$sum" : {
						"$const" : 1
					}
				}
			}
		}
	],
	"ok" : 1
}
MongoDB Enterprise alt4:PRIMARY> db.t1.explain().aggregate([ {$match:{username:/^a/}}, {$group:{_id:null,count:{$sum:1}}}])
{
	"stages" : [
		{
			"$cursor" : {
				"query" : {
					"username" : /^a/
				},
				"queryPlanner" : {
					"plannerVersion" : 1,
					"namespace" : "test.t1",
					"indexFilterSet" : false,
					"parsedQuery" : {
						"username" : /^a/
					},
					"winningPlan" : {
						"stage" : "FETCH",
						"inputStage" : {
							"stage" : "IXSCAN",
							"keyPattern" : {
								"username" : 1
							},
							"indexName" : "username_1",
							"isMultiKey" : false,
							"multiKeyPaths" : {
								"username" : [ ]
							},
							"isUnique" : false,
							"isSparse" : false,
							"isPartial" : false,
							"indexVersion" : 2,
							"direction" : "forward",
							"indexBounds" : {
								"username" : [
									"[\"a\", \"b\")",
									"[/^a/, /^a/]"
								]
							}
						}
					},
					"rejectedPlans" : [ ]
				}
			}
		},
		{
			"$group" : {
				"_id" : {
					"$const" : null
				},
				"count" : {
					"$sum" : {
						"$const" : 1
					}
				}
			}
		}
	],
	"ok" : 1
}

This may be a duplicate but I didn't find a match.

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