Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-48987

MongoDB is choosing the wrong index / execution plan.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major - P3
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: Backlog
    • Component/s: Indexing
    • Labels:
    • Operating System:
      ALL
    • Sprint:
      Query 2020-07-27, Query 2020-08-10, Query 2020-08-24, Query 2020-09-07

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

        Attachments

          Activity

            People

            Assignee:
            backlog-query-optimization Backlog - Query Optimization
            Reporter:
            anansingh@netgear.com Anand Singh
            Participants:
            Votes:
            0 Vote for this issue
            Watchers:
            11 Start watching this issue

              Dates

              Created:
              Updated: