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?
Best Answer
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
worksscore). Theworksscore 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 estimated execution time in this explain output is only informational. The execution estimate is not factored into the
worksscores 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.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?.