[SERVER-12475] Query on compound multi-key indexes lost performance between 2.4.6 and 2.5.5-pre- Created: 24/Jan/14  Updated: 11/Jul/16  Resolved: 10/Feb/14

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: 2.6.0-rc0

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

Issue Links:
Related
is related to SERVER-12470 document and audit multikey bounds be... Closed
Operating System: ALL
Participants:

 Description   

Set up the environment using the following:

for (var i = 0; i < 1000; i++) { db.foo.insert({x: {a: 'y', b: [i, 3*i, "wassup yo"]}}); }
db.foo.ensureIndex({a:1, b:1})

In 2.4.6, performant behavior:

> db.foo.find({'a': 'y', 'b': 244}).explain()
{
	"cursor" : "BtreeCursor a_1_b_1",
	"isMultiKey" : true,
	"n" : 2,
	"nscannedObjects" : 2,
	"nscanned" : 2,
	"nscannedObjectsAllPlans" : 2,
	"nscannedAllPlans" : 2,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"a" : [
			[
				"y",
				"y"
			]
		],
		"b" : [
			[
				244,
				244
			]
		]
	},
	"server" : "Kestrel.local:27017"
}

In 2.5.5-pre-, huge slowdown:

> db.foo.find({'a': 'y', 'b': 244}).explain()
{
	"cursor" : "BtreeCursor a_1_b_1",
	"isMultiKey" : true,
	"n" : 1,
	"nscannedObjects" : 1000,
	"nscanned" : 2999,
	"nscannedObjectsAllPlans" : 1099,
	"nscannedAllPlans" : 3098,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 23,
	"nChunkSkips" : 0,
	"millis" : 7,
	"indexBounds" : {
		"a" : [
			[
				"y",
				"y"
			]
		],
		"b" : [
			[
				{
					"$minElement" : 1
				},
				{
					"$maxElement" : 1
				}
			]
		]
	},
	"server" : "Kestrel.local:27017"
}

It's unclear which of these we should expect to see, based on SERVER-7959 and other incidents. However, the performance slowdown is going to surprise a lot of users. (Also, pretty sure that the functionality we use is encouraged by our docs/training.)

However, not 100% sure if the 2.4.6 functionality lost results. Bears further investigation.



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

Author:

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

Message: SERVER-12470 SERVER-12475 SERVER-9532 fix multikey index bounds for compound indices and elemMatch

The new query system multikey index bounds behavior now largely matches that of 2.4.
For a detailed description of the rules for building multikey index bounds, see
the long comment atop db/query/planner_access.h.
Branch: master
https://github.com/mongodb/mongo/commit/bbca96a512c580f13e044eeb7de15132fb3ce193

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