apache / apache/datafusion

Push down common aggregation filters

Open
#18,774 4 comments 0 reactions 0 assignees View on GitHub
enhancement
Dominant language
Rust
Stars
9.3k
Forks
2.4k
Avg merge
3d 7h
Merged PRs (30d)
344

Description

### Is your feature request related to a problem or challenge?

When a SQL query contains multiple aggregations with filters, those filters are currently not pushed down. For instance in a query like

```
select
sum(a) filter (where a > 1 and a % 2 = 0),
count(a) filter (where a > 1)
from t;
```

`a > 1` is common for both aggregates so the query could be rewritten as

```
select
sum(a) filter (where a % 2 = 0),
count(a)
from t
where a > 1;
```

### Describe the solution you'd like

When all aggregations of an aggregate node share partial or complete filter expressions, extract the common parts to a filter node. Rather than executing the above query as

```
AggregateExec: mode=Single, gby=[], aggr=[sum(t.a) FILTER (WHERE t.a > Int64(1) AND t.a % Int64(2) = Int64(0)), count(t.a) FILTER (WHERE t.a > Int64(1))]
ProjectionExec: expr=[column1@0 > 1 as __common_expr_1, column1@0 as a]
DataSourceExec: partitions=1, partition_sizes=[1]
```

it could be executed as

```
AggregateExec: mode=Single, gby=[], aggr=[sum(t.a) FILTER (WHERE t.a % Int64(2) = Int64(0)), count(t.a)]
ProjectionExec: expr=[column1@0 as a]
CoalesceBatchesExec: target_batch_size=8192
FilterExec: column1@0 > 1
DataSourceExec: partitions=1, partition_sizes=[1]
```

### Describe alternatives you've considered

_No response_

### Additional context

_No response_

Contributor guide

Open the contributing guide

Research direction

The issue names no files, tests, or entry points, so first locate DataFusion's aggregate planning and filter-rewrite logic. Use the supplied SQL and EXPLAIN-style plans as the starting case; done means common filter expressions move to a FilterExec and are removed from the aggregate filters without changing query results.

Written by the indexing model from the issue text.

Assessment

Tech stack
rust, sql
Domain
data-engineering, databases
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.