[SERVER-28365] Multikey index uses inefficient indexBounds Created: 17/Mar/17  Updated: 31/May/17  Resolved: 28/Mar/17

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

Type: Improvement Priority: Minor - P4
Reporter: Tudor Aursulesei Assignee: Mark Agarunov
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Participants:

 Description   

> db.testcol.find().limit(7)
{ "_id" : "id1", "field" : [ { "age" : 23, "height" : 137 } ] }
{ "_id" : "id2", "field" : [ { "age" : 10, "height" : 137 }, { "age" : 24, "height" : 138 }, { "age" : 30, "height" : 141 }, { "age" : 10, "height" : 163 } ] }
{ "_id" : "id3", "field" : [ { "age" : 24, "height" : 163 }, { "age" : 26, "height" : 164 }, { "age" : 30, "height" : 130 }, { "age" : 10, "height" : 140 } ] }
{ "_id" : "id4", "field" : [ { "age" : 25, "height" : 163 } ] }
{ "_id" : "id5", "field" : [ { "age" : 10, "height" : 144 } ] }
{ "_id" : "id6", "field" : [ { "age" : 22, "height" : 169 }, { "age" : 26, "height" : 151 }, { "age" : 29, "height" : 171 } ] }
{ "_id" : "id7", "field" : [ { "age" : 30, "height" : 136 }, { "age" : 10, "height" : 142 }, { "age" : 27, "height" : 136 } ] }
> db.testcol.getIndexes()
[
	{
		"v" : 2,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_",
		"ns" : "testdb.testcol"
	},
	{
		"v" : 2,
		"key" : {
			"field.age" : 1,
			"field.height" : 1
		},
		"name" : "field.age_1_field.height_1",
		"ns" : "testdb.testcol"
	}
]
> db.testcol.find({"field.age": 14, "field.height": 163}).explain()
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "testdb.testcol",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"field.age" : {
						"$eq" : 14
					}
				},
				{
					"field.height" : {
						"$eq" : 163
					}
				}
			]
		},
		"winningPlan" : {
			"stage" : "FETCH",
			"filter" : {
				"field.height" : {
					"$eq" : 163
				}
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"field.age" : 1,
					"field.height" : 1
				},
				"indexName" : "field.age_1_field.height_1",
				"isMultiKey" : true,
				"multiKeyPaths" : {
					"field.age" : [
						"field"
					],
					"field.height" : [
						"field"
					]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"field.age" : [
						"[14.0, 14.0]"
					],
					"field.height" : [
						"[MinKey, MaxKey]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "MacBook-Pro.local",
		"port" : 27017,
		"version" : "3.4.2",
		"gitVersion" : "3f76e40c105fc223b3e5aac3e20dcd026b83b38b"
	},
	"ok" : 1
}

why is it using minkey maxkey on the second field? This seems to hurt performance.



 Comments   
Comment by Tudor Aursulesei [ 28/Mar/17 ]

I've had to read your response several times over to understand what i'm doing wrong. I think the documentation needs to be a bit more explicit - your example made it very clear. Mongo cannot know if there are multiple elements in the embedded document which might match.
I still never would have imagined until today that

> c.find({"field.age": 14, "field.height": 163})

would match

{ "_id" : "id8", "field" : [ { "age" : 14, "height" : 137 }, { "age" : 18, "height" : 163 } ] }

Thanks.

Comment by Mark Agarunov [ 28/Mar/17 ]

Hello thestick613,

Thank you for the report. Looking over the output you've provided, this looks to be expected behavior. According to the documentation,

If the query does not join the conditions on the indexed array fields with $elemMatch, MongoDB cannot compound their bounds.

The query you provided could be rewritten to use $elemMatch which would allow MongoDB to calculate the bounds:

> c.find({"field.age": 14, "field.height": 163})
{ "_id" : "id8", "field" : [ { "age" : 14, "height" : 137 }, { "age" : 18, "height" : 163 } ] }
{ "_id" : "id9", "field" : [ { "age" : 14, "height" : 163 } ] }
>
> c.find({"field":{$elemMatch:{"age": 14, "height": 163}}})
{ "_id" : "id9", "field" : [ { "age" : 14, "height" : 163 } ] }
>
> c.find({"field":{$elemMatch:{"age": 14, "height": 163}}}).explain()
...
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"field.age" : 1,
					"field.height" : 1
				},
				"indexName" : "field.age_1_field.height_1",
				"isMultiKey" : true,
				"multiKeyPaths" : {
					"field.age" : [
						"field"
					],
					"field.height" : [
						"field"
					]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"field.age" : [
						"[14.0, 14.0]"
					],
					"field.height" : [
						"[163.0, 163.0]"
					]
				}
			}

Please note that SERVER project is for reporting bugs or feature suggestions for the MongoDB server. For MongoDB-related support discussion please post on the mongodb-user group or Stack Overflow with the mongodb tag. A question like this involving more discussion would be best posted on the mongodb-user group.

Thanks,
Mark

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