Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-15572

Explain reports same plan with and without hint, but without is 6x slower

    XMLWordPrintableJSON

Details

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major - P3 Major - P3
    • None
    • 2.6.3
    • Querying
    • None
    • ALL

    Description

      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
      }

      Attachments

        Activity

          People

            david.storch@mongodb.com David Storch
            doug@ebackpack.com Doug Mayer
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: