1

Compound Index:

{
  "A": 1,
  "C": 1,
  "D": 1,
  "E": 1,
  "F": 1,
  "G": 1,
  "H": 1,
  "I": 1,

  "B": 1
}

Fast query:

    const result = await sandbox
      .aggregate([
        {
          $match: {
            A: { $in: [1, 2] },
            C: { $in: [1, 2] },
            D: { $in: [1, 2] },
            E: { $in: [1, 2] },
            F: { $in: [1, 2] },
            G: { $in: [1, 2] },
            H: { $in: [1, 2] },
            I: { $in: [1, 2] },

            B: { $eq: 500 },
          },
        },
      ])

The query becomes significantly slower if it is modified to be this:

    const result = await sandbox
      .aggregate([
        {
          $match: {
            $or: [{ A: { $eq: 1 } }, { A: { $eq: 2 } }],
            $or: [{ C: { $eq: 1 } }, { C: { $eq: 2 } }],
            $or: [{ D: { $eq: 1 } }, { D: { $eq: 2 } }],
            $or: [{ E: { $eq: 1 } }, { E: { $eq: 2 } }],
            $or: [{ F: { $eq: 1 } }, { F: { $eq: 2 } }],
            $or: [{ G: { $eq: 1 } }, { G: { $eq: 2 } }],
            $or: [{ H: { $eq: 1 } }, { H: { $eq: 2 } }],
            $or: [{ I: { $eq: 1 } }, { I: { $eq: 2 } }],

            B: { $eq: 500 },
          },
        },
      ])

What is the reason for this?

EDITED:

After using explain, it is confirmed that in the fast query, the index was scanned while in the slow query with $or statement, the collection scan was used.

Now the question becomes, why isn't MongoDB able to use the index scan with $or conditions.

Bear Bile Farming is Torture
  • 4,317
  • 4
  • 27
  • 61
  • 2
    have you tried using `explain` to see what's different? – Joe Dec 28 '22 at 11:21
  • 1
    I just did that. In the fast query, the index scan was used as the winning plan. In the slow query with the `$or` statements, the collection scan was used. Now the question becomes why isn't MongoDB smart enough to use the index scan when $or is used. – Bear Bile Farming is Torture Dec 28 '22 at 19:19
  • 1
    As written in the question the query with `$or` is invalid because the fieldname "$or" is repeated multiple time in the same object. You might wrap those in ``$and`, and try without the `$eq`, like `{$or: [{ A: 1, A:2}]}` – Joe Dec 28 '22 at 22:52

0 Answers0