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

query optimizer does not consider query plans for $and:[$or:[conditions]]

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 2.0.6
    • Component/s: Querying
    • None
    • Environment:
      any
    • ALL

      I have 2 sets of "ored" conditions because I need all rows that meet conditions A or B or C, and that also meet conditions D or E or F.
      Since I can only have a single $or statement, I use this:
      $or:[

      {A}

      ,

      {B}

      ,

      {C}

      ],$and[{$or:[

      {D}

      ,

      {E}

      ,

      {F}

      ]}]
      This returns the correct results, and by the way, uses indexes on A, B and C and runs in parallel, which is fantastic (good job!!!).
      However, if my indexes on D,E and F are even better choices than A,B and C, let's say they have better selectivity, the query optimizer completely ignores them as viable plans. Moreover, there is no way for me to give a hint to use them, because hint can only take a single index. Even if I had a combined index on D_E_F and passed it as a hint, it would not help because the query on E and on F would not be able to use it.

      I think we need either a mechanism to specify multiple first level $ors (I know JS does not make it easy) or a smarter query optimizer that considers more alternatives, even if they are hidden inside an $and.

            Assignee:
            aaron Aaron Staple
            Reporter:
            arie@klip.com Arie Grapa
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved: