MongoDB is choosing the wrong index / execution plan

Ops and Admin
, ,
1

In load MongoDB log file, we can see a lot of slow queries. Most of them are not using the best index and consequently the best execution plan is not used. However, when I run the same queries by myself in mongo shell, the correct index is used. So why for same query, we don’t have same execution plan ? MongoDB V:3.4.23

Kindly find the log below using wrong index :

2020-05-25T04:55:51.624+0000 I COMMAND [conn301319] command mydb.mycoll command: aggregate { aggregate: “mycoll”, pipeline: [ { $match: { networkId.$id:{ $in: [ ObjectId(‘5e0ed9eb60b2533bda7a0fa8’) ] }, status: “0”, alarmType: “1” } }, { $group: { _id:
{ networkId: “$networkId” }, alarmCount: { $sum: 1 } } } ] } planSummary: IXSCAN { status: 1 } keysExamined:35350 docsExamined:35350 numYields:280 nreturned:0 reslen:135 locks:{ Global: { acquireCount:{ r: 574 }, acquireWaitCount: { r: 92 }, timeAcquiringMicros: { r: 1590945 } }, Database: { acquireCount:{ r: 287 }}, Collection: { acquireCount:{ r: 286 }
} } protocol:op_query 1980ms

Index On collection:

db.getCollection('mycoll').getIndexes()
[
   {
      "v":1,
      "key":{
         "_id":1
      },
      "name":" *id* ",
      "ns":"mydb.mycoll"
   },
   {
      "v":1,
      "key":{
         "networkId.$id":1,
         "status":1,
         "alarmType":1
      },
      "name":"networkId.$id_1_status_1_alarmType_1",
      "ns":"mydb.mycoll"
   },
   {
      "v":2,
      "key":{
         "status":1
      },
      "name":"status_1",
      "ns":"mydb.mycoll",
      "background":true
   }
]
2

Could you please repost your code using the code or pre html element so that we get proper indentation? That would make it easier to understand it.

3

can you check it again ? I have updated the logs .

4

I really do not know why it uses the index ‘status’. However I would ask myself a few questions.

  1. Is the index status really needed?
  2. Could it be made a partial index by removing the value 0? This might help depending of the use cases.
  3. Could I change the order of the index I want by swapping the order of alarmType and status?
  4. Depending on the relation between the status and alarmType, an index on alarmType might be more suited. For example if status:0 means there is an alarm, I would have an index on alarmType, specially if the field is not present in document where status is 0. The index will potentially be much smaller.

I know you can specify index hint to find() but I do not know if there is an equivalent for $match. I tried to find it but to no avail. But in this particular case since you only counting alarms, you could use find() and provide the hint.

5

Thanks for the reply.Find you answers below
1-Yes it is need we are removing data from “mycoll” on the basis of status .
2-No
3-staus and alarmType both have the same number of possible values . for status we have 0,1,2 and for alarmType we have 1,2,3 .So that swapping the order of alarmType and status does not make any changes.
4.We have both the key present for all the records .