$lookup stage increased query time by 5x despite joining on _id on the same collection

Working with Data
, ,
1 
db.user.aggregate([
        {
        $lookup: {
          from: "user",
          as: "user",
          localField: "_id",
          foreignField: "_id",
        },
      },
     {
        $project: { ... }
      }
])

The user collection has 100,000 documents. Without the $lookup stage, it takes 3 seconds. With it, it takes 15 seconds.

Is this expected? What can explain this? The _id field is indexed and unique. I know that the index is used due to the explain. And if I were to join on a column that is not index, this query will timeout with an error.

So why is that even though an index is used, it still takes 5x longer?

EDIT:

There is actually a $project stage that comes after the $lookup. However, if I move the $project stage to before the $lookup or actually remove it entirely, the query returns to 3 seconds again.

So now the question becomes, what explains the $project stage in all of this?

3

Hi @Big_Cat_Public_Safety_Act and welcome to MongoDB community forums!!

Could you provide the output for explain() of the test aggregations performed on the sample data mentioned earlier to enhance my understanding?
Additionally, could you kindly share sample documents from all the necessary collections, which would help me to replicate the issue in my local environment?
Lastly, we would greatly appreciate it if you could inform us of the MongoDB version you are currently using.

Regards
Aasawari