Why no index is used in a findAndUpdate command?

Hi,
I see in the Profiler the following findOneAndUpdate query where an index is not used although there is an index that satisfies the “query” part, and I am not sure why.

Here is the entire info about the command from the Profiler:

{
  "type": "command",
  "ns": "xxx.myCollection",
  "command": {
    "findAndModify": "myCollection",
    "query": {
      "providerEventId": "xyz"
    },
    "remove": false,
    "new": true,
    "upsert": true,
    "writeConcern": {
      "w": "majority"
    },
    "update": {
      "$setOnInsert": {
        "field1": "value1",
        "field2": "value2"
      },
      "$set": {
        "providerEventId": "xyz",
        "field1": "value1",
        "field2": "value2",
        "field3": "value3"
      }
    },
    "collation": {
      "locale": "en",
      "strength": 2
    },
    "lsid": {
      "id": {
        "$binary": {
          "base64": "6xe+nFKiSOubKGJNTRVVVw==",
          "subType": "04"
        }
      }
    },
    "txnNumber": 14,
    "$clusterTime": {
      "clusterTime": {
        "$timestamp": {
          "t": 1712161687,
          "i": 4
        }
      },
      "signature": {
        "hash": {
          "$binary": {
            "base64": "uRAa2cTG4n11XYyAwuzBPnrN8CQ=",
            "subType": "00"
          }
        },
        "keyId": 7295816926595383000
      }
    },
    "$db": "xxx"
  },
  "planSummary": "COLLSCAN",
  "totalOplogSlotDurationMicros": 176,
  "keysExamined": 0,
  "docsExamined": 97726,
  "nMatched": 0,
  "nModified": 0,
  "nUpserted": 1,
  "keysInserted": 6,
  "numYields": 97,
  "queryHash": "4E49A046",
  "planCacheKey": "21DB8781",
  "reslen": 703,
  "locks": {
    "ParallelBatchWriterMode": {
      "acquireCount": {
        "r": 99
      }
    },
    "FeatureCompatibilityVersion": {
      "acquireCount": {
        "w": 99
      }
    },
    "ReplicationStateTransition": {
      "acquireCount": {
        "w": 100
      }
    },
    "Global": {
      "acquireCount": {
        "w": 99
      }
    },
    "Database": {
      "acquireCount": {
        "w": 99
      }
    },
    "Collection": {
      "acquireCount": {
        "w": 99
      }
    },
    "Mutex": {
      "acquireCount": {
        "r": 2
      }
    }
  },
  "flowControl": {
    "acquireCount": 98,
    "timeAcquiringMicros": 59
  },
  "readConcern": {
    "level": "local",
    "provenance": "implicitDefault"
  },
  "writeConcern": {
    "w": "majority",
    "wtimeout": 0,
    "provenance": "clientSupplied"
  },
  "storage": {
    "data": {
      "bytesRead": 24122,
      "timeReadingMicros": 3489
    }
  },
  "remote": "0.0.0.0:34052",
  "protocol": "op_msg",
  "durationMillis": 100,
  "v": "6.0.14",
  "isTruncated": false
}

And here is the result of the getIndexes() on the relevant collection:

idxs

You can see that there is an index on providerEventId which is the only field specified on the query (and there is no sort at all).

Any idea?
Thx.

Hi @YuvalW

The upsert is using a collation {locale:'en', strength:2} the index does not have a matching collation so it is not being used for this operation.

Remove the collation from the upsert or create an Index that supports the collation.

4 Likes

Oh gee thank you! That collection should not have any collation indeed but its “mongoose schema” is wrongly defined…

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.