Why MongoDB queries does COLLSCAN instead of INDEXSCAN?

Question

There are the indexes created on a collection named "taches"

> db.taches.getIndexes()
[
    {
        "v" : 2,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "test.taches"
    },
    {
        "v" : 2,
        "key" : {
            "idProjet" : -1
        },
        "name" : "idProjet_-1",
        "ns" : "test.taches"
    },
    {
        "v" : 2,
        "key" : {
            "id" : -1
        },
        "name" : "id_-1",
        "ns" : "test.taches"
    }
]
>

Theses two exemples show that theses queries are not using the indexes, and they examinate all the collections documents, don't understand why ? :

2023-03-26T15:36:41.691+0000 I  COMMAND  [conn11] command XXXX.taches command: find { find: "taches", filter: { idProjet: { $in: [ 73293, 71255, 71256, 75455, 75454, 75456, 75847, 75846, 75853, 75850, 75851, 75852, 75872, 75871, 75873, 75874, 75880, 75878, 75879, 75881, 76157, 76156, 76158, 76188, 76186, 76189, 76190, 76191, 76477, 76474, 76478, 77021, 77020, 77022, 77023, 77362, 77359, 77360, 77361, 77421, 77419, 77420, 78055, 78052, 78053, 78054, 78090, 78089, 78091, 78794, 78791, 78792, 78793, 79059, 79058, 79063, 79064 ] } }, projection: { id: 1, idProjet: 1, nom: 1, status: 1, ordre: 1 }, $db: "XXXX" } planSummary: COLLSCAN cursorid:7041921742719850182 keysExamined:0 docsExamined:564790 numYields:4412 nreturned:101 queryHash:D536B4A0 planCacheKey:D536B4A0 reslen:10061 locks:{ ReplicationStateTransition: { acquireCount: { w: 4413 } }, Global: { acquireCount: { r: 4413 } }, Database: { acquireCount: { r: 4413 } }, Collection: { acquireCount: { r: 4413 } }, Mutex: { acquireCount: { r: 1 } } } storage:{} protocol:op_query 385ms

2023-03-26T15:47:56.482+0000 I  COMMAND  [conn11] command XXXX.taches command: find { find: "taches", filter: { id: 939185 }, $db: "XXXX" } planSummary: COLLSCAN keysExamined:0 docsExamined:593553 cursorExhausted:1 numYields:4637 nreturned:1 queryHash:6DAB46EC planCacheKey:6DAB46EC reslen:1207 locks:{ ReplicationStateTransition: { acquireCount: { w: 4638 } }, Global: { acquireCount: { r: 4638 } }, Database: { acquireCount: { r: 4638 } }, Collection: { acquireCount: { r: 4638 } }, Mutex: { acquireCount: { r: 1 } } } storage:{} protocol:op_query 393ms

Thanks a lot

I tried many indexes rules

{ "_id" : ObjectId("5c51d06e67d8b06eb0676293"), "id" : NumberInt(1), "idProjet" : NumberInt(1), "nom" : "Profile Front", "status" : "done", "heuresEstim" : NumberInt(0), "contacts" : [ NumberInt(3) ], "notes" : [ ], "dateDebut" : "", "dateFin" : "", "groupes" : [ ] } — Bastien Bonhoure, Mar 26 '23 at 16:16
db.tester.find({id: { $in: [ 73293, 71255, 71256, 75455, 75454, 75456, 75847, 75846, 75853, 75850, 75851, 75852, 75872, 75871, 75873, 75874, 75880, 75878, 75879, 75881, 76157, 76156, 76158, 76188, 76186, 76189, 76190, 76191, 76477, 76474, 76478, 77021, 77020, 77022, 77023, 77362, 77359, 77360, 77361, 77421, 77419, 77420, 78055, 78052, 78053, 78054, 78090, 78089, 78091, 78794, 78791, 78792, 78793, 79059, 79058, 79063, 79064 ] } }).explain() , i have used this cmd, you can check index — Mahesh Bhatnagar, Mar 26 '23 at 16:40
Things is with this queries, the CPU increase at 500% (we have 32 vcpu available) — Bastien Bonhoure, Mar 26 '23 at 16:45
Is the `XXXX` database name from the log files the same as `test`from your `.getIndexes()` output? Said another way, make sure the indexes are in the correct namespace — user20042973, Mar 26 '23 at 17:53
Oh i change it to not show the right database name. It seems that the query is more complexe than the index so Mongo Prefer using COLLSCAN insteaf of INDEXSCAN — Bastien Bonhoure, Mar 26 '23 at 20:21

score 0 · Answer 1 · answered Mar 26 '23 at 22:07

There is definitely some mistake in your sample code, recreating your scenario; it works as expected (IXSCAN):

db.testCollection.drop();
var arr = [...Array(5000).keys()];
var searchIds = [];

var insertManyArray = [];
arr.forEach(item => {
  insertManyArray.push({ id: item + 7000, name: item.toString() })
  if(item % 3 == 0)
    searchIds.push(item)
});

db.testCollection.insertMany(insertManyArray);
db.testCollection.createIndex({id: -1})
db.testCollection.find({id: { $in: searchIds }}).explain()

Explain plan

"winningPlan": {
  "stage": "FETCH",
  "inputStage": {
    "stage": "IXSCAN",
    "keyPattern": {
      "id": -1
    },
    "indexName": "id_-1",

Why MongoDB queries does COLLSCAN instead of INDEXSCAN?

1 Answers1