Why this query is not using the index? ( keysExamined: 0 docsExamined: 27417057 )

[kfern]

I have a logs collection with more than 27M records:

This collection is indexed by a timestamp field named "createdAt" at logs.class.js

  this.Model = db.collection('logs');
  this.Model.createIndex( { createdAt: -1 }, { name: 'createdAt_1' });    

db.logs.getIndexes()

[
    {
      "v": 2,
      "key": {
        "createdAt": -1
      },
      "name": "createdAt_1",
      "ns": "dbtest.logs"
    },
  ]

I enabled mongodb profiling for testing:

db.setProfilingLevel(1, { slowms: 2000 })

A query like "{ '$limit': '10', '$skip': '0', '$sort': { createdAt: '-1' } }" is very slow:

show profile

command: {
    "aggregate": "logs",
    "pipeline": [
      {
        "$match": {}
      },
      {
        "$group": {
          "_id": 1,
          "n": {
            "$sum": 1
          }
        }
      }
    ],
    "cursor": {}
  } keysExamined: 0 docsExamined: 27417057 cursorExhausted numYield: 214195 locks: {
    "Global": {
      "acquireCount": {
        "r": NumberLong(428398)
      }
    },
    "Database": {
      "acquireCount": {
        "r": NumberLong(214199)
      }
    },
    "Collection": {
      "acquireCount": {
        "r": NumberLong(214198)
      }
    }
  }

Why this query is not using the index? ( keysExamined: 0 docsExamined: 27417057 )

Can I do something?

Steps to reproduce

• Fill a mongodb collection with 27M
• Search using find

Expected behavior

Fast results

Actual behavior

Slow results

System configuration

Module versions (especially the part that's not working): "@feathersjs/feathers": "^4.5.11", "feathers-mongodb": "^6.3.0", "mongodb": "^3.6.10", "mongodb-core": "^3.2.7",

MongoDB versión: Tested with 3.4 and 4.4.7 NodeJS version: 12.22.1 Operating System: Ubuntu 18.04 & Ubuntu 21.04

NOTE: The following stack works fine:

MongoDB versión: 3.4 

"@feathersjs/feathers": "^3.3.1",
"feathers-mongodb": "^2.9.1",
"mongodb": "^2.2.35",
"mongodb-core": "^3.2.7",

[kfern]

UPDATE: Using mongo shell, this queries are fast:

db.logs.find({"createdAt" : 1404205105000})
db.logs.find().limit(10).sort({ createdAt: -1 })
db.logs.find().limit(10).sort({ createdAt: 1 })
db.logs.find().sort({ createdAt: -1 }).limit(10)
db.logs.find().sort({ createdAt: 1 }).limit(10)

[DaddyWarbucks]

Have you tried the useEstimatedDocumentCount outlined in the README? That may be helpful.

[kfern]

@DaddyWarbucks Thanks a lot. useEstimatedDocumentCount = true solves the problem