1

In the logs I found slow queries, but when running them manually it is fast or at least taking indexes into account.
The logs tell me the query part is not using an index, which is available.
Running the query takes the index into account.
Running on AWS DocumentDB (which is MongoDB 3.6 mostly compatible).
The logs tell me this (anonymized field names for the internets ;-) ) :

{"op":"update",
"ts":1578041832906,
"ns":"collection",
"command":{
  "q":{"array.field1":{"$binary":"Hzy2Q0TBYEAWadeCtxpXuA==","$type":"3"}},
  "u":{"$set":{"array.$.field2":174}}},
  "nMatched":0,
  "nModified":0,
  "protocol":"op_query",
  "millis":694,
  "planSummary":"IXSCAN",
  "execStats":{
    "stage":"UPDATE",
    "nReturned":"0",
    "executionTimeMillisEstimate":"694.497",
    "inputStages":[{
      "stage":"LIMIT_SKIP",
      "nReturned":"0",
      "executionTimeMillisEstimate":"694.488",
      "inputStage":{
        "stage":"COLLSCAN",
        "nReturned":"0",
        "executionTimeMillisEstimate":"694.486"
      }
    },{
     "stage":"IXSCAN",
     "nReturned":"0",
     "executionTimeMillisEstimate":"694.492", 
     "indexName":"_idDocField3",
     "direction":"forward"
    }]
  },
}

When running an explain for this on the update

collection.explain().update(
    {"array.field1": BinData(3, "Hzy2Q0TBYEAWadeCtxpXuA==")},
    {$set: {"array.$.field2":174}}
)

the queryPlanner shows me this

{
  "queryPlanner": {
    "plannerVersion": 1,
    "namespace": "collection",
    "winningPlan": {
      "stage": "UPDATE",
      "inputStages": [
        {
          "stage": "LIMIT_SKIP",
          "inputStage": {
            "stage": "FETCH",
            "inputStage": {
              "stage": "IXSCAN",
              "indexName": "arrayField1"
            }
          }
        },
        {
          "stage": "IXSCAN",
          "indexName": "_idDocField3",
          "direction": "forward"
        }
      ]
    }
  },
  "serverInfo": {
    "host": "xxxxxxx",
    "port": 27017,
    "version": "3.6.0"
  },
  "ok": 1
}

Indexes available are:

  • _id_ { _id:1 }, ops: ~56,000,
  • _idDocField3 { _id: 1, docField3: 1 }, ops: ~4,200,000,
  • arrayField1 { "array.field1": 1 }, ops: ~400,
  • arrayField1DocField3 { "array.field1": 1, docField3: 1 }, ops: 0

array is an array containing a document and the ids are UUID.
The collection size is about 20,000,000

Could it be an idea to remove the compound indexes and rely on index intersection?
Or switch the order of the compound indexes?
Anyone who can help me optimizing this?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.