MongoDB query choosing the wrong index in winning plan, Though in executionTimeMillisEstimate as lower for the other index?

Question

MongoDB Query chooses the wrong index in the winning plan. I have two indexes for the same field, one is a single field index and a Compound index with another field.

Eg. Field name: Field1, Contains Yes or No Field name: Field2, Contains 0 or 1 or 2 or 3

Index 1: {'Field1':1} Single Field Index Index 2: {'Field1':1,'Field2':1} Compound Index.

On Search Query {'Field1':'Yes'} for Field1 it uses the compound index, instead of single key index. Attached below is the query execution plan.

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "xxxx",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "Field1" : {
                "$eq" : "Yes"
            }
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "Field1" : 1,
                    "Field2" : 1
                },
                "indexName" : "Field1_Field2_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "Field1" : [],
                    "Field2" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "Field1" : [ 
                        "[\"Yes\", \"Yes\"]"
                    ],
                    "Field2" : [ 
                        "[MinKey, MaxKey]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ 
            {
                "stage" : "FETCH",
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "Field1" : 1
                    },
                    "indexName" : "Field1_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "Field1" : []
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "Field1" : [ 
                            "[\"Yes\", \"Yes\"]"
                        ]
                    }
                }
            }
        ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 762490,
        "executionTimeMillis" : 379131,
        "totalKeysExamined" : 762490,
        "totalDocsExamined" : 762490,
        "executionStages" : {
            "stage" : "FETCH",
            "nReturned" : 762490,
            "executionTimeMillisEstimate" : 377572,
            "works" : 762491,
            "advanced" : 762490,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 16915,
            "restoreState" : 16915,
            "isEOF" : 1,
            "invalidates" : 0,
            "docsExamined" : 762490,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 762490,
                "executionTimeMillisEstimate" : 1250,
                "works" : 762491,
                "advanced" : 762490,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 16915,
                "restoreState" : 16915,
                "isEOF" : 1,
                "invalidates" : 0,
                "keyPattern" : {
                    "Field1" : 1,
                    "Field2" : 1
                },
                "indexName" : "Field1_Field2_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "Field1" : [],
                    "Field2" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "Field1" : [ 
                        "[\"Yes\", \"Yes\"]"
                    ],
                    "Field2" : [ 
                        "[MinKey, MaxKey]"
                    ]
                },
                "keysExamined" : 762490,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0
            }
        },
        "allPlansExecution" : [ 
            {
                "nReturned" : 101,
                "executionTimeMillisEstimate" : 0,
                "totalKeysExamined" : 101,
                "totalDocsExamined" : 101,
                "executionStages" : {
                    "stage" : "FETCH",
                    "nReturned" : 101,
                    "executionTimeMillisEstimate" : 0,
                    "works" : 101,
                    "advanced" : 101,
                    "needTime" : 0,
                    "needYield" : 0,
                    "saveState" : 10,
                    "restoreState" : 10,
                    "isEOF" : 0,
                    "invalidates" : 0,
                    "docsExamined" : 101,
                    "alreadyHasObj" : 0,
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "nReturned" : 101,
                        "executionTimeMillisEstimate" : 0,
                        "works" : 101,
                        "advanced" : 101,
                        "needTime" : 0,
                        "needYield" : 0,
                        "saveState" : 10,
                        "restoreState" : 10,
                        "isEOF" : 0,
                        "invalidates" : 0,
                        "keyPattern" : {
                            "Field1" : 1
                        },
                        "indexName" : "Field1_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "Field1" : []
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "Field1" : [ 
                                "[\"Yes\", \"Yes\"]"
                            ]
                        },
                        "keysExamined" : 101,
                        "seeks" : 1,
                        "dupsTested" : 0,
                        "dupsDropped" : 0,
                        "seenInvalidated" : 0
                    }
                }
            }, 
            {
                "nReturned" : 101,
                "executionTimeMillisEstimate" : 260,
                "totalKeysExamined" : 101,
                "totalDocsExamined" : 101,
                "executionStages" : {
                    "stage" : "FETCH",
                    "nReturned" : 101,
                    "executionTimeMillisEstimate" : 260,
                    "works" : 101,
                    "advanced" : 101,
                    "needTime" : 0,
                    "needYield" : 0,
                    "saveState" : 10,
                    "restoreState" : 10,
                    "isEOF" : 0,
                    "invalidates" : 0,
                    "docsExamined" : 101,
                    "alreadyHasObj" : 0,
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "nReturned" : 101,
                        "executionTimeMillisEstimate" : 0,
                        "works" : 101,
                        "advanced" : 101,
                        "needTime" : 0,
                        "needYield" : 0,
                        "saveState" : 10,
                        "restoreState" : 10,
                        "isEOF" : 0,
                        "invalidates" : 0,
                        "keyPattern" : {
                            "Field1" : 1,
                            "Field2" : 1
                        },
                        "indexName" : "Field1_Field2_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "Field1" : [],
                            "Field2" : []
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "Field1" : [ 
                                "[\"Yes\", \"Yes\"]"
                            ],
                            "Field2" : [ 
                                "[MinKey, MaxKey]"
                            ]
                        },
                        "keysExamined" : 101,
                        "seeks" : 1,
                        "dupsTested" : 0,
                        "dupsDropped" : 0,
                        "seenInvalidated" : 0
                    }
                }
            }
        ]
    },
    "serverInfo" : {
        "host" : "xxxxx",
        "port" : 27017,
        "version" : "3.6.0",
        "gitVersion" : "xxxxx"
    },
    "ok" : 1.0
}

The executionTimeMillisEstimate for single filed index is 0 where us executionTimeMillisEstimate for the compound index is 260, then why still it uses the compound index in winning plan. I am using a single field query for single field index why it uses compound index?

Answer 1

When evaluating candidate query plans MongoDB determines which query plan returns the first batch of results (by default 101 documents) with the least amount of overall work (indicated by the works score). The works score is a proxy for different effort involved in query stages (index key comparisons, fetching documents, etc). If multiple plans perform identical work during evaluation, there are some small tie-breaking bonuses that can help choose a plan to cache (for example, if a plan is a covered query or does not require an in-memory sort).

In this case both of your plans do the same amount of work so there is no deterministic winner. In fact, your single key index is a prefix of the compound index so the compound index can answer all of the same queries. You should drop the single index rather than having both.

The executionTimeMillisEstimate for single filed index is 0 where us executionTimeMillisEstimate for the compound index is 260, then why still it uses the compound index in winning plan.

The estimated execution time in this explain output is only informational. The execution estimate is not factored into the works scores since it will change frequently based on other concurrent activity. If you run the same explain multiple times the estimates are likely to be 0 once all the relevant indexes and documents are loaded into memory.

I am using a single field query for single field index why it uses compound index?

Since the field you are querying on is a prefix of the compound index, it is also a candidate evaluated by the query planner.

For more information, see How does the MongoDB query optimizer evaluate candidate plans?.

Answer 2

The order in which the indexes are defined seems to be influential in its selection. and you can use Partial Indexes for example :

db.collection.createIndex(
    {'Field1':1}
)
db.collection.createIndex(
    {'Field1':1,'Field2':1},
    { partialFilterExpression: { Field2: { $exists: true} } }
)