[SERVER-13549] MongoDB 2.6 doesn't automatically use sparse index Created: 10/Apr/14  Updated: 10/Dec/14  Resolved: 10/Apr/14

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Querying
Affects Version/s: 2.6.0
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Stefan Meyer-Kahlen Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-8067 incorrect use of sparse index to sort... Closed
Operating System: ALL
Participants:

 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
}



 Comments   
Comment by Stefan Meyer-Kahlen [ 10/Apr/14 ]

Ah ok, so this is a feature. Thanks for explaining.

Comment by J Rassi [ 10/Apr/14 ]

This is a behavior change introduced in MongoDB 2.6. It fixes the issue that results from a query used to differ depending on whether or not a sparse index was chosen for the query. Given a query predicate, if it is determined that choice of a sparse index could miss results then the sparse index is considered ineligible (for example, choosing a sparse index index {a:1} to satisfy a query predicate {} would "incorrectly" omit the document {a:null} from the results). See the following excerpt from the sparse index documentation, <http://docs.mongodb.org/manual/core/index-sparse/#index-type-sparse>:

Changed in version 2.6: If a sparse index results in an incomplete result set for queries and sort operations, MongoDB will not use that index unless a hint() explicitly specifies the index. For example, the query { x: { $exists: false } } will not use a sparse index on the x field unless explicitly hinted.

Generated at Thu Feb 08 03:32:05 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.