Support different EXPLAIN formats via SQL

[alamb]

Is your feature request related to a problem or challenge?

Suggested by @waynexia #14914 (comment)

Currently, DataFusion supports EXPLAIN and EXPLAIN ANALYZE as documented here: https://datafusion.apache.org/user-guide/sql/explain.html#id1

Both those commands produce a text based explain format:

> explain select * from values (1), (2);
+---------------+---------------------------------------------------+
| plan_type     | plan                                              |
+---------------+---------------------------------------------------+
| logical_plan  | Values: (Int64(1)), (Int64(2))                    |
| physical_plan | DataSourceExec: partitions=1, partition_sizes=[1] |
|               |                                                   |
+---------------+---------------------------------------------------+
2 row(s) fetched.
Elapsed 0.031 seconds.

DataFusion has at least three other text based ways to generate explain plans:

• graphviz via displayable::graphviz
• json via pgjson
• tree -- see [EPIC] Complete SQL EXPLAIN Tree Rendering #14914

Also, @milenkovicm suggested on #14914 (comment) that getting an SVG version would also be useful

As of now it is not possible to see those formats via SQL (you need to use the API or set a config option)

Describe the solution you'd like

I would like to be able to access the different explain formats via SQL

Describe alternatives you've considered

@waynexia suggests (and I agree) on #14914 (comment)

That we follow the EXPLAIN [ANALYZE] FORMAT <format> grammar?:

EXPLAIN [ANALYZE] FORMAT <format>

So this would look something like

-- show explain plan in default text format
EXPLAIN  SELECT * from foo
-- show explain plan in tree format
EXPLAIN FORMAT tree SELECT * from foo
-- show explain plan in json format
EXPLAIN FORMAT json SELECT * from foo
-- ...

@waynexia suggests on #14914 (comment)

For the setting side, how about using the existing EXPLAIN [ANALYZE] FORMAT <format> grammar? sqlparser can parse and generate a AnalyzeFormat, which the planner can use. But our current implementation just drops this format field:

datafusion/datafusion/sql/src/statement.rs

Lines 213 to 222 in ed517ef

	Statement::Explain {
	verbose,
	statement,
	analyze,
	format: _,
	describe_alias: _,
	..
	} => {
	self.explain_to_plan(verbose, analyze, DFStatement::Statement(statement))
	}

We can also try to support GRAPHVIZ format in this way

However, It seems like AnalyzeFormat currently only supports Text, JSON, and GRAPHVIZ 🤔

https://docs.rs/sqlparser/latest/sqlparser/ast/enum.AnalyzeFormat.html

Additional context

No response

[milenkovicm]

getting json output may be sufficient, we could derive SVG from it if needed

[waynexia]

I can implement the JSON part

[alamb]

I was thinking about implementing this feature

I think it would be a relatively straightforward implementation:

1. Extend the DataFusion explain statement to have a format field too https://docs.rs/datafusion/latest/datafusion/sql/parser/struct.ExplainStatement.html
2. Extend DFParser to parse FORMAT ...

Then we would just need to update the code in planning:

datafusion/datafusion/sql/src/statement.rs

Line 177 in da42933

DFStatement::Explain(ExplainStatement {

[milenkovicm]

as mentioned in #14914 (comment)

ballista uses graphviz-rust which generates those sub-optimal images (and needs local graph viz installation). spark uses

• https://github.com/dagrejs/graphlib-dot

a javascript framework which works much better for them. Maybe we should keep dot representation if we already have it

[alamb]

I so want this feature (and I would like an excuse to write some code) I am going to take a shot at doing so

[alamb]

I have two PRs to implement this feature

• Prepare refactor: Minor: split EXPLAIN and ANALYZE planning into different functions #15188
• Implementation: Support EXPLAIN ... FORMAT <indent | tree | json | graphviz > ... #15166

[waynexia]

Thank you @alamb, and apologize for postponing this so long 🙈 (excuse: keeping receiving unscheduled prioritiiii things from @killme2008

[alamb]

Thank you @alamb, and apologize for postponing this so long 🙈 (excuse: keeping receiving unscheduled prioritiiii things from @killme2008

LOL I totally understand. I happened to have some time and wanted some excuse to write some code :)

(Thanks for the review!)