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

Allow compound geo indexes to be able to provide sort

    • Type: Icon: Improvement Improvement
    • Resolution: Unresolved
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 2.4.6
    • Component/s: Geo, Querying
    • Query Integration

      I have a user with a collection of documents with points and timestamps. He performs a geo query on the points and a sort on the timestamps.

      I thought it would be useful to build a compound index with timestamp first and position second (timestamp_-1_position_2dsphere) and use that in the sort. However, the explain shows that the sort does not recognize that the timestamp is first in the index and instead goes straight to an S2Cursor.

      I recreated the user environment with the following javascript:

      for(var i = 0; i < 100000; i++){
          xrand = Math.random()*179+1;
          yrand = Math.random()*89+1;
          x = xrand.toFixed(2);
          y = yrand.toFixed(2);
          t = new Date();
          doc = {
              position: {
                  type: "Point",
                  coordinates: [parseFloat(x), parseFloat(y)]
              },
              timestamp: t
          };
      
          db.randomcoordinates.insert(doc);
      }
      
      db.randomcoordinates.ensureIndex({timestamp:-1,position:'2dsphere'})
      

      and executed the following query and explain:

      localhost(mongod-2.4.6) test> db.randomcoordinates.find({position: {$geoWithin: {$geometry: {type: "Polygon", coordinates: [[[1, 1], [1, 90], [180, 90], [180, 1], [1, 1]]]}}}}).sort({timestamp: -1}).limit(5).hint("timestamp_-1_position_2dsphere").explain()
      {
        "cursor": "S2Cursor",
        "isMultiKey": true,
        "n": 5,
        "nscannedObjects": 47285,
        "nscanned": 114006,
        "nscannedObjectsAllPlans": 47285,
        "nscannedAllPlans": 114006,
        "scanAndOrder": true,
        "indexOnly": false,
        "nYields": 2,
        "nChunkSkips": 0,
        "millis": 1272,
        "indexBounds": {
      
        },
        "nscanned": 114006,
        "matchTested": NumberLong("66721"),
        "geoTested": NumberLong("66721"),
        "cellsInCover": NumberLong("14"),
        "server": "localhost:27017"
      }
      

      As you can see an S2Cursor was used, scanAndOrder is true, and it does not appear to use the timestamp index. It is perfectly happy however to use timestamp as a single index.

            Assignee:
            backlog-query-integration [DO NOT USE] Backlog - Query Integration
            Reporter:
            jacob.ribnik@mongodb.com Jacob Ribnik
            Votes:
            15 Vote for this issue
            Watchers:
            26 Start watching this issue

              Created:
              Updated: