[SERVER-61497] Improve the readability of explain plans in 5.X and above Created: 15/Nov/21 Updated: 24/Oct/23 |
|
| Status: | Backlog |
| Project: | Core Server |
| Component/s: | None |
| Affects Version/s: | 5.1.0 |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Harshad Dhavale | Assignee: | Backlog - Query Execution |
| Resolution: | Unresolved | Votes: | 2 |
| Labels: | explain-plan, query | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||
| Assigned Teams: |
Query Execution
|
||||||||
| Participants: | |||||||||
| 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:
While the "winningPlan" section as well as other sections above are useful, the "slotBasedPlan" section cannot be comprehended easily:
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. |
| Comments |
| Comment by Irina Yatsenko (Inactive) [ 16/Feb/22 ] | |||||||||||||||
|
Would it be possible/useful to replace the leading node id ([N]) with the name of the node from the query plan? | |||||||||||||||
| Comment by Harshad Dhavale [ 01/Dec/21 ] | |||||||||||||||
|
Hi david.storch - thanks for the detailed explanation of the current structure for the slotBasedPlan section, how to better read-and-interpret it, and the reasoning for keeping that section in the explain() output! That makes sense, and answers most of the concerns. I understand why there's not much that can be done to change the format of the slotBasedPlan section. I like the idea of providing field names alongside each of the parameters to a stage, while still keeping it in the string format, for enhancing the understanding/readability of that section. | |||||||||||||||
| Comment by David Storch [ 01/Dec/21 ] | |||||||||||||||
|
harshad.dhavale thanks for the additional info! I think it is valuable to display the SBE plan, so I doubt that we would just remove the slotBasedPlan section – for instance, this could help us determine if there is a bug in the code which constructs the slotBasedPlan based on the tree displayed in the queryPlan section. The SBE plan is arguably more difficult to interpret because its constituent stages and expressions are lower level than their counterparts from the classic query engine, and thus bear less resemblance to the input MQL query. This is a fundamental property of the SBE architecture, and there is not really much we can do to change it. That said, there are probably some tweaks we could make to aid readability. I think one thing that would help a lot is providing field names alongside each of the parameters to a stage. For instance, "seek s9 s13 s14 s5 s6 s7 s8 [] @\"2c41ddb0-4c30-488c-a2dd-11240fa088e8\" true false" could instead be written more like so:
That at least gives some hint of what the seek stage's parameters are. Another idea is to abandon the string format and always report the SBE plan as JSON; this would result in a bunch more braces, but might aid readability compared the string format. The other important thing to note that may be useful is the leading "[1]" or "[2]" that comes before each stage in the SBE plan. This is displaying the so-called "planNodeId", and it can be used to correlate the SBE plan with the corresponding tree in the queryPlan section. In this example, the IXSCAN stage has planNodeId=1 and the FETCH stage has planNodeId=2. The SBE subtree which is tagged with planNodeId=1 is implementing the IXSCAN stage:
In short, this is an index scan of the index named "a_1" whose bounds are from KeyString value KS(2B020104) to KeyString value KS(2B02FE04). It would definitely be helpful if we provided decoded versions of these KeyString values so that you can understand what the index bounds are, but you can see from the corresponding IXSCAN stage that the bounds are in fact "[1.0, 1.0]". Similarly, the part of the tree tagged with "[2]" corresponds to the FETCH operation:
This is essentially just feeding the record id values produced by the underlying index scan to the seek stage, which is SBE's equivalent of FETCH. | |||||||||||||||
| Comment by Harshad Dhavale [ 30/Nov/21 ] | |||||||||||||||
|
Hi david.storch - thanks for looking into this! While pretty-printing the contents of the "slotBasedPlan" would be nice-to-have, my request was actually more to do with fundamentally changing how the "slotBasedPlan" is represented in the explain() output. Specifically, my concern was that there is no way to decipher the contents of the "slotBasedPlan" section. For instance, when we look at these stages (pretty-printed):
I don't know how this info should be interpreted. AFAIK, "slots" refer to "places" where the data lives. However, for instance, it's hard for users to understand what is s11 = KS(2B02FE04), or what is seek s9 s13 s14 s5 s6 s7 s8 [] @\"2c41ddb0-4c30-488c-a2dd-11240fa088e8\" true false", etc., and how each slot and the relevant stages fit in the execution plan. If the representation cannot be changed, then from a user-perspective it doesn't make sense to include the "slotBasedPlan" section in the explain() output, because it cannot be understood anyway. | |||||||||||||||
| Comment by David Storch [ 29/Nov/21 ] | |||||||||||||||
|
harshad.dhavale I'd like to better understand this request. Are you simply asking for the SBE plan string to be pretty-printed so that the tree structure is visible, and newlines appear as actual newlines instead of "\n"? Or are you asking for a fundamental change in how the SBE plan is represented? If the latter, could you be more specific about how the same information could be presented in a better way? |