Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-69257

COUNT_SCAN plan not selected when unfiltered $group + $sum on _id performed

    • Type: Icon: Bug Bug
    • Resolution: Unresolved
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: Query Execution
    • None
    • Query Optimization
    • ALL
    • QO 2022-09-19, QO 2023-08-07, QO 2023-08-21

      As of DRIVERS-501 the driver CRUD specification defines a pipeline to be used to count documents without resorting to the count command (due to potential inaccurate counts without query predicates).

      db.foo.drop();
      db.foo.insertMany([{},{},{},{},{}]);
      
      // Driver CRUD spec definition for countDocuments ////////////////////
      filter = {}
      skip   = null
      limit  = null
      
      pipeline = [{'$match': filter}]
      if (skip) {
        pipeline.push({'$skip': skip})
      }
      if (limit) {
        pipeline.push({'$limit': limit})
      }
      pipeline.push({'$group': {'_id': 1, 'n': {'$sum': 1}}})
      //////////////////////////////////////////////////////////////////////
      
      db.foo.explain("executionStats").aggregate(pipeline)
      

      Without specifying a filter the empty $match stage would result in a COLLSCAN plan winning, though a COUNT_SCAN should be appropriate.

      /*
      "nReturned": 5,
      "executionTimeMillis": 0,
      "totalKeysExamined": 0,
      "totalDocsExamined": 5,
      "executionStages": {
        "stage": "COLLSCAN",
      */
      

      This could be worked around by unshifting a {$sort: { _id: 1 }} stage to the pipeline, however after discussing with david.storch@mongodb.com this may be addressable server-side.

            Assignee:
            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Reporter:
            alex.bevilacqua@mongodb.com Alex Bevilacqua
            Votes:
            0 Vote for this issue
            Watchers:
            17 Start watching this issue

              Created:
              Updated: