Slow Query in MongoDB with collection wide aggregate query using group

user_3900973 0
Viewed 1185

I am having performance issues on the API I am developing using NodeJS+Express+MongoDB.

On running the aggregate with $match on particular product, the performance is good but for a open search it's really slow.

I want to run a group on two columns: country and exporter and then fetch the result limited to 3 results per group on country.

Requirement: Total Count of unique exporters from each country along with any 3 records from each country.

On running explain() on my aggregate function I am getting the following key pointers that flag about my queries being slow. Please correct me if I am wrong.

  1. "indexFilterSet": false
  2. "winningPlan": { "stage": "COLLSCAN", "direction": "forward" },

Ran the query on 9,264,947 records and time taken is about 32 seconds. I have tried using compound index as well as single field index but it's not helping at all, as I feel the index is not being used with $match being empty {}

Below is the query I am running on mongoDB using mongoose driver

Model.aggregate([
  {"$match" : query},
  { $group : {_id: {country: "$Country", exporter: "$Exporter"}, id: {$first: "$_id"}, product: { $first: "$Description" }}},
  { $group : {_id: "$_id.country", data: {$push: { id: "$id", company: "$_id.exporter", product: "$product" }}, count:{$sum:1}}},
  { "$sort": { "count": -1 } },
  { 
    $project: { 
      "data": { "$slice": [ "$data", 3 ] },
      "_id": 1,
      "count": 1
    }
  },
]).allowDiskUse(true).explain()

where, query is dynamically build and is by default empty {} for a collection-wide search. Indexed fields are

  1. Compound Index: {Country: 1, Exporter: 1}

  2. Text Index: {Description: "text"}

Full explain() response:

{
"success": "Successfull",
"status": 200,
"data": {
    "stages": [
        {
            "$cursor": {
                "query": {},
                "fields": {
                    "Country": 1,
                    "Description": 1,
                    "Exporter": 1,
                    "_id": 1
                },
                "queryPlanner": {
                    "plannerVersion": 1,
                    "namespace": "db.OpenExportData",
                    "indexFilterSet": false,
                    "parsedQuery": {},
                    "winningPlan": {
                        "stage": "COLLSCAN",
                        "direction": "forward"
                    },
                    "rejectedPlans": []
                }
            }
        },
        {
            "$group": {
                "_id": {
                    "country": "$Country",
                    "exporter": "$Exporter"
                },
                "id": {
                    "$first": "$_id"
                },
                "product": {
                    "$first": "$Description"
                }
            }
        },
        {
            "$group": {
                "_id": "$_id.country",
                "data": {
                    "$push": {
                        "id": "$id",
                        "company": "$_id.exporter",
                        "product": "$product"
                    }
                },
                "count": {
                    "$sum": {
                        "$const": 1
                    }
                }
            }
        },
        {
            "$sort": {
                "sortKey": {
                    "count": -1
                }
            }
        },
        {
            "$project": {
                "_id": true,
                "count": true,
                "data": {
                    "$slice": [
                        "$data",
                        {
                            "$const": 3
                        }
                    ]
                }
            }
        }
    ],
    "ok": 1
}
}

Collection Size : 9,264,947 records & 10.2 GB

Response Time : 32154 ms

The query is getting slower as the size of my collection is increasing.

mongoose node.js mongodb aggregate
2 Answers

Using the aggregate like this means that mongodb have to go through all the record, then group the data (load 10 Gb), then slice the array it would have created.

Ofc the more your collection grow, the longer it is.

I think that instead of optimizing your actual request, it's worth reconsidering your approach of it.

I would first find every country name using one request. Then use one request for each country getting the first 3 exporter.

Using indexes on country and on exporter.

It's a lot more of request, but way smaller ones, which not requires to load all the data. With direct access to the data using proper indexes.

And considering there are not thousands of different country out there

user_33370800

If your query is {}, mongo engine skips the $match stage and go right into $group. No index will be used. You can verify above from explain() result. The $match and $sort pipeline operators can take advantage of an index when they occur at the beginning of the pipeline. Looking at your pipeline, you group them using Country and Exporter. What you can do is to create an index on {Country: 1, Exporter: 1}, and use $sort on {Country: 1, Exporter: 1} as the first stage of the pipeline. This will make $group more efficient.

user_108493500
Related