I have a query indexes like below in a fairly large collection:
field1_1: {field1: 1}
field2_1: {field2: 1}
field3_1: {field3: 1}
When I tried to run:
db.getCollection('someCollection').find({
"field1" : "some value",
"field2" : 1,
"field3" : "some ID"
});
The query took more than 1 second. So I tried to run explain(), according to the result, the winner plan should use field3_1 index (see result below).
But if I force index field3_1 hint the query executes less than a second.
And if I force index field1_1 hint the query executes more than a second.
This gives me the impression the index Mongo showed in the explain result (field3_1) is not the same with the index it use on actual run (probably field1_1).
The explain result (I omitted some part due to max size permitted by SO):
{
"queryPlanner" : {
"plannerVersion" : 1.0,
"namespace" : "somedb.someCollection",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"field3" : {
"$eq" : "some-random-id"
}
},
{
"field2" : {
"$eq" : 1.0
}
},
{
"field1" : {
"$eq" : "some-value"
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"field2" : {
"$eq" : 1.0
}
},
{
"field1" : {
"$eq" : "some-value"
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"field3" : 1.0
},
"indexName" : "field3_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"field3" : [
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2.0,
"direction" : "forward",
"indexBounds" : {
"field3" : [
"["some-random-id", "some-random-id"]"
]
}
}
},
"rejectedPlans" : [/* OMITTED */]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1.0,
"executionTimeMillis" : 0.0,
"totalKeysExamined" : 3.0,
"totalDocsExamined" : 3.0,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"field2" : {
"$eq" : 1.0
}
},
{
"field1" : {
"$eq" : "some-value"
}
}
]
},
"nReturned" : 1.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 5.0,
"advanced" : 1.0,
"needTime" : 2.0,
"needYield" : 0.0,
"saveState" : 0.0,
"restoreState" : 0.0,
"isEOF" : 1.0,
"invalidates" : 0.0,
"docsExamined" : 3.0,
"alreadyHasObj" : 0.0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 3.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 4.0,
"advanced" : 3.0,
"needTime" : 0.0,
"needYield" : 0.0,
"saveState" : 0.0,
"restoreState" : 0.0,
"isEOF" : 1.0,
"invalidates" : 0.0,
"keyPattern" : {
"field3" : 1.0
},
"indexName" : "field3_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"field3" : [
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2.0,
"direction" : "forward",
"indexBounds" : {
"field3" : [
"["some-random-id", "some-random-id"]"
]
},
"keysExamined" : 3.0,
"seeks" : 1.0,
"dupsTested" : 0.0,
"dupsDropped" : 0.0,
"seenInvalidated" : 0.0
}
},
"allPlansExecution" : [
{
"nReturned" : 0.0,
"executionTimeMillisEstimate" : 0.0,
"totalKeysExamined" : 4.0,
"totalDocsExamined" : 4.0,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"field3" : {
"$eq" : "some-random-id"
}
},
{
"field2" : {
"$eq" : 1.0
}
}
]
},
"nReturned" : 0.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 4.0,
"advanced" : 0.0,
"needTime" : 4.0,
"needYield" : 0.0,
"saveState" : 0.0,
"restoreState" : 0.0,
"isEOF" : 0.0,
"invalidates" : 0.0,
"docsExamined" : 4.0,
"alreadyHasObj" : 0.0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 4.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 4.0,
"advanced" : 4.0,
"needTime" : 0.0,
"needYield" : 0.0,
"saveState" : 0.0,
"restoreState" : 0.0,
"isEOF" : 0.0,
"invalidates" : 0.0,
"keyPattern" : {
"field1" : 1.0
},
"indexName" : "field1_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"field1" : [
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2.0,
"direction" : "forward",
"indexBounds" : {
"field1" : [
"["some-value", "some-value"]"
]
},
"keysExamined" : 4.0,
"seeks" : 1.0,
"dupsTested" : 0.0,
"dupsDropped" : 0.0,
"seenInvalidated" : 0.0
}
}
},
{
"nReturned" : 0.0,
"executionTimeMillisEstimate" : 0.0,
"totalKeysExamined" : 4.0,
"totalDocsExamined" : 4.0,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"field3" : {
"$eq" : "some-random-id"
}
},
{
"field1" : {
"$eq" : "some-value"
}
}
]
},
"nReturned" : 0.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 4.0,
"advanced" : 0.0,
"needTime" : 4.0,
"needYield" : 0.0,
"saveState" : 0.0,
"restoreState" : 0.0,
"isEOF" : 0.0,
"invalidates" : 0.0,
"docsExamined" : 4.0,
"alreadyHasObj" : 0.0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 4.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 4.0,
"advanced" : 4.0,
"needTime" : 0.0,
"needYield" : 0.0,
"saveState" : 0.0,
"restoreState" : 0.0,
"isEOF" : 0.0,
"invalidates" : 0.0,
"keyPattern" : {
"field2" : 1.0
},
"indexName" : "field2_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"field2" : [
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2.0,
"direction" : "forward",
"indexBounds" : {
"field2" : [
"[1.0, 1.0]"
]
},
"keysExamined" : 4.0,
"seeks" : 1.0,
"dupsTested" : 0.0,
"dupsDropped" : 0.0,
"seenInvalidated" : 0.0
}
}
},
{
"nReturned" : 0.0,
"executionTimeMillisEstimate" : 0.0,
"totalKeysExamined" : 4.0,
"totalDocsExamined" : 1.0,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"field3" : {
"$eq" : "some-random-id"
}
},
{
"field1" : {
"$eq" : "some-value"
}
},
{
"field2" : {
"$eq" : 1.0
}
}
]
},
"nReturned" : 0.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 4.0,
"advanced" : 0.0,
"needTime" : 4.0,
"needYield" : 0.0,
"saveState" : 0.0,
"restoreState" : 0.0,
"isEOF" : 0.0,
"invalidates" : 0.0,
"docsExamined" : 1.0,
"alreadyHasObj" : 0.0,
"inputStage" : {
"stage" : "AND_SORTED",
"nReturned" : 1.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 4.0,
"advanced" : 1.0,
"needTime" : 3.0,
"needYield" : 0.0,
"saveState" : 0.0,
"restoreState" : 0.0,
"isEOF" : 0.0,
"invalidates" : 0.0,
"flagged" : 0.0,
"failedAnd_0" : 1.0,
"failedAnd_1" : 0.0,
"inputStages" : [
{
"stage" : "IXSCAN",
"nReturned" : 3.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 3.0,
"advanced" : 3.0,
"needTime" : 0.0,
"needYield" : 0.0,
"saveState" : 0.0,
"restoreState" : 0.0,
"isEOF" : 0.0,
"invalidates" : 0.0,
"keyPattern" : {
"field3" : 1.0
},
"indexName" : "field3_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"field3" : [
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2.0,
"direction" : "forward",
"indexBounds" : {
"field3" : [
"["some-random-id", "some-random-id"]"
]
},
"keysExamined" : 3.0,
"seeks" : 1.0,
"dupsTested" : 0.0,
"dupsDropped" : 0.0,
"seenInvalidated" : 0.0
},
{
"stage" : "IXSCAN",
"nReturned" : 1.0,
"executionTimeMillisEstimate" : 0.0,
"works" : 1.0,
"advanced" : 1.0,
"needTime" : 0.0,
"needYield" : 0.0,
"saveState" : 0.0,
"restoreState" : 0.0,
"isEOF" : 0.0,
"invalidates" : 0.0,
"keyPattern" : {
"field1" : 1.0
},
"indexName" : "field1_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"field1" : [
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2.0,
"direction" : "forward",
"indexBounds" : {
"field1" : [
"["some-value", "some-value"]"
]
},
"keysExamined" : 1.0,
"seeks" : 1.0,
"dupsTested" : 0.0,
"dupsDropped" : 0.0,
"seenInvalidated" : 0.0
}
]
}
}
},
/** SOME PLANS OMITTED **/
]
},
"serverInfo" : {
"host" : "someHost",
"port" : 27017.0,
"version" : "3.4.24-3.0",
"gitVersion" : "f86383fc4327d70eb4abaea689e609333e924db6"
},
"ok" : 1.0
}
Anyone ever experience this? What makes this behaviour to happen? Any way to know actual index mongo use when executing query?