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: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]

Reply via email to