MongoDB – Slow aggregate query

It takes around 6min to get the result from MongoDB, when I use the following aggregate query.

db.barcodes.aggregate([     {         $  lookup: {             from: 'company',             localField: 'company',             foreignField: '_id',             as: 'company'         }     },     {         $  match: {             'company.name': 'ABCd'         }     } ]); 

I have two collections in my DB, company and barcode. If I search with text ‘ABC’ (instead of ‘ABCd’, company name ‘ABC’ already exists in the DB) it takes only 0.05Sec to complete the result.

Total 42,14,301 documents in barcode collection and 2 documents in company collection.

Sample documents

Company

{      "_id" : ObjectId("615dd7873c4f710b71438772"),      "name" : "ABC",      "isActive" : true } 

Barcode

{      "_id" : ObjectId("615dd8ff3c4f710b71438773"),      "barcode" : "1",      "company" : ObjectId("615dd7873c4f710b71438772"),      "comment" : "text 1" } 

Indexed fields

  • company._id
  • company.name
  • company.isActive
  • barcode.company

Mongo clients used: Studio 3t and MongoDB CLI