$lookup on an indexed view fails to use index

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

      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

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

              Created:
              Updated: