Details
-
Improvement
-
Resolution: Unresolved
-
Minor - P4
-
None
-
None
-
None
-
Query Optimization
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?