MongoDB geospatial query with sort - performance issues

Question

I have query (which is very slow ~2,5s):

db.markers.find({ latlng: { '$within': { '$box': [ [ -16, -140 ], [ 75, 140 ] ] } } }).sort({_id: -1}).limit(1000)

When I run explain for this query I get

{
   "cursor" : "GeoBrowse-box",
   "isMultiKey" : false,
   "n" : 1000,
   "nscannedObjects" : 242331,
   "nscanned" : 242331,
   "nscannedObjectsAllPlans" : 242331,
   "nscannedAllPlans" : 242331,
   "scanAndOrder" : true,
   "indexOnly" : false,
   "nYields" : 1383,
    "nChunkSkips" : 0,
    "millis" : 2351,
    "indexBounds" : {
        "latlng" : [ ]
    },
    "lookedAt" : NumberLong(262221),
    "matchesPerfd" : NumberLong(242331),
    "objectsLoaded" : NumberLong(242331),
    "pointsLoaded" : NumberLong(0),
    "pointsSavedForYield" : NumberLong(0),
    "pointsChangedOnYield" : NumberLong(0),
    "pointsRemovedOnYield" : NumberLong(0),
    "server" : "xx:27017"
}

When I remove sort({_id: -1}) explain gives me (fast query 5 millis):

{
    "cursor" : "GeoBrowse-box",
    "isMultiKey" : false,
    "n" : 1000,
    "nscannedObjects" : 1000,
    "nscanned" : 1000,
    "nscannedObjectsAllPlans" : 1000,
    "nscannedAllPlans" : 1000,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 5,
    "indexBounds" : {
        "latlng" : [ ]
    },
    "lookedAt" : NumberLong(1000),
    "matchesPerfd" : NumberLong(1000),
    "objectsLoaded" : NumberLong(1000),
    "pointsLoaded" : NumberLong(0),
    "pointsSavedForYield" : NumberLong(0),
    "pointsChangedOnYield" : NumberLong(0),
    "pointsRemovedOnYield" : NumberLong(0),
        "server" : "xx:27017"
}

I have 2d index on latlng, desc index on _id and compound indexes.

db.markers.ensureIndex({latlng: '2d', _id:-1})
db.markers.ensureIndex({ latlng: '2d' })
db.markers.ensureIndex({ _id: -1 })

What I want to achieve is to get markers from a particular area sorted from newest.

Any ideas or suggestions how to do that in much less time than 2.5 seconds?

You should take care of the order of your coordinates. The order is longitude then latitude. — user15609
– user15609, Commented Nov 25, 2012 at 19:45

Adam C · Accepted Answer · 2012-10-17 23:04:32Z

3

A query in MongoDB can only use one index at a time, so it's a case of one or the other - it can't use the 2d index first, then do a sort on the _id index. In order to use indexes for both the selection and the sort, you would need a compound index like this:

db.markers.ensureIndex( { latlng : "2d" , _id : 1 } );

Try that, or similar and see how it impacts the results bearing in mind that once you define it, you can remove the original 2d index to save space and that this new index will have to be loaded into memory to be efficient.

Update: as mentioned in the summary, the above did not improve things, and the resulting query results in a scanAndOrder result. This also happens with range based queries, as explained in this excellent blog post:

http://blog.mongolab.com/2012/06/cardinal-ins/

As explained in that post, the usual resolution for range based query performance is to switch the order of the indexes. However this is currently not possible with geo indexes. There is a Jira issue already open for this here for voting and tracking purposes:

https://jira.mongodb.org/browse/SERVER-4247

edited Oct 17, 2012 at 23:04

answered Oct 16, 2012 at 16:36

Adam C

9,2453 gold badges28 silver badges45 bronze badges

I tried this, with the same result (millis: 2430, nscanned: 242331, scanAndOrder: true)

dancio
– dancio

2012-10-17 06:23:06 +00:00
Commented Oct 17, 2012 at 6:23
I was afraid of that. The normal way around this problem (scanAndOrder happening on a range based query, not using the index for sorting) is to have the sort field as the first element of the index to ensure that the sort is effective. However, currently with geo indexing that is not possible (jira.mongodb.org/browse/SERVER-4247 is the issue that needs to be tracked on this). Is there any chance you could try the sort on the application side?

Adam C
– Adam C

2012-10-17 09:54:56 +00:00
Commented Oct 17, 2012 at 9:54
Thanks for ticket info. In edge case I would load 264k objects into memory and sort them in each request, so this is not fast solution ;)

dancio
– dancio

2012-10-17 11:39:17 +00:00
Commented Oct 17, 2012 at 11:39
Wait, so the geo query is not selective at all? Or is 264k the subset of results that the geo query would return without the limit? I was thinking that if the result set from the geo query limited the result set, you could save the sort until later.

Adam C
– Adam C

2012-10-17 16:09:52 +00:00
Commented Oct 17, 2012 at 16:09
Geo query is selective but if you zoom out on map to see whole world, then I would need to load all 264k markers just to sort them and display latest 1000. Even if the query is very selective at very high zoom it still takes a long time (1155 ms). This is very important because the view of the world is the starting point of my application.

dancio
– dancio

2012-10-18 11:32:02 +00:00
Commented Oct 18, 2012 at 11:32

| Show 1 more comment

Stack Exchange Network

MongoDB geospatial query with sort - performance issues

1 Answer 1

Your Answer

Hot Network Questions

MongoDB geospatial query with sort - performance issues

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions