[SERVER-48430] MongoDB is choosing the wrong index / execution plan. Created: 27/May/20  Updated: 19/Jun/20  Resolved: 27/May/20

Status: Closed
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: 3.4.23
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Anand Singh Assignee: Unassigned
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Participants:

 Description   

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 ?

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



 Comments   
Comment by Anand Singh [ 19/Jun/20 ]

Hi carl.champain,

We have upgraded MongoDB to 3.6 version but we are  seeing  this issue .

Comment by Carl Champain (Inactive) [ 27/May/20 ]

Hi anansingh@netgear.com,

MongoDB 3.4 is already end-of-lifed, which means it is unsupported by us. We strongly recommend you upgrade to a supported version. If you need assistance troubleshooting, I encourage you to ask our community by posting on the MongoDB Community Forums or on Stack Overflow with the mongodb tag. I will now close this ticket.

Kind regards,
Carl

Generated at Thu Feb 08 05:17:06 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.