[SERVER-17024] 2dsphere index doesn't work when you put it as third field in ensureIndex() Created: 23/Jan/15  Updated: 09/Feb/15  Resolved: 09/Feb/15

Status: Closed
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: 2.6.7
Fix Version/s: None

Type: Bug Priority: Minor - P4
Reporter: airs0urce Assignee: J Rassi
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
Operating System: ALL
Steps To Reproduce:

db.users.ensureIndex({'l':'2dsphere', sex:1, dob:1})
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 2,
	"numIndexesAfter" : 3,
	"ok" : 1
}
 
db.users.find({
	l: { $nearSphere: { $geometry: { type: "Point", coordinates: [ 38.911417, 47.257067 ] }, $maxDistance: 600000 } } ,  
	sex: 2, 
	dob: { $gte: new Date(-313657981006), $lte: new Date(1390495618998) }} 
).explain()
{
	"cursor" : "S2NearCursor",
	"isMultiKey" : false,
	"n" : 105,
	"nscannedObjects" : 364,
	"nscanned" : 364,
	"nscannedObjectsAllPlans" : 364,
	"nscannedAllPlans" : 364,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 2,
	"indexBounds" : {
		
	},
	"server" : "airs0urce.local:27017",
	"filterSet" : false
}

Query time - 2 milliseconds. That's good.
Then I drop that index and create new one:

db.users.ensureIndex({sex:1, dob:1, 'l':'2dsphere'})
db.users.find({	
	sex: 2,
	dob: { $gte: new Date(-313657981006), $lte: new Date(1390495618998) },
	l: { $nearSphere: { $geometry: { type: "Point", coordinates: [ 38.911417, 47.257067 ] }, $maxDistance: 600000 } } }
).explain()
 
{
	"cursor" : "S2NearCursor",
	"isMultiKey" : false,
	"n" : 105,
	"nscannedObjects" : 383926,
	"nscanned" : 383926,
	"nscannedObjectsAllPlans" : 383926,
	"nscannedAllPlans" : 383926,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 1410,
	"indexBounds" : {
		
	},
	"server" : "airs0urce.local:27017",
	"filterSet" : false
}

Query time - 1410 milliseconds. Also nscanned=383926 (I have 49640 documents in users collection)

Participants:

 Description   

I have "users" collection with these fields:
dob - ISODate
sex - Int
l - geoJSON

I want index for all the fields.

http://docs.mongodb.org/manual/tutorial/build-a-2dsphere-index/
From docs I see, that: "Unlike the 2d index, a compound 2dsphere index does not require the location field to be the first field indexed."

So, it means I can create indexes different ways

Works in queries:

 db.users.ensureIndex({'l':'2dsphere', sex:1, dob:1})

Works in queries:

db.users.ensureIndex({sex:1, 'l':'2dsphere', dob:1})

In this case index will not be used in queries

db.users.ensureIndex({sex:1, dob:1, 'l':'2dsphere'}) 

Not sure if this is bug. But if not then it makes sense to change docs and inform
developers that location field must be first or second field in index.



 Comments   
Comment by J Rassi [ 09/Feb/15 ]

Hi,

This is expected behavior. The order of fields in an index definition dictates exactly what kinds of queries can be performed efficiently using that index. For your example query, two of the three indexes generate efficient plans, and one of the three indexes does not generate an efficient plan.

To give an example, suppose I wanted to create an index to efficiently support the query {firstName: "John", lastName: {$gte: "N"}} over a collection of user documents. The index {firstName: 1, lastName: 1} can efficiently answer this query, as the query will scan at most the set of documents where firstName is "John" (presumably a small set). The index {lastName: 1, firstName: 1} won't efficiently answer this query, as the query will scan at most the set of documents where lastName starts with one of the letters N-Z (presumably a large set).

When creating a compound index to support a particular query, the fields corresponding to the most selective predicates should come first in the index definition. My suspicion as to why you're seeing poor query performance is that the predicate on "l" is your most selective of your three predicates.

See the following blog post for more information on optimizing MongoDB queries: <http://emptysqua.re/blog/optimizing-mongodb-compound-indexes/>. Please post on the mongodb-user mailing list for future questions about how to optimize queries: <https://groups.google.com/forum/#!forum/mongodb-user>.

~ Jason Rassi

Comment by airs0urce [ 04/Feb/15 ]

I also could share users collection as now there is only test data (500,000 users now)

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