I want data from two collection but it take much time nearly 10-30 sec

Hello,
I’m want Data from two different table and there was relation using fault_id but when i do query using the fault_id.user_id = user_id it take much time
i will provide the query here

const aggregatedData = await connection
      .collection('coming_fault')
      .aggregate([
        {
          $match: {
            created_at: {
              $gt: sevenDaysAgo,
            },
          },
        },
        {
          $lookup: {
            from: 'faults',
            localField: 'fault_id',
            foreignField: '_id',
            as: 'fault_data',
            pipeline: [
              {
                $match: {
                  user_id: user_id,
                  category: 'Fault',
                },
              },
            ],
          },
        },
        {
          $unwind: '$fault_data',
        },
        {
          $group: {
            _id: { fault_id: '$fault_id' },
            display_text: {
              $addToSet: '$fault_data.display_text',
            },
            component: {
              $addToSet: '$fault_data.component',
            },
            type: { $addToSet: '$fault_data.type' },
            category: {
              $addToSet: '$fault_data.category',
            },
            severity: {
              $addToSet: '$fault_data.severity',
            },
            countNm: {
              $sum: 1,
            },
            vehicles_count: { $addToSet: '$UID' },
            created_at: { $addToSet: '$created_at' },
          },
        },
        {
          $project: {
            _id: 0,
            fault_id: '$_id.fault_id',
            display_text: {
              $arrayElemAt: ['$display_text', 0],
            },
            component: {
              $arrayElemAt: ['$component', 0],
            },
            type: {
              $arrayElemAt: ['$type', 0],
            },
            category: {
              $arrayElemAt: ['$category', 0],
            },
            severity: {
              $arrayElemAt: ['$severity', 0],
            },
            fault_count: '$countNm',
             vehicle_count: {
               $size: '$vehicles_count',
             },
            vehicle_id: '$vehicles_count',
            created_at: {
              $arrayElemAt: ['$created_at', 0],
            },
          },
        },
        {
          $sort: {
            created_at: -1,
            fault_id: 1,
          },
        },
        {
          $limit: 5,
        },
      ])
      .toArray()

Share the explain plan of your query.

Sorry for late reply
In this Query i get real time coming fault that are coming and volume of coming fault right now is 100K now i store detail about Fault in Fault Table.

I have one dashboard and above query is for the dashboard API now we want Display text, Component ( type), type (Fault,alert), category, severity from Fault table and coming_fault table we want fault_count, vehicle_count,vehicle_id and created_at

One more detail.

  • We are using PRISMA in backend so if you want to show schema i will provide