[SERVER-21377] Index bounds over trailing field are not constrained for AND_HASH plan using a multikey index Created: 28/Oct/15  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Minor - P4
Reporter: Alexander Komyagin Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Optimization
Participants:

 Description   

Trying to evaluate the behavior of index intersection (I enabled hash-based intersection manually), I noticed that for the following query with the following index the resulting plan doesn't have bounds for the "x" object:

db.test.ensureIndex({mbox_id:1,x:1})
db.test.insert({x: [1,2,3]}) // Makes the index multikey.
db.adminCommand({setParameter: 1, internalQueryPlannerEnableHashIntersection: true}) // Enable AND_HASH.
db.test.find({$and:[{mbox_id:1, x:{k:'name',v:'alex'}},{mbox_id:1, x:{k:'string',v:'dunno'}},{mbox_id:1, x:{k:'num',v:4}}]}).explain()

			{
				"stage" : "KEEP_MUTATIONS",
				"inputStage" : {
					"stage" : "FETCH",
					"filter" : {
						"$and" : [
							{
								"x" : {
									"$eq" : {
										"k" : "name",
										"v" : "alex"
									}
								}
							},
							{
								"x" : {
									"$eq" : {
										"k" : "string",
										"v" : "dunno"
									}
								}
							},
							{
								"x" : {
									"$eq" : {
										"k" : "num",
										"v" : 4
									}
								}
							}
						]
					},
					"inputStage" : {
						"stage" : "AND_HASH",
						"inputStages" : [
							{
								"stage" : "IXSCAN",
								"keyPattern" : {
									"mbox_id" : 1,
									"x" : 1
								},
								"indexName" : "mbox_id_1_x_1",
								"isMultiKey" : true,
								"direction" : "forward",
								"indexBounds" : {
									"mbox_id" : [
										"[1.0, 1.0]"
									],
									"x" : [
										"[MinKey, MaxKey]"
									]
								}
							},
							{
								"stage" : "IXSCAN",
								"keyPattern" : {
									"mbox_id" : 1,
									"x" : 1
								},
								"indexName" : "mbox_id_1_x_1",
								"isMultiKey" : true,
								"direction" : "forward",
								"indexBounds" : {
									"mbox_id" : [
										"[1.0, 1.0]"
									],
									"x" : [
										"[MinKey, MaxKey]"
									]
								}
							},
							{
								"stage" : "IXSCAN",
								"keyPattern" : {
									"mbox_id" : 1,
									"x" : 1
								},
								"indexName" : "mbox_id_1_x_1",
								"isMultiKey" : true,
								"direction" : "forward",
								"indexBounds" : {
									"mbox_id" : [
										"[1.0, 1.0]"
									],
									"x" : [
										"[MinKey, MaxKey]"
									]
								}
							}
						]
					}
				}
			}
		]
	},
	"serverInfo" : {
		"host" : "AD-MAC10G-2.local",
		"port" : 27017,
		"version" : "3.0.7",
		"gitVersion" : "6ce7cbe8c6b899552dadd907604559806aa2e9bd"
	},
	"ok" : 1
}

It appears that when we self-intersect a compound index we don't use the second field. Is that correct? If so, is it a known and understood limitation?



 Comments   
Comment by Alexander Komyagin [ 10/Nov/15 ]

Thanks Dave

Comment by David Storch [ 10/Nov/15 ]

The problem is in our multikey handling in PlanEnumerator::enumerateAndIntersect():

https://github.com/mongodb/mongo/blob/c836472353e736424c9bb87868508c9e633b892d/src/mongo/db/query/plan_enumerator.cpp#L714-L727

Comment by David Storch [ 10/Nov/15 ]

alex.komyagin, I looked into this and discovered why the planner is behaving this way. It does not appear to be intentional, and I am not aware of any outstanding feature requests to tighten the bounds. The behavior is not incorrect in that the plan will produce the correct results. However, this could be a performance problem since the bounds are too loose. I will move this to the SERVER project and add it to the query team's backlog.

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