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

[CQF] Satisfy 'Traverse ComposeM' predicates with IndexScan + Filter

    • Type: Icon: Improvement Improvement
    • Resolution: Unresolved
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • Query Optimization
    • QO 2023-05-15, QO 2023-05-29, QO 2023-06-12, QO 2023-06-26, QO 2023-07-10, QO 2023-07-24, QO 2023-08-07, QO 2023-08-21, QO 2023-09-04, QO 2023-09-18, QO 2023-10-02, QO 2023-10-16, QO 2023-10-30

      If you have an elemMatch with a conjunction:

      db.c.find({a: {$elemMatch: {x: 1, y: 1}}})
      

      Then we could generate a plan that scans a multikey index on {a: 1}, and apply the {x: 1, y: 1} predicate to each index key. (And then do the "is array" check after fetching.)

      Bonsai does not consider this plan, because we relax the predicate to two separate Traverses:

      Filter ... Get [a] Traverse ComposeM (Get [x] ...) (Get [y] ...)
      ->
      Sargable
        Get [a] Traverse Get [x] ...  perfOnly
        Get [a] Traverse Get [y] ...  perfOnly
      

      This means we consider plans such as:

      • Full index scan, filter one predicate, fetch, filter on whole predicate
      • Index intersection of {a: 1} with itself
      • Collection scan

      (This is still an improvement over classic, which only considers a collection scan. Even with hint({a: 1}), classic does not consider evaluating either predicate on the index side to reduce fetching.)

      Maybe this would break down into sub-features:

      • an exploration rewrite that turns Traverse predicates into Unwind
      • a rewrite that matches Unwind with a multikey index

            Assignee:
            Unassigned Unassigned
            Reporter:
            david.percy@mongodb.com David Percy
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated: