title: Mongodb Aggregation pipeline performance issue after lookup stage
body: <p>I tried to run a query on huge data set but after lookup stage(stage 3) it took too much amount of time (approximately 12 hour) . How do I improve query performance . I am using single node with 16 cpu, 64 gb RAM with mongodb 4.0 version .</p>
  
  <pre><code>// Pipeline
  [
      // Stage 1
      {
          $match: {
           "ID" : "XXXXX"
          }
      },
  
      // Stage 2
      {
          $redact: {
             $cond: [ { $or: [ { $eq: [ "$Masterid", "" ] }, { $or: [ { $eq: [ "$VKORG", "" ] }, { $or: [ { $eq: [ "$VTWEG", "" ] },{ $eq: [ "$UDATE", "" ] } ] } ] } ] } ] } ] } ] } ] }, "$$PRUNE", "$$KEEP" ] 
          }
      },
  
      // Stage 3
      {
          $lookup: {
              from: "BU07-02", let: { raw_Masterid: "$Masterid", raw_VKORG: "$VKORG", raw_VTWEG: "$VTWEG", raw_SPART: "$SPART", raw_KUNNR: "$KUNNR",
                 raw_VBELN: "$VBELN", raw_POSNR: "$POSNR", raw_UDATE: "$UDATE", raw_KNUMH: "$KNUMH", raw_AUART: "$AUART", raw_FKART: "$FKART",
                  }, pipeline: [ { $match: { $expr: { $and: [ { $eq: [ "$$raw_Masterid", "$Masterid" ] }, 
                    { $and: [ { $eq: [ "$$raw_VKORG", "$VKORG" ] }, { $and: [ { $eq: [ "$$raw_VTWEG", "$VTWEG" ] }, { $and: [ { $eq: [ "$$raw_SPART", "$SPART" ] },
                       { $and: [ { $eq: [ "$$raw_KUNNR", "$KUNNR" ] }, { $and: [ { $eq: [ "$$raw_VBELN", "$AUBEL" ] }, { $and: [ { $eq: [ "$$raw_POSNR", "$AUPOS" ] }, 
                         { $lte: [ "$$raw_UDATE", "$UDATE" ] } ] } ] } ] } ] } ] } ] } ] } } } ], as: "BU07-02"
          }
      },
  
      // Stage 4
      {
          $unwind: {
              path : "$BU07-02",
  
          }
      },
  
      // Stage 5
      {
          $redact: {
          $cond: [ { $and: [ { $eq: [ "$Masterid", "$BU07-02.Masterid" ] }, { $and: [ { $eq: [ "$VKORG", "$BU07-02.VKORG" ] }, { $and: [ { $eq:
             [ "$VTWEG", "$BU07-02.VTWEG" ] }, { $and: [ { $eq: [ "$SPART", "$BU07-02.SPART" ] }, { $and: [ { $eq: [ "$KUNNR", "$BU07-02.KUNNR" ] }, 
  
                       { $and: [ { $ne: [ "$BU07-02.FKART", "ZBS1" ] },
                                { $and: [ { $ne: [ "$BU07-02.FKART", "ZBS2" ] }, { $and: [ { $ne: [ "$VBTYP", "U" ] }, { $and: [ { $ne: [ "$BU07-02.VBTYP", "U" ] }, 
                                  { $ne: [ "$BASE_ENTITY", true ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] } ] }, "$$KEEP", 
                                  "$$PRUNE" ] }
      },      
  ],
  </code></pre>
  
  <p>);</p>