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

Remove elemMatch in residual predicates when each sub-query is satisfied by exact bounds

XMLWordPrintableJSON

    • Type: Icon: Improvement Improvement
    • Resolution: Unresolved
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • Labels:
      None
    • Query Optimization
    • QO 2024-04-01

      Consider query

      {a: {$elemMatch: {b: 1, c: 1}}}

      and index

      {"a.b": 1, "a.c": 1}

       

      For a query like this, classic currently creates an index scan with a residual predicate for the entire elemMatch:

      "winningPlan" : {
...         "isCached" : false,
...         "stage" : "FETCH",
...         "filter" : {"a": {"$elemMatch": {"b": 1, "c": 1}}}, // the whole elemMatch, unchanged
...         "inputStage" : {
...                 "stage" : "IXSCAN",
...                 ...
...                 "indexBounds" : {
...                         "a.b" : [
...                                 "[1.0, 1.0]"
...                         ],
...                         "a.c" : [
...                                 "[1.0, 1.0]"
...                         ]
...                 }
...         }
... }

       

      For this particular example, it seems like it should be sufficient to have the residual predicate just check that "a" is an array (rather than re-checking the entire elemMatch). 

       

      The simplest thing to do is a very targeted optimization: only if every predicate under the elemMatch generates exact bounds, then we can replace the residual filter. Otherwise, we can leave the plan as-is.

            Assignee:
            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Reporter:
            hana.pearlman@mongodb.com Hana Pearlman
            Votes:
            0 Vote for this issue
            Watchers:
            9 Start watching this issue

              Created:
              Updated: