How to optimize a count query

Working with Data
, ,
1

Using the c# driver to get a document count, using CountDocumentsAsync is see the following “very slow” command in Atlas profiler:

{
  "command": {
    "aggregate": "contacts",
    "pipeline": [
      {
        "$match": {
          "accountId": 158,
          "deleted": null
        }
      },
      {
        "$group": {
          "_id": 1,
          "n": {
            "$sum": 1
          }
        }
      }
    ],
    "cursor": {},
    "$db": "Loopify",
    "lsid": {
      "id": {
        "$uuid": "a84a49c6-c5f5-d6ce-9f33-b8e38d45e1b3"
      }
    },
    "$clusterTime": {
      "clusterTime": {
        "$timestamp": {
          "t": 1583238446,
          "i": 60
        }
      },
      "signature": {
        "hash": {
          "$binary": "PHh4eHh4eD4=",
          "$type": 0
        },
        "keyId": 6748499292626878000
      }
    }
  },
  "planSummary": [
    {
      "IXSCAN": {
        "accountId": 1,
        "deleted": 1,
        "tagGroups.name": 1,
        "tagGroups.tags": 1,
        "firstName": 1,
        "lastName": 1
      }
    }
  ],
  "keysExamined": 5677198,
  "docsExamined": 139458,
  "cursorExhausted": 1,
  "numYields": 44674,
  "nreturned": 1,
  "reslen": 238,
  "locks": {
    "Global": {
      "acquireCount": {
        "r": 89352
      }
    },
    "Database": {
      "acquireCount": {
        "r": 44676
      }
    },
    "Collection": {
      "acquireCount": {
        "r": 44676
      }
    }
  },
  "protocol": "op_msg",
  "millis": 32279
}

The collection has 760K documents, so I don’t understand keysExamined": 5677198.
The count takes 32 sec, while a find query on the same fields takes under a sec.

What can be done to optimize this?

MongodB: 3.6.17
C# Driver: 2.10.2

1 Like
2

@Erlend_Baerland Welcome to the forum!

Can you provide some more details to help investigate this:

  • Atlas tier & type of deployment (replica set or sharded cluster)
  • Output of explaining the equivalent aggregation in the mongo shell with explain("executionStats") verbosity.

The executionStats output includes more detail of query processing stages (work performed such as keys & documents examined, ranges for index searches, etc).

Thanks,
Stennie

3

Hi,
We’re using M10 (General) - Replica Set - 3 nodes

With this query:

db.contacts.explain("executionStats").aggregate(
[
      {
        "$match": {
          "accountId": 158,
          "deleted": null
        }
      },
      {
        "$group": {
          "_id": 1,
          "n": {
            "$sum": 1
          }
        }
      }
    ]
)

The result is:

{ 
    "serverInfo" : {
        "host" : "<removed>", 
        "port" : 27017.0, 
        "version" : "3.6.17", 
        "gitVersion" : "3d6953c361213c5bfab23e51ab274ce592edafe6"
    }, 
    "stages" : [
        {
            "$cursor" : {
                "query" : {
                    "accountId" : 158.0, 
                    "deleted" : null
                }, 
                "queryPlanner" : {
                    "plannerVersion" : 1.0, 
                    "namespace" : "Loopify.contacts", 
                    "indexFilterSet" : false, 
                    "parsedQuery" : {
                        "$and" : [
                            {
                                "accountId" : {
                                    "$eq" : 158.0
                                }
                            }, 
                            {
                                "deleted" : {
                                    "$eq" : null
                                }
                            }
                        ]
                    }, 
                    "winningPlan" : {
                        "stage" : "FETCH", 
                        "filter" : {
                            "deleted" : {
                                "$eq" : null
                            }
                        }, 
                        "inputStage" : {
                            "stage" : "IXSCAN", 
                            "keyPattern" : {
                                "accountId" : 1.0, 
                                "deleted" : 1.0, 
                                "tagGroups.name" : 1.0, 
                                "tagGroups.tags" : 1.0, 
                                "firstName" : 1.0, 
                                "lastName" : 1.0
                            }, 
                            "indexName" : "accountId_1_deleted_1_tagGroups.name_1_tagGroups.tags_1_firstName_1_lastName_1", 
                            "isMultiKey" : true, 
                            "multiKeyPaths" : {
                                "accountId" : [

                                ], 
                                "deleted" : [

                                ], 
                                "tagGroups.name" : [
                                    "tagGroups"
                                ], 
                                "tagGroups.tags" : [
                                    "tagGroups", 
                                    "tagGroups.tags"
                                ], 
                                "firstName" : [

                                ], 
                                "lastName" : [

                                ]
                            }, 
                            "isUnique" : false, 
                            "isSparse" : false, 
                            "isPartial" : false, 
                            "indexVersion" : 2.0, 
                            "direction" : "forward", 
                            "indexBounds" : {
                                "accountId" : [
                                    "[158.0, 158.0]"
                                ], 
                                "deleted" : [
                                    "[null, null]"
                                ], 
                                "tagGroups.name" : [
                                    "[MinKey, MaxKey]"
                                ], 
                                "tagGroups.tags" : [
                                    "[MinKey, MaxKey]"
                                ], 
                                "firstName" : [
                                    "[MinKey, MaxKey]"
                                ], 
                                "lastName" : [
                                    "[MinKey, MaxKey]"
                                ]
                            }
                        }
                    }, 
                    "rejectedPlans" : [
                        {
                            "stage" : "FETCH", 
                            "filter" : {
                                "deleted" : {
                                    "$eq" : null
                                }
                            }, 
                            "inputStage" : {
                                "stage" : "IXSCAN", 
                                "keyPattern" : {
                                    "accountId" : 1.0, 
                                    "mobile" : 1.0
                                }, 
                                "indexName" : "accountId_1_mobile_1", 
                                "isMultiKey" : false, 
                                "multiKeyPaths" : {
                                    "accountId" : [

                                    ], 
                                    "mobile" : [

                                    ]
                                }, 
                                "isUnique" : false, 
                                "isSparse" : false, 
                                "isPartial" : false, 
                                "indexVersion" : 2.0, 
                                "direction" : "forward", 
                                "indexBounds" : {
                                    "accountId" : [
                                        "[158.0, 158.0]"
                                    ], 
                                    "mobile" : [
                                        "[MinKey, MaxKey]"
                                    ]
                                }
                            }
                        }, 
                        {
                            "stage" : "FETCH", 
                            "filter" : {
                                "deleted" : {
                                    "$eq" : null
                                }
                            }, 
                            "inputStage" : {
                                "stage" : "IXSCAN", 
                                "keyPattern" : {
                                    "accountId" : 1.0, 
                                    "email" : 1.0
                                }, 
                                "indexName" : "accountId_1_email_1", 
                                "isMultiKey" : false, 
                                "multiKeyPaths" : {
                                    "accountId" : [

                                    ], 
                                    "email" : [

                                    ]
                                }, 
                                "isUnique" : false, 
                                "isSparse" : false, 
                                "isPartial" : false, 
                                "indexVersion" : 2.0, 
                                "direction" : "forward", 
                                "indexBounds" : {
                                    "accountId" : [
                                        "[158.0, 158.0]"
                                    ], 
                                    "email" : [
                                        "[MinKey, MaxKey]"
                                    ]
                                }
                            }
                        }, 
                        {
                            "stage" : "FETCH", 
                            "filter" : {
                                "deleted" : {
                                    "$eq" : null
                                }
                            }, 
                            "inputStage" : {
                                "stage" : "IXSCAN", 
                                "keyPattern" : {
                                    "accountId" : 1.0, 
                                    "_id" : 1.0
                                }, 
                                "indexName" : "accountId_1__id_1", 
                                "isMultiKey" : false, 
                                "multiKeyPaths" : {
                                    "accountId" : [

                                    ], 
                                    "_id" : [

                                    ]
                                }, 
                                "isUnique" : true, 
                                "isSparse" : false, 
                                "isPartial" : false, 
                                "indexVersion" : 2.0, 
                                "direction" : "forward", 
                                "indexBounds" : {
                                    "accountId" : [
                                        "[158.0, 158.0]"
                                    ], 
                                    "_id" : [
                                        "[MinKey, MaxKey]"
                                    ]
                                }
                            }
                        }, 
                        {
                            "stage" : "FETCH", 
                            "filter" : {
                                "deleted" : {
                                    "$eq" : null
                                }
                            }, 
                            "inputStage" : {
                                "stage" : "IXSCAN", 
                                "keyPattern" : {
                                    "accountId" : 1.0
                                }, 
                                "indexName" : "accountId_1", 
                                "isMultiKey" : false, 
                                "multiKeyPaths" : {
                                    "accountId" : [

                                    ]
                                }, 
                                "isUnique" : false, 
                                "isSparse" : false, 
                                "isPartial" : false, 
                                "indexVersion" : 2.0, 
                                "direction" : "forward", 
                                "indexBounds" : {
                                    "accountId" : [
                                        "[158.0, 158.0]"
                                    ]
                                }
                            }
                        }, 
                        {
                            "stage" : "FETCH", 
                            "filter" : {
                                "deleted" : {
                                    "$eq" : null
                                }
                            }, 
                            "inputStage" : {
                                "stage" : "IXSCAN", 
                                "keyPattern" : {
                                    "accountId" : 1.0, 
                                    "deleted" : 1.0, 
                                    "firstName" : 1.0, 
                                    "lastName" : 1.0
                                }, 
                                "indexName" : "accountId_1_deleted_1_firstName_1_lastName_1", 
                                "isMultiKey" : false, 
                                "multiKeyPaths" : {
                                    "accountId" : [

                                    ], 
                                    "deleted" : [

                                    ], 
                                    "firstName" : [

                                    ], 
                                    "lastName" : [

                                    ]
                                }, 
                                "isUnique" : false, 
                                "isSparse" : false, 
                                "isPartial" : false, 
                                "indexVersion" : 2.0, 
                                "direction" : "forward", 
                                "indexBounds" : {
                                    "accountId" : [
                                        "[158.0, 158.0]"
                                    ], 
                                    "deleted" : [
                                        "[null, null]"
                                    ], 
                                    "firstName" : [
                                        "[MinKey, MaxKey]"
                                    ], 
                                    "lastName" : [
                                        "[MinKey, MaxKey]"
                                    ]
                                }
                            }
                        }, 
                        {
                            "stage" : "FETCH", 
                            "filter" : {
                                "deleted" : {
                                    "$eq" : null
                                }
                            }, 
                            "inputStage" : {
                                "stage" : "IXSCAN", 
                                "keyPattern" : {
                                    "accountId" : 1.0, 
                                    "firstName" : 1.0, 
                                    "lastName" : 1.0
                                }, 
                                "indexName" : "accountId_1_firstName_1_lastName_1", 
                                "isMultiKey" : false, 
                                "multiKeyPaths" : {
                                    "accountId" : [

                                    ], 
                                    "firstName" : [

                                    ], 
                                    "lastName" : [

                                    ]
                                }, 
                                "isUnique" : false, 
                                "isSparse" : false, 
                                "isPartial" : false, 
                                "indexVersion" : 2.0, 
                                "direction" : "forward", 
                                "indexBounds" : {
                                    "accountId" : [
                                        "[158.0, 158.0]"
                                    ], 
                                    "firstName" : [
                                        "[MinKey, MaxKey]"
                                    ], 
                                    "lastName" : [
                                        "[MinKey, MaxKey]"
                                    ]
                                }
                            }
                        }, 
                        {
                            "stage" : "FETCH", 
                            "filter" : {
                                "deleted" : {
                                    "$eq" : null
                                }
                            }, 
                            "inputStage" : {
                                "stage" : "IXSCAN", 
                                "keyPattern" : {
                                    "accountId" : 1.0, 
                                    "externalId" : 1.0, 
                                    "deleted" : 1.0
                                }, 
                                "indexName" : "accountId_1_externalId_1_deleted_1", 
                                "isMultiKey" : false, 
                                "multiKeyPaths" : {
                                    "accountId" : [

                                    ], 
                                    "externalId" : [

                                    ], 
                                    "deleted" : [

                                    ]
                                }, 
                                "isUnique" : false, 
                                "isSparse" : false, 
                                "isPartial" : false, 
                                "indexVersion" : 2.0, 
                                "direction" : "forward", 
                                "indexBounds" : {
                                    "accountId" : [
                                        "[158.0, 158.0]"
                                    ], 
                                    "externalId" : [
                                        "[MinKey, MaxKey]"
                                    ], 
                                    "deleted" : [
                                        "[null, null]"
                                    ]
                                }
                            }
                        }, 
                        {
                            "stage" : "FETCH", 
                            "filter" : {
                                "deleted" : {
                                    "$eq" : null
                                }
                            }, 
                            "inputStage" : {
                                "stage" : "IXSCAN", 
                                "keyPattern" : {
                                    "accountId" : 1.0, 
                                    "deleted" : 1.0, 
                                    "tagGroups.name" : 1.0, 
                                    "tagGroups.tags" : 1.0
                                }, 
                                "indexName" : "accountId_1_deleted_1_tagGroups.name_1_tagGroups.tags_1", 
                                "isMultiKey" : true, 
                                "multiKeyPaths" : {
                                    "accountId" : [

                                    ], 
                                    "deleted" : [

                                    ], 
                                    "tagGroups.name" : [
                                        "tagGroups"
                                    ], 
                                    "tagGroups.tags" : [
                                        "tagGroups", 
                                        "tagGroups.tags"
                                    ]
                                }, 
                                "isUnique" : false, 
                                "isSparse" : false, 
                                "isPartial" : false, 
                                "indexVersion" : 2.0, 
                                "direction" : "forward", 
                                "indexBounds" : {
                                    "accountId" : [
                                        "[158.0, 158.0]"
                                    ], 
                                    "deleted" : [
                                        "[null, null]"
                                    ], 
                                    "tagGroups.name" : [
                                        "[MinKey, MaxKey]"
                                    ], 
                                    "tagGroups.tags" : [
                                        "[MinKey, MaxKey]"
                                    ]
                                }
                            }
                        }, 
                        {
                            "stage" : "FETCH", 
                            "filter" : {
                                "deleted" : {
                                    "$eq" : null
                                }
                            }, 
                            "inputStage" : {
                                "stage" : "IXSCAN", 
                                "keyPattern" : {
                                    "accountId" : 1.0, 
                                    "deleted" : 1.0
                                }, 
                                "indexName" : "accountId_1_deleted_1", 
                                "isMultiKey" : false, 
                                "multiKeyPaths" : {
                                    "accountId" : [

                                    ], 
                                    "deleted" : [

                                    ]
                                }, 
                                "isUnique" : false, 
                                "isSparse" : false, 
                                "isPartial" : false, 
                                "indexVersion" : 2.0, 
                                "direction" : "forward", 
                                "indexBounds" : {
                                    "accountId" : [
                                        "[158.0, 158.0]"
                                    ], 
                                    "deleted" : [
                                        "[null, null]"
                                    ]
                                }
                            }
                        }, 
                        {
                            "stage" : "FETCH", 
                            "filter" : {
                                "deleted" : {
                                    "$eq" : null
                                }
                            }, 
                            "inputStage" : {
                                "stage" : "IXSCAN", 
                                "keyPattern" : {
                                    "accountId" : 1.0, 
                                    "deleted" : 1.0, 
                                    "_id" : 1.0, 
                                    "firstName" : 1.0, 
                                    "lastName" : 1.0
                                }, 
                                "indexName" : "accountId_1_deleted_1__id_1_firstName_1_lastName_1", 
                                "isMultiKey" : false, 
                                "multiKeyPaths" : {
                                    "accountId" : [

                                    ], 
                                    "deleted" : [

                                    ], 
                                    "_id" : [

                                    ], 
                                    "firstName" : [

                                    ], 
                                    "lastName" : [

                                    ]
                                }, 
                                "isUnique" : false, 
                                "isSparse" : false, 
                                "isPartial" : false, 
                                "indexVersion" : 2.0, 
                                "direction" : "forward", 
                                "indexBounds" : {
                                    "accountId" : [
                                        "[158.0, 158.0]"
                                    ], 
                                    "deleted" : [
                                        "[null, null]"
                                    ], 
                                    "_id" : [
                                        "[MinKey, MaxKey]"
                                    ], 
                                    "firstName" : [
                                        "[MinKey, MaxKey]"
                                    ], 
                                    "lastName" : [
                                        "[MinKey, MaxKey]"
                                    ]
                                }
                            }
                        }
                    ]
                }, 
                "executionStats" : {
                    "executionSuccess" : true, 
                    "nReturned" : 153151.0, 
                    "executionTimeMillis" : 48409.0, 
                    "totalKeysExamined" : 6319057.0, 
                    "totalDocsExamined" : 153151.0, 
                    "executionStages" : {
                        "stage" : "FETCH", 
                        "filter" : {
                            "deleted" : {
                                "$eq" : null
                            }
                        }, 
                        "nReturned" : 153151.0, 
                        "executionTimeMillisEstimate" : 46454.0, 
                        "works" : 6319058.0, 
                        "advanced" : 153151.0, 
                        "needTime" : 6165906.0, 
                        "needYield" : 0.0, 
                        "saveState" : 49917.0, 
                        "restoreState" : 49917.0, 
                        "isEOF" : 1.0, 
                        "invalidates" : 0.0, 
                        "docsExamined" : 153151.0, 
                        "alreadyHasObj" : 0.0, 
                        "inputStage" : {
                            "stage" : "IXSCAN", 
                            "nReturned" : 153151.0, 
                            "executionTimeMillisEstimate" : 14458.0, 
                            "works" : 6319058.0, 
                            "advanced" : 153151.0, 
                            "needTime" : 6165906.0, 
                            "needYield" : 0.0, 
                            "saveState" : 49917.0, 
                            "restoreState" : 49917.0, 
                            "isEOF" : 1.0, 
                            "invalidates" : 0.0, 
                            "keyPattern" : {
                                "accountId" : 1.0, 
                                "deleted" : 1.0, 
                                "tagGroups.name" : 1.0, 
                                "tagGroups.tags" : 1.0, 
                                "firstName" : 1.0, 
                                "lastName" : 1.0
                            }, 
                            "indexName" : "accountId_1_deleted_1_tagGroups.name_1_tagGroups.tags_1_firstName_1_lastName_1", 
                            "isMultiKey" : true, 
                            "multiKeyPaths" : {
                                "accountId" : [

                                ], 
                                "deleted" : [

                                ], 
                                "tagGroups.name" : [
                                    "tagGroups"
                                ], 
                                "tagGroups.tags" : [
                                    "tagGroups", 
                                    "tagGroups.tags"
                                ], 
                                "firstName" : [

                                ], 
                                "lastName" : [

                                ]
                            }, 
                            "isUnique" : false, 
                            "isSparse" : false, 
                            "isPartial" : false, 
                            "indexVersion" : 2.0, 
                            "direction" : "forward", 
                            "indexBounds" : {
                                "accountId" : [
                                    "[158.0, 158.0]"
                                ], 
                                "deleted" : [
                                    "[null, null]"
                                ], 
                                "tagGroups.name" : [
                                    "[MinKey, MaxKey]"
                                ], 
                                "tagGroups.tags" : [
                                    "[MinKey, MaxKey]"
                                ], 
                                "firstName" : [
                                    "[MinKey, MaxKey]"
                                ], 
                                "lastName" : [
                                    "[MinKey, MaxKey]"
                                ]
                            }, 
                            "keysExamined" : 6319057.0, 
                            "seeks" : 1.0, 
                            "dupsTested" : 6319057.0, 
                            "dupsDropped" : 6165906.0, 
                            "seenInvalidated" : 0.0
                        }
                    }
                }
            }
        }, 
        {
            "$group" : {
                "_id" : {
                    "$const" : 1.0
                }, 
                "n" : {
                    "$sum" : {
                        "$const" : 1.0
                    }
                }
            }
        }
    ], 
    "ok" : 1.0, 
    "operationTime" : Timestamp(1583831635, 28), 
    "$clusterTime" : {
        "clusterTime" : Timestamp(1583831635, 28), 
        "signature" : {
            "hash" : BinData(0, "higLMSwtyXkmzuObjj6EgAsotPk="), 
            "keyId" : 6748499292626878465
        }
    }
}
4

I think I’m running into this: https://jira.mongodb.org/browse/SERVER-18861

If I remove the null check from the match, I get a COUNT_SCAN and executionTimeMillis is down to 420ms.

I need to filter out documents where the “deleted” field has a value, so how can I rewrite this and still get the COUNT_SCAN?

I’ve tried:

deleted: {$eq:null}

and:

deleted: {$type:10}

… but it’s still not using a covered query

5

With this modified query the explain output showed no FETCH stage and the query plan has a "stage" : "PROJECTION_COVERED".

Note that I created an index : { accountId: 1, deleted: 1 }. I am using MongoDB Enterprise version 4.2.3 on a local PC.

db.test.aggregate( [
  { 
      $match: { accountId: 12 } 
  },
  { 
      $group: { 
          _id: null, 
          n: { 
              $sum: { 
                  $cond: [ { $eq: [ "$deleted", null ] }, 1, 0 ]  
              } 
          } 
      } 
  }
] )

The query plan (part of it):

{
        "stages" : [
                {
                        "$cursor" : {
                                "query" : {
                                        "accountId" : 12
                                },
                                "fields" : {
                                        "deleted" : 1,
                                        "_id" : 0
                                },
                                "queryPlanner" : {
                                        "plannerVersion" : 1,
                                        "namespace" : "text.test",
                                        "indexFilterSet" : false,
                                        "parsedQuery" : {
                                                "accountId" : {
                                                        "$eq" : 12
                                                }
                                        },
                                        "queryHash" : "DED4FE97",
                                        "planCacheKey" : "C9C471AD",
                                        "winningPlan" : {
                                                "stage" : "PROJECTION_COVERED",
                                                "transformBy" : {
                                                        "deleted" : 1,
                                                        "_id" : 0
                                                },
                                                "inputStage" : {
                                                        "stage" : "IXSCAN",
                                                        "keyPattern" : {
                                                                "accountId" : 1,
                                                                "deleted" : 1
                                                        },
                                                        "indexName" : "accountId_1_deleted_1",
                                                        "isMultiKey" : false,
                                                        ...
}
6

Thanks!

I’m getting a PROJECTION stage (not a PROJECTION_COVERED), but my query now runs in under a sec. That’s a huge improvement (from 30+ secs)

For this to work with the c# driver, I guess I need to construct the aggregate pipeline manually instead of using the countDocumentsAsync method.

Anyway, thanks a lot for your suggestion.