[SERVER-56193] Write a JS script to translate "executionStats" section of explain from SBE into classic format Created: 20/Apr/21  Updated: 29/Oct/23  Resolved: 14/Jul/21

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 5.1.0-rc0

Type: Task Priority: Major - P3
Reporter: Anton Korshunov Assignee: David Storch
Resolution: Fixed Votes: 0
Labels: sbe-post-v1, sbe-rollout
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File massage_sbe_explain.js    
Issue Links:
Depends
Backwards Compatibility: Fully Compatible
Sprint: Query Execution 2021-05-03, Query Execution 2021-05-17, Query Execution 2021-05-31, Query Execution 2021-06-14, Query Execution 2021-06-28, Query Execution 2021-07-12, Query Execution 2021-07-26
Participants:

 Description   

The "executionStats" section of the explain output in SBE is drastically different from the classic engine. To help in transitioning from the old to the new format, we need a tool which would allow to convert the "executionStats" section from SBE into classic format.

The tool would work off the "queryPlanner" section using it as a backbone for the translated "executionStats" section. For each QSN from the "queryPlanner" section we'd need to map it to an SBE sub-tree in the "executionStats" section based on the nodeId, then collect cumulative execution stats for this sub-tree and attach it to a "stage" element in the "executionStats" tree created from the corresponding QSN.



 Comments   
Comment by Vivian Ge (Inactive) [ 06/Oct/21 ]

Updating the fixversion since branching activities occurred yesterday. This ticket will be in rc0 when it’s been triggered. For more active release information, please keep an eye on #server-release. Thank you!

Comment by Githook User [ 14/Jul/21 ]

Author:

{'name': 'David Storch', 'email': 'david.storch@mongodb.com', 'username': 'dstorch'}

Message: SERVER-56193 Add 'Explain.sbeReformatExperimental()' shell helper

This experimental shell helper can be used to present
explain output from a system with the SBE engine enabled in
a more user-friendly fashion.
Branch: master
https://github.com/mongodb/mongo/commit/e9e4e99d0ff87ec9b6ed2a689a4c42cdba7f5527

Comment by David Storch [ 29/Apr/21 ]

A few conclusions from a meeting about this work today with myself, christopher.harris, and anton.korshunov. CC alex.bevilacqua.

  • Chris likes the general direction where this is headed. The array-of-plans format is useful and more intuitive than what explain produces today.
  • We will start by putting this function into the shell, but will mark it as experimental. Suggested name is Explain.reformatExperimental().
    • There is no harm in also adding similar functionality to Query Doctor or other tooling.
    • In some support scenarios, we might ask customers to run an operation such as Explain.reformatExperimental(customerCollection.find(customerPredicate)). We don't see this as necessarily problematic, though there are a few caveats: 1) This may mask the full view of the data. You might sometimes want the original explain output to understand what's really going on. 2) This script is marked "experimental", meaning its behavior is subject to change.
  • Anton points out that the script should handle reporting of nChunkSkips. This is the number of orphan documents rejected by the sharding filter. The script should report it alongside the SHARDING_FILTER stage.
  • Chris will look into whether there are other execution stats which the script should handle that are not handled yet. We can always add them later on.

Let me know if there are any other points from the meeting that I missed!

Comment by David Storch [ 28/Apr/21 ]

Hi christopher.harris, alex.bevilacqua, and anton.korshunov!

I've produced a draft of a JS script that I would like your feedback on. It is attached as "massage_sbe_explain.js". I also pushed it to a branch on my fork of mongodb/mongo here: https://github.com/dstorch/mongo/tree/SERVER-56193. Here's a quick example of what it looks like. I ran the following, which uses the shell to load my script and then runs the massageSbeExplain() function provided by the script:

load("massage_sbe_explain.js");
let explain = db.c.find({a: 1, b: 1}).explain(true);
let massaged =
    massageSbeExplain(explain, ["planNumber", "queryPlan", "summaryStats", "trialSummaryStats"]);
printjson(massaged);

The output looks like this:

[
	{
		"queryPlan" : {
			"stage" : "FETCH",
			"planNodeId" : 2,
			"filter" : {
				"a" : {
					"$eq" : 1
				}
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"planNodeId" : 1,
				"keyPattern" : {
					"b" : 1
				},
				"indexName" : "b_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"b" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"b" : [
						"[1.0, 1.0]"
					]
				},
				"execStats" : {
					"advances" : 5,
					"opens" : 1,
					"closes" : 1,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 1,
					"executionTimeMillisEstimate" : 0,
					"seeks" : 1,
					"numReads" : 5
				},
				"trialExecStats" : {
					"advances" : 5,
					"opens" : 1,
					"closes" : 1,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 1,
					"executionTimeMillisEstimate" : 0,
					"seeks" : 1,
					"numReads" : 5
				}
			},
			"execStats" : {
				"advances" : 2,
				"opens" : 1,
				"closes" : 2,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 1,
				"executionTimeMillisEstimate" : 0,
				"numReads" : 5
			},
			"trialExecStats" : {
				"advances" : 2,
				"opens" : 1,
				"closes" : 1,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 1,
				"executionTimeMillisEstimate" : 0,
				"numReads" : 5
			}
		},
		"planNumber" : 0,
		"summaryStats" : {
			"executionSuccess" : true,
			"nReturned" : 2,
			"executionTimeMillis" : 2,
			"totalKeysExamined" : 5,
			"totalDocsExamined" : 5
		},
		"trialSummaryStats" : {
			"nReturned" : 2,
			"executionTimeMillisEstimate" : 0,
			"totalKeysExamined" : 5,
			"totalDocsExamined" : 5
		}
	},
	{
		"queryPlan" : {
			"stage" : "FETCH",
			"planNodeId" : 2,
			"filter" : {
				"b" : {
					"$eq" : 1
				}
			},
			"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]"
					]
				},
				"trialExecStats" : {
					"advances" : 6,
					"opens" : 1,
					"closes" : 1,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 1,
					"executionTimeMillisEstimate" : 0,
					"seeks" : 1,
					"numReads" : 6
				}
			},
			"trialExecStats" : {
				"advances" : 2,
				"opens" : 1,
				"closes" : 2,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 1,
				"executionTimeMillisEstimate" : 0,
				"numReads" : 6
			}
		},
		"planNumber" : 1,
		"trialSummaryStats" : {
			"nReturned" : 2,
			"executionTimeMillisEstimate" : 0,
			"totalKeysExamined" : 6,
			"totalDocsExamined" : 6
		}
	},
	{
		"queryPlan" : {
			"stage" : "FETCH",
			"planNodeId" : 4,
			"filter" : {
				"$and" : [
					{
						"a" : {
							"$eq" : 1
						}
					},
					{
						"b" : {
							"$eq" : 1
						}
					}
				]
			},
			"inputStage" : {
				"stage" : "AND_SORTED",
				"planNodeId" : 3,
				"inputStages" : [
					{
						"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]"
							]
						},
						"trialExecStats" : {
							"advances" : 5,
							"opens" : 1,
							"closes" : 1,
							"saveState" : 0,
							"restoreState" : 0,
							"isEOF" : 0,
							"executionTimeMillisEstimate" : 0,
							"seeks" : 1,
							"numReads" : 5
						}
					},
					{
						"stage" : "IXSCAN",
						"planNodeId" : 2,
						"keyPattern" : {
							"b" : 1
						},
						"indexName" : "b_1",
						"isMultiKey" : false,
						"multiKeyPaths" : {
							"b" : [ ]
						},
						"isUnique" : false,
						"isSparse" : false,
						"isPartial" : false,
						"indexVersion" : 2,
						"direction" : "forward",
						"indexBounds" : {
							"b" : [
								"[1.0, 1.0]"
							]
						},
						"trialExecStats" : {
							"advances" : 5,
							"opens" : 1,
							"closes" : 1,
							"saveState" : 0,
							"restoreState" : 0,
							"isEOF" : 1,
							"executionTimeMillisEstimate" : 0,
							"seeks" : 1,
							"numReads" : 5
						}
					}
				],
				"trialExecStats" : {
					"advances" : 2,
					"opens" : 1,
					"closes" : 1,
					"saveState" : 0,
					"restoreState" : 0,
					"isEOF" : 1,
					"executionTimeMillisEstimate" : 0
				}
			},
			"trialExecStats" : {
				"advances" : 2,
				"opens" : 1,
				"closes" : 2,
				"saveState" : 0,
				"restoreState" : 0,
				"isEOF" : 1,
				"executionTimeMillisEstimate" : 0,
				"numReads" : 2
			}
		},
		"planNumber" : 2,
		"trialSummaryStats" : {
			"nReturned" : 2,
			"executionTimeMillisEstimate" : 0,
			"totalKeysExamined" : 10,
			"totalDocsExamined" : 2
		}
	}
]

There are three candidate plans with associated plan numbers 0, 1, and 2. The plan with id 0 is the winning plan – the script always puts the winning plan first. Each of the nodes in the winning plan has been augmented with a field called "execStats". These stats are derived by the script using the full-blown SBE output. The winning plan and the rejected plans additionally are augmented to have "trialExecStats" which are derived from the "allPlansExecution" output of the original explain. The output also prominently features the plan summary stats in case you are not interested in the detailed description of the plan.

For additional documentation and a more complete description of the output generated by the script, see the this comment.

This work is definitely not complete, and there are several open questions and TODOs:

  • Where should this code ultimately live? Query doctor? jstests/libs/ in the mongodb/mongo repository? As a builtin function in the shell?
  • Do you like the way that this presents the information as an array of plans, or should it more closely resemble the original explain format that it consumes?
  • Are there any important execution stats that are not handled by the script?
  • It still needs to handle output from sharded clusters.
  • It still needs to handle agg explain.

This is a longer discussion, so it's probably best to find a time to meet, but let me know if you have thoughts in the meantime.

Generated at Thu Feb 08 05:38:37 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.