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

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

XMLWordPrintableJSON

    • 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.

        1. SERVER-69257-bench.js
          2 kB
          Milena Ivanova

            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: