Wildcard indices scan MinKey even when not needed

XMLWordPrintableJSON

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

      db.a.drop()
      db.a.createIndex({a:1, "b.$**":1, c: 1})
      db.a.insertOne({})
      db.a.explain().find({a: 3, 'b.x': {$ne: null}, c: 5})

      This results in a plan with an index scan with an IndexBounds with two $_path keys. It seems mongosh only shows the last occurance, while the legacy mongo shell shows the first occurance. But you can set eg. a breakpoint at the plan ranker and evaluate statusWithMultiPlanSolns.getValue()[0]->toString() to see both the keys. This gives (ignore the strikethrough formatting):

      (std::string) "FETCH\n-{}ns = test.a\n{}filter:\n        $and || Selected Index #1 pos 0 combine 1\n            $not\n                b.x $eq null\n            c $eq 5\n{}nodeId = 2\n{}fetched = 1\n{}sortedByDiskLoc = 0\n{}providedSorts = {baseSortPattern: {}, ignoredFields: [a]}\n{}Child:\n{}--{}IXSCAN\n{}-----{}ns = test.a\n{}-----{}indexName = a_1_b.$**_1_c_1\n{}-----{}keyPattern = { a: 1, $_path: 1, $_path: 1, c: 1 }\n{}-----{}direction = 1\n{}-----{}bounds = field #0['a']: [3, 3], field #1['$_path']: [MinKey, MinKey], [\"\", {}), field #2['$_path']: [MinKey, MaxKey], field #3['c']: [MinKey, MaxKey]\n{}-----{}nodeId = 1\n{}-----{}fetched = 0\n{}-----{}sortedByDiskLoc = 0\n{}-------providedSorts = {baseSortPattern: {}, ignoredFields: [a]}\n"

       
      For some context: wildcard indices use MinKey in the $_path field to denote an object where the wildcard object is empty/absent. In this case the query specifies $ne: null, so I don't think the extra point scan on MinKey is needed. The created plan also has a residual filter in this case, so this should not lead to a correctness issue.

            Assignee:
            Unassigned
            Reporter:
            Kartal Kaan Bozdogan
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated: