Lookup performance clarifications

Is it right to assume that when we perform a lookup stage we are doing one query on the target collection for each incoming document in the lookup stage? Otherwise, what kind of optimizations does the lookup do to improve its performance?

Thanks!
Ricardo

1 Like

@Ricardo_Vasquez1 Refer to this link for optimization techniques.

Hi Sudhesh, thank you for the information, but that documentation page barely talks about how lookup works, and that’s the main part of my question :confused:

I’d like to know if, for each incoming document in the lookup stage, MongoDB is going to do one query on the ‘from’ collection

@Ricardo_Vasquez1, can you provide the sample document and the query that you tried?
It will be easy for everyone to answer you.

@Sudhesh_Gnanasekaran sure, think in these two collections:

Collection A with the following documents:

    [
     {_id: 0, b_id: 0, value: 1}, 
     {_id: 1, b_id: 0, value: 2},
     {_id: 2, b_id: 1, value: 4}
   ]

And Collection B with the following documents:

    [{_id: 0, b_value: 0},
     {_id: 1, b_value: 1},
     {_id: 2, b_value: 2}]

If I do this aggregate:

    db.getCollection('A').aggregate([
     {
      $group: {
        _id: "$b_id",
        sum_value: {$sum: "$value"}
      }
     },
    {
      $lookup: {
        from: 'B',
        localField: '_id',
        foreignField: '_id',
        as: 'b_data'
      }
    }

I know that the output documents will look like this:

    [
     {_id: 0, sum_value: 3, b_data: [{b_value: 0},
     {_id: 1, sum_value: 4, b_data: [{b_value: 1}
    ]

But again, my question is how lookup works internally. In this case, is lookup doing one find (or something similar) for each one of the two ids incoming to the lookup stage?

I’d like to know the details to be aware of them when I prepare my pipelines.

Thank you!

You have 2 values. You have to do 2 lookup. Imagine that you have a phone book and you want to know the phone number of 2 different people. There is no way you can look at 2 different entries with a single lookup. There is no magic. A index will help because searches will be much faster.

Hi steeve, that’s exactly what I wanted to confirm.

Thank you so much!

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.