$sequentialCache optimization can interfere with $match pushdown in a $lookup sub-pipeline

XMLWordPrintableJSON

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

      If an aggregate includes a $lookup on a view using a pipeline with a $match that can be partially supported by an index, and the pipeline uses constants, it is executed in a few milliseconds and using a single index scan on the base collection of the view.
      If the pipeline uses locally defined variables, still assigned to the same constants, the generated plan does a full collection scan, ignoring the predicate

      See attached repro case

        1. lookup_on_view.js
          4 kB

              Assignee:
              Unassigned
              Reporter:
              Alberto Massari
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

                Created:
                Updated: