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

SORT_MERGE does not support combining clauses from different $or selectors

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

      The query planner does not consider combining together clauses from different $or selectors in a cartesian product style, even when this would produce significantly more efficient index scans at the cost of having more individual index scans.

      Steps to reproduce:

      db.a.createIndex({ a: 1, b: 1, c: 1 });
      
      let filter = {
        $and: [
          {
            $or: [
              {
                a: 0,
              },
              {
                a: 1,
              },
            ],
          },
          {
            $or: [
              {
                b: 0,
                c: { $gt: 0 },
              },
              { b: { $gt: 0 } },
            ],
          },
        ],
      };
      
      db.a.explain().find(filter).sort({ b: 1 });
      

      This produces two index scans, both only constrained on the a field, merged using a SORT_MERGE.

      A more efficient way would be to combine the two $or selectors using a cartesian product to produce four index scans, like the following query does with the same index:

      let filter = {
        $or: [
          {
            a: 0,
            b: 0,
            c: { $gt: 0 },
          },
          {
            a: 1,
            b: 0,
            c: { $gt: 0 },
          },
          {
            a: 0,
            b: { $gt: 0 }
          },
          {
            a: 1,
            b: { $gt: 0 }
          },
        ],
      };
      
      db.a.explain().find(filter).sort({ b: 1 });
      

      Note that the two queries are semantically equivalent, but all the index scans made by the latter are constrained on both the a and b fields, and two are additionally constrained on the c field, potentially scanning a significantly lower number of documents.

            Assignee:
            Unassigned Unassigned
            Reporter:
            kartalkaan.bozdogan@ocell.io Kartal Kaan Bozdoğan
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              None
              None
              None
              None