-
Type: Improvement
-
Resolution: Unresolved
-
Priority: Major - P3
-
None
-
Affects Version/s: 2.6.0
-
Component/s: Querying
-
Query Optimization
-
(copied to CRM)
Updated description:
For a compound index {a: 1, b: 1}, a range query {b: {$gt: 10, $lt: 20}} (using a hint on the compound index) produces the following index bounds:
// 2.4: {a: [{$minElement: 1}, {$maxElement: 1}], b: [10, 20]} // 2.6 {a: [{$minElement: 1}, {$maxElement: 1}], b: [{$minElement: 1}, {$maxElement: 1}]}
Bounds could be tighter under 2.6.
As a workaround for 2.6, consider adding the leading field to the query.
// before: {b: {$gt: 10, $lt: 20}} // workaround: {a: {{$gt: MinKey, $lt: MaxKey}, b: {$gt: 10, $lt: 20}}
--------
This is the output from the reproduction steps in current 2.4 releases:
{ "cursor" : "BtreeCursor name_1_age_1", "isMultiKey" : false, "n" : 60803, "nscannedObjects" : 60803, "nscanned" : 60827, "nscannedObjectsAllPlans" : 60803, "nscannedAllPlans" : 60827, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 207, "indexBounds" : { "name" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ], "age" : [ [ 21, 30 ] ] }, "server" : "ubuntu:27017" }
The range shown in the index bound comes from the range on the query, and is the second element in the compound key.
The same data in 2.6.0-rc0 and rc1 produces this:
{ "cursor" : "BtreeCursor name_1_age_1", "isMultiKey" : false, "n" : 60803, "nscannedObjects" : 200000, "nscanned" : 200000, "nscannedObjectsAllPlans" : 200000, "nscannedAllPlans" : 200000, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 1562, "nChunkSkips" : 0, "millis" : 646, "indexBounds" : { "name" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ], "age" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ] }, "server" : "raring:27017", "filterSet" : false }
So the range is now gone, nscanned is up to the full size of the collection as also has the execution time increased as a result of the scan.
As stated, the matching range is on the second element of the compound key, but if this is intentional why does the prior version match the same number of documents and in considerably less time?
- is duplicated by
-
SERVER-28957 Index bounds are not detected correctly
- Closed
-
SERVER-31064 query isn't index only unless projection references all indexed columns
- Closed
-
SERVER-31100 Since 2.6, full collection scan instead of index scan, on range after sort
- Closed
-
SERVER-2140 query optimizer should be able to pick index when there is no constraint on the first key
- Closed
-
SERVER-15636 Query Optimizer Index Scans when first predicate is unbounded.
- Closed
- related to
-
SERVER-15239 serverStatus metrics incorrect: "operation.idhack", "queryExecutor.scanned", "queryExecutor.scannedObjects"
- Closed