Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-14264

Compound index on 2dsphere and datetime very slow in 2.6.1 and different result than in 2.4.10

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Duplicate
    • Affects Version/s: 2.6.1
    • Fix Version/s: None
    • Component/s: Querying
    • Labels:
      None
    • Backwards Compatibility:
      Fully Compatible
    • Operating System:
      ALL
    • Sprint:
      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}

      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

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                9 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: