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

Mongodb compound 2dsphere index dosen't work as expected

      I have a collection called search2 with about 20000 documents like this:

      {
      "loc":

      { "type": "Polygon", "coordinates": [ [ [ 43.78526674007639, 11.14739998758569 ], [ 43.78526674007639, 11.183372851822439 ], [ 43.79443488391605, 11.183372851822439 ], [ 43.79443488391605, 11.264311796355125 ], [ 43.812771171595415, 11.264311796355125 ], [ 43.83110745927479, 11.264311796355125 ], [ 43.83110745927479, 11.273305012414314 ], [ 43.849443746954144, 11.273305012414314 ], [ 43.858611890793824, 11.273305012414314 ], [ 43.858611890793824, 11.264311796355125 ], [ 43.8769481784732, 11.264311796355125 ], [ 43.8769481784732, 11.246325364236752 ], [ 43.88611632231286, 11.246325364236752 ], [ 43.88611632231286, 11.237332148177565 ], [ 43.895284466152546, 11.237332148177565 ], [ 43.895284466152546, 11.228338932118376 ], [ 43.904452609992234, 11.228338932118376 ], [ 43.904452609992234, 11.165386419704065 ], [ 43.895284466152546, 11.165386419704065 ], [ 43.895284466152546, 11.156393203644878 ], [ 43.88611632231286, 11.156393203644878 ], [ 43.8769481784732, 11.156393203644878 ], [ 43.858611890793824, 11.156393203644878 ], [ 43.849443746954144, 11.156393203644878 ], [ 43.849443746954144, 11.165386419704065 ], [ 43.83110745927479, 11.165386419704065 ], [ 43.83110745927479, 11.156393203644878 ], [ 43.812771171595415, 11.156393203644878 ], [ 43.812771171595415, 11.14739998758569 ], [ 43.79443488391605, 11.14739998758569 ], [ 43.78526674007639, 11.14739998758569 ] ] ] }

      ,
      "docId": 1,
      "docVote": 0,
      "title": "title-1",
      "_id":

      { "$oid": "5248725d2dd5622510000001" }

      }
      I define an index with this command:

      db.search2.ensureIndex(

      {"docVote": 1,"loc":"2dsphere"}

      );
      On the collection there are only this index and the default index on "_id" field.

      When i execute the following query i expect "nscannedObjects" to be = 10 :

      db.search2.find({
      loc: {
      $geoIntersects: {
      $geometry:

      { type: "Polygon", coordinates: [ [ [43.7269795, 11.1540365], [43.8329368, 11.1540365], [43.8329368, 11.3310908], [43.7269795, 11.3310908], [43.7269795, 11.1540365] ] ] }

      }
      }
      },

      { "docVote": 1, _id: 0 }

      ).sort(

      { "docVote": 1 }

      ).limit(10).hint(

      { "docVote": 1, "loc": "2dsphere" }

      ).explain()
      But this is the result:

      {
      "cursor" : "S2Cursor",
      "isMultiKey" : true,
      "n" : 10,
      "nscannedObjects" : 44283,
      "nscanned" : 648117,
      "nscannedObjectsAllPlans" : 44283,
      "nscannedAllPlans" : 648117,
      "scanAndOrder" : true,
      "indexOnly" : false,
      "nYields" : 13,
      "nChunkSkips" : 0,
      "millis" : 12632,
      "indexBounds" : {

      },
      "nscanned" : 648117,
      "matchTested" : NumberLong(46642),
      "geoTested" : NumberLong(46642),
      "cellsInCover" : NumberLong(8),
      "server" : "*********"
      }
      If i remove sort from the query i obtain this:

      {
      "cursor" : "S2Cursor",
      "isMultiKey" : true,
      "n" : 10,
      "nscannedObjects" : 10,
      "nscanned" : 25,
      "nscannedObjectsAllPlans" : 10,
      "nscannedAllPlans" : 25,
      "scanAndOrder" : false,
      "indexOnly" : false,
      "nYields" : 0,
      "nChunkSkips" : 0,
      "millis" : 3,
      "indexBounds" : {

      },
      "nscanned" : 25,
      "matchTested" : NumberLong(10),
      "geoTested" : NumberLong(10),
      "cellsInCover" : NumberLong(8),
      "server" : "******"
      }
      So, why index is not used to sort results? From this documentation: http://docs.mongodb.org/manual/applications/geospatial-indexes/ http://docs.mongodb.org/manual/tutorial/sort-results-with-indexes/ I understand that MongoDb support "A compound index with scalar index fields (i.e. ascending or descending) as a prefix or suffix of the 2dsphere index field" and that "If the sort document is a subset of a compound index and starts from the beginning of the index, MongoDB can use the index to both retrieve and sort the query results."

      What am I missing?

      Thanks in advance

            Assignee:
            Unassigned Unassigned
            Reporter:
            MarcoBiagi Marco Biagi
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: