MongoDB - Slow aggregate query

Question

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
• barcode._id

Mongo clients used: Studio 3t and MongoDB CLI

Output of explain

{ 
    "stages" : [
        {
            "$cursor" : {
                "query" : {

                }, 
                "queryPlanner" : {
                    "plannerVersion" : 1.0, 
                    "namespace" : "diet.barcodes", 
                    "indexFilterSet" : false, 
                    "parsedQuery" : {

                    }, 
                    "winningPlan" : {
                        "stage" : "COLLSCAN", 
                        "direction" : "forward"
                    }, 
                    "rejectedPlans" : [

                    ]
                }
            }
        }, 
        {
            "$lookup" : {
                "from" : "company", 
                "as" : "company", 
                "localField" : "company", 
                "foreignField" : "_id"
            }
        }, 
        {
            "$match" : {
                "company.name" : {
                    "$eq" : "ABCd"
                }
            }
        }
    ], 
    "ok" : 1.0
}

Answer 1

Your winning query is running against diet.barcodes and is doing a COLLSCAN because it doesn't have an index.

Create an index of that collection.