improve query performance mongodb

Question

I am using aggregation query to retrieve 20000 records. while retrieving it is taking much time. I will mention my query below, Please help me to improve the query performance.

Query:

[err, data] = await to(LeadsLog.aggregate([
    {$lookup:{
      from: "leads",
      localField: "leadId",
      foreignField: "_id",
      as: "leadId"
    }},
    {$lookup:{
      from: "company_contacts",
      localField: "leadId.assignedTo",
      foreignField: "_id",
      as: "assignedTo"
    }},
    {
      $unwind:{
       path: "$leadId",
       preserveNullAndEmptyArrays: true
     }
    },
    {
      $match:{"leadId.assignedTo":new mongoose.Types.ObjectId(userId),
               "result":{$eq:null}}
    },
    { '$facet'    : {
        metadata: [ { $count: "total" }, { $addFields: { page: 1 } } ],
        data: [ { $skip: 0 }, { $limit: 20000 } ] 
    } }
] ));

LeadId:

{
    "_id" : ObjectId("617a84b401c98424e00d1310"),
    "status" : true,
    "address" : "Howmif Trail",
    "city" : "Kinawnet",
    "state" : "LA",
    "country" : "LA",
    "pincode" : null,
    "extraFormObject" : null,
    "lead_name" : "Jayden",
    "phone" : "(524) 387-4912",
    "email" : "niligis@taptehe.cm",
    "company" : ObjectId("6155c2758609663d10fff796"),
    "createdBy" : ObjectId("6155c2758609663d10fff798"),
    "createdAt" : ISODate("2021-10-28T11:08:40.433Z"),
    "updatedAt" : ISODate("2021-10-30T04:43:49.490Z")
}

LeadLog:

{
    "_id" : ObjectId("617a84bf01c98424e00daf52"),
    "callLogId" : null,
    "result" : null,
    "assignedTo" : ObjectId("6155c2758609663d10fff798"),
    "extraFormObject" : null,
    "subResult" : null,
    "apptDate" : null,
    "nextcallDate" : ISODate("2021-10-28T11:02:50.516Z"),
    "callDate" : null,
    "leadId" : ObjectId("617a84b401c98424e00d1310"),
    "company" : ObjectId("6155c2758609663d10fff796"),
    "createdAt" : ISODate("2021-10-28T11:08:50.962Z"),
    "updatedAt" : ISODate("2021-10-30T04:43:50.281Z")
}

Please help me with better solution. thank you.

Answer 1

There are a few simple tweaks that you can improve your existing query:

• make intermediate result as small as possible; one of the common ways is pushing $match stages as early as possible
• use Pipeline Coalescence Optimization as much as possible; one of the common tuples would be $lookup + $unwind combination
• index the $match fields and $lookup fields

Based on the first 2 points, here is my suggested form of your query:

• You can see result : {$eq: null} is pushed to first stage. The performance gain will depends on the selectivity of the clause.
• the $lookup and $unwind leads are grouped together to utilize the coalescence optimization.
• "leadId.assignedTo": new mongoose.Types.ObjectId(userId) is moved earlier to minimize intermediate result size
• Don't forget to index the relevant $match fields and $lookup fields. From my personal experience, good usage of index will help most with the performance.

[err, data] = await to(LeadsLog.aggregate([
  {
    $match: {
      "result": {
        $eq: null
      }
    }
  },
  {
    $lookup: {
      from: "leads",
      localField: "leadId",
      foreignField: "_id",
      as: "leadId"
    }
  },
  {
    $unwind: {
      path: "$leadId",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $match: {
      "leadId.assignedTo": new mongoose.Types.ObjectId(userId)
    }
  },
  {
    $lookup: {
      from: "company_contacts",
      localField: "leadId.assignedTo",
      foreignField: "_id",
      as: "assignedTo"
    }
  },
  {
    "$facet": {
      metadata: [
        {
          $count: "total"
        },
        {
          $addFields: {
            page: 1
          }
        }
      ],
      data: [
        {
          $skip: 0
        },
        {
          $limit: 20000
        }
      ]
    }
  }
]));