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

[CQF] Use a union plan avoid fetching non-null indexed fields

    • Query Optimization
    • Fully Compatible
    • QO 2023-07-10, QO 2023-07-24

      When an index includes all the fields that a query requires:

      coll.createIndex({a: 1, b: 1});
      coll.find({a: 5}, {_id: 0, b: 1});
      

      Then we want to generate a plan that only touches the index, with no FETCH / Seek stage. But indexes don't distinguish null and missing, so we do need to check the collection for any index entry where b is null/missing. This is unsatisfying because maybe b is actually never null/missing, in which case we're doing many expensive Seeks unnecessarily.

      One solution could be to construct a plan like this:

      Union
      |   |
      |   Scan {a:1, b:1} where a == 5 and b != null
      |
      Fetch
      Scan {a:1, b:1} where a == 5 and b == null
      

      That way we only fetch the documents where b is null/missing.

      In this example we would expect the b == null predicate to produce tight index bounds, so if that predicate is always false then the Fetch Scan branch will do very little work.

            Assignee:
            chii.huang@mongodb.com Chi-I Huang
            Reporter:
            david.percy@mongodb.com David Percy
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: