[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() , { , { , |
| 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 ] |
|
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, |