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

Allow compound geo indexes to be able to provide sort

    XMLWordPrintable

Details

    • Improvement
    • Status: Backlog
    • Major - P3
    • Resolution: Unresolved
    • 2.4.6
    • None
    • Geo, Querying
    • None

    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

              backlog-query-optimization Backlog - Query Optimization
              jacob.ribnik@mongodb.com Jacob Ribnik
              Votes:
              15 Vote for this issue
              Watchers:
              25 Start watching this issue

              Dates

                Created:
                Updated: