[SERVER-10005] $exists:false prevents use of a sparse index on a different field Created: 24/Jun/13  Updated: 11/Jul/16  Resolved: 06/Dec/13

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.4.0, 2.5.4
Fix Version/s: 2.5.5

Type: Bug Priority: Major - P3
Reporter: David Wagner Assignee: Benety Goh
Resolution: Done Votes: 1
Labels: indexing, query_triage
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Linux devpocmdb01 3.5.0-17-generic #28-Ubuntu SMP Tue Oct 9 19:31:23 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux


Issue Links:
Depends
depends on SERVER-11446 don't punt all not/nor to collscan. Closed
Related
is related to SERVER-10608 {$exists: false} will not use index Closed
Backwards Compatibility: Fully Compatible
Operating System: Linux
Steps To Reproduce:

POC0:PRIMARY> db.foo.find()

{ "_id" : ObjectId("51c886922b4c67a8571a6720"), "a" : 1 } { "_id" : ObjectId("51c8869a2b4c67a8571a6721"), "a" : 1, "b" : true }

POC0:PRIMARY> db.foo.ensureIndex(

{a:1}

,

{sparse:true}

)
POC0:PRIMARY> db.foo.find({a:1, b:{$exists:true}}).explain()
{
"cursor" : "BtreeCursor a_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 2,
"nscanned" : 2,
"nscannedObjectsAllPlans" : 2,
"nscannedAllPlans" : 2,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" :

{ "a" : [ [ 1, 1 ] ] }

,
"server" : "devpocmdb01:27017"
}
POC0:PRIMARY> db.foo.find({a:1, b:{$exists:false}}).explain()
{
"cursor" : "BasicCursor",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 2,
"nscanned" : 2,
"nscannedObjectsAllPlans" : 2,
"nscannedAllPlans" : 2,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {

},
"server" : "devpocmdb01:27017"
}

Participants:

 Description   

If you query on two fields, and the first field is sparsly indexed, querying for $exists:true on the second field will use the first field's index. However, querying for $exists:false on the second field will prevent the use of the FIRST field's index!

The problem does not occur if the first field is fully (not sparsely) indexed.



 Comments   
Comment by Githook User [ 06/Dec/13 ]

Author:

{u'username': u'benety', u'name': u'Benety Goh', u'email': u'benety@mongodb.com'}

Message: SERVER-10005 added test cases for exists operator with sparse index on different field
Branch: master
https://github.com/mongodb/mongo/commit/0f0bd1d058375234a639928f90dab26293a584eb

Comment by David Wagner [ 28/Jun/13 ]

Please explain how to use a hint in an update command from the C# driver.

Comment by Daniel Pasette (Inactive) [ 28/Jun/13 ]

This is a side effect of work done in SERVER-3918. See the last comment on that ticket.

The workaround right now is to use a $hint.

> db.foo.find({a:1, b:{$exists:true}}).hint({a:1}).explain()
{
	"cursor" : "BtreeCursor a_1",
	"isMultiKey" : false,
	"n" : 1,
	"nscannedObjects" : 2,
	"nscanned" : 2,
	"nscannedObjectsAllPlans" : 2,
	"nscannedAllPlans" : 2,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"a" : [
			[
				1,
				1
			]
		]
	},
	"server" : "crucible-2.local:27017"
}

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