[jira] [Created] (CALCITE-7446) Add rule to pushdown conditions from filtered aggregates

["Stamatis Zampetakis (Jira)" <ji...@apache.org>]

Stamatis Zampetakis created CALCITE-7446:
--------------------------------------------

             Summary: Add rule to pushdown conditions from filtered aggregates
                 Key: CALCITE-7446
                 URL: https://issues.apache.org/jira/browse/CALCITE-7446
             Project: Calcite
          Issue Type: Improvement
          Components: core
            Reporter: Stamatis Zampetakis
            Assignee: Stamatis Zampetakis


Add a new rule, AggregateExtractFilterRule, to extract and pushdown conditions from filtered aggregates.

In SQL syntax, the transformation on a very simple query is outlined below.

+Before+
{code:sql}
SELECT
  SUM(sal) FILTER (WHERE deptno = 10),
  SUM(sal) FILTER (WHERE deptno = 20),
FROM emp
{code}
+After+
{code:sql}
SELECT
  SUM(sal) FILTER (WHERE deptno = 10),
  SUM(sal) FILTER (WHERE deptno = 20),
FROM emp
WHERE deptno = 10 OR deptno = 20
{code}
For the transformation to be valid all aggregate functions must have a filter condition.
The initial version of the rule will only deal with grand totals (no GROUP BY).

At first the additional filter in the WHERE clause may appear somewhat redundant but in practice it can help to greatly reduce the rows that reach the aggregation functions especially if the table is large. In combination with other rules/techniques (such as index selection, partition pruning, filter pushdown) it can make a big different in performance.

Below a slightly more complex example showcasing how the new rule combined with the existing JOIN pushdown rules can lead to a better plan.
{code:sql}
SELECT
  SUM(e.sal) FILTER (WHERE d.name = 'SALES'),
  SUM(e.sal) FILTER (WHERE d.name = 'RESEARCH')
FROM emp e JOIN dept d ON e.deptno = d.deptno
{code}
Currently, the aggregate processes all rows that come from the join of {{emp}} and {{dept}} tables. However, the aggregate will throw away all rows that don't involve the SALES and RESEARCH department so we can pushdown these conditions below the join and just above the scan of the {{dept}} table.

+Before+
{noformat}
LogicalAggregate(group=[{}], EXPR$0=[SUM($0) FILTER $1], EXPR$1=[SUM($0) FILTER $2])
  LogicalProject(SAL=[$5], $f1=[=($10, 'SALES')], $f2=[=($10, 'RESEARCH')])
    LogicalJoin(condition=[=($7, $9)], joinType=[inner])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{noformat}
+After+
{noformat}
LogicalAggregate(group=[{}], EXPR$0=[SUM($0) FILTER $1], EXPR$1=[SUM($0) FILTER $2])
  LogicalProject(SAL=[$5], $f1=[=($10, 'SALES')], $f2=[=($10, 'RESEARCH')])
    LogicalJoin(condition=[=($7, $9)], joinType=[inner])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      LogicalFilter(condition=[SEARCH($1, Sarg['RESEARCH':VARCHAR(10), 'SALES':VARCHAR(10)]:VARCHAR(10))])
        LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{noformat}
The JOIN example above is purely for demonstration purposes. The new rule is not gonna have any specific logic for handling join or other operators. The pushdown of the filter below the join (if possible) will be handled by the existing rules.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)