Aggregation performance issue

I have more than 300k data in my product table. and my pipeline look like this.

[{
    $match: {
      "destinations.ref": {
        $in: [
          "24414",
          "24415",
          "25290",
          "23061",
          "23063",
          "28836",
          "28837",
          "5642",
          "23406",
          "824",
          "4553",
          "4799",
          "4192",
          "24042",
          "28718",
          "5044",
          "21942",
          "4193",
          "21943",
          "4201",
          "50321",
          "50322",
          "50324",
          "50327",
          "50326",
          "4908",
          "50409",
          "50411",
          "50413",
          "50412",
          "50415",
          "50414",
          "5512",
          "24041",
          "812",
          "24268",
          "50916",
          "24269",
          "27067",
          "5437",
          "829",
          "174",
          "51227",
          "22940",
          "5502",
          "175",
          "24181",
          "5047",
          "26516",
          "23856",
          "468",
          "5036",
          "50245",
          "177",
          "50937",
          "51325",
          "51299",
          "25311",
          "22737",
          "5030",
          "179",
          "25737",
          "23076",
          "24308",
          "5223",
          "4195",
          "21941",
          "50323",
          "478",
          "50249",
          "786",
          "181",
          "26343",
          "26433",
          "28884",
          "752",
          "22036",
          "22048",
          "22037",
          "183",
          "25906",
          "5469",
          "4199",
          "5089",
          "909",
          "50325",
          "50417",
          "50416",
          "184",
          "50935",
          "26221",
          "50932",
          "5228",
          "483",
          "485",
          "813",
          "51197",
          "51351",
          "5049",
          "178",
          "25736",
          "23883",
          "24202",
          "5092",
          "25406",
          "5636",
          "50925",
          "763",
          "5637",
          "479",
          "51",
        ],
      },
      "reviews.combinedAverageRating": {
        $gte: 1,
        $lte: 5,
      },
    },
  },
  {
    $lookup: {
      from: "availability_schedules",
      localField: "productCode",
      foreignField: "productCode",
      as: "schedules",
    },
  },
  {
    $lookup: {
      from: "exchange_rates",
      let: {
        scheduleCurrency: "$schedules.currency",
      },
      pipeline: [{
        $match: {
          $expr: {
            $in: [
              "$sourceCurrency",
              "$$scheduleCurrency",
            ],
          },
        },
      }, ],
      as: "exchangeRates",
    },
  },
  {
    $addFields: {
      exchangeRate: {
        $arrayElemAt: ["$exchangeRates.rate", 0],
      },
    },
  },
  {
    $addFields: {
      usdPrice: {
        $multiply: [
          "$exchangeRate",
          {
            $first: "$schedules.summary.fromPrice",
          },
        ],
      },
    },
  },
  {
    $lookup: {
      from: "exchange_rates",
      let: {
        scheduleCurrency: "$schedules.currency",
      },
      pipeline: [{
        $match: {
          $expr: {
            $in: ["$targetCurrency", ["ILS"]],
          },
        },
      }, ],
      as: "ilsRates",
    },
  },
  {
    $addFields: {
      ilsRate: {
        $arrayElemAt: ["$ilsRates.rate", 0],
      },
    },
  },
  {
    $addFields: {
      convertedPrice: {
        $multiply: ["$ilsRate", "$usdPrice"],
      },
    },
  },
  {
    $match: {
      convertedPrice: {
        $gte: 5000
      },
    },
  },
  {
    $skip: 0,
  },
  {
    $limit: 15,
  },
  {
    $project: {
      title: 1,
      productCode: 1,
      "images.variants.url": 1,
      "description.he": 1,
      "reviews.combinedAverageRating": 1,
      "itinerary.duration": 1,
      "schedules.summary": 1,
      "schedules.currency": 1,
      convertedPrice: 1,
    },
  },
]

if I remove $match pipeline on convertedPrice it takes 2sec. but when I add this pipeline it takes more than 2min. can you please how to resolve it.

Hi @Biplab_Das and welcome to MongoDB community forums!!

The match conditions seems to be long list of array that you are trying to match.
Can you confirm if there is an index created on the destinations.ref field. If not, the recommendation would be to create Index on the field which would help with matching the correct values with enhanced performance.
You can read more about Multikey Indexes from the official documentation.

If there is an index already created, could you help me with the following information:

  1. A few sample documents which would help me understand the document structure.
  2. The desired output you need from the aggregation pipeline.
  3. The Indexes defined in the collection.
  4. The MongoDB version you are using.
  5. The example output for the query being used.

These information helps the community to understand the requirement and provide a possible solution.

Best Regards
Aasawari