Details
-
Bug
-
Status: Closed
-
Major - P3
-
Resolution: Duplicate
-
2.6.0
-
None
-
None
-
ALL
Description
I need to find the biggest value for the field "tourney" in the collection "games". I have added an appropriate index which was automatically used by all MongoDB versions prior to 2.6. In 2.6. MongoDB doesn't use the index but does a full table scan instead. Given the right hint the query uses the index and works as expected. Here are more details.
I have a sparse index in the "games" collection:
{
|
"v" : 1,
|
"key" : {
|
"tourney" : 1,
|
"tourneyRd" : 1
|
},
|
"ns" : "server.games",
|
"name" : "tourney_1_tourneyRd_1",
|
"sparse" : true
|
}
|
This query does a full table scan in MongoDB 2.6.0:
db.games.find({},{tourney:1}).sort({tourney:-1}).limit(1).explain()
|
{
|
"clauses" : [
|
{
|
"cursor" : "BasicCursor",
|
"isMultiKey" : false,
|
"n" : 1,
|
"nscannedObjects" : 12619952,
|
"nscanned" : 12619952,
|
"scanAndOrder" : true,
|
"indexOnly" : false,
|
"nChunkSkips" : 0
|
},
|
{
|
"cursor" : "BasicCursor",
|
"isMultiKey" : false,
|
"n" : 0,
|
"nscannedObjects" : 0,
|
"nscanned" : 0,
|
"scanAndOrder" : true,
|
"indexOnly" : false,
|
"nChunkSkips" : 0
|
}
|
],
|
"cursor" : "QueryOptimizerCursor",
|
"n" : 1,
|
"nscannedObjects" : 12619952,
|
"nscanned" : 12619952,
|
"nscannedObjectsAllPlans" : 12619952,
|
"nscannedAllPlans" : 12619952,
|
"scanAndOrder" : false,
|
"nYields" : 98593,
|
"nChunkSkips" : 0,
|
"millis" : 77564,
|
"server" : "test:27017",
|
"filterSet" : false
|
}
|
Adding a hint does the correct query:
db.games.find({},{tourney:1}).sort({tourney:-1}).limit(1).hint({tourney:1,tourneyRd:1}).explain()
|
{
|
"cursor" : "BtreeCursor tourney_1_tourneyRd_1 reverse",
|
"isMultiKey" : false,
|
"n" : 1,
|
"nscannedObjects" : 1,
|
"nscanned" : 2,
|
"nscannedObjectsAllPlans" : 1,
|
"nscannedAllPlans" : 2,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nYields" : 0,
|
"nChunkSkips" : 0,
|
"millis" : 0,
|
"indexBounds" : {
|
"tourney" : [
|
[
|
{
|
"$maxElement" : 1
|
},
|
{
|
"$minElement" : 1
|
}
|
]
|
],
|
"tourneyRd" : [
|
[
|
{
|
"$maxElement" : 1
|
},
|
{
|
"$minElement" : 1
|
}
|
]
|
]
|
},
|
"server" : "test:27017",
|
"filterSet" : false
|
}
|
A work-around for me right now is to avoid the empty query with:
db.games.find({tourney:{$gt:0}},{tourney:1}).sort({tourney:-1}).limit(1).explain()
|
{
|
"cursor" : "BtreeCursor tourney_1_tourneyRd_1 reverse",
|
"isMultiKey" : false,
|
"n" : 1,
|
"nscannedObjects" : 1,
|
"nscanned" : 2,
|
"nscannedObjectsAllPlans" : 1,
|
"nscannedAllPlans" : 2,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nYields" : 0,
|
"nChunkSkips" : 0,
|
"millis" : 0,
|
"indexBounds" : {
|
"tourney" : [
|
[
|
Infinity,
|
0
|
]
|
],
|
"tourneyRd" : [
|
[
|
{
|
"$maxElement" : 1
|
},
|
{
|
"$minElement" : 1
|
}
|
]
|
]
|
},
|
"server" : "test:27017",
|
"filterSet" : false
|
}
|
Attachments
Issue Links
- duplicates
-
SERVER-8067 incorrect use of sparse index to sort query results
-
- Closed
-