Repository navigation
Support different EXPLAIN formats via SQL #15021
Description
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 EXPLAINTree 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?sqlparsercan parse and generate aAnalyzeFormat, 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
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
Activity
milenkovicm commentedon Mar 5, 2025
getting json output may be sufficient, we could derive SVG from it if needed
waynexia commentedon Mar 6, 2025
I can implement the JSON part
alamb commentedon Mar 6, 2025
I was thinking about implementing this feature
I think it would be a relatively straightforward implementation:
FORMAT ...Then we would just need to update the code in planning:
datafusion/datafusion/sql/src/statement.rs
Line 177 in da42933
milenkovicm commentedon Mar 6, 2025
as mentioned in #14914 (comment)
ballista uses graphviz-rust which generates those sub-optimal images (and needs local graph viz installation). spark uses
a javascript framework which works much better for them. Maybe we should keep
dotrepresentation if we already have italamb commentedon Mar 11, 2025
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 commentedon Mar 12, 2025
I have two PRs to implement this feature
EXPLAIN ... FORMAT <indent | tree | json | graphviz > ...#15166waynexia commentedon Mar 12, 2025
Thank you @alamb, and apologize for postponing this so long 🙈 (excuse: keeping receiving unscheduled prioritiiii things from @killme2008
alamb commentedon Mar 12, 2025
LOL I totally understand. I happened to have some time and wanted some excuse to write some code :)
(Thanks for the review!)