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

Efficiency of one $or branch during planning may not be representative of overall plan efficiency

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

      Related to SERVER-20616, but specific to plans which perform more than one index scan and not reliant on data skew/affinity.  

      Consider the following scenario:

      • A contained $or query shape of { <Common Predicates> , $or: [ { <CLAUSE1> }

        , { <CLAUSE2> } ] }

      • Predicate values for CLAUSE1 such that there are at least 101 matching documents
      • Indexes A and B
      • CLAUSE1 (along with the pushed down Common Predicates) is perfectly served by index A (meaning a key scanned to doc returned ratio of 1).  Similarly CLAUSE 2 plus Common Predicates is perfectly served by index B

      The optimizer will produce, at least, the following four plans:

      1. index A, index A
      2. index A, index B
      3. index B, index A
      4. index B, index B

      Given the parameters above, we would expect the trial phase to terminate after 101 works/index keys are scanned.  Assuming all plans test CLAUSE1 first (SERVER-42090), this means that plans 1 and 2 will score the same since they are evaluating the same index and spend the entire trial phase in the clause.  The overall efficiency of the two plans will be very different though since a different index is used for CLAUSE2.  

            Assignee:
            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Reporter:
            christopher.harris@mongodb.com Chris Harris
            Votes:
            0 Vote for this issue
            Watchers:
            19 Start watching this issue

              Created:
              Updated: