Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-61497

Improve the readability of explain plans in 5.X and above

    • Type: Icon: Improvement Improvement
    • Resolution: Unresolved
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 5.1.0
    • Component/s: None
    • Query Execution

      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.

            Assignee:
            backlog-query-execution [DO NOT USE] Backlog - Query Execution
            Reporter:
            harshad.dhavale@mongodb.com Harshad Dhavale
            Votes:
            2 Vote for this issue
            Watchers:
            14 Start watching this issue

              Created:
              Updated: