Details
-
Bug
-
Resolution: Won't Fix
-
Major - P3
-
None
-
2.0.5, 2.0.6
-
Windows, Linux
-
ALL
Description
I noticed very strange behavior IMO when range queries used with compound index where one of indexed fields is array.
Example, I have collection of documents with HierarchyLevel as integer and Parents as array of strings, and other fieds. I make simple query:
{"HierarchyLevel" : {"$gte" : 1, "$lte" : 2}}
|
When I use simple key index or compound with simple type fields query optimizer runs as expected:
{
|
"cursor" : "BtreeCursor HierarchyLevel_1",
|
"nscanned" : 153,
|
"nscannedObjects" : 153,
|
"n" : 153,
|
"millis" : 1,
|
"nYields" : 0,
|
"nChunkSkips" : 0,
|
"isMultiKey" : false,
|
"indexOnly" : false,
|
"indexBounds" : {
|
"HierarchyLevel" : [
|
[
|
1,
|
2
|
]
|
]
|
}
|
}
|
{
|
"cursor" : "BtreeCursor HierarchyLevel_1_Path_1",
|
"nscanned" : 153,
|
"nscannedObjects" : 153,
|
"n" : 153,
|
"millis" : 1,
|
"nYields" : 0,
|
"nChunkSkips" : 0,
|
"isMultiKey" : false,
|
"indexOnly" : false,
|
"indexBounds" : {
|
"HierarchyLevel" : [
|
[
|
1,
|
2
|
]
|
],
|
"Path" : [
|
[
|
{
|
"$minElement" : 1
|
},
|
{
|
"$maxElement" : 1
|
}
|
]
|
]
|
}
|
}
|
But when I use compound index with array field, $lte condition is simply ignored, and all index entries from lower bound is scanned
{
|
"cursor" : "BtreeCursor HierarchyLevel_1_Parents_1",
|
"nscanned" : 283630,
|
"nscannedObjects" : 283630,
|
"n" : 153,
|
"millis" : 836,
|
"nYields" : 0,
|
"nChunkSkips" : 0,
|
"isMultiKey" : true,
|
"indexOnly" : false,
|
"indexBounds" : {
|
"HierarchyLevel" : [
|
[
|
1,
|
1.7976931348623157e+308
|
]
|
],
|
"Parents" : [
|
[
|
{
|
"$minElement" : 1
|
},
|
{
|
"$maxElement" : 1
|
}
|
]
|
]
|
}
|
}
|
In this case count of scanned objects is bigger that count of actual documents, and query took lot of time.
Attachments
Issue Links
- duplicates
-
SERVER-4180 Allow multiple constraints (on same field) within a $elemMatch clause to be used with a multikey index
-
- Closed
-
- is duplicated by
-
SERVER-21963 Index range scan inaccurate when compound index has an array field
-
- Closed
-
- is related to
-
SERVER-15059 index on date range on embedded collection is not used correctly
-
- Closed
-
- related to
-
SERVER-41594 Covered query don't use index for range $gt+$lt. Only for one side and FETCH filter for another
-
- Closed
-