[SERVER-10971] Mongodb compound 2dsphere index dosen't work as expected Created: 30/Sep/13  Updated: 10/Dec/14  Resolved: 03/Oct/13

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

Type: Question Priority: Major - P3
Reporter: Marco Biagi Assignee: Unassigned
Resolution: Duplicate Votes: 1
Labels: 2dsphere, geo, geoIntersects, index
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-10801 Allow compound geo indexes to be able... Backlog
Participants:

 Description   

I have a collection called search2 with about 20000 documents like this:

{
"loc":

{ "type": "Polygon", "coordinates": [ [ [ 43.78526674007639, 11.14739998758569 ], [ 43.78526674007639, 11.183372851822439 ], [ 43.79443488391605, 11.183372851822439 ], [ 43.79443488391605, 11.264311796355125 ], [ 43.812771171595415, 11.264311796355125 ], [ 43.83110745927479, 11.264311796355125 ], [ 43.83110745927479, 11.273305012414314 ], [ 43.849443746954144, 11.273305012414314 ], [ 43.858611890793824, 11.273305012414314 ], [ 43.858611890793824, 11.264311796355125 ], [ 43.8769481784732, 11.264311796355125 ], [ 43.8769481784732, 11.246325364236752 ], [ 43.88611632231286, 11.246325364236752 ], [ 43.88611632231286, 11.237332148177565 ], [ 43.895284466152546, 11.237332148177565 ], [ 43.895284466152546, 11.228338932118376 ], [ 43.904452609992234, 11.228338932118376 ], [ 43.904452609992234, 11.165386419704065 ], [ 43.895284466152546, 11.165386419704065 ], [ 43.895284466152546, 11.156393203644878 ], [ 43.88611632231286, 11.156393203644878 ], [ 43.8769481784732, 11.156393203644878 ], [ 43.858611890793824, 11.156393203644878 ], [ 43.849443746954144, 11.156393203644878 ], [ 43.849443746954144, 11.165386419704065 ], [ 43.83110745927479, 11.165386419704065 ], [ 43.83110745927479, 11.156393203644878 ], [ 43.812771171595415, 11.156393203644878 ], [ 43.812771171595415, 11.14739998758569 ], [ 43.79443488391605, 11.14739998758569 ], [ 43.78526674007639, 11.14739998758569 ] ] ] }

,
"docId": 1,
"docVote": 0,
"title": "title-1",
"_id":

{ "$oid": "5248725d2dd5622510000001" }

}
I define an index with this command:

db.search2.ensureIndex(

{"docVote": 1,"loc":"2dsphere"}

);
On the collection there are only this index and the default index on "_id" field.

When i execute the following query i expect "nscannedObjects" to be = 10 :

db.search2.find({
loc: {
$geoIntersects: {
$geometry:

{ type: "Polygon", coordinates: [ [ [43.7269795, 11.1540365], [43.8329368, 11.1540365], [43.8329368, 11.3310908], [43.7269795, 11.3310908], [43.7269795, 11.1540365] ] ] }

}
}
},

{ "docVote": 1, _id: 0 }

).sort(

{ "docVote": 1 }

).limit(10).hint(

{ "docVote": 1, "loc": "2dsphere" }

).explain()
But this is the result:

{
"cursor" : "S2Cursor",
"isMultiKey" : true,
"n" : 10,
"nscannedObjects" : 44283,
"nscanned" : 648117,
"nscannedObjectsAllPlans" : 44283,
"nscannedAllPlans" : 648117,
"scanAndOrder" : true,
"indexOnly" : false,
"nYields" : 13,
"nChunkSkips" : 0,
"millis" : 12632,
"indexBounds" : {

},
"nscanned" : 648117,
"matchTested" : NumberLong(46642),
"geoTested" : NumberLong(46642),
"cellsInCover" : NumberLong(8),
"server" : "*********"
}
If i remove sort from the query i obtain this:

{
"cursor" : "S2Cursor",
"isMultiKey" : true,
"n" : 10,
"nscannedObjects" : 10,
"nscanned" : 25,
"nscannedObjectsAllPlans" : 10,
"nscannedAllPlans" : 25,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 3,
"indexBounds" : {

},
"nscanned" : 25,
"matchTested" : NumberLong(10),
"geoTested" : NumberLong(10),
"cellsInCover" : NumberLong(8),
"server" : "******"
}
So, why index is not used to sort results? From this documentation: http://docs.mongodb.org/manual/applications/geospatial-indexes/ http://docs.mongodb.org/manual/tutorial/sort-results-with-indexes/ I understand that MongoDb support "A compound index with scalar index fields (i.e. ascending or descending) as a prefix or suffix of the 2dsphere index field" and that "If the sort document is a subset of a compound index and starts from the beginning of the index, MongoDB can use the index to both retrieve and sort the query results."

What am I missing?

Thanks in advance



 Comments   
Comment by Daniel Pasette (Inactive) [ 03/Oct/13 ]

duplicate of SERVER-10801

Comment by Marco Biagi [ 02/Oct/13 ]

I think that is a bug, not a question. It can be related to SERVER-10801. In issue 10801 sort field is a timestamp. In my case is a float/integer.

Comment by Marco Biagi [ 02/Oct/13 ]

What did you mean with " debugging with submitter"? I must do something?

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