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.

Answer 2

Consider the aggregate query from the question post. The following is based upon the MongoDB version 7.

$lookup Performance Considerations says:

Equality Match with a Single Join: $lookup operations that perform equality matches with a single join typically perform better when the source collection contains an index on the foreignField.

So, the foreign field is _id of the company collection. This index already exists (_id field has a unique index, by default). Though, the query plan output doesn't show this explicitly, the index optimization is there in the $lookup stage.

In this case there are only two documents in the company collection, and the index is not of much use.

---

Generate a query plan for the aggregate query:

db.barcodes.explain().aggregate([ ... ])

The explain plan output shows the following (partial output shown here):

  ...winningPlan: {
        queryPlan: {
          stage: 'EQ_LOOKUP',
          planNodeId: 2,
          foreignCollection: 'test.company',
          localField: 'company',
          foreignField: '_id',
          asField: 'r_company',
          strategy: 'IndexedLoopJoin',
          indexName: '_id_',
          indexKeyPattern: { _id: 1 },
          inputStage: {
            stage: 'COLLSCAN',
            planNodeId: 1,
            filter: {},
            direction: 'forward'
          }
        },
        slotBasedPlan: { ...

Some points to note from the winningPlan of the explain output:

• stage: 'EQ_LOOKUP' - EQ_LOOKUP means "equality lookup"
• winningPlan.slotBasedPlan - slot-based query engine usage

---

To find and return query results, MongoDB uses one of the following query engines:

• The classic query engine
• The slot-based query execution engine (MongoDB v5.1 or higher)

MongoDB automatically selects the engine to execute the query. You cannot manually specify an engine for a particular query.

MongoDB can use the slot-based query execution engine for a subset of queries which are eligible and provided certain conditions are met. In most cases, the slot-based execution engine provides improved performance and lower CPU and memory costs compared to the classic query engine.

The winningPlan.slotBasedPlan field in the above plan output and the 'EQ_LOOKUP' stage indicate the usage of the slot-based query engine.

Reference: $lookup Optimization (MongoDB v6.0 or higher).

---

The $lookup stage in the query already benefits from this. The $match stage after the initial $lookup cannot use any indexes in this case.

More details from the explain can be produced and studied by using the "executionStats" option of the explain().