[SERVER-12830] query does not select best index - uses wrong bounds in full explain Created: 21/Feb/14  Updated: 11/Jul/16  Resolved: 25/Feb/14

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Querying
Affects Version/s: 2.6.0-rc0
Fix Version/s: 2.6.0-rc1

Type: Bug Priority: Major - P3
Reporter: Asya Kamsky Assignee: David Storch
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-12866 explain should re-run all plans for t... Closed
Operating System: ALL
Participants:

 Description   

Collection has index

{o:1}

and also

{o:1, m:1}

where m is an array.
Optimizer picks o_1 instread of o_1_m_1 even though the latter results in no documents looked at and former results in 100 looked it...

test@local(2.6.0-rc0) > db.multi.find({o:"c",m:99999}).explain(true)
{
	"cursor" : "BtreeCursor o_1",
	"isMultiKey" : false,
	"n" : 0,
	"nscannedObjects" : 100,
	"nscanned" : 100,
	"nscannedObjectsAllPlans" : 100,
	"nscannedAllPlans" : 103,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"o" : [
			[
				"c",
				"c"
			]
		]
	},
	"allPlans" : [
		{
			"cursor" : "BtreeCursor o_1",
			"isMultiKey" : false,
			"n" : 0,
			"nscannedObjects" : 100,
			"nscanned" : 100,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nChunkSkips" : 0,
			"indexBounds" : {
				"o" : [
					[
						"c",
						"c"
					]
				]
			}
		},
		{
			"cursor" : "BtreeCursor o_1_m_1",
			"isMultiKey" : true,
			"n" : 0,
			"nscannedObjects" : 0,
			"nscanned" : 0,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nChunkSkips" : 0,
			"indexBounds" : {
				"o" : [
					[
						"a",
						"a"
					]
				],
				"m" : [
					[
						99999,
						99999
					]
				]
			}
		},
		{
			"cursor" : "BtreeCursor m_1",
			"isMultiKey" : true,
			"n" : 0,
			"nscannedObjects" : 0,
			"nscanned" : 0,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nChunkSkips" : 0,
			"indexBounds" : {
				"m" : [
					[
						99999,
						99999
					]
				]
			}
		},
		{
			"cursor" : "Complex Plan",
			"n" : 0,
			"nscannedObjects" : 0,
			"nscanned" : 1,
			"nChunkSkips" : 0
		},
		{
			"cursor" : "Complex Plan",
			"n" : 0,
			"nscannedObjects" : 0,
			"nscanned" : 2,
			"nChunkSkips" : 0
		},
		{
			"cursor" : "BasicCursor",
			"isMultiKey" : false,
			"n" : 0,
			"nscannedObjects" : 0,
			"nscanned" : 0,
			"scanAndOrder" : false,
			"indexOnly" : false,
			"nChunkSkips" : 0
		}
	],
	"server" : "asyasmacbook.local:27017",
	"filterSet" : false,
	"stats" : {
		"type" : "FETCH",
		"works" : 101,
		"yields" : 0,
		"unyields" : 0,
		"invalidates" : 0,
		"advanced" : 0,
		"needTime" : 100,
		"needFetch" : 0,
		"isEOF" : 1,
		"alreadyHasObj" : 0,
		"forcedFetches" : 0,
		"matchTested" : 0,
		"children" : [
			{
				"type" : "IXSCAN",
				"works" : 100,
				"yields" : 0,
				"unyields" : 0,
				"invalidates" : 0,
				"advanced" : 100,
				"needTime" : 0,
				"needFetch" : 0,
				"isEOF" : 1,
				"keyPattern" : "{ o: 1.0 }",
				"boundsVerbose" : "field #0['o']: [\"c\", \"c\"]",
				"isMultiKey" : 0,
				"yieldMovedCursor" : 0,
				"dupsTested" : 0,
				"dupsDropped" : 0,
				"seenInvalidated" : 0,
				"matchTested" : 0,
				"keysExamined" : 100,
				"children" : [ ]
			}
		]
	}
}
test@local(2.6.0-rc0) > db.multi.find({o:"c",m:99999}).explain()
{
	"cursor" : "BtreeCursor o_1",
	"isMultiKey" : false,
	"n" : 0,
	"nscannedObjects" : 100,
	"nscanned" : 100,
	"nscannedObjectsAllPlans" : 100,
	"nscannedAllPlans" : 103,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"o" : [
			[
				"c",
				"c"
			]
		]
	},
	"server" : "asyasmacbook.local:27017",
	"filterSet" : false
}



 Comments   
Comment by Githook User [ 25/Feb/14 ]

Author:

{u'username': u'dstorch', u'name': u'David Storch', u'email': u'david.storch@10gen.com'}

Message: SERVER-12796 SERVER-12829 SERVER-12830 better plan ranking in the case of EOF or zero results produced

If the winning plan returns zero results but does not hit EOF, then we no longer cache it.

Fixes a bug which would allow plans that did not hit EOF in the multi plan runner to be chosen over EOF plans.
Branch: master
https://github.com/mongodb/mongo/commit/8bf16f9e54e61a6befa791a6378ffdd06ad35a5b

Comment by Asya Kamsky [ 21/Feb/14 ]

Just saw why the right index wasn't chosen - the full explain shows the wrong value for o being used:

	"allPlans" : [
		{
			"cursor" : "BtreeCursor o_1",
			"nChunkSkips" : 0,
			"indexBounds" : {
				"o" : [
					[
						"c",
						"c"
					]
				]
			}
		},
		{
			"cursor" : "BtreeCursor o_1_m_1",
			"nChunkSkips" : 0,
			"indexBounds" : {
				"o" : [
					[
						"a",
						"a"
					]
				],
				"m" : [
					[
						99999,
						99999
					]
				]	

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