Suggestion: Improve execution plan readability by using concise notation for execution_method

[apstndb]

Spanner execution plans are becoming increasingly complex over time. Even plans for simple queries, which used to format neatly, now require significantly more screen width than before. A major contributor to this is the execution_method metadata, which now commonly appears on almost every operator.

For example, consider the plan for EXPLAIN SELECT * FROM Singers;:

EXPLAIN
SELECT * FROM Singers;

+----+------------------------------------------------------------------------------------------------------+
| ID | Query_Execution_Plan                                                                                 |
+----+------------------------------------------------------------------------------------------------------+
|  0 | Distributed Union (distribution_table: Singers, execution_method: Row, split_ranges_aligned: false)  |
|  1 | +- Local Distributed Union (execution_method: Row)                                                   |
|  2 |    +- Serialize Result (execution_method: Row)                                                       |
|  3 |       +- Table Scan (Full scan: true, Table: Singers, execution_method: Row, scan_method: Automatic) |
+----+------------------------------------------------------------------------------------------------------+

This causes a couple of issues:

• It significantly impacts screen width, as execution_method is almost always included, often making lines much longer.
• It makes the execution plan overly verbose.

There is precedent within spanner-cli for displaying necessary information concisely instead of showing raw metadata, balancing readability and horizontal space savings. Previous efforts include:

• Support QueryMode=PROFILE a.k.a EXPLAIN ANALYZE #57 (comment)
• Combine metadata fields #63

Following this approach, I propose introducing a dedicated, more concise notation specifically for the execution_method metadata to save screen width and improve readability.

Note: While simply hiding execution_method entirely might seem like an option, it's an important concept documented for query optimization (SQL best practices: Optimize query execution). A tool useful for query optimization probably shouldn't hide such a key piece of information.

For reference, in a fork I'm currently developing (spanner-mycli), I am experimenting with a notation that displays it like <Row> or <Batch> appended to the operator's display name.

See example implementation and the resulting output format:
apstndb/spanner-mycli#178

+----+-------------------------------------------------------------------------------------+
| ID | Query_Execution_Plan                                                                |
+----+-------------------------------------------------------------------------------------+
|  0 | Distributed Union <Row> (distribution_table: Singers, split_ranges_aligned: false)  |
|  1 | +- Local Distributed Union <Row>                                                    |
|  2 |    +- Serialize Result <Row>                                                        |
|  3 |       +- Table Scan <Row> (Full scan: true, Table: Singers, scan_method: Automatic) |
+----+-------------------------------------------------------------------------------------+

Adopting a similar concise format in spanner-cli could significantly improve the usability of execution plan analysis, especially on screens with limited width.

[yfuruyama]

Thank you for the suggestion. Let me take time to think about that.

[yfuruyama]

Again, thank you for the suggestion.

So the issue we're facing is that Spanner is returning a lot of metadata and it takes wide screen size to show that.

I think your suggestion to use concise notation for execution_method has some downside.

1. The notation <Row> is not familiar with user, so it could confuse them. I think <Row> is likely to be interrupted as "It returns a single row" or something like that.
2. It's only solving the partial issue. I agree that execution_method is common metadata across operators, but it could happen that other common metadata will be introduced in the future. Then, we have to think about another notation for that metadata.

I was thinking about alternative ways to show metadata in concise format, but I guess the perfect solution might not exist. Only thing I came up with is that showing some set of important metadata in the table while showing full metadata outside the table (like we're doing for predicates).

The example is as follows:

# Current notation
+----+------------------------------------------------------------------------------------------------------+
| ID | Query_Execution_Plan                                                                                 |
+----+------------------------------------------------------------------------------------------------------+
|  0 | Distributed Union (distribution_table: Players, execution_method: Row, split_ranges_aligned: false)  |
|  1 | +- Local Distributed Union (execution_method: Row)                                                   |
|  2 |    +- Serialize Result (execution_method: Row)                                                       |
|  3 |       +- Table Scan (Full scan: true, Table: Players, execution_method: Row, scan_method: Automatic) |
+----+------------------------------------------------------------------------------------------------------+

# Concise notation
+----+-------------------------------------------------------+
| ID | Query_Execution_Plan                                  |
+----+-------------------------------------------------------+
|  0 | Distributed Union (distribution_table: Players)       |
|  1 | +- Local Distributed Union                            |
|  2 |    +- Serialize Result                                |
|  3 |       +- Table Scan (Full scan: true, Table: Players) |
+----+-------------------------------------------------------+
Full Metadata
 0: distribution_table: Players, execution_method: Row, split_ranges_aligned: false
 1: execution_method: Row
 2: execution_method: Row
 3: Full scan: true, Table: Players, execution_method: Row, scan_method: Automatic

[apstndb]

I agree that execution_method is common metadata across operators, but it could happen that other common metadata will be introduced in the future. Then, we have to think about another notation for that metadata.

My opinion is that we can change it later so don't need to think about it until it actually happens.

In your notation, I think that in the case of a large execution plan, it becomes more difficult to understand by switching between the tree in the table, the predicate section, and the full metadata section.

This is more of a problem than the increased width.

Another solution may be introducing auto wrapping of operator column.

FYI: tablewriter supports multiline cell and treerprint can render multiline node.
xlab/treeprint#13

[yfuruyama]

In your notation, I think that in the case of a large execution plan, it becomes more difficult to understand by switching between the tree in the table, the predicate section, and the full metadata section.

I think it’s tradeoff between the width of the rendered table and how dense we can show the data.

If the user uses the compact screen size, it might be acceptable to see the subset of metadata in the table.

Note that my idea is providing an option to change how dense the table is rendered. Not all users use the compact screen size, so those user don’t need to use that option.

[yfuruyama]

Another solution may be introducing auto wrapping of operator column.

FYI: tablewriter supports multiline cell and treerprint can render multiline node.
xlab/treeprint#13

If we show a single operator in multiple columns, then the vertical size can be longer, so for a complicated execution plan, it’s likely not to fit in a screen vertically.

So I think this is a tradeoff.

[apstndb]

The original reason I wanted to discuss this issue was to avoid the 16-character string execution_method that was added to almost all operators constantly taking up screen space for all users. I don't think this issue alone is a trade-off.

If you don't want to create a new syntax just for execution_method, let's close this issue.

[apstndb]

If we show a single operator in multiple columns, then the vertical size can be longer, so for a complicated execution plan, it’s likely not to fit in a screen vertically.

IMO, the impact on line count is limited since the terminal already breaks the tree view and wraps it to multiple lines if it doesn't fit within the screen width.

[yfuruyama]

The original reason I wanted to discuss this issue was to avoid the 16-character string execution_method that was added to almost all operators constantly taking up screen space for all users. I don't think this issue alone is a trade-off.

Sure, let's focus on this issue.

I agree that it's verbose to show 16-character string for every operation, but I want to avoid a notation that could confuse users, as I commented in #208 (comment).

Some other random idea that can reduce the width:

• Use abbreviated term. For example, execution_method to exec_method.
• Use alias. For example,

+----+-----------------------------------------------------------------------------------------+
| ID | Query_Execution_Plan                                                                    |
+----+-----------------------------------------------------------------------------------------+
|  0 | Distributed Union (distribution_table: Players, split_ranges_aligned: false, *em: Row)  |
|  1 | +- Local Distributed Union (*em: Row)                                                   |
|  2 |    +- Serialize Result (*em: Row)                                                       |
|  3 |       +- Table Scan (Full scan: true, Table: Players, scan_method: Automatic, *em: Row) |
+----+-----------------------------------------------------------------------------------------+
Aliases
  *em: execution_method

[apstndb]

I want to avoid a notation that could confuse users, as I commented in #208 (comment).
The notation <Row> is not familiar with user, so it could confuse them. I think <Row> is likely to be interrupted as "It returns a single row" or something like that.

I don't quite understand the reasoning why execution_method=Row wouldn't be interpreted similarly.
My opinion is that execution plans are generally read by people with a certain level of expertise, and I don't think we need to worry about something that poses no problem once it's understood.

Refocusing the discussion, I believe the two main points are now:

Q1. Is it worth introducing special notation for execution_method?
Q2. Should we introduce aliases for metadata?

These aren't mutually exclusive; we could potentially do both.

While I don't think my tool and spanner-cli necessarily need to make the same choice, here's my current stance:

Re: Q1. Is it worth introducing special notation for execution_method?

Yes, I believe execution_method is significant enough to introduce special notation.

• It's currently the only metadata present in almost every operator node.
• Distinguishing it with special notation could potentially improve readability.
• It's often part of the widest lines in the plan, impacting screen width.

For these reasons, I'm positive to have special notation.
As for the potential confusion caused by the new notation, this can be mitigated with clear explanations.
For example, if the meaning is explained in a header (like below), I see no downside compared to writing execution_method out on every node.

+-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID  | Query_Execution_Plan <execution_method> (metadata, ...)                                                                                                        |
+-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
|   0 | Serialize Result <Row>                                                                                                                                         |
|  *1 | +- Filter <Row>                                                                                                                                                |
|   2 |    +- DataBlockToRow                                                                                                                                           |
|   3 |       +- Recursive Union                                                                                                                                       |
|   4 |          +- Union Input                                                                                                                                        |
|   5 |          |  +- RowToDataBlock                                                                                                                                  |
|   6 |          |     +- Cross Apply <Row>                                                                                                                            |
|   7 |          |        +- Stream Aggregate <Row> (scalar_aggregate: true)                                                                                           |
|   8 |          |        |  +- Compute Struct <Row>                                                                                                                   |
|   9 |          |        |     +- Empty Relation <Row>                                                                                                                |
|  22 |          |        +- [Map] DataBlockToRow                                                                                                                      |
| *23 |          |           +- Distributed Union <Batch> (distribution_table: Account, split_ranges_aligned: false)                                                   |
|  24 |          |              +- RowToDataBlock                                                                                                                      |
|  25 |          |                 +- Local Distributed Union <Row>                                                                                                    |
|  26 |          |                    +- Compute <Row>                                                                                                                 |
|  27 |          |                       +- Filter Scan <Row> (seekable_key_size: 0)                                                                                   |
| *28 |          |                          +- Table Scan <Row> (Table: Account, scan_method: Row)                                                                     |
|  47 |          +- Union Input                                                                                                                                        |
|  48 |             +- RowToDataBlock                                                                                                                                  |
|  49 |                +- Compute <Row>                                                                                                                                |
| *50 |                   +- Hash Join <Row> (join_type: INNER)                                                                                                        |
|  51 |                      +- [Build] BloomFilterBuild <Row>                                                                                                         |
|  52 |                      |  +- DataBlockToRow                                                                                                                      |
|  53 |                      |     +- Recursive Spool Scan                                                                                                             |
|  64 |                      +- [Probe] DataBlockToRow                                                                                                                 |
|  65 |                         +- Distributed Union <Batch> (distribution_table: IDX_AccountTransferAccount_to_id_8F1D3A3A173541D1, split_ranges_aligned: false)      |
|  66 |                            +- RowToDataBlock                                                                                                                   |
|  67 |                               +- Local Distributed Union <Row>                                                                                                 |
|  68 |                                  +- Compute Struct <Row>                                                                                                       |
| *69 |                                     +- Filter Scan <Row> (seekable_key_size: 0)                                                                                |
|  70 |                                        +- Index Scan <Row> (Full scan: true, Index: IDX_AccountTransferAccount_to_id_8F1D3A3A173541D1, scan_method: Automatic) |
+-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
Predicates(identified by ID):
  1: Condition: NOT(IS_NOT_DISTINCT_FROM($element'3_$graph_node_key_arg_1'12, $tail'_$graph_node_key_arg_1'18))
 23: Split Range: ($id'5 = 7)
 28: Seek Condition: ($id'5 = 7)
 50: Condition: IS_NOT_DISTINCT_FROM($tail'_id'18, $id_3'6)
 69: Residual Condition: BLOOM_FILTER_MATCH($existence_filter, $id_3'5)

In this case, the special notation can highlight <Batch> at ID 65.

Re: Q2. Should we introduce aliases for metadata?

Regarding this, I'd prefer to hold off on implementing aliases in my own tool for now. My reasons are:

• Focusing only on execution_method, aliasing might decrease readability compared to special notation.
• Introducing a general alias concept opens the door to needing aliases for other metadata beyond just execution_method.
• This raises questions: Should the tool author curate aliases? Or should they be user-configurable?

This suggestion is just sharing my preference ("I do it this way, what about you?"), not trying to impose changes on spanner-cli against your will. While unified notation has some user benefits, this ultimately only affects which notation I personally use.

[yfuruyama]

As for the potential confusion caused by the new notation, this can be mitigated with clear explanations.
For example, if the meaning is explained in a header (like below), I see no downside compared to writing execution_method out on every node.

Thank you for this suggestion. I think this would be simpler than using an alias, so I'm positive on this direction.

[apstndb]

I'm glad we've reached an agreement on the direction.
I don't have any particular preference as to who implements it.