Details
-
Bug
-
Resolution: Done
-
Major - P3
-
None
-
2.6.5
-
None
-
Linux
Description
I have made a test and saw that on some cases the index isnt used when i am using $gte / $lt operators in an array attribute.
My documents looks like this:
{
|
"_id": 34000,
|
"attr": [
|
{ "k": "attr1", "s": 1, "v": 4 },
|
{ "k": "attr2", "s": 0, "v": [3] },
|
{ "k": "attr3", "s": 1, "v": 649 }
|
]
|
}
|
attr3:v is random number 1 ~ 1200
Created Index:
db.collection.ensureIndex({"attr.k": 1, "attr.s": 1, "attr.v": 1}); |
Quering trough documents
Case 1:
db.collection.find({"attr":{"$all":[{"$elemMatch":{"k":"attr2","s":0,"v":3}},{"$elemMatch":{"k":"attr1","s":1,"v":{"$in":[2,4]}}},{"$elemMatch":{"k":"attr3","s":1,"v":{"$gte":100,"$lt":1100}}}]}}).limit(1).explain(true); |
Here is the output of explain:
{
|
"cursor" : "BtreeCursor attr.k_1_attr.s_1_attr.v_1",
|
"isMultiKey" : true,
|
"n" : 1,
|
"nscannedObjects" : 3,
|
"nscanned" : 3,
|
"nscannedObjectsAllPlans" : 9,
|
"nscannedAllPlans" : 9,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nYields" : 0,
|
"nChunkSkips" : 0,
|
"millis" : 0,
|
"indexBounds" : {
|
"attr.k" : [
|
[
|
"attr2",
|
"attr2"
|
]
|
],
|
"attr.s" : [
|
[
|
0,
|
0
|
]
|
],
|
"attr.v" : [
|
[
|
3,
|
3
|
]
|
]
|
},
|
"allPlans" : [
|
{
|
"cursor" : "BtreeCursor attr.k_1_attr.s_1_attr.v_1",
|
"isMultiKey" : true,
|
"n" : 1,
|
"nscannedObjects" : 3,
|
"nscanned" : 3,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nChunkSkips" : 0,
|
"indexBounds" : {
|
"attr.k" : [
|
[
|
"attr2",
|
"attr2"
|
]
|
],
|
"attr.s" : [
|
[
|
0,
|
0
|
]
|
],
|
"attr.v" : [
|
[
|
3,
|
3
|
]
|
]
|
}
|
},
|
{
|
"cursor" : "BtreeCursor attr.k_1_attr.s_1_attr.v_1",
|
"isMultiKey" : true,
|
"n" : 1,
|
"nscannedObjects" : 3,
|
"nscanned" : 3,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nChunkSkips" : 0,
|
"indexBounds" : {
|
"attr.k" : [
|
[
|
"attr1",
|
"attr1"
|
]
|
],
|
"attr.s" : [
|
[
|
1,
|
1
|
]
|
],
|
"attr.v" : [
|
[
|
2,
|
2
|
],
|
[
|
4,
|
4
|
]
|
]
|
}
|
},
|
{
|
"cursor" : "BtreeCursor attr.k_1_attr.s_1_attr.v_1",
|
"isMultiKey" : true,
|
"n" : 0,
|
"nscannedObjects" : 3,
|
"nscanned" : 3,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nChunkSkips" : 0,
|
"indexBounds" : {
|
"attr.k" : [
|
[
|
"attr3",
|
"attr3"
|
]
|
],
|
"attr.s" : [
|
[
|
1,
|
1
|
]
|
],
|
"attr.v" : [
|
[
|
-Infinity,
|
1100
|
]
|
]
|
}
|
}
|
],
|
"server" : "elastica:27017",
|
"filterSet" : false,
|
"stats" : {
|
"type" : "LIMIT",
|
"works" : 3,
|
"yields" : 0,
|
"unyields" : 0,
|
"invalidates" : 0,
|
"advanced" : 1,
|
"needTime" : 2,
|
"needFetch" : 0,
|
"isEOF" : 1,
|
"children" : [
|
{
|
"type" : "KEEP_MUTATIONS",
|
"works" : 3,
|
"yields" : 0,
|
"unyields" : 0,
|
"invalidates" : 0,
|
"advanced" : 1,
|
"needTime" : 2,
|
"needFetch" : 0,
|
"isEOF" : 0,
|
"children" : [
|
{
|
"type" : "FETCH",
|
"works" : 3,
|
"yields" : 0,
|
"unyields" : 0,
|
"invalidates" : 0,
|
"advanced" : 1,
|
"needTime" : 2,
|
"needFetch" : 0,
|
"isEOF" : 0,
|
"alreadyHasObj" : 0,
|
"forcedFetches" : 0,
|
"matchTested" : 1,
|
"children" : [
|
{
|
"type" : "IXSCAN",
|
"works" : 3,
|
"yields" : 0,
|
"unyields" : 0,
|
"invalidates" : 0,
|
"advanced" : 3,
|
"needTime" : 0,
|
"needFetch" : 0,
|
"isEOF" : 0,
|
"keyPattern" : "{ attr.k: 1.0, attr.s: 1.0, attr.v: 1.0 }",
|
"isMultiKey" : 1,
|
"boundsVerbose" : "field #0['attr.k']: [\"attr2\", \"attr2\"], field #1['attr.s']: [0.0, 0.0], field #2['attr.v']: [3.0, 3.0]",
|
"yieldMovedCursor" : 0,
|
"dupsTested" : 3,
|
"dupsDropped" : 0,
|
"seenInvalidated" : 0,
|
"matchTested" : 0,
|
"keysExamined" : 3,
|
"children" : [ ]
|
}
|
]
|
}
|
]
|
}
|
]
|
}
|
}
|
Case 2 (now i am going to increase $gte value)
db.collection.find({"attr":{"$all":[{"$elemMatch":{"k":"attr2","s":0,"v":3}},{"$elemMatch":{"k":"attr1","s":1,"v":{"$in":[2,4]}}},{"$elemMatch":{"k":"attr3","s":1,"v":{"$gte":1000,"$lt":1100}}}]}}).limit(1).explain(true); |
And this is what i get from explain:
{
|
"cursor" : "BtreeCursor attr.k_1_attr.s_1_attr.v_1",
|
"isMultiKey" : true,
|
"n" : 0,
|
"nscannedObjects" : 34000,
|
"nscanned" : 34000,
|
"nscannedObjectsAllPlans" : 54400,
|
"nscannedAllPlans" : 54400,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nYields" : 424,
|
"nChunkSkips" : 0,
|
"millis" : 410,
|
"indexBounds" : {
|
"attr.k" : [
|
[
|
"attr2",
|
"attr2"
|
]
|
],
|
"attr.s" : [
|
[
|
0,
|
0
|
]
|
],
|
"attr.v" : [
|
[
|
3,
|
3
|
]
|
]
|
},
|
"allPlans" : [
|
{
|
"cursor" : "BtreeCursor attr.k_1_attr.s_1_attr.v_1",
|
"isMultiKey" : true,
|
"n" : 0,
|
"nscannedObjects" : 34000,
|
"nscanned" : 34000,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nChunkSkips" : 0,
|
"indexBounds" : {
|
"attr.k" : [
|
[
|
"attr2",
|
"attr2"
|
]
|
],
|
"attr.s" : [
|
[
|
0,
|
0
|
]
|
],
|
"attr.v" : [
|
[
|
3,
|
3
|
]
|
]
|
}
|
},
|
{
|
"cursor" : "BtreeCursor attr.k_1_attr.s_1_attr.v_1",
|
"isMultiKey" : true,
|
"n" : 0,
|
"nscannedObjects" : 10200,
|
"nscanned" : 10200,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nChunkSkips" : 0,
|
"indexBounds" : {
|
"attr.k" : [
|
[
|
"attr1",
|
"attr1"
|
]
|
],
|
"attr.s" : [
|
[
|
1,
|
1
|
]
|
],
|
"attr.v" : [
|
[
|
2,
|
2
|
],
|
[
|
4,
|
4
|
]
|
]
|
}
|
},
|
{
|
"cursor" : "BtreeCursor attr.k_1_attr.s_1_attr.v_1",
|
"isMultiKey" : true,
|
"n" : 0,
|
"nscannedObjects" : 10200,
|
"nscanned" : 10200,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nChunkSkips" : 0,
|
"indexBounds" : {
|
"attr.k" : [
|
[
|
"attr3",
|
"attr3"
|
]
|
],
|
"attr.s" : [
|
[
|
1,
|
1
|
]
|
],
|
"attr.v" : [
|
[
|
-Infinity,
|
1100
|
]
|
]
|
}
|
}
|
],
|
"server" : "elastica:27017",
|
"filterSet" : false,
|
"stats" : {
|
"type" : "LIMIT",
|
"works" : 34001,
|
"yields" : 424,
|
"unyields" : 424,
|
"invalidates" : 0,
|
"advanced" : 0,
|
"needTime" : 34000,
|
"needFetch" : 0,
|
"isEOF" : 1,
|
"children" : [
|
{
|
"type" : "KEEP_MUTATIONS",
|
"works" : 34001,
|
"yields" : 424,
|
"unyields" : 424,
|
"invalidates" : 0,
|
"advanced" : 0,
|
"needTime" : 34000,
|
"needFetch" : 0,
|
"isEOF" : 1,
|
"children" : [
|
{
|
"type" : "FETCH",
|
"works" : 34001,
|
"yields" : 424,
|
"unyields" : 424,
|
"invalidates" : 0,
|
"advanced" : 0,
|
"needTime" : 34000,
|
"needFetch" : 0,
|
"isEOF" : 1,
|
"alreadyHasObj" : 0,
|
"forcedFetches" : 0,
|
"matchTested" : 0,
|
"children" : [
|
{
|
"type" : "IXSCAN",
|
"works" : 34001,
|
"yields" : 424,
|
"unyields" : 424,
|
"invalidates" : 0,
|
"advanced" : 34000,
|
"needTime" : 0,
|
"needFetch" : 0,
|
"isEOF" : 1,
|
"keyPattern" : "{ attr.k: 1.0, attr.s: 1.0, attr.v: 1.0 }",
|
"isMultiKey" : 1,
|
"boundsVerbose" : "field #0['attr.k']: [\"attr2\", \"attr2\"], field #1['attr.s']: [0.0, 0.0], field #2['attr.v']: [3.0, 3.0]",
|
"yieldMovedCursor" : 0,
|
"dupsTested" : 34000,
|
"dupsDropped" : 0,
|
"seenInvalidated" : 0,
|
"matchTested" : 0,
|
"keysExamined" : 34000,
|
"children" : [ ]
|
}
|
]
|
}
|
]
|
}
|
]
|
}
|
}
|
Now if i compare this two explain outputs i see two things:
1. nscanned attributes are completely different 3, 34000
2. attr.v of attr3 shows -Infinity:
"attr.v" : [
|
[
|
-Infinity,
|
1100
|
]
|
]
|
Records in database: 34000
MongoDB version: 2.6.5
OS: Ubuntu 12.04