-
Type: Bug
-
Resolution: Duplicate
-
Priority: Major - P3
-
None
-
Affects Version/s: 2.6.1
-
Component/s: Querying
-
None
-
Fully Compatible
-
ALL
-
RPL 0 3/13/15
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}
the bounds look correct:
"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
- 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