$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 Execution
    • v8.2, v8.1, v8.0
    • 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:
              David Storch
              Reporter:
              Alberto Massari
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

                Created:
                Updated: