Details
-
Bug
-
Resolution: Duplicate
-
Major - P3
-
None
-
3.0.1
-
None
-
ALL
Description
find() with the $all keyword is slow even on indexed fields. The query planner chooses a plan requiring examining many extra documents, even though it could use an index intersection. It seems that the collection scan is the first to return the 101 entries, but the executionTimeMillisEstimate is much higher (2060 vs 10) than for the AND_SORTED query, which should be the fastest. I'm not sure why the collection scan is beating the AND_SORTED query, given the radically different executionTimeMillisEstimate.
{
|
"queryPlanner" : {
|
"plannerVersion" : 1,
|
"namespace" : "shangrila.materials",
|
"indexFilterSet" : false,
|
"parsedQuery" : {
|
"$and" : [
|
{
|
"elements" : {
|
"$eq" : "Li"
|
}
|
},
|
{
|
"elements" : {
|
"$eq" : "F"
|
}
|
},
|
{
|
"elements" : {
|
"$eq" : "Mn"
|
}
|
}
|
]
|
},
|
"winningPlan" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"$and" : [
|
{
|
"elements" : {
|
"$eq" : "F"
|
}
|
},
|
{
|
"elements" : {
|
"$eq" : "Mn"
|
}
|
}
|
]
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"elements" : 1
|
},
|
"indexName" : "elements_1",
|
"isMultiKey" : true,
|
"direction" : "forward",
|
"indexBounds" : {
|
"elements" : [
|
"[\"Li\", \"Li\"]"
|
]
|
}
|
}
|
},
|
"rejectedPlans" : [
|
{
|
"stage" : "FETCH",
|
"filter" : {
|
"$and" : [
|
{
|
"elements" : {
|
"$eq" : "F"
|
}
|
},
|
{
|
"elements" : {
|
"$eq" : "Mn"
|
}
|
}
|
]
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"elements" : 1,
|
"nelements" : 1
|
},
|
"indexName" : "elements_1_nelements_1",
|
"isMultiKey" : true,
|
"direction" : "forward",
|
"indexBounds" : {
|
"elements" : [
|
"[\"Li\", \"Li\"]"
|
],
|
"nelements" : [
|
"[MinKey, MaxKey]"
|
]
|
}
|
}
|
},
|
{
|
"stage" : "FETCH",
|
"filter" : {
|
"$and" : [
|
{
|
"elements" : {
|
"$eq" : "Li"
|
}
|
},
|
{
|
"elements" : {
|
"$eq" : "F"
|
}
|
},
|
{
|
"elements" : {
|
"$eq" : "Mn"
|
}
|
}
|
]
|
},
|
"inputStage" : {
|
"stage" : "AND_SORTED",
|
"inputStages" : [
|
{
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"elements" : 1
|
},
|
"indexName" : "elements_1",
|
"isMultiKey" : true,
|
"direction" : "forward",
|
"indexBounds" : {
|
"elements" : [
|
"[\"Li\", \"Li\"]"
|
]
|
}
|
},
|
{
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"elements" : 1
|
},
|
"indexName" : "elements_1",
|
"isMultiKey" : true,
|
"direction" : "forward",
|
"indexBounds" : {
|
"elements" : [
|
"[\"F\", \"F\"]"
|
]
|
}
|
},
|
{
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"elements" : 1
|
},
|
"indexName" : "elements_1",
|
"isMultiKey" : true,
|
"direction" : "forward",
|
"indexBounds" : {
|
"elements" : [
|
"[\"Mn\", \"Mn\"]"
|
]
|
}
|
}
|
]
|
}
|
}
|
]
|
},
|
"executionStats" : {
|
"executionSuccess" : true,
|
"nReturned" : 1297,
|
"executionTimeMillis" : 77874,
|
"totalKeysExamined" : 59901,
|
"totalDocsExamined" : 59901,
|
"executionStages" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"$and" : [
|
{
|
"elements" : {
|
"$eq" : "F"
|
}
|
},
|
{
|
"elements" : {
|
"$eq" : "Mn"
|
}
|
}
|
]
|
},
|
"nReturned" : 1297,
|
"executionTimeMillisEstimate" : 74860,
|
"works" : 59902,
|
"advanced" : 1297,
|
"needTime" : 58604,
|
"needFetch" : 0,
|
"saveState" : 3878,
|
"restoreState" : 3878,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"docsExamined" : 59901,
|
"alreadyHasObj" : 0,
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"nReturned" : 59901,
|
"executionTimeMillisEstimate" : 220,
|
"works" : 59902,
|
"advanced" : 59901,
|
"needTime" : 0,
|
"needFetch" : 0,
|
"saveState" : 3878,
|
"restoreState" : 3878,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"keyPattern" : {
|
"elements" : 1
|
},
|
"indexName" : "elements_1",
|
"isMultiKey" : true,
|
"direction" : "forward",
|
"indexBounds" : {
|
"elements" : [
|
"[\"Li\", \"Li\"]"
|
]
|
},
|
"keysExamined" : 59901,
|
"dupsTested" : 59901,
|
"dupsDropped" : 0,
|
"seenInvalidated" : 0,
|
"matchTested" : 0
|
}
|
},
|
"allPlansExecution" : [
|
{
|
"nReturned" : 88,
|
"executionTimeMillisEstimate" : 2471,
|
"totalKeysExamined" : 3985,
|
"totalDocsExamined" : 3985,
|
"executionStages" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"$and" : [
|
{
|
"elements" : {
|
"$eq" : "F"
|
}
|
},
|
{
|
"elements" : {
|
"$eq" : "Mn"
|
}
|
}
|
]
|
},
|
"nReturned" : 88,
|
"executionTimeMillisEstimate" : 2471,
|
"works" : 3985,
|
"advanced" : 88,
|
"needTime" : 3897,
|
"needFetch" : 0,
|
"saveState" : 3878,
|
"restoreState" : 3878,
|
"isEOF" : 0,
|
"invalidates" : 0,
|
"docsExamined" : 3985,
|
"alreadyHasObj" : 0,
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"nReturned" : 3985,
|
"executionTimeMillisEstimate" : 0,
|
"works" : 3985,
|
"advanced" : 3985,
|
"needTime" : 0,
|
"needFetch" : 0,
|
"saveState" : 3878,
|
"restoreState" : 3878,
|
"isEOF" : 0,
|
"invalidates" : 0,
|
"keyPattern" : {
|
"elements" : 1,
|
"nelements" : 1
|
},
|
"indexName" : "elements_1_nelements_1",
|
"isMultiKey" : true,
|
"direction" : "forward",
|
"indexBounds" : {
|
"elements" : [
|
"[\"Li\", \"Li\"]"
|
],
|
"nelements" : [
|
"[MinKey, MaxKey]"
|
]
|
},
|
"keysExamined" : 3985,
|
"dupsTested" : 3985,
|
"dupsDropped" : 0,
|
"seenInvalidated" : 0,
|
"matchTested" : 0
|
}
|
}
|
},
|
{
|
"nReturned" : 62,
|
"executionTimeMillisEstimate" : 10,
|
"totalKeysExamined" : 3985,
|
"totalDocsExamined" : 62,
|
"executionStages" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"$and" : [
|
{
|
"elements" : {
|
"$eq" : "Li"
|
}
|
},
|
{
|
"elements" : {
|
"$eq" : "F"
|
}
|
},
|
{
|
"elements" : {
|
"$eq" : "Mn"
|
}
|
}
|
]
|
},
|
"nReturned" : 62,
|
"executionTimeMillisEstimate" : 10,
|
"works" : 3985,
|
"advanced" : 62,
|
"needTime" : 3923,
|
"needFetch" : 0,
|
"saveState" : 3878,
|
"restoreState" : 3878,
|
"isEOF" : 0,
|
"invalidates" : 0,
|
"docsExamined" : 62,
|
"alreadyHasObj" : 21,
|
"inputStage" : {
|
"stage" : "AND_SORTED",
|
"nReturned" : 62,
|
"executionTimeMillisEstimate" : 10,
|
"works" : 3985,
|
"advanced" : 62,
|
"needTime" : 3923,
|
"needFetch" : 0,
|
"saveState" : 3878,
|
"restoreState" : 3878,
|
"isEOF" : 0,
|
"invalidates" : 0,
|
"flagged" : 0,
|
"matchTested" : 0,
|
"failedAnd_0" : 347,
|
"failedAnd_1" : 278,
|
"failedAnd_2" : 109,
|
"inputStages" : [
|
{
|
"stage" : "IXSCAN",
|
"nReturned" : 2215,
|
"executionTimeMillisEstimate" : 0,
|
"works" : 2215,
|
"advanced" : 2215,
|
"needTime" : 0,
|
"needFetch" : 0,
|
"saveState" : 3878,
|
"restoreState" : 3878,
|
"isEOF" : 0,
|
"invalidates" : 0,
|
"keyPattern" : {
|
"elements" : 1
|
},
|
"indexName" : "elements_1",
|
"isMultiKey" : true,
|
"direction" : "forward",
|
"indexBounds" : {
|
"elements" : [
|
"[\"Li\", \"Li\"]"
|
]
|
},
|
"keysExamined" : 2215,
|
"dupsTested" : 2215,
|
"dupsDropped" : 0,
|
"seenInvalidated" : 0,
|
"matchTested" : 0
|
},
|
{
|
"stage" : "IXSCAN",
|
"nReturned" : 1083,
|
"executionTimeMillisEstimate" : 10,
|
"works" : 1083,
|
"advanced" : 1083,
|
"needTime" : 0,
|
"needFetch" : 0,
|
"saveState" : 3878,
|
"restoreState" : 3878,
|
"isEOF" : 0,
|
"invalidates" : 0,
|
"keyPattern" : {
|
"elements" : 1
|
},
|
"indexName" : "elements_1",
|
"isMultiKey" : true,
|
"direction" : "forward",
|
"indexBounds" : {
|
"elements" : [
|
"[\"F\", \"F\"]"
|
]
|
},
|
"keysExamined" : 1083,
|
"dupsTested" : 1083,
|
"dupsDropped" : 0,
|
"seenInvalidated" : 0,
|
"matchTested" : 0
|
},
|
{
|
"stage" : "IXSCAN",
|
"nReturned" : 687,
|
"executionTimeMillisEstimate" : 0,
|
"works" : 687,
|
"advanced" : 687,
|
"needTime" : 0,
|
"needFetch" : 0,
|
"saveState" : 3878,
|
"restoreState" : 3878,
|
"isEOF" : 0,
|
"invalidates" : 0,
|
"keyPattern" : {
|
"elements" : 1
|
},
|
"indexName" : "elements_1",
|
"isMultiKey" : true,
|
"direction" : "forward",
|
"indexBounds" : {
|
"elements" : [
|
"[\"Mn\", \"Mn\"]"
|
]
|
},
|
"keysExamined" : 687,
|
"dupsTested" : 687,
|
"dupsDropped" : 0,
|
"seenInvalidated" : 0,
|
"matchTested" : 0
|
}
|
]
|
}
|
}
|
},
|
{
|
"nReturned" : 101,
|
"executionTimeMillisEstimate" : 2060,
|
"totalKeysExamined" : 3985,
|
"totalDocsExamined" : 3985,
|
"executionStages" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"$and" : [
|
{
|
"elements" : {
|
"$eq" : "F"
|
}
|
},
|
{
|
"elements" : {
|
"$eq" : "Mn"
|
}
|
}
|
]
|
},
|
"nReturned" : 101,
|
"executionTimeMillisEstimate" : 2060,
|
"works" : 3985,
|
"advanced" : 101,
|
"needTime" : 3884,
|
"needFetch" : 0,
|
"saveState" : 233,
|
"restoreState" : 233,
|
"isEOF" : 0,
|
"invalidates" : 0,
|
"docsExamined" : 3985,
|
"alreadyHasObj" : 0,
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"nReturned" : 3985,
|
"executionTimeMillisEstimate" : 10,
|
"works" : 3985,
|
"advanced" : 3985,
|
"needTime" : 0,
|
"needFetch" : 0,
|
"saveState" : 233,
|
"restoreState" : 233,
|
"isEOF" : 0,
|
"invalidates" : 0,
|
"keyPattern" : {
|
"elements" : 1
|
},
|
"indexName" : "elements_1",
|
"isMultiKey" : true,
|
"direction" : "forward",
|
"indexBounds" : {
|
"elements" : [
|
"[\"Li\", \"Li\"]"
|
]
|
},
|
"keysExamined" : 3985,
|
"dupsTested" : 3985,
|
"dupsDropped" : 0,
|
"seenInvalidated" : 0,
|
"matchTested" : 0
|
}
|
}
|
}
|
]
|
},
|
"serverInfo" : {
|
"host" : "xxxx",
|
"port" : 27017,
|
"version" : "3.0.1",
|
"gitVersion" : "534b5a3f9d10f00cd27737fbcd951032248b5952"
|
},
|
"ok" : 1
|
}
|
Attachments
Issue Links
- duplicates
-
SERVER-13740 Improve plan ranking for index intersection
-
- Backlog
-