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

Problem with 2dsphere index and compound index

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 2.6.0
    • Component/s: Geo, Querying
    • None
    • ALL

      Hi

      I have just tested the 2.6 and i always have the problem describe in teh Jira SERVER-9257 for my case. In this comment i use the query and index detailed in the description of the jira SERVER-9257.

      Case 1 : old index

      With the old "2d" index the query on position+date don't use the 2d index but only the index on creationDate.

      db.ntr_tracks.find({"detection.position": { $geoWithin: { $box: [ [ 0.7724536441932998, 50.61956018018893 ], [ 2.483031806726593, 51.42851043057254 ] ] } }, creationDate: { $gt: new ISODate("20120801T000000"), $lt: ISODate("20120802T000000") } }).sort({ creationDate: 1 }).limit(1000).explain();
      {
          "cursor" : "BtreeCursor creationDate",
          "isMultiKey" : false,
          "n" : 1000,
          "nscannedObjects" : 47625,
          "nscanned" : 47626,
          "nscannedObjectsAllPlans" : 52620,
          "nscannedAllPlans" : 52621,
          "scanAndOrder" : false,
          "indexOnly" : false,
          "nYields" : 411,
          "nChunkSkips" : 0,
          "millis" : 216,
          "indexBounds" : {
              "creationDate" : [ 
                  [ 
                      ISODate("2012-08-01T00:00:00.000Z"), 
                      ISODate("2012-08-02T00:00:00.000Z")
                  ]
              ]
          },
          "server" : "fmwd0012:27017",
          "filterSet" : false,
          "stats" : {
              "type" : "LIMIT",
              "works" : 47625,
              "yields" : 411,
              "unyields" : 411,
              "invalidates" : 0,
              "advanced" : 1000,
              "needTime" : 46625,
              "needFetch" : 0,
              "isEOF" : 1,
              "children" : [ 
                  {
                      "type" : "FETCH",
                      "works" : 47625,
                      "yields" : 411,
                      "unyields" : 411,
                      "invalidates" : 0,
                      "advanced" : 1000,
                      "needTime" : 46625,
                      "needFetch" : 0,
                      "isEOF" : 0,
                      "alreadyHasObj" : 0,
                      "forcedFetches" : 0,
                      "matchTested" : 1000,
                      "children" : [ 
                          {
                              "type" : "IXSCAN",
                              "works" : 47625,
                              "yields" : 411,
                              "unyields" : 411,
                              "invalidates" : 0,
                              "advanced" : 47625,
                              "needTime" : 0,
                              "needFetch" : 0,
                              "isEOF" : 0,
                              "keyPattern" : "{ creationDate: 1 }",
                              "boundsVerbose" : "field #0['creationDate']: (new Date(1343779200000), new Date(1343865600000))",
                              "isMultiKey" : 0,
                              "yieldMovedCursor" : 0,
                              "dupsTested" : 0,
                              "dupsDropped" : 0,
                              "seenInvalidated" : 0,
                              "matchTested" : 0,
                              "keysExamined" : 47626,
                              "children" : []
                          }
                      ]
                  }
              ]
          }
      }
      

      If i force using the 2d index with hint the query doesn't respond (i have +16Go of data). I think that the index isn't used.

      db.ntr_tracks.find({"detection.position": { $geoWithin: { $box: [ [ 0.7724536441932998, 50.61956018018893 ], [ 2.483031806726593, 51.42851043057254 ] ] } }, creationDate: { $gt: new ISODate("20120801T000000"), $lt: ISODate("20120802T000000") } }).sort({ creationDate: 1 }).hint('detection.position_2d_creationDate_1').limit(1000).explain()
      

      Edit: i finally have a result:

      {
          "cursor" : "GeoBrowse-box",
          "isMultiKey" : false,
          "n" : 1000,
          "nscannedObjects" : 400694,
          "nscanned" : 400694,
          "nscannedObjectsAllPlans" : 400694,
          "nscannedAllPlans" : 400694,
          "scanAndOrder" : true,
          "indexOnly" : false,
          "nYields" : 6618,
          "nChunkSkips" : 0,
          "millis" : 1903392,
          "indexBounds" : {
              "detection.position" : [ 
                  [ 
                      [ 
                          1.40625, 
                          50.625
                      ], 
                      [ 
                          2.8125, 
                          52.03125
                      ]
                  ], 
                  [ 
                      [ 
                          1.0546875, 
                          50.2734375
                      ], 
                      [ 
                          1.40625, 
                          50.625
                      ]
                  ], 
                  [ 
                      [ 
                          0.703125, 
                          50.2734375
                      ], 
                      [ 
                          1.0546875, 
                          50.625
                      ]
                  ], 
                  [ 
                      [ 
                          0.703125, 
                          50.625
                      ], 
                      [ 
                          1.40625, 
                          51.328125
                      ]
                  ], 
                  [ 
                      [ 
                          1.0546875, 
                          51.328125
                      ], 
                      [ 
                          1.40625, 
                          51.6796875
                      ]
                  ], 
                  [ 
                      [ 
                          0.703125, 
                          51.328125
                      ], 
                      [ 
                          1.0546875, 
                          51.6796875
                      ]
                  ], 
                  [ 
                      [ 
                          2.4609375, 
                          50.2734375
                      ], 
                      [ 
                          2.8125, 
                          50.625
                      ]
                  ], 
                  [ 
                      [ 
                          2.109375, 
                          50.2734375
                      ], 
                      [ 
                          2.4609375, 
                          50.625
                      ]
                  ], 
                  [ 
                      [ 
                          1.7578125, 
                          50.2734375
                      ], 
                      [ 
                          2.109375, 
                          50.625
                      ]
                  ], 
                  [ 
                      [ 
                          1.40625, 
                          50.2734375
                      ], 
                      [ 
                          1.7578125, 
                          50.625
                      ]
                  ]
              ]
          },
          "server" : "fmwd0012:27017",
          "filterSet" : false,
          "stats" : {
              "type" : "SORT",
              "works" : 401697,
              "yields" : 6618,
              "unyields" : 6618,
              "invalidates" : 0,
              "advanced" : 1000,
              "needTime" : 400696,
              "needFetch" : 0,
              "isEOF" : 1,
              "forcedFetches" : 0,
              "memUsage" : 580000,
              "memLimit" : 33554432,
              "children" : [ 
                  {
                      "type" : "KEEP_MUTATIONS",
                      "works" : 400696,
                      "yields" : 6618,
                      "unyields" : 6618,
                      "invalidates" : 0,
                      "advanced" : 1786,
                      "needTime" : 398909,
                      "needFetch" : 0,
                      "isEOF" : 1,
                      "children" : [ 
                          {
                              "type" : "FETCH",
                              "works" : 400696,
                              "yields" : 6618,
                              "unyields" : 6618,
                              "invalidates" : 0,
                              "advanced" : 1786,
                              "needTime" : 398909,
                              "needFetch" : 0,
                              "isEOF" : 1,
                              "alreadyHasObj" : 400694,
                              "forcedFetches" : 0,
                              "matchTested" : 1786,
                              "children" : [ 
                                  {
                                      "type" : "GEO_2D",
                                      "works" : 400694,
                                      "yields" : 0,
                                      "unyields" : 0,
                                      "invalidates" : 0,
                                      "advanced" : 400694,
                                      "needTime" : 0,
                                      "needFetch" : 0,
                                      "isEOF" : 1,
                                      "geometryType" : "box",
                                      "field" : "detection.position",
                                      "boundsVerbose" : [ 
                                          "(1.40625,50.625) -->> (2.8125,52.0313)", 
                                          "(1.05469,50.2734) -->> (1.40625,50.625)", 
                                          "(0.703125,50.2734) -->> (1.05469,50.625)", 
                                          "(0.703125,50.625) -->> (1.40625,51.3281)", 
                                          "(1.05469,51.3281) -->> (1.40625,51.6797)", 
                                          "(0.703125,51.3281) -->> (1.05469,51.6797)", 
                                          "(2.46094,50.2734) -->> (2.8125,50.625)", 
                                          "(2.10938,50.2734) -->> (2.46094,50.625)", 
                                          "(1.75781,50.2734) -->> (2.10938,50.625)", 
                                          "(1.40625,50.2734) -->> (1.75781,50.625)"
                                      ],
                                      "children" : []
                                  }
                              ]
                          }
                      ]
                  }
              ]
          }
      }
      
      Case 2 : new index, date first - query on date

      With a second test where i have the following index only :

      { "creationDate": 1, "detection.position": "2dsphere" }
      

      The following query

      db.ntr_tracks.find({creationDate: { $gt: new ISODate("20120801T000000"), $lt: ISODate("20120802T000000") }}).sort({ creationDate: 1 }).limit(1000).explain()
      

      Take 1168608 ms .... (but if i create an index on creationDate the query take less than 100ms).

      The explain show that we use BtreeCursor on 2dsphere is used :

      {
          "cursor" : "BtreeCursor 2dsphere",
          "isMultiKey" : false,
          "n" : 1000,
          "nscannedObjects" : 11629283,
          "nscanned" : 11629284,
          "nscannedObjectsAllPlans" : 11629283,
          "nscannedAllPlans" : 11629284,
          "scanAndOrder" : false,
          "indexOnly" : false,
          "nYields" : 433063,
          "nChunkSkips" : 0,
          "millis" : 118608,
          "indexBounds" : {
              "creationDate" : [ 
                  [ 
                      {
                          "$minElement" : 1
                      }, 
                      {
                          "$maxElement" : 1
                      }
                  ]
              ],
              "detection.position" : [ 
                  [ 
                      {
                          "$minElement" : 1
                      }, 
                      {
                          "$maxElement" : 1
                      }
                  ]
              ]
          },
          "server" : "fmwd0012:27017",
          "filterSet" : false,
          "stats" : {
              "type" : "LIMIT",
              "works" : 12062212,
              "yields" : 433063,
              "unyields" : 433063,
              "invalidates" : 0,
              "advanced" : 1000,
              "needTime" : 11628283,
              "needFetch" : 432929,
              "isEOF" : 1,
              "children" : [ 
                  {
                      "type" : "FETCH",
                      "works" : 12062212,
                      "yields" : 433063,
                      "unyields" : 433063,
                      "invalidates" : 0,
                      "advanced" : 1000,
                      "needTime" : 11628283,
                      "needFetch" : 432929,
                      "isEOF" : 0,
                      "alreadyHasObj" : 0,
                      "forcedFetches" : 0,
                      "matchTested" : 1000,
                      "children" : [ 
                          {
                              "type" : "IXSCAN",
                              "works" : 11629283,
                              "yields" : 433063,
                              "unyields" : 433063,
                              "invalidates" : 0,
                              "advanced" : 11629283,
                              "needTime" : 0,
                              "needFetch" : 0,
                              "isEOF" : 0,
                              "keyPattern" : "{ creationDate: 1, detection.position: \"2dsphere\" }",
                              "boundsVerbose" : "field #0['creationDate']: [MinKey, MaxKey], field #1['detection.position']: [MinKey, MaxKey]",
                              "isMultiKey" : 0,
                              "yieldMovedCursor" : 0,
                              "dupsTested" : 0,
                              "dupsDropped" : 0,
                              "seenInvalidated" : 0,
                              "matchTested" : 0,
                              "keysExamined" : 11629284,
                              "children" : []
                          }
                      ]
                  }
              ]
          }
      }
      
      Case 3 : new index, date first - query on date and position

      If i make query that search by position, the query is very quick (so no problem, it is better than 2.4)

      db.ntr_tracks.find({"detection.position": { $geoWithin: { $geometry: { type: "Polygon", coordinates: [ [ [ 0.7724536441932998, 50.61956018018893 ], [ 0.7724536441932998, 51.42851043057254 ], [ 2.483031806726593, 51.42851043057254 ], [ 2.483031806726593, 50.61956018018893 ], [ 0.7724536441932998, 50.61956018018893 ] ] ] } } }, creationDate: { $gt: new ISODate("20121001T000000"), $lt: ISODate("20121002T000000") } }).sort({ creationDate: 1 }).limit(1000).explain()
      
      {
          "cursor" : "BtreeCursor 2dsphere",
          "isMultiKey" : false,
          "n" : 1000,
          "nscannedObjects" : 1752,
          "nscanned" : 47634,
          "nscannedObjectsAllPlans" : 1752,
          "nscannedAllPlans" : 47634,
          "scanAndOrder" : false,
          "indexOnly" : false,
          "nYields" : 14,
          "nChunkSkips" : 0,
          "millis" : 184,
          "indexBounds" : {
              "creationDate" : [ 
                  [ 
                      ISODate("2012-08-01T00:00:00.000Z"), 
                      ISODate("2012-08-02T00:00:00.000Z")
                  ]
              ],
              "detection.position" : [ 
                  [ 
                      "2f0332302", 
                      "2f0332303"
                  ], 
                  [ 
                      "2f0332312", 
                      "2f0332312"
                  ], 
                  [ 
                      "2f03323123", 
                      "2f03323123"
                  ], 
                  [ 
                      "2f033231233", 
                      "2f033231234"
                  ], 
                  [ 
                      "2f0332313", 
                      "2f0332314"
                  ], 
                  [ 
                      "2f0332320", 
                      "2f0332321"
                  ], 
                  [ 
                      "2f0332321", 
                      "2f0332322"
                  ], 
                  [ 
                      "2f0332322", 
                      "2f0332322"
                  ], 
                  [ 
                      "2f03323220", 
                      "2f03323221"
                  ], 
                  [ 
                      "2f0332323", 
                      "2f0332324"
                  ], 
                  [ 
                      "2f0332330", 
                      "2f0332331"
                  ], 
                  [ 
                      "2f0332331", 
                      "2f0332332"
                  ]
              ]
          },
          "server" : "fmwd0012:27017",
          "filterSet" : false,
          "stats" : {
              "type" : "LIMIT",
              "works" : 1752,
              "yields" : 14,
              "unyields" : 14,
              "invalidates" : 0,
              "advanced" : 1000,
              "needTime" : 752,
              "needFetch" : 0,
              "isEOF" : 1,
              "children" : [ 
                  {
                      "type" : "FETCH",
                      "works" : 1752,
                      "yields" : 14,
                      "unyields" : 14,
                      "invalidates" : 0,
                      "advanced" : 1000,
                      "needTime" : 752,
                      "needFetch" : 0,
                      "isEOF" : 0,
                      "alreadyHasObj" : 0,
                      "forcedFetches" : 0,
                      "matchTested" : 1000,
                      "children" : [ 
                          {
                              "type" : "IXSCAN",
                              "works" : 1752,
                              "yields" : 14,
                              "unyields" : 14,
                              "invalidates" : 0,
                              "advanced" : 1752,
                              "needTime" : 0,
                              "needFetch" : 0,
                              "isEOF" : 0,
                              "keyPattern" : "{ creationDate: 1, detection.position: \"2dsphere\" }",
                              "boundsVerbose" : "field #0['creationDate']: (new Date(1343779200000), new Date(1343865600000)), field #1['detection.position']: [\"2f0332302\", \"2f0332303\"), [\"2f0332312\", \"2f0332312\"], [\"2f03323123\", \"2f03323123\"], [\"2f033231233\", \"2f033231234\"), [\"2f0332313\", \"2f0332314\"), [\"2f0332320\", \"2f0332321\"), [\"2f0332321\", \"2f0332322\"), [\"2f0332322\", \"2f0332322\"], [\"2f03323220\", \"2f03323221\"), [\"2f0332323\", \"2f0332324\"), [\"2f0332330\", \"2f0332331\"), [\"2f0332331\", \"2f0332332\")",
                              "isMultiKey" : 0,
                              "yieldMovedCursor" : 0,
                              "dupsTested" : 0,
                              "dupsDropped" : 0,
                              "seenInvalidated" : 0,
                              "matchTested" : 0,
                              "keysExamined" : 47634,
                              "children" : []
                          }
                      ]
                  }
              ]
          }
      }
      

      Thanks for the help.

            Assignee:
            david.storch@mongodb.com David Storch
            Reporter:
            phoenix741 Ulrich VANDENHEKKE
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: