Use index for $eq to $$REMOVE expression

    • Type: Improvement
    • Resolution: Unresolved
    • Priority: Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • None
    • Query Optimization
    • None
    • 3
    • TBD
    • None
    • None
    • None
    • None
    • None
    • None

      This ticket is spun off from related ticket SERVER-105057. Consider the following toy example:

      db.c.drop();
      assert.commandWorked(db.c.insert([
          {_id: 1, a: 1},
          {_id: 2, a: null},
          {_id: 3, a: undefined},
          {_id: 4},
      ]));
      
      assert.commandWorked(db.c.createIndex({a: 1}));
      
      let explain = db.c.find({$expr: {$eq: ["$a", "$$REMOVE"]}}).explain("executionStats");
      printjson(explain);
      let results = db.c.find({$expr: {$eq: ["$a", "$$REMOVE"]}}).toArray();
      printjson(results);
      

      If you run this, you will see from the explain that the system uses a COLLSCAN plan. Even without changing the index format to distinguish between null and missing values (SERVER-12869), it is possible to use an index scan for this query. The index scan would find any null, missing, and undefined values. Then, a residual filter must be applied to discard the literal nulls. (Currently the query matches undefined in addition to missing, which is arguably incorrect. Since the undefined type has long been deprecated and is little used, I'm not sure that we should worry about this detail.)

      A predicate like this is typically not written directly by the application, since it is more appropriate to use something like {$exists:false} or {$eq:null}. However, this is still an important optimization since it can matter when this query occurs on the inner side of a $lookup. See related ticket SERVER-105057 for a detailed description of how this can happen, as well as a draft fix implemented by our very own bernard.gorman@mongodb.com.

            Assignee:
            Unassigned
            Reporter:
            David Storch
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated: