You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by "alamb (via GitHub)" <gi...@apache.org> on 2024/02/27 19:37:30 UTC
[I] Implement Nicer / DuckDB style explain plans [arrow-datafusion]
alamb opened a new issue, #9371:
URL: https://github.com/apache/arrow-datafusion/issues/9371
### Is your feature request related to a problem or challenge?
When trying to understand what a plan does, DataFusion EXPLAIN plans have all the detail, but are sometimes hard to read
For example if we run `EXPLAIN` on ClickBench query 28,
```sql
EXPLAIN SELECT REGEXP_REPLACE("Referer", '^https?://(?:www\\.)?([^/]+)/.*$', '\\1') AS k, AVG(length("Referer")) AS l, COUNT(*) AS c, MIN("Referer") FROM "hits.parquet" WHERE "Referer" <> '' GROUP BY k HAVING COUNT(*) > 100000 ORDER BY l DESC LIMIT 25;
```
Results in this explain plan
```sql
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type | plan |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| logical_plan | Limit: skip=0, fetch=25 |
| | Sort: l DESC NULLS FIRST, fetch=25 |
| | Projection: regexp_replace(hits.parquet.Referer,Utf8("^https?://(?:www\.)?([^/]+)/.*$"),Utf8("\1")) AS k, AVG(character_length(hits.parquet.Referer)) AS l, COUNT(*) AS c, MIN(hits.parquet.Referer) |
| | Filter: COUNT(*) > Int64(100000) |
| | Aggregate: groupBy=[[regexp_replace(hits.parquet.Referer, Utf8("^https?://(?:www\.)?([^/]+)/.*$"), Utf8("\1"))]], aggr=[[AVG(CAST(character_length(hits.parquet.Referer) AS Float64)), COUNT(UInt8(1)) AS COUNT(*), MIN(hits.parquet.Referer)]] |
| | Filter: hits.parquet.Referer != Utf8("") |
| | TableScan: hits.parquet projection=[Referer], partial_filters=[hits.parquet.Referer != Utf8("")] |
| physical_plan | GlobalLimitExec: skip=0, fetch=25 |
| | SortPreservingMergeExec: [l@1 DESC], fetch=25 |
| | SortExec: TopK(fetch=25), expr=[l@1 DESC] |
| | ProjectionExec: expr=[regexp_replace(hits.parquet.Referer,Utf8("^https?://(?:www\.)?([^/]+)/.*$"),Utf8("\1"))@0 as k, AVG(character_length(hits.parquet.Referer))@1 as l, COUNT(*)@2 as c, MIN(hits.parquet.Referer)@3 as MIN(hits.parquet.Referer)] |
| | CoalesceBatchesExec: target_batch_size=8192 |
| | FilterExec: COUNT(*)@2 > 100000 |
| | AggregateExec: mode=FinalPartitioned, gby=[regexp_replace(hits.parquet.Referer,Utf8("^https?://(?:www\.)?([^/]+)/.*$"),Utf8("\1"))@0 as regexp_replace(hits.parquet.Referer,Utf8("^https?://(?:www\.)?([^/]+)/.*$"),Utf8("\1"))], aggr=[AVG(character_length(hits.parquet.Referer)), COUNT(*), MIN(hits.parquet.Referer)] |
| | CoalesceBatchesExec: target_batch_size=8192 |
| | RepartitionExec: partitioning=Hash([regexp_replace(hits.parquet.Referer,Utf8("^https?://(?:www\.)?([^/]+)/.*$"),Utf8("\1"))@0], 16), input_partitions=16 |
| | AggregateExec: mode=Partial, gby=[regexp_replace(Referer@0, ^https?://(?:www\.)?([^/]+)/.*$, \1) as regexp_replace(hits.parquet.Referer,Utf8("^https?://(?:www\.)?([^/]+)/.*$"),Utf8("\1"))], aggr=[AVG(character_length(hits.parquet.Referer)), COUNT(*), MIN(hits.parquet.Referer)] |
| | CoalesceBatchesExec: target_batch_size=8192 |
| | FilterExec: Referer@0 != |
| | ParquetExec: file_groups={16 groups: [[Users/andrewlamb/Downloads/hits.parquet:0..923748528], [Users/andrewlamb/Downloads/hits.parquet:923748528..1847497056], [Users/andrewlamb/Downloads/hits.parquet:1847497056..2771245584], [Users/andrewlamb/Downloads/hits.parquet:2771245584..3694994112], [Users/andrewlamb/Downloads/hits.parquet:3694994112..4618742640], ...]}, projection=[Referer], predicate=Referer@14 != , pruning_predicate=Referer_min@0 != OR != Referer_max@1, required_guarantees=[Referer not in ()] |
| | |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set. Query took 0.045 seconds.
```
This has all the details but it takes non trivial expertise to understand what it is doing. The challenge is even more pronounced for queries with Unions or Joins where some nodes have multiple children
### Describe the solution you'd like
I would like DuckDB style explain plans (probably as an option / in addition to the current ones)
```
andrewlamb@Andrews-MacBook-Pro:~/Downloads$ duckdb
v0.9.2 3c695d7ba9
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D EXPLAIN SELECT REGEXP_REPLACE("Referer", '^https?://(?:www\\.)?([^/]+)/.*$', '\\1') AS k, AVG(length("Referer")) AS l, COUNT(*) AS c, MIN("Referer") FROM hits.parquet" WHERE "Referer" <> '' GROUP BY k HAVING COUNT(*) > 100000 ORDER BY l DESC LIMIT 25;
┌─────────────────────────────┐
│┌───────────────────────────┐│
││ Physical Plan ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│ TOP_N │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ Top 25 │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ avg(length(hits.Referer)) │
│ DESC │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ 0 │
│ l │
│ c │
│ min(Referer) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ FILTER │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ (count_star() > 100000) │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ EC: 99997497 │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HASH_GROUP_BY │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ #0 │
│ count_star() │
│ avg(#1) │
│ min(#2) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ k │
│ length(Referer) │
│ Referer │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ FILTER │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ (Referer != '') │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ EC: 99997497 │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PARQUET_SCAN │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ Referer │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ EC: 99997497 │
└───────────────────────────┘
```
### Describe alternatives you've considered
There are several functions already for creating graphviz plans such as [this](https://docs.rs/datafusion/latest/datafusion/physical_plan/display/struct.DisplayableExecutionPlan.html#method.graphviz), but I think they get limited use because they can't be displayed directly (they need to be rendered into visual format)
### Additional context
_No response_
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Implement Nicer / DuckDB style explain plans [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #9371:
URL: https://github.com/apache/datafusion/issues/9371#issuecomment-2695593933
@irenjj has a nice initial implementation here
- https://github.com/apache/datafusion/pull/14677
We are going to track completing the project here
- https://github.com/apache/datafusion/issues/14914
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Implement Nicer / DuckDB style explain plans [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #9371:
URL: https://github.com/apache/datafusion/issues/9371#issuecomment-2624551049
Some suggestions for anyone who wants to implement this feature:
Add a configuration option like `datafusion.explain.format` that defaults to `'indent'` that controls how the explain plan is displayed
For example,
```sql
> set datafusion.explain.format = 'pretty'
> EXPLAIN .....
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type | plan |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| logical_plan | Limit: skip=0, fetch=25 |
| | Sort: l DESC NULLS FIRST, fetch=25 |
| | Projection: regexp_replace(hits.parquet.Referer,Utf8("^https?://(?:www\.)?([^/]+)/.*$"),Utf8("\1")) AS k, AVG(character_length(hits.parquet.Referer)) AS l, COUNT(*) AS c, MIN(hits.parquet.Referer) |
| | Filter: COUNT(*) > Int64(100000) |
| | Aggregate: groupBy=[[regexp_replace(hits.parquet.Referer, Utf8("^https?://(?:www\.)?([^/]+)/.*$"), Utf8("\1"))]], aggr=[[AVG(CAST(character_length(hits.parquet.Referer) AS Float64)), COUNT(UInt8(1)) AS COUNT(*), MIN(hits.parquet.Referer)]] |
| | Filter: hits.parquet.Referer != Utf8("") |
| | TableScan: hits.parquet projection=[Referer], partial_filters=[hits.parquet.Referer != Utf8("")] |
| physical_plan |
┌─────────────────────────────┐
│┌───────────────────────────┐│
││ Physical Plan ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│ TOP_N │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ Top 25 │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ avg(length(hits.Referer)) │
│ DESC │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ 0 │
│ l │
│ c │
│ min(Referer) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ FILTER │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ (count_star() > 100000) │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ EC: 99997497 │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ HASH_GROUP_BY │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ #0 │
│ count_star() │
│ avg(#1) │
│ min(#2) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ k │
│ length(Referer) │
│ Referer │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ FILTER │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ (Referer != '') │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ EC: 99997497 │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PARQUET_SCAN │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ Referer │
│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │
│ EC: 99997497 │
└───────────────────────────┘
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set. Query took 0.045 seconds.
```
## Implementation suggestion:
1. Add a new variant to https://docs.rs/datafusion/latest/datafusion/physical_plan/enum.DisplayFormatType.html for `Pretty`
2. Implement it only for for a few nodes first (`Projection`, `FilterExec`, etc) and leave the implementation of other nodes as placeholders
3. Add a test in `sqllogictests/test_files/explain_pretty.slt` for a basic query like `SELECT * FROM foo`
Once we get the basic framework in place we can then fill out the implementation of the rest of the ExecutionPlan nodes and figure out how to do the same for `LogicalPlan`
This strategy would let us get this project started in an incremental way without having to make one massive PR
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Implement Nicer / DuckDB style explain plans [datafusion]
Posted by "tinfoil-knight (via GitHub)" <gi...@apache.org>.
tinfoil-knight commented on issue #9371:
URL: https://github.com/apache/datafusion/issues/9371#issuecomment-2106289301
I'm un-assigning myself since I didn't get the time to plan and implement this feature.
For other contributors, these links might help:
- https://github.com/duckdb/duckdb/pull/1046
- https://github.com/duckdb/duckdb/pull/2613
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Implement Nicer / DuckDB style explain plans [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #9371: Implement Nicer / DuckDB style explain plans
URL: https://github.com/apache/datafusion/issues/9371
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Implement Nicer / DuckDB style explain plans [arrow-datafusion]
Posted by "tinfoil-knight (via GitHub)" <gi...@apache.org>.
tinfoil-knight commented on issue #9371:
URL: https://github.com/apache/arrow-datafusion/issues/9371#issuecomment-2007231663
take
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Implement Nicer / DuckDB style explain plans [datafusion]
Posted by "irenjj (via GitHub)" <gi...@apache.org>.
irenjj commented on issue #9371:
URL: https://github.com/apache/datafusion/issues/9371#issuecomment-2638964832
take
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org