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
}
]