$near query uses unbounded memory

XMLWordPrintableJSON

    • Query Execution
    • ALL
    • Query 15 (06/03/16), QE 2022-10-17, QE 2022-10-31, QE 2022-11-14, QE 2022-11-28, QE 2022-12-12, QE 2022-12-26, QE 2023-01-09
    • None
    • 3
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      With following index:

          db.c.createIndex({p: "2dsphere", s: 1})
      

      and 10M of the following documents:

          x = ''
          for (i=0; i<1000; i++)
              x += 'x'
          doc = {p: [0,0], s: 0, x: x}
      

      run following query, which finds 0 documents:

          db.c.find({
              p: {
                  $near: {
                      $geometry: {
                          coordinates: [1, 0],
                          type: "Point"
                      },
                  },
              },
              s: {$ne: 0}
          }).itcount()
      }
      

      Result as follows:

      • peak memory usage is about 13 GB in excess of WT cache
      • query has scanned 20M documents to return 0, in spite of index. Changing query to e.g. s: 1 instead of s: {$ne: 0} eliminates document scan and excess memory usage.
      • less highly skewed point distribution reduces problem presumably because skewed distribution requires accumulating all documents in memory for sort

        1. near.png
          near.png
          113 kB
        2. server22224_explain.log
          57 kB
        3. heap-profile-calltree.png
          heap-profile-calltree.png
          357 kB
        4. heap-profile-timeseries.png
          heap-profile-timeseries.png
          132 kB

              Assignee:
              [DO NOT USE] Backlog - Query Execution
              Reporter:
              Bruce Lucas (Inactive)
              Votes:
              3 Vote for this issue
              Watchers:
              39 Start watching this issue

                Created:
                Updated: