Details
Description
I've a database with over 3 million documents.
When running a find() on a GeoJSON Point field with a 2DSphere index the query is very slow (12,000 ms), while running the same find() using a 2D index is very fast (under 1 ms).
Steps to reproduce:
1. Create a collection named "objects" with more than 1 million documents.
2. Use the following simple schema:
{
|
"id": 1,
|
"location": {
|
"type": "Point",
|
"coordinates": [-118.491356, 34.02444]
|
}
|
}
|
3. Create a 2DSphere index in the location field:
objects.ensureIndex(
);
4. Create a 2D index in the location.coordinates field:objects.ensureIndex(
{"location.coordinates": "2d"});
5. Run this 2DSphere search query on the mongo client:
db.objects.find({
|
"location": {
|
"$near": {
|
"$maxDistance": 10000,
|
"$geometry": {
|
"type": "Point",
|
"coordinates": [-118.491356, 34.02444]
|
}
|
}
|
}
|
}).limit(1000).explain();
|
You'll notice a high number of scanned objects and a very high response
time, even though the S2NearCursor is being used. Here's the output I get from explain:
... }).limit(1000).explain();
|
{
|
"cursor" : "S2NearCursor",
|
"isMultiKey" : true,
|
"n" : 1000,
|
"nscannedObjects" : 1000,
|
"nscanned" : 1842767,
|
"nscannedObjectsAllPlans" : 1000,
|
"nscannedAllPlans" : 1842767,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nYields" : 7,
|
"nChunkSkips" : 0,
|
"millis" : 12565,
|
"indexBounds" : {
|
|
},
|
"nscanned" : 1842767,
|
"matchTested" : NumberLong(1695623),
|
"geoMatchTested" : NumberLong(1695623),
|
"numShells" : NumberLong(1),
|
"keyGeoSkip" : NumberLong(147144),
|
"returnSkip" : NumberLong(3577),
|
"btreeDups" : NumberLong(0),
|
"inAnnulusTested" : NumberLong(1695623),
|
"server" : "mongoserver:27017"
|
}
|
6. Run this 2D search query on the mongo client:
db.objects.find({
|
"location.coordinates": {
|
"$near": [-118.491356, 34.02444],
|
"$maxDistance": 10000
|
}
|
}).limit(1000).explain();
|
Now you'll get a very fast response time. Here's the output I get from explain:
{
|
"cursor" : "GeoSearchCursor",
|
"isMultiKey" : false,
|
"n" : 1000,
|
"nscannedObjects" : 1000,
|
"nscanned" : 1000,
|
"nscannedObjectsAllPlans" : 1000,
|
"nscannedAllPlans" : 1000,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nYields" : 0,
|
"nChunkSkips" : 0,
|
"millis" : 0,
|
"indexBounds" : {
|
|
},
|
"server" : "mongoserver:27017"
|
}
|
7. You can use geoNear instead of near when searching on the 2DSphere-indexed field and you'll get the same huge response time.
Attachments
Issue Links
- duplicates
-
SERVER-19039 geoNear scans the same index cells multiple times, slowing down queries with many search intervals
-
- Closed
-