Details
-
Improvement
-
Status: Backlog
-
Major - P3
-
Resolution: Unresolved
-
5.1.0
-
None
-
None
-
Query Execution
Description
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.