andygrove opened a new issue, #12599:
URL: https://github.com/apache/datafusion/issues/12599
### Is your feature request related to a problem or challenge?
When running TPC-H q1 in Spark + DataFusion Comet, the expression
`l_extendedprice#21 * (1 - l_discount#22)` appears twice in the query and
currently gets evaluated twice. This could be optimized out so that it is only
evaluated once. I was able to test this by manually rewriting the query.
## Original Query
```sql
select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= date '1998-12-01' - interval '68 days'
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
```
## Optimized Query
```sql
select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(foo) as sum_disc_price,
sum(foo * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from (select
l_returnflag,
l_linestatus,
l_quantity,
l_extendedprice,
l_extendedprice * (1 - l_discount) as foo,
l_tax,
l_discount
from lineitem
where
l_shipdate <= date '1998-12-01' - interval '68 days')
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
```
## Timings (Original)
```
13.752424478530884,
11.648030281066895,
11.35965895652771,
11.335061311721802,
11.383593797683716,
11.291191101074219,
11.31091046333313,
11.351991653442383,
11.32134222984314,
11.374904155731201
```
## Timings (Optimized)
```
12.734412908554077,
10.684742212295532,
10.157625198364258,
10.06518030166626,
10.043614149093628,
9.986022233963013,
9.939271688461304,
9.925782918930054,
10.024176120758057,
10.018519401550293
```
### Describe the solution you'd like
I would like a physical optimizer rule in DataFusion for common
subexpression elimination. IIRC, we already have a logical rule for doing this,
but that does not help for projects that are using other query front ends and
then mapping to DataFusion's physical plan.
An alternative option would be to implement this directly in Comet.
### Describe alternatives you've considered
_No response_
### 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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
