-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Minor - P4
-
None
-
Affects Version/s: None
-
Component/s: Querying
-
None
-
Query Optimization
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?