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

Allow compound geo indexes to be able to provide sort

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major - P3
    • Resolution: Unresolved
    • Affects Version/s: 2.4.6
    • Fix Version/s: Backlog
    • Component/s: Geo, Querying
    • Labels:
      None
    • Case:

      Description

      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.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              backlog-query-optimization Backlog - Query Optimization
              Reporter:
              jacob.ribnik Jacob Ribnik
              Participants:
              Votes:
              13 Vote for this issue
              Watchers:
              21 Start watching this issue

                Dates

                Created:
                Updated: