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

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:
            thomas.rueckstiess@mongodb.com Thomas Rueckstiess
            Reporter:
            MarcoBiagi Marco Biagi
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved: