Make Clickbench Q29 5x faster for datafusion by extracting SUM(..) clauses

Is your feature request related to a problem or challenge?

Our datafusion is 5x slower than duckdb for q29, it's easy for us to optimize to 5x faster, here is the try:

Extraction of Constants in Multiple AGG Calls

In ClickBench, some SQL queries can be optimized using RBO (Rule-Based Optimization) without changing semantics. For example, Q29 computes SUM(ResolutionWidth + constant) 90 times, requiring 90 columns in execution. Using the distributive property, we can rewrite it as:

Before Optimization

SELECT SUM(ResolutionWidth), SUM(ResolutionWidth + 1), ..., SUM(ResolutionWidth + 89) 
FROM hits;

After Optimization

SELECT SUM(ResolutionWidth), 
       SUM(ResolutionWidth) + 1 * COUNT(*), 
       ..., 
       SUM(ResolutionWidth) + 89 * COUNT(*) 
FROM hits;

This reduces redundant computations and improves execution efficiency.

Testing result:

Before rewrite:

cargo run --profile release-nonlto   --target aarch64-apple-darwin --bin dfbench -- clickbench  -p benchmarks/data/hits_partitioned -q 29
    Finished `release-nonlto` profile [optimized] target(s) in 0.26s
     Running `target/aarch64-apple-darwin/release-nonlto/dfbench clickbench -p benchmarks/data/hits_partitioned -q 29`
Running benchmarks with the following options: RunOpt { query: Some(29), common: CommonOpt { iterations: 3, partitions: None, batch_size: 8192, mem_pool_type: "fair", memory_limit: None, sort_spill_reservation_bytes: None, debug: false }, path: "benchmarks/data/hits_partitioned", queries_path: "benchmarks/queries/clickbench/queries.sql", output_path: None }
Q29: SELECT SUM("ResolutionWidth"), SUM("ResolutionWidth" + 1), SUM("ResolutionWidth" + 2), SUM("ResolutionWidth" + 3), SUM("ResolutionWidth" + 4), SUM("ResolutionWidth" + 5), SUM("ResolutionWidth" + 6), SUM("ResolutionWidth" + 7), SUM("ResolutionWidth" + 8), SUM("ResolutionWidth" + 9), SUM("ResolutionWidth" + 10), SUM("ResolutionWidth" + 11), SUM("ResolutionWidth" + 12), SUM("ResolutionWidth" + 13), SUM("ResolutionWidth" + 14), SUM("ResolutionWidth" + 15), SUM("ResolutionWidth" + 16), SUM("ResolutionWidth" + 17), SUM("ResolutionWidth" + 18), SUM("ResolutionWidth" + 19), SUM("ResolutionWidth" + 20), SUM("ResolutionWidth" + 21), SUM("ResolutionWidth" + 22), SUM("ResolutionWidth" + 23), SUM("ResolutionWidth" + 24), SUM("ResolutionWidth" + 25), SUM("ResolutionWidth" + 26), SUM("ResolutionWidth" + 27), SUM("ResolutionWidth" + 28), SUM("ResolutionWidth" + 29), SUM("ResolutionWidth" + 30), SUM("ResolutionWidth" + 31), SUM("ResolutionWidth" + 32), SUM("ResolutionWidth" + 33), SUM("ResolutionWidth" + 34), SUM("ResolutionWidth" + 35), SUM("ResolutionWidth" + 36), SUM("ResolutionWidth" + 37), SUM("ResolutionWidth" + 38), SUM("ResolutionWidth" + 39), SUM("ResolutionWidth" + 40), SUM("ResolutionWidth" + 41), SUM("ResolutionWidth" + 42), SUM("ResolutionWidth" + 43), SUM("ResolutionWidth" + 44), SUM("ResolutionWidth" + 45), SUM("ResolutionWidth" + 46), SUM("ResolutionWidth" + 47), SUM("ResolutionWidth" + 48), SUM("ResolutionWidth" + 49), SUM("ResolutionWidth" + 50), SUM("ResolutionWidth" + 51), SUM("ResolutionWidth" + 52), SUM("ResolutionWidth" + 53), SUM("ResolutionWidth" + 54), SUM("ResolutionWidth" + 55), SUM("ResolutionWidth" + 56), SUM("ResolutionWidth" + 57), SUM("ResolutionWidth" + 58), SUM("ResolutionWidth" + 59), SUM("ResolutionWidth" + 60), SUM("ResolutionWidth" + 61), SUM("ResolutionWidth" + 62), SUM("ResolutionWidth" + 63), SUM("ResolutionWidth" + 64), SUM("ResolutionWidth" + 65), SUM("ResolutionWidth" + 66), SUM("ResolutionWidth" + 67), SUM("ResolutionWidth" + 68), SUM("ResolutionWidth" + 69), SUM("ResolutionWidth" + 70), SUM("ResolutionWidth" + 71), SUM("ResolutionWidth" + 72), SUM("ResolutionWidth" + 73), SUM("ResolutionWidth" + 74), SUM("ResolutionWidth" + 75), SUM("ResolutionWidth" + 76), SUM("ResolutionWidth" + 77), SUM("ResolutionWidth" + 78), SUM("ResolutionWidth" + 79), SUM("ResolutionWidth" + 80), SUM("ResolutionWidth" + 81), SUM("ResolutionWidth" + 82), SUM("ResolutionWidth" + 83), SUM("ResolutionWidth" + 84), SUM("ResolutionWidth" + 85), SUM("ResolutionWidth" + 86), SUM("ResolutionWidth" + 87), SUM("ResolutionWidth" + 88), SUM("ResolutionWidth" + 89) FROM hits;
Query 29 iteration 0 took 341.5 ms and returned 1 rows
Query 29 iteration 1 took 320.7 ms and returned 1 rows
Query 29 iteration 2 took 303.0 ms and returned 1 rows
Query 29 avg time: 321.73 ms

After rewrite:

cargo run --profile release-nonlto   --target aarch64-apple-darwin --bin dfbench -- clickbench  -p benchmarks/data/hits_partitioned -q 29
    Finished `release-nonlto` profile [optimized] target(s) in 0.26s
     Running `target/aarch64-apple-darwin/release-nonlto/dfbench clickbench -p benchmarks/data/hits_partitioned -q 29`
Running benchmarks with the following options: RunOpt { query: Some(29), common: CommonOpt { iterations: 3, partitions: None, batch_size: 8192, mem_pool_type: "fair", memory_limit: None, sort_spill_reservation_bytes: None, debug: false }, path: "benchmarks/data/hits_partitioned", queries_path: "benchmarks/queries/clickbench/queries.sql", output_path: None }
Q29: SELECT SUM("ResolutionWidth"), SUM("ResolutionWidth") + 1 * COUNT(*), SUM("ResolutionWidth") + 2 * COUNT(*), SUM("ResolutionWidth") + 3 * COUNT(*), SUM("ResolutionWidth") + 4 * COUNT(*), SUM("ResolutionWidth") + 5 * COUNT(*), SUM("ResolutionWidth") + 6 * COUNT(*), SUM("ResolutionWidth") + 7 * COUNT(*), SUM("ResolutionWidth") + 8 * COUNT(*), SUM("ResolutionWidth") + 9 * COUNT(*), SUM("ResolutionWidth") + 10 * COUNT(*), SUM("ResolutionWidth") + 11 * COUNT(*), SUM("ResolutionWidth") + 12 * COUNT(*), SUM("ResolutionWidth") + 13 * COUNT(*), SUM("ResolutionWidth") + 14 * COUNT(*), SUM("ResolutionWidth") + 15 * COUNT(*), SUM("ResolutionWidth") + 16 * COUNT(*), SUM("ResolutionWidth") + 17 * COUNT(*), SUM("ResolutionWidth") + 18 * COUNT(*), SUM("ResolutionWidth") + 19 * COUNT(*), SUM("ResolutionWidth") + 20 * COUNT(*), SUM("ResolutionWidth") + 21 * COUNT(*), SUM("ResolutionWidth") + 22 * COUNT(*), SUM("ResolutionWidth") + 23 * COUNT(*), SUM("ResolutionWidth") + 24 * COUNT(*), SUM("ResolutionWidth") + 25 * COUNT(*), SUM("ResolutionWidth") + 26 * COUNT(*), SUM("ResolutionWidth") + 27 * COUNT(*), SUM("ResolutionWidth") + 28 * COUNT(*), SUM("ResolutionWidth") + 29 * COUNT(*), SUM("ResolutionWidth") + 30 * COUNT(*), SUM("ResolutionWidth") + 31 * COUNT(*), SUM("ResolutionWidth") + 32 * COUNT(*), SUM("ResolutionWidth") + 33 * COUNT(*), SUM("ResolutionWidth") + 34 * COUNT(*), SUM("ResolutionWidth") + 35 * COUNT(*), SUM("ResolutionWidth") + 36 * COUNT(*), SUM("ResolutionWidth") + 37 * COUNT(*), SUM("ResolutionWidth") + 38 * COUNT(*), SUM("ResolutionWidth") + 39 * COUNT(*), SUM("ResolutionWidth") + 40 * COUNT(*), SUM("ResolutionWidth") + 41 * COUNT(*), SUM("ResolutionWidth") + 42 * COUNT(*), SUM("ResolutionWidth") + 43 * COUNT(*), SUM("ResolutionWidth") + 44 * COUNT(*), SUM("ResolutionWidth") + 45 * COUNT(*), SUM("ResolutionWidth") + 46 * COUNT(*), SUM("ResolutionWidth") + 47 * COUNT(*), SUM("ResolutionWidth") + 48 * COUNT(*), SUM("ResolutionWidth") + 49 * COUNT(*), SUM("ResolutionWidth") + 50 * COUNT(*), SUM("ResolutionWidth") + 51 * COUNT(*), SUM("ResolutionWidth") + 52 * COUNT(*), SUM("ResolutionWidth") + 53 * COUNT(*), SUM("ResolutionWidth") + 54 * COUNT(*), SUM("ResolutionWidth") + 55 * COUNT(*), SUM("ResolutionWidth") + 56 * COUNT(*), SUM("ResolutionWidth") + 57 * COUNT(*), SUM("ResolutionWidth") + 58 * COUNT(*), SUM("ResolutionWidth") + 59 * COUNT(*), SUM("ResolutionWidth") + 60 * COUNT(*), SUM("ResolutionWidth") + 61 * COUNT(*), SUM("ResolutionWidth") + 62 * COUNT(*), SUM("ResolutionWidth") + 63 * COUNT(*), SUM("ResolutionWidth") + 64 * COUNT(*), SUM("ResolutionWidth") + 65 * COUNT(*), SUM("ResolutionWidth") + 66 * COUNT(*), SUM("ResolutionWidth") + 67 * COUNT(*), SUM("ResolutionWidth") + 68 * COUNT(*), SUM("ResolutionWidth") + 69 * COUNT(*), SUM("ResolutionWidth") + 70 * COUNT(*), SUM("ResolutionWidth") + 71 * COUNT(*), SUM("ResolutionWidth") + 72 * COUNT(*), SUM("ResolutionWidth") + 73 * COUNT(*), SUM("ResolutionWidth") + 74 * COUNT(*), SUM("ResolutionWidth") + 75 * COUNT(*), SUM("ResolutionWidth") + 76 * COUNT(*), SUM("ResolutionWidth") + 77 * COUNT(*), SUM("ResolutionWidth") + 78 * COUNT(*), SUM("ResolutionWidth") + 79 * COUNT(*), SUM("ResolutionWidth") + 80 * COUNT(*), SUM("ResolutionWidth") + 81 * COUNT(*), SUM("ResolutionWidth") + 82 * COUNT(*), SUM("ResolutionWidth") + 83 * COUNT(*), SUM("ResolutionWidth") + 84 * COUNT(*), SUM("ResolutionWidth") + 85 * COUNT(*), SUM("ResolutionWidth") + 86 * COUNT(*), SUM("ResolutionWidth") + 87 * COUNT(*), SUM("ResolutionWidth") + 88 * COUNT(*), SUM("ResolutionWidth") + 89 * COUNT(*) FROM hits;
Query 29 iteration 0 took 86.9 ms and returned 1 rows
Query 29 iteration 1 took 59.3 ms and returned 1 rows
Query 29 iteration 2 took 42.3 ms and returned 1 rows
Query 29 avg time: 62.85 ms

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response

Clickhouse also has the optimization:
https://github.com/ClickHouse/ClickHouse/pull/57853/files

take

@alamb

Can anyone guide me how to do this in datafusion, i am not familiar with the rewrite now. So need some code reference.

cc @alamb @jonahgao @jayzhan211
Thanks!

You can implement this in rewrite rule SimplifyExpressions in logical optimizer

datafusion/datafusion/optimizer/src/simplify_expressions/expr_simplifier.rs

Lines 730 to 748 in 507f6b6

    
           Ok(match expr { 
        
               // 
        
               // Rules for Eq 
        
               // 
        
               // true = A  --> A 
        
               // false = A --> !A 
        
               // null = A --> null 
        
               Expr::BinaryExpr(BinaryExpr { 
        
                   left, 
        
                   op: Eq, 
        
                   right, 
        
               }) if is_bool_lit(&left) && info.is_boolean_type(&right)? => { 
        
                   Transformed::yes(match as_bool_lit(&left)? { 
        
                       Some(true) => *right, 
        
                       Some(false) => Expr::Not(right), 
        
                       None => lit_bool_null(), 
        
                   }) 
        
               }

if there is sum(column + const_a) then you return sum(column) + const_a * count(*).

@jayzhan211

Thank you @jayzhan211 for the guide, i will try this!

Nice idea!
I think it makes sense to avoid doing this optimization when there is only a single case of this - otherwise the number of accumulators gets bigger (combined with group by this will lead to higher memory usage).

@zhuqi-lucas

As long as this optimization is plausibly general purpose (not just for ClickBench) and the code required is isolated / doesn't add a bunch of complexity, I am in support of this optimization

Thank you @zhuqi-lucas

I found a duckdb implementation of a seemingling similar optimization: https://github.com/duckdb/duckdb/blob/7912713493b38b1eda162f29b7759d5024989a5f/src/optimizer/sum_rewriter.cpp#L25

@Dandandan

Thank you @Dandandan @alamb for double check and confirm!

I found the rationale behind adding it in DuckDB was also ClickBench.

duckdb/duckdb#15017

As a randomly selected query, let's run this query from ClickBench:

😆

So we're now officially benchmaxxing!

I'm wondering if instead of this specialized

SUM(x + c) -> SUM(x) * COUNT() * c

We can do instead the more general (distributive property of summation - doesn't need to be a constant):

SUM(x + y) -> SUM(x) + SUM(y)

And have other optimizations follow (transform SUM(y) -> COUNT() * y when y is constant, common subexpression elimination, etc.)

The nice part of that is that addition in SUM can be quite common and SUM(x) + SUM(y) is faster than SUM(x + y) (even without transforming to use COUNT)

@Dandandan

I'm wondering if instead of this specialized

SUM(x + c) -> SUM(x) * COUNT() * c

We can do instead the more general (distributive property of summation - doesn't need to be a constant):
SUM(x + y) -> SUM(x) + SUM(y)
And have other optimizations follow (transform SUM(y) -> COUNT() * y when y is constant, common subexpression elimination, etc.)

Thank you @Dandandan for this good idea, i agree with you, it's a more common solution and it will benefit more cases.

And meanwhile, may be we can do more cases in future, such as when MAX(x) > 0:

MAX(5 * x) => 5 * MAX(x)

etc

@alamb

Need help, i am not sure if i do the right direction for this ticket:

I am still debugging my draft PR, if folks can cooperate, feel free to add a fix to my PR because it will meet more corner cases i believe, and i am still not fixing the existing error:

#15532

More problems need to be fixed based on my PR, for example:

It seems, simplify_expressions will change the sum(a + b) => sum(a) + sum(b), so the error happens:

logical_plan after simplify_expressions
01)Projection: sum(simple_explain_test.a + simple_explain_test.b)
02)--Aggregate: groupBy=[[]], aggr=[[sum(simple_explain_test.a) + sum(simple_explain_test.b) AS sum(simple_explain_test.a + simple_explain_test.b)]]
03)----TableScan: simple_explain_test

logical_plan
01)Aggregate: groupBy=[[]], aggr=[[sum(simple_explain_test.a) + sum(simple_explain_test.b) AS sum(simple_explain_test.a + simple_explain_test.b)]]
02)--TableScan: simple_explain_test projection=[a, b]
physical_plan_error
01)Internal error: Invalid aggregate expression 'BinaryExpr(BinaryExpr { left: AggregateFunction(AggregateFunction { func: AggregateUDF { inner: Sum { signature: Signature { type_signature: UserDefined, volatility: Immutable } } }, params: AggregateFunctionParams { args: [Column(Column { relation: Some(Bare { table: "simple_explain_test" }), name: "a" })], distinct: false, filter: None, order_by: None, null_treatment: None } }), op: Plus, right: AggregateFunction(AggregateFunction { func: AggregateUDF { inner: Sum { signature: Signature { type_signature: UserDefined, volatility: Immutable } } }, params: AggregateFunctionParams { args: [Column(Column { relation: Some(Bare { table: "simple_explain_test" }), name: "b" })], distinct: false, filter: None, order_by: None, null_treatment: None } }) })'.
02)This was likely caused by a bug in DataFusion's code and we would welcome that you file an bug report in our issue tracker

Some unit tests are failing, need to investigate.
Need to check if the datatype changes, it is still correct for the rewrite.
etc

@alamb
And it seems duckdb also only do the sum(a + C), i am wandering if we can do similar first besides the sum(a + b) => sum(a) + sum(b)

duckdb/duckdb#15017

I found a duckdb implementation of a seemingling similar optimization: https://github.com/duckdb/duckdb/blob/7912713493b38b1eda162f29b7759d5024989a5f/src/optimizer/sum_rewriter.cpp#L25

@berkaysynnada

So in my opinion before we get too fancy we should figure out what optimizaton we are really trying to do

I understand it may be possible to do a more general rewrite such as @berkaysynnada suggests above #15524 (comment)

However, I think it would help to have an actual usecase / real query.

The only usecase I know of is the click bench query

SELECT SUM(ResolutionWidth), SUM(ResolutionWidth + 1), SUM(ResolutionWidth + 2), SUM(ResolutionWidth + 3), SUM(ResolutionWidth + 4), SUM(ResolutionWidth + 5), SUM(ResolutionWidth + 6), SUM(ResolutionWidth + 7), SUM(ResolutionWidth + 8), SUM(ResolutionWidth + 9), SUM(ResolutionWidth + 10), SUM(ResolutionWidth + 11), SUM(ResolutionWidth + 12), SUM(ResolutionWidth + 13), SUM(ResolutionWidth + 14), SUM(ResolutionWidth + 15), SUM(ResolutionWidth + 16), SUM(ResolutionWidth + 17), SUM(ResolutionWidth + 18), SUM(ResolutionWidth + 19), SUM(ResolutionWidth + 20), SUM(ResolutionWidth + 21), SUM(ResolutionWidth + 22), SUM(ResolutionWidth + 23), SUM(ResolutionWidth + 24), SUM(ResolutionWidth + 25), SUM(ResolutionWidth + 26), SUM(ResolutionWidth + 27), SUM(ResolutionWidth + 28), SUM(ResolutionWidth + 29), SUM(ResolutionWidth + 30), SUM(ResolutionWidth + 31), SUM(ResolutionWidth + 32), SUM(ResolutionWidth + 33), SUM(ResolutionWidth + 34), SUM(ResolutionWidth + 35), SUM(ResolutionWidth + 36), SUM(ResolutionWidth + 37), SUM(ResolutionWidth + 38), SUM(ResolutionWidth + 39), SUM(ResolutionWidth + 40), SUM(ResolutionWidth + 41), SUM(ResolutionWidth + 42), SUM(ResolutionWidth + 43), SUM(ResolutionWidth + 44), SUM(ResolutionWidth + 45), SUM(ResolutionWidth + 46), SUM(ResolutionWidth + 47), SUM(ResolutionWidth + 48), SUM(ResolutionWidth + 49), SUM(ResolutionWidth + 50), SUM(ResolutionWidth + 51), SUM(ResolutionWidth + 52), SUM(ResolutionWidth + 53), SUM(ResolutionWidth + 54), SUM(ResolutionWidth + 55), SUM(ResolutionWidth + 56), SUM(ResolutionWidth + 57), SUM(ResolutionWidth + 58), SUM(ResolutionWidth + 59), SUM(ResolutionWidth + 60), SUM(ResolutionWidth + 61), SUM(ResolutionWidth + 62), SUM(ResolutionWidth + 63), SUM(ResolutionWidth + 64), SUM(ResolutionWidth + 65), SUM(ResolutionWidth + 66), SUM(ResolutionWidth + 67), SUM(ResolutionWidth + 68), SUM(ResolutionWidth + 69), SUM(ResolutionWidth + 70), SUM(ResolutionWidth + 71), SUM(ResolutionWidth + 72), SUM(ResolutionWidth + 73), SUM(ResolutionWidth + 74), SUM(ResolutionWidth + 75), SUM(ResolutionWidth + 76), SUM(ResolutionWidth + 77), SUM(ResolutionWidth + 78), SUM(ResolutionWidth + 79), SUM(ResolutionWidth + 80), SUM(ResolutionWidth + 81), SUM(ResolutionWidth + 82), SUM(ResolutionWidth + 83), SUM(ResolutionWidth + 84), SUM(ResolutionWidth + 85), SUM(ResolutionWidth + 86), SUM(ResolutionWidth + 87), SUM(ResolutionWidth + 88), SUM(ResolutionWidth + 89) FROM hits;

