Under what circumstances would mongo use a compond index for a query that does not match the prefix fields of the index?

user_11349316 0
Viewed 24

When explaining a query on a collection having these indexes:

{"user_id": 1, "req.time_us": 1}
{"user_id": 1, "req.uri":1, "req.time_us": 1}

with command like:

db.some_collection.find({"user_id":12345,"req.time_us":{"$gte":1657509059545812,"$lt":1667522903018337}}).limit(20).explain("executionStats")

The winning plan was:

"inputStage" : {
    "stage" : "IXSCAN",
    "nReturned" : 20,
    "executionTimeMillisEstimate" : 0,
    "works" : 20,
    "advanced" : 20,
    ...
    "keyPattern" : {
        "user_id" : 1,
        "req.uri" : 1,
        "req.time_us" : 1
    },
    "indexName" : "user_id_1_req.uri_1_req.time_us_1",
    "isMultiKey" : false,
    "multiKeyPaths" : {
        "user_id" : [ ],
        "req.uri" : [ ],
        "req.time_us" : [ ]
    },
    ...
    "indexVersion" : 2,
    "direction" : "forward",
    "indexBounds" : {
        "user_id" : [
            "[23456.0, 23456.0]"
        ],
        "req.uri" : [
            "[MinKey, MaxKey]"
        ],
        "req.time_us" : [
            "[1657509059545812.0, 1667522903018337.0)"
        ]
    },
    "keysExamined" : 20,
    "seeks" : 1,
    ...
}

Why was the index user_id_1_req.uri_1_req.time_us_1 used but not user_id_1_req.time_us_1? Since the official manual says a compound index can supports queries that match the prefix fields of the index.

mongodb
1 Answers

This behavior can be explained in the docs documentation page. To paraphrase:

MongoDB runs the query optimizer to choose the winning plan and executes the winning plan to completion.

During plan selection, if there are more than one index that can satisfy a query, MongoDB will run a trial using all the valid plans to determine which one performed to be the best. You can read about this process more here.

As of MongoDB 3.4.6, the plan selection involves running candidate plans in parallel in a "race", and see which candidate plan returns 101 results first.

So basically these 2 indexes had a mini competition and the "wrong" index one, this can happen as these competitions can be heavily skewed depending on data distribution for similar indexes. ( For example imagine the first 101 documents in the collection match the query then the "better" index will actually be slower as it will continue to scan the index tree deeper while the "worse" index start fetching them immediately)

I recommend for cases like this to use $hint which essentially forces Mongo to use the index you deem most fit.

user_107703700
Related