Details
-
Bug
-
Status: Closed
-
Major - P3
-
Resolution: Duplicate
-
2.6.1
-
None
-
None
-
Fully Compatible
-
ALL
-
RPL 0 3/13/15
Description
I have a collection with documents, which contain a datetime field 'from' (and also 'to', but that was not used in queries) and multiple polygons.
Until Version 2.4.10 I solved that by having an array of geometry subdocuments in "location.geometry".
The index looked like that and worked very well. I had query times of ~50ms
{from:1, 'location.geometry' : '2dsphere'}
|
Version 2.6 supports MultiPolygons and it seems, that my workaround of manual "multi polygons" does not work anymore. The query times are much slower now.
I converted my polygons to use MultiPolygons in the field "geometry" and now I try to get the queries as fast as in 2.4.
The pure geo query: (on 2.6.1 the geo field name is "geometry")
{
|
"location.geometry" : {
|
"$near" : {
|
"$geometry" : {
|
"type" : "Point",
|
"coordinates" : [
|
13.4059717,
|
52.5208876
|
]
|
},
|
"$maxDistance" : 1
|
}
|
}
|
}
|
Both versions return 4599 results.
It's faster on 2.6.1. Here it takes 548 ms against 1691 ms on 2.4.10
But when I query for the date and the location, it get's problematic. The full query is: (on 2.6.1 the geo field name is "geometry")
{
|
"from" : {
|
"$gte" : ISODate("2014-06-19T00:00:00Z"),
|
"$lt" : ISODate("2014-06-20T00:00:00Z")
|
},
|
"location.geometry" : {
|
"$near" : {
|
"$geometry" : {
|
"type" : "Point",
|
"coordinates" : [
|
13.4059717,
|
52.5208876
|
]
|
},
|
"$maxDistance" : 1
|
}
|
}
|
}
|
On 2.4.10 the query takes only 49 ms:
{
|
"cursor" : "S2NearCursor",
|
"isMultiKey" : true,
|
"n" : 55,
|
"nscannedObjects" : 55,
|
"nscanned" : 6226,
|
"nscannedObjectsAllPlans" : 55,
|
"nscannedAllPlans" : 6226,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nYields" : 0,
|
"nChunkSkips" : 0,
|
"millis" : 49,
|
"indexBounds" : {
|
|
},
|
"nscanned" : 6226,
|
"matchTested" : NumberLong(3189),
|
"geoMatchTested" : NumberLong(56),
|
"numShells" : NumberLong(2),
|
"keyGeoSkip" : NumberLong(3027),
|
"returnSkip" : NumberLong(0),
|
"btreeDups" : NumberLong(10),
|
"inAnnulusTested" : NumberLong(56)
|
}
|
But on 2.6.1 the query takes ~200ms and the number of results differs a lot:
{
|
"cursor" : "BtreeCursor from_1_geometry_2dsphere",
|
"isMultiKey" : true,
|
"n" : 209,
|
"nscannedObjects" : 6805,
|
"nscanned" : 262252,
|
"nscannedObjectsAllPlans" : 6805,
|
"nscannedAllPlans" : 262252,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nYields" : 2048,
|
"nChunkSkips" : 0,
|
"millis" : 205,
|
"indexBounds" : {
|
"from" : [
|
[
|
true,
|
ISODate("2014-06-20T00:00:00Z")
|
]
|
],
|
"geometry" : [
|
[
|
{
|
"$minElement" : 1
|
},
|
{
|
"$maxElement" : 1
|
}
|
]
|
]
|
},
|
"filterSet" : false
|
}
|
The indexBounds look a bit strange. If I instead query just for a date (with an index on
{from:1}
|
"indexBounds" : {
|
"from" : [
|
[
|
ISODate("2014-06-19T00:00:00Z"),
|
ISODate("2014-06-20T00:00:00Z")
|
]
|
]
|
}
|
What is the correct way, to index and query a collection for a specific date and geo location?
Or how can I optimize the data structure?
Thanks a lot!
Fabian
Attachments
Issue Links
- duplicates
-
SERVER-13687 Results of $near query on compound multi-key 2dsphere index not sorted by distance
-
- Closed
-
-
SERVER-17279 Intersect bounds over non-geo field of compound geo index
-
- Closed
-
- is related to
-
SERVER-16042 Optimise $all/$and to select smallest subset as initial index bounds
-
- Closed
-