I have never seen such a query elsewhere and I would struggle to explain to someone in human language what that was calculating and why it was written that way

Thus for this one, I would personally suggest either:

just following the Duck and make a benchmark specific optimization (and don't try to handle any other cases)
take the high road and say we aren't going to benchmaxx

Alternately we can spend some time trying to do something more general, but I probably don't have much time to help here as I don't think it will be widely applicable

@zhuqi-lucas

just following the Duck and make a benchmark specific optimization (and don't try to handle any other cases)

take the high road and say we aren't going to benchmaxx

If we prefer doing 1. option first and @zhuqi-lucas completes the work, it is ok. But I know some people who possibly want to work on the 2nd option, and I can help reviewing/iterating.

@alamb

Thank you @alamb , @berkaysynnada for further step suggestion, i will create a follow-up ticket to do the 2nd option if i completed option 1.

I'm very interested in this issue, so I try a simple POC. Below are my observations and recommendations.

If my understanding is correct, a truly linear function should satisfy the identity:

f(3x + 2y + 1) = 3f(x) + 2f(y) + f(1)，

However, in practice, none of the basic SQL aggregate functions—SUM, COUNT, MAX, or MIN—exhibit this linearity.

SUM

Take the expression:

sum(3x + 2y + 1),

we want to split it into

3sum(x) + 2sum(y) + sum(1)

This conversion is based on the assumption that both x and y are non-null:

For any row, assuming x is null, the corresponding sum(3x + 2y + 1) should accumulate 0, but after splitting, sum(1) will accumulate 1

To make the transformation semantically equivalent, we need to add null filtering from the original expression. A more accurate rewrite would be:

3 * sum(x) filter (y is not null) + 2 * sum(y) filter (x is not null) + sum(1) filter (x, y is not null)

Similarly, for Q29, SUM("ResolutionWidth" + 1) should be converted to SUM(ResolutionWidth) + 1 * COUNT(ResolutionWidth) rather than COUNT(*).

MAX/MIN

We seem to be able to support only the most basic scenarios e.g..

max(a + 1)=max(a) + 1

Consider the following scenario, the conversions should not be equivalent.

max(1 - a) != 1 - max(a)

max(a + b) != max(a) + max(b)

COUNT

COUNT has almost none of the characteristics of linear, e.g..
count(x + 1) != count(x) + 1
The optimization I can think of is: if all fields in expr are null and expr always outputs null, then we can eliminate the computation of expr except for the fields. i.e.
count(x + 1) => count(x)

Conclusion

In conclusion, unfortunately, I haven't found a universal approach to handle the envisioned "linear functions" as none fully meet the criteria of linear functions.

Moreover, in my view, the optimization scenarios described above have very limited applicability. I'm uncertain whether such queries would occur in real-world scenarios. However, for Q29, the SUM optimization can provide significant performance improvements.

@suibianwanwank

Moreover, in my view, the optimization scenarios described above have very limited applicability. I'm uncertain whether such queries would occur in real-world scenarios. However, for Q29, the SUM optimization can provide significant performance improvements.

Hi @suibianwanwank. Thank you for your kind and detailed explanation. As you've mentioned, the only applicable function seems to be SUM (and when the field is not nullable) in general, but, as being always, it is better to implement a generic approach as many other custom UDF's can utilize this optimization.

I think we can fix this issue by implementing simplify for sum

datafusion/datafusion/expr/src/udaf.rs

Lines 682 to 684 in 35ff4ab

    
               fn simplify(&self) -> Option<AggregateFunctionSimplification> { 
        
                   None 
        
               }

, just like in #18837.

After this, we can support rewrites like:

sum(col+6) -> sum(col) + 6 * count(col)
sum(col*6) -> sum(col) * 6
max(-6*col) -> -6*min(col)

I have a proposal ready for review (results in 7x faster Q29)

Tests:

Add tests for simplifying multiple aggregate expressions #20723

Rewrite:

Rewrite SUM(expr + scalar) --> SUM(expr) + scalar*COUNT(expr) #20749

Add tests for simplifying multiple aggregate expressions (#20723)

It seems like to me that aggregations AGG over SQL-integers $\mathbb{Z}_\bot$ need to be examined on a case-by-case basis just like AGG over integers $\mathbb{Z}$. For aggregations (applied row-wise ignoring null row) are defined over columns ($c_0, ..., c_n$) we see that

$\text{AGG}(c_0 \oplus^1 c_1 \oplus^2 ... \oplus^n c_n) = AGG(c_0 \oplus^1 c_1 \oplus^2 ... \oplus^n c_n\ \text{filter}\ m)$

where $m$ is the set of row where all elements are defined and all ops $\oplus^1, .., \oplus^n$ are all null NULL-propagating/annihilating ($\forall x. \bot \oplus^i x = \bot$). Furthermore RHS is all the contributing rows. This means under the filter $m$ we can use integer reasoning since there are by definition no nulls in any rows.

Also notice that is handle non-nullable columns easily.

Now lets think about specific examples:

SUM

Consider a sum in sql SUM(a+b+c) we can write this as SUM(a filter m) + SUM(b filter m) + SUM(c filter m) where m = a and b and c is not null. Then we can use regular mathematical equivalencies over $\sum_i^m c^1_i \oplus ... \oplus' c^n_i$.

Count

I think the same idea holds for count COUNT(a+b+c) = COUNT(m)

MAX/MIN

This is likely irreducible due to the subadditivity $\forall a, b \in \mathbb{Z}.\max(a + b) \leq \max(a) + \max(b)$.

Rewrite `SUM(expr + scalar)` --> `SUM(expr) + scalar*COUNT(expr)` (#2…

Add tests for simplifying multiple aggregate expressions (apache#20723)

Rewrite `SUM(expr + scalar)` --> `SUM(expr) + scalar*COUNT(expr)` (ap…

Navigation Menu

Repository navigation

Make Clickbench Q29 5x faster for datafusion by extracting `SUM(..)` clauses #15524

Is your feature request related to a problem or challenge?

Extraction of Constants in Multiple AGG Calls

Before Optimization

After Optimization

Testing result:

Describe the solution you'd like

Describe alternatives you've considered

Additional context

4 remaining items

SUM

MAX/MIN

COUNT

Conclusion

SUM

Count

MAX/MIN

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

	Ok(match expr {
	//
	// Rules for Eq
	//

	// true = A --> A
	// false = A --> !A
	// null = A --> null
	Expr::BinaryExpr(BinaryExpr {
	left,
	op: Eq,
	right,
	}) if is_bool_lit(&left) && info.is_boolean_type(&right)? => {
	Transformed::yes(match as_bool_lit(&left)? {
	Some(true) => *right,
	Some(false) => Expr::Not(right),
	None => lit_bool_null(),
	})
	}

	fn simplify(&self) -> Option<AggregateFunctionSimplification> {
	None
	}

Make Clickbench Q29 5x faster for datafusion by extracting SUM(..) clauses #15524

Description

Is your feature request related to a problem or challenge?

Extraction of Constants in Multiple AGG Calls

Before Optimization

After Optimization

Testing result:

Describe the solution you'd like

Describe alternatives you've considered

Additional context

Activity

zhuqi-lucas commented on Apr 1, 2025

zhuqi-lucas commented on Apr 1, 2025

zhuqi-lucas commented on Apr 1, 2025

jayzhan211 commented on Apr 1, 2025

zhuqi-lucas commented on Apr 1, 2025

Dandandan commented on Apr 1, 2025

alamb commented on Apr 1, 2025

alamb commented on Apr 1, 2025

zhuqi-lucas commented on Apr 2, 2025

Dandandan commented on Apr 2, 2025

Dandandan commented on Apr 2, 2025

Dandandan commented on Apr 2, 2025

zhuqi-lucas commented on Apr 2, 2025

4 remaining items

zhuqi-lucas commented on Apr 3, 2025

alamb commented on Apr 4, 2025

berkaysynnada commented on Apr 6, 2025

zhuqi-lucas commented on Apr 6, 2025

suibianwanwank commented on Apr 8, 2025

SUM

MAX/MIN

COUNT

Conclusion

berkaysynnada commented on Apr 8, 2025

UBarney commented on Feb 10, 2026

alamb commented on Mar 9, 2026

joseph-isaacs commented on Mar 10, 2026

SUM

Count

MAX/MIN

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Make Clickbench Q29 5x faster for datafusion by extracting `SUM(..)` clauses #15524