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

MongoDB 2.6 doesn't automatically use sparse index

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major - P3
    • Resolution: Duplicate
    • 2.6.0
    • None
    • Index Maintenance, Querying
    • 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

          Activity

            People

              Unassigned Unassigned
              info@shredderchess.com Stefan Meyer-Kahlen
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: