Mongo aggregation query take more time

MongoDB Atlas Atlas Search
, , ,
1 
*await mongoose.connection.db.collection('users').aggregate([
    { $lookup: { from: `education`, localField: `user_id`, foreignField: `user`, as: `education` } },
    { $unwind: { path: `$education`, preserveNullAndEmptyArrays: true } },
    { $lookup: { from: `userinfo`, localField: `user_id`, foreignField: `user`, as: `userinfo` } },
    { $unwind: { path: `$userinfo`, preserveNullAndEmptyArrays: true } },
    { $match: { is_deleted: false } },
    { $match: criteria },
    {
        `$facet`: {
            `totalLocation`: [
                { $match: criteria },
                { `$count`: `count` },
            ],
        }
    },
    {
        `$project`: {
            `totalLocation`: { `$arrayElemAt`: [`$totalLocation.count`, 0] },
        }
    }
], { allowDiskUse: true, collation: { locale: 'en_US', alternate: `shifted` } }).toArray();

This query works completely fine and return data as expected. But now as data are growing so this query became slower and we would make it faster. one of solution which I found that to create an index in a way so we can have faster result. I have tried but it doesn't works for me*
2

Why are those $match stages separated? They could be collapsed to a single one:

$match:{ is_deleted:false, ...criteria }

And I don’t think you need a $facet, do you?

As it is written the $project and $facet can be removed and replaced by $group if I get it correctly.

You can use db.collection('users').explain().aggregate(yourPipeline) to get some statistics and details about the execution.

1 Like
3

In addition, is_deleted is a field from documents of the users collections so you should $match this first. What you do right now is that you do 2 $lookup and 2 $unwind on documents that you gonna eliminate anyway. You are better off eliminating them right at the start.

The $lookup of userinfo is not dependant on the result of $unwind education. So you should do the $lookup of userinfo before the $unwind of education.

Hopefully you have indexes user in both education and userinfo collections.

1 Like