Query having large Keys Examined value even after index is used

Working with Data
, ,
1

I have a collection where there are 2 attributes as below:-

#<Aggregate::UniqueJob:0x00007f456ddf1380> {
           :_id => BSON::ObjectId('647f39ea1c1ea08d1ac6a7aa'),
     :arguments => [
        [0] 234,
        [1] 7186,
        [2] "course",
        [3] "update_method_name!"
    ],
     :job_class => "JobName"
}

Values for arguments attribute could have variable elements in the array.

    "filter": {
      "job_class": "JobName",
      "arguments": [
        "ClassName",
        42322,
        170849,
        81,
        468,
        "update_method_name!"
      ]
    },

When there is where query applied on arguments and job_class query execution time is faster but Key Examined value (~40 k) is going very high as compared to Docs returned (0/1) even after index on ‘arguments’ column is used.

So what could be the case that even after index being used Key Examined values are high? and can this be the reason for CPU utilization spike up?

3

Hi @Viraj_Chheda and welcome to MongoDB community forums!!

If the keyExamined value is higher than docsReturned value, it means that more index entries were examined during the query execution than the number of matching documents found.

This may occur because of the following reasons:

  1. When the indexes not have much distinct value which makes the query scan more documents the retuning the matching documents.

  2. This would also depend on how the query has been written for it to make efficient use of the indexes.
    For instance, I tried to replicate this in my local environment using version 6.0.5.
    Sample data:

test> db.testarry.findOne()
{
  _id: ObjectId("64898d1834d0ff0b9c218515"),
  names: [ 'A', 'B', 'C', 8, 5, 7 ]
}

Index is created on names field and I use the
test> db.testarry.find({ names: { $gt: 7}}).explain('executionStats')
which gives me

....
executionStats: {
    executionSuccess: true,
    nReturned: 304,
    executionTimeMillis: 4,
    totalKeysExamined: 893,
    totalDocsExamined: 304,
    executionStages: {
....

which is similar to what you are seeing.

Can you provide the query details and your expectations for the given sample document which would give us more clarity about the issue?

  1. Finally, while using the query, are you experiencing any performance issue with the query being used.

Additionally, please provide the following information to help me efficiently understand and address the issue:

  1. The explain output of the query you’re using.
  2. The deployment architecture (standalone, replica set, or sharded cluster).
  3. The version of MongoDB you’re using.
  4. The index definition for the arguments field.

Finally, my recommendation would be to follow the documentation on Indexing Strategies to find efficient method for using indexes.

Regards
Aasawari

4

Hi @aasawari_sahasrabuddhe
I am also facing a similar issue in our production environment where we have for a query
“keysExamined”:1405142,“docsExamined”:1405142,“nreturned”:4
“fromMultiPlanner”:true,“cursorExhausted”:true,“numYields”:4902,
we saw this query read approximately → “bytesRead”:29890117105 and “durationMillis”:58425

query:
“command”:
{“aggregate”:“collectionName”,“pipeline”:[{“$match”:{“$and”:[{“ApplicationId”:“abcd”},
{“Values”:{“$elemMatch”:{“k”:“a”,“v._v.v”:“646c”}}},{“Values”:{“$elemMatch”:{“k”:“j”,“v._v.v”:“646e”}}}]}},
{“$match”:{“$or”:[{“Allowed._id”:{“$exists”:false}},{“Allowed._id”:{“$in”:[“de81”,“426f”]}}]}},
{“$group”:{“_id”:{“avs9n”:“$ValuesDocument.avs9n”},“count”:{“$sum”:1}}}]

The deployment architecture for us is replica set of 3 - 1 primary, 2 secondary
we are using version 6 for MongoDB

we have a compund index on Values.k_1_Values.v._v.v_1_ApplicationId_1_TrackingId_1
where Values is an Array and ApplicationId and TrackingId are string

This operation also appeared to be running at the time when we observed a spike in replication lag.

Any suggestions/recommendations here? We have created all the indexes the performance advisor in MongoDB atlas suggested