-
Type:
Bug
-
Resolution: Done
-
Priority:
Major - P3
-
None
-
Affects Version/s: 2.6.5
-
Component/s: Querying
-
None
-
Linux
-
None
-
None
-
None
-
None
-
None
-
None
-
None
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