Clarity required around use of indexes in uncorrelated sub-query using $lookup

Working with Data
1

The MongoDB docs at https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup/#perform-multiple-joins-and-a-correlated-subquery-with--lookup make the following statement (for 6.0):

  • Indexes are not used for comparisons with more than one field path operand.

We have the following $lookup query in our pipeline:

{
    $lookup: {
      from: "metadata",
      let: {
        a_local: "$a",
        b_local: "$b",
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ["$aEqv", "$$a_local"] },
                { $eq: ["$bEqv", "$$b_local"] },
              ],
            },
          },
        },
      ],
      as: "metadata",
    },
  },

along with a compound index,

{"metadata.bEqv": 1, "metadata.aEqv": 1, metadata.fieldOfInterest: 1}

This lookup will be used over a massive number of Documents so it must use an index. In this particular case it is not strictly critical that it uses the complete compound index - just the metadata.bEqv part of it would be ok as that has high cardinality, though if it could cover the whole query using the complete compound index that would be ideal.

Unfortunately, the documentation does not provide any examples of more than 1 field path, so it is unclear whether an index will be used in the above query. Enlightenment would be much appreciated.

6

Hi @Jonathan_Goodwin,

Apologies for the late response.

You can use the explain method to see a document with the query plan and, which index it’s utilizing.

Although, a general rule of thumb one considers when dealing with compound indexes is that you don’t need an additional index if your query can be covered by the prefix of the existent compound index.

I would highly suggest you read the following to cement your knowledge about indexes.

Please let us know if there’s any confusion in this. Feel free to reach out for anything else as well.

~ Kushagra

1 Like