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

Count queries matching all of null, missing, and [] should be fully covered by a multikey index

    • Type: Icon: Improvement Improvement
    • Resolution: Fixed
    • Priority: Icon: Major - P3 Major - P3
    • 5.2.0
    • Affects Version/s: None
    • Component/s: None
    • Labels:
      None
    • Fully Compatible
    • QO 2021-11-01, QO 2021-11-15

      Based on discussion in SERVER-18861, it may be possible to avoid adding a fetch + filter stage for a count when the query predicate on an indexed field matches all of these values: null, missing, undefined, and [], since a multikey index can fully cover that case.

      A multikey index cannot differentiate between these values, so we would still need a FETCH stage if we are returning the value of the indexed field (but not if we're just counting how many documents actually match this predicate).

      In the case of a compound index, the query could still be covered as long as we are not returning the value indexed by the multikey index. Some examples of queries we may be able to optimize:

      1. If we have a multikey index on {a: 1}, the following queries can be fully covered by the index:

      db.coll.count({a: {$in: [null, []]}})
      db.coll.aggregate([
         {$match: {$expr: {$or: [{$eq: ["$a", null]}, {$eq: ["$a", []]}]}}},
         {$count: "c"}
      ])

       

      2. If we have a compound index {a: 1, b: 1} such that the index is multikey along the “a” path, we should be able to cover the above queries and the following:

      db.coll.count({a: {$in: [null, []]}, b: 1})
      db.coll.aggregate([
         {$match: {$expr: {$or: [{$eq: ["$a", null]}, {$eq: ["$a",[]]}]}}},
         {$project: {b: 1}},
      ])
      

       

            Assignee:
            hana.pearlman@mongodb.com Hana Pearlman
            Reporter:
            alya.berciu@mongodb.com Alya Berciu
            Votes:
            0 Vote for this issue
            Watchers:
            10 Start watching this issue

              Created:
              Updated:
              Resolved: