[SERVER-20433] Unexpected non-covered query in 3.0 and 2.6 (that is covered in 2.4) Created: 16/Sep/15  Updated: 16/Sep/15  Resolved: 16/Sep/15

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.6.11, 3.0.6
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Joanna Cheng Assignee: J Rassi
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-12769 Queries that scan an entire index to ... Backlog
Operating System: ALL
Participants:

 Description   

Query:

db.foo.find({"age" : {"$gte" : 21}},{"_id":0, "username":1, "age":1}).explain(true)

Index

{"username" : 1, "age" : 1}

is not covered in 3.0.6

> db.foo.find({"age" : {"$gte" : 21}},{"_id":0, "username":1, "age":1}).sort({username:1}).explain(true)
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.foo",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"age" : {
				"$gte" : 21
			}
		},
		"winningPlan" : {
			"stage" : "PROJECTION",
			"transformBy" : {
				"_id" : 0,
				"username" : 1,
				"age" : 1
			},
			"inputStage" : {
				"stage" : "FETCH",
				"filter" : {
					"age" : {
						"$gte" : 21
					}
				},
				"inputStage" : {
					"stage" : "IXSCAN",
					"keyPattern" : {
						"username" : 1,
						"age" : 1
					},
					"indexName" : "username_1_age_1",
					"isMultiKey" : false,
					"direction" : "forward",
					"indexBounds" : {
						"username" : [
							"[MinKey, MaxKey]"
						],
						"age" : [
							"[MinKey, MaxKey]"
						]
					}
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 9,
		"executionTimeMillis" : 0,
		"totalKeysExamined" : 32,
		"totalDocsExamined" : 32,
		"executionStages" : {
			"stage" : "PROJECTION",
			"nReturned" : 9,
			"executionTimeMillisEstimate" : 0,
			"works" : 33,
			"advanced" : 9,
			"needTime" : 23,
			"needFetch" : 0,
			"saveState" : 0,
			"restoreState" : 0,
			"isEOF" : 1,
			"invalidates" : 0,
			"transformBy" : {
				"_id" : 0,
				"username" : 1,
				"age" : 1
			},
			"inputStage" : {
				"stage" : "FETCH",
				"filter" : {
					"age" : {
						"$gte" : 21
					}
				},
				"nReturned" : 9,
				"executionTimeMillisEstimate" : 0,
				"works" : 33,
				"advanced" : 9,
				"needTime" : 23,
				"needFetch" : 0,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 1,
				"invalidates" : 0,
				"docsExamined" : 32,
				"alreadyHasObj" : 0,
				"inputStage" : {
					"stage" : "IXSCAN",
					"nReturned" : 32,
					"executionTimeMillisEstimate" : 0,
					"works" : 32,
					"advanced" : 32,
					"needTime" : 0,
					"needFetch" : 0,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 1,
					"invalidates" : 0,
					"keyPattern" : {
						"username" : 1,
						"age" : 1
					},
					"indexName" : "username_1_age_1",
					"isMultiKey" : false,
					"direction" : "forward",
					"indexBounds" : {
						"username" : [
							"[MinKey, MaxKey]"
						],
						"age" : [
							"[MinKey, MaxKey]"
						]
					},
					"keysExamined" : 32,
					"dupsTested" : 0,
					"dupsDropped" : 0,
					"seenInvalidated" : 0,
					"matchTested" : 0
				}
			}
		},
		"allPlansExecution" : [ ]
	},
	"serverInfo" : {
		"host" : "Joannas-MacBook-Pro.local",
		"port" : 27017,
		"version" : "3.0.6",
		"gitVersion" : "1ef45a23a4c5e3480ac919b28afcba3c615488f2"
	},
	"ok" : 1
}

However it is covered in 2.4.14

replset:PRIMARY> db.foo.find({"age" : {"$gte" : 21}},{"_id":0, "username":1, "age":1}).sort({username:1}).explain(true)
{
	"cursor" : "BtreeCursor username_1_age_1",
	"isMultiKey" : false,
	"n" : 9,
	"nscannedObjects" : 0,
	"nscanned" : 10,
	"nscannedObjectsAllPlans" : 11,
	"nscannedAllPlans" : 21,
	"scanAndOrder" : false,
	"indexOnly" : true,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"username" : [
			[
				{
					"$minElement" : 1
				},
				{
					"$maxElement" : 1
				}
			]
		],
		"age" : [
			[
				21,
				1.7976931348623157e+308
			]
		]
	},
	"allPlans" : [
		{
			"cursor" : "BtreeCursor username_1_age_1",
			"n" : 9,
			"nscannedObjects" : 0,
			"nscanned" : 10,
			"indexBounds" : {
				"username" : [
					[
						{
							"$minElement" : 1
						},
						{
							"$maxElement" : 1
						}
					]
				],
				"age" : [
					[
						21,
						1.7976931348623157e+308
					]
				]
			}
		},
		{
			"cursor" : "BasicCursor",
			"n" : 0,
			"nscannedObjects" : 11,
			"nscanned" : 11,
			"indexBounds" : {
 
			}
		}
	],
	"oldPlan" : {
		"cursor" : "BtreeCursor username_1_age_1",
		"indexBounds" : {
			"username" : [
				[
					{
						"$minElement" : 1
					},
					{
						"$maxElement" : 1
					}
				]
			],
			"age" : [
				[
					21,
					1.7976931348623157e+308
				]
			]
		}
	},
	"server" : "Joannas-MacBook-Pro.local:27018"
}



 Comments   
Comment by J Rassi [ 16/Sep/15 ]

Thanks for the report, Joanna. I can confirm this issue as a dup of SERVER-12769, and am closing this ticket as such.

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