-
Type:
Bug
-
Resolution: Done
-
Priority:
Major - P3
-
None
-
Affects Version/s: 2.6.3
-
Component/s: Querying
-
None
-
ALL
-
None
-
None
-
None
-
None
-
None
-
None
-
None
The following explain plans appear to be exactly the same (save for "all plans" counts, which would make sense). However, the first does not use a hint, and the second does use a hint. All parameters reported by explain are the same, though. The one without a hint takes about 650ms, the one with a hint takes about 110ms.
main:SECONDARY> db.users.find({
... account_id: ObjectId('513778770cf200dd51234567'),
... deleted_at: { $exists: false },
... _type: { $in: [ "Staff" ] }
... }).sort({ user_name: 1 }).limit(20).explain()
{
"cursor" : "BtreeCursor account_id_1_user_name_1",
"isMultiKey" : false,
"n" : 20,
"nscannedObjects" : 19863,
"nscanned" : 19864,
"nscannedObjectsAllPlans" : 149646,
"nscannedAllPlans" : 158905,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 1241,
"nChunkSkips" : 0,
"millis" : 647,
"indexBounds" : {
"account_id" : [
[
ObjectId("513778770cf200dd51234567"),
ObjectId("513778770cf200dd51234567")
]
],
"user_name" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
},
"server" : "db02:27017",
"filterSet" : false
}
main:SECONDARY> db.users.find({
... account_id: ObjectId('513778770cf200dd51234567'),
... deleted_at: { $exists: false },
... _type: { $in: [ "Staff" ] }
... }).sort({ user_name: 1 }).limit(20).hint({account_id:1, user_name: 1}).explain()
{
"cursor" : "BtreeCursor account_id_1_user_name_1",
"isMultiKey" : false,
"n" : 20,
"nscannedObjects" : 19863,
"nscanned" : 19864,
"nscannedObjectsAllPlans" : 19863,
"nscannedAllPlans" : 19864,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 155,
"nChunkSkips" : 0,
"millis" : 111,
"indexBounds" : {
"account_id" : [
[
ObjectId("513778770cf200dd51234567"),
ObjectId("513778770cf200dd51234567")
]
],
"user_name" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
},
"server" : "db02:27017",
"filterSet" : false
}