2

My case is very similar a this (MongoDB aggregate count is too much slow) case, I have a 40.000 docs and this aggregate takes 8 seconds to give me the total count (40.000) showing only the 10 docs (limit).

P.S. If I run customers.find().count(), it returns the count for 40.000 in less than 1 seconds

This is my query:

aggregate([
{
  "$match": {}
},
{
  "$lookup": {
    "from": "core.entities",
    "localField": "entityId",
    "foreignField": "_id",
    "as": "entity"
  }
},
{
  "$unwind": "$entity"
},
{
  "$project": {
    "entity._id": 0
  }
},
{
  "$replaceRoot": {
    "newRoot": {
      "$mergeObjects": [
        "$entity",
        "$$ROOT"
      ]
    }
  }
},
{
  "$project": {
    "entity": 0
  }
},
{
  $facet: {
    paginatedResults: [
      {
        $skip: 0
      },
      {
        $limit: 10
      }
    ],
    totalCount: [
      {
        $count: 'count'
      }
    ]
  }
}])

This is my customers collection indexes:

[{
  "v": 2,
  "key": {
    "_id": 1
  },
  "name": "_id_",
  "ns": "applekkus-gmp.core.customers"
},
{
  "v": 2,
  "key": {
    "name": 1
  },
  "name": "name_1",
  "ns": "applekkus-gmp.core.customers"
}]

... and this is my entities collection index:

[{
  "v" : 2,
  "key" : {
      "_id" : 1
  },
  "name" : "_id_",
  "ns" : "applekkus-gmp.core.entities"
}]

... and this is my aggregate explain():

"stages": [
{
  "$cursor": {
    "query": {
    },
    "queryPlanner": {
      "plannerVersion": 1,
      "namespace": "applekkus-gmp.core.customers",
      "indexFilterSet": false,
      "parsedQuery": {
      },
      "winningPlan": {
        "stage": "COLLSCAN",
        "direction": "forward"
      },
      "rejectedPlans": []
    }
  }
},
{
  "$lookup": {
    "from": "core.entities",
    "as": "entity",
    "localField": "entityId",
    "foreignField": "_id",
    "unwinding": {
      "preserveNullAndEmptyArrays": false
    }
  }
},
{
  "$project": {
    "entity": {
      "_id": false
    }
  }
},
{
  "$replaceRoot": {
    "newRoot": {
      "$mergeObjects": [
        "$entity",
        "$$ROOT"
      ]
    }
  }
},
{
  "$project": {
    "entity": false
  }
},
{
  "$facet": {
    "paginatedResults": [
      {
        "$limit": NumberLong(10)
      }
    ],
    "totalCount": [
      {
        "$group": {
          "_id": {
            "$const": null
          },
          "count": {
            "$sum": {
              "$const": 1
            }
          }
        }
      },
      {
        "$project": {
          "_id": false,
          "count": true
        }
      }
    ]
  }
}
],"ok": 1
Biruel Rick
  • 776
  • 1
  • 8
  • 17
  • I also faced same problem when use $facet for pagination, it run much slower than ```count()```, ```skip()```, ```limit()``` directly – spike 王建 Nov 30 '20 at 06:46

0 Answers0