Query is slow when $lookup

Working with Data
,
1

There are collections: user, address, addressComment:

user

{ "_id" : "2", "name" : "user2" }
{ "_id" : "1", "name" : "user1" }

address

{ "_id" : "a1", "street" : "Address1", "user_id" : "1" }

addressComment

{ "_id" : "ac2", "address_id" : "a1", "comment" : "Comment2" }
{ "_id" : "ac1", "address_id" : "a1", "comment" : "Comment1" }

I am making a $aggregate

[
{ $lookup: {
       "from" : "address",
       "let" : {
       "userId" : "$_id"},
       "pipeline" : [
        {
           "$match" : {
               "$expr" : {
                   "$and" : [
                       {
                           "$eq" : [
                               "$user_id", 
                               "$userId"
                           ]
                       }
                    ]
                }
            }
          }], 
          "as" : "address"
        }
    },
    {
        $unwind: {
            path: "$address",
            reserveNullAndEmptyArrays: true
        }
    }
    { 
        $lookup: {
            "from" : "addressComment",
            "let" : {
            "addressId" : "$_id"},
            "pipeline" : [{
                "$match" : {
                    "$expr" : {
                        "$and" : [{
                            "$eq" : [
                                "$address_id",
                                "$addressId"
                            ]
                        }]
                    }
                }
            }], 
            "as" : "address.comments"
        }
    }
]

If, as in the code above, attach the comments documents to the joined “address” document, then with the number of documents in the “comments” collection > 20,000, the query execution time reaches 10 seconds. There are indexes on “address_id” and “user_id”. Also, if you just join “addressComment” to “address”, then the request is completed in 0.3 seconds. That is, there is a problem in joining the joined document.

1 Like
2

what index you have on each of collections?
user will have default _id index and the same with rest of the collections but if you have user_id index on address and address_id index on addressComments then lookup will be faster.

also if you are not using project for lookup then you lookup can be simplified ad below:

{$lookup:{
  from: 'address',
  localField: '$_id',
  foreignField: '$user_id',
  as: 'address'
}