-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: 5.1.0
-
Component/s: None
-
Query Execution
-
None
-
None
-
None
-
None
-
None
-
None
-
None
MongoDB version 5.X introduced the slot-based execution (SBE) query engine. Running an explain() plan on a query that is using SBE provides an output like this:
PRIMARY> db.test1.explain().find({a:1})
{
"explainVersion" : "2",
"queryPlanner" : {
"namespace" : "test.test1",
"indexFilterSet" : false,
"parsedQuery" : {
"a" : {
"$eq" : 1
}
},
"queryHash" : "4B53BE76",
"planCacheKey" : "2DEF680C",
"maxIndexedOrSolutionsReached" : false,
"maxIndexedAndSolutionsReached" : false,
"maxScansToExplodeReached" : false,
"winningPlan" : {
"queryPlan" : {
"stage" : "FETCH",
"planNodeId" : 2,
"inputStage" : {
"stage" : "IXSCAN",
"planNodeId" : 1,
"keyPattern" : {
"a" : 1
},
"indexName" : "a_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[1.0, 1.0]"
]
}
}
},
"slotBasedPlan" : {
"slots" : "$$RESULT=s13 $$RID=s14 env: { s2 = Nothing (SEARCH_META), s1 = TimeZoneDatabase(Canada/Yukon...America/Rainy_River) (timeZoneDB), s4 = 1637002207166 (NOW), s3 = Timestamp(1637002203, 1) (CLUSTER_TIME) }",
"stages" : "[2] nlj [] [s9, s5, s6, s7, s8] \n left \n [1] nlj [s6, s8] [s10, s11] \n left \n [1] project [s6 = \"a_1\", s8 = {\"a\" : 1}, s10 = KS(2B020104), s11 = KS(2B02FE04)] \n [1] limit 1 \n [1] coscan \n right \n [1] project [s5 = s12] \n [1] ixseek s10 s11 s7 s9 s12 [] @\"2c41ddb0-4c30-488c-a2dd-11240fa088e8\" @\"a_1\" true \n \n \n right \n [2] limit 1 \n [2] seek s9 s13 s14 s5 s6 s7 s8 [] @\"2c41ddb0-4c30-488c-a2dd-11240fa088e8\" true false \n \n"
}
},
"rejectedPlans" : [ ]
},
"command" : {
"find" : "test1",
"filter" : {
"a" : 1
},
"$db" : "test"
},
"serverInfo" : {
"host" : "atlas-9ev3lx-shard-00-02.oz4jm.mongodb.net",
"port" : 27017,
"version" : "5.1.0",
"gitVersion" : "2454bbc8c20c8d6d6d9590a66e939d568b9e7222"
},
"serverParameters" : {
"internalQueryFacetBufferSizeBytes" : 104857600,
"internalQueryFacetMaxOutputDocSizeBytes" : 104857600,
"internalLookupStageIntermediateDocumentMaxSizeBytes" : 104857600,
"internalDocumentSourceGroupMaxMemoryBytes" : 104857600,
"internalQueryMaxBlockingSortMemoryUsageBytes" : 104857600,
"internalQueryProhibitBlockingMergeOnMongoS" : 0,
"internalQueryMaxAddToSetBytes" : 104857600,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600
},
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1637002203, 1),
"signature" : {
"hash" : BinData(0,"qMUDrkJ1eQV3Y+0cDewpDaiM+s0="),
"keyId" : NumberLong("7029021327763701764")
}
},
"operationTime" : Timestamp(1637002203, 1)
}
While the "winningPlan" section as well as other sections above are useful, the "slotBasedPlan" section cannot be comprehended easily:
"slotBasedPlan" : { "slots" : "$$RESULT=s13 $$RID=s14 env: { s2 = Nothing (SEARCH_META), s1 = TimeZoneDatabase(Canada/Yukon...America/Rainy_River) (timeZoneDB), s4 = 1637002207166 (NOW), s3 = Timestamp(1637002203, 1) (CLUSTER_TIME) }", "stages" : "[2] nlj [] [s9, s5, s6, s7, s8] \n left \n [1] nlj [s6, s8] [s10, s11] \n left \n [1] project [s6 = \"a_1\", s8 = {\"a\" : 1}, s10 = KS(2B020104), s11 = KS(2B02FE04)] \n [1] limit 1 \n [1] coscan \n right \n [1] project [s5 = s12] \n [1] ixseek s10 s11 s7 s9 s12 [] @\"2c41ddb0-4c30-488c-a2dd-11240fa088e8\" @\"a_1\" true \n \n \n right \n [2] limit 1 \n [2] seek s9 s13 s14 s5 s6 s7 s8 [] @\"2c41ddb0-4c30-488c-a2dd-11240fa088e8\" true false \n \n" }
Hence, this request is for improving the readability of the explain plans in 5.X and above that use the slot-based execution query engine.
- related to
-
-
- Backlog
-