executionTimeMillis is much larger than executionTimeMillisEstimate

Working with Data
,
#1

I’m analyzing a count command on a collection, which has 440k docs:

db.collection.count({"a" : 100, "b" : {$in : [1, 4]}, "c" : -1, "d" : {$in : [1,2,3]}, "e" : "hello"})

In order to improve the performance, I create the following index:

{"a" : -1, "b" : -1, "c" : -1, "d" : -1, "e" : -1}

The result of explain shows that mongodb does a IXSCAN step to do the counting. However, I’m confused by the executionTimeMillis:

{
    "executionStats": {
        "executionSuccess": true,
        "nReturned": 141603,
        "executionTimeMillis": 177,   <-------------
        "totalKeysExamined": 141604,
        "totalDocsExamined": 0,
        "executionStages": {
            "stage": "IXSCAN",
            "nReturned": 141603,
            "executionTimeMillisEstimate": 13   <-----------
        }
    }
}

You can see that executionStats.executionTimeMillis is much larger than executionStats.executionStages.executionTimeMillisEstimate .

It seem that the IXSCAN step only takes 13ms, but why count takes 117ms? Is it possible to optimize this?

#2

@new_sewe and welcome to the community.

executionStats.executionTimeMillis is the overall time for execution. This time is not only the time that the query runs, but it includes the time it takes to generate/pick the execution plan. Do you have multiple indexes that could be used to satisfy this query? Any indexes that have any of the fields in the query will be considered when building out a plan.

As for index optimization, you generally want to have your equality match fields (‘a’, ‘c’ and ‘e’) in your example as the left most fields in your index. Which of those would be placed first would be based on your data and how selective it is. This means the first field in the index would be the one that filters out the most data to be returned.

After your equality match fields, the next fields in the index will generally be used for sorting. The last fields in the index will be used for inequality/range based matches (‘b’ and ‘e’ in your query).

Indexing is an art that must be practiced to get right. Make sure you don’t have unnecessary indexes, and make sure the indexes you do have are optimized for the queries you run the most.

1 Like