[jira] [Commented] (CALCITE-844) Push Project through Window

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-844?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14730184#comment-14730184 ] 

Julian Hyde commented on CALCITE-844:
-------------------------------------

The patch looks good.

Did you consider adding {code}RelFieldTrimmer.trimFields(
      Window window,
      ImmutableBitSet fieldsUsed,
      Set<RelDataTypeField> extraFields){code}

I think it might have accomplished the same goal.

> Push Project through Window
> ---------------------------
>
>                 Key: CALCITE-844
>                 URL: https://issues.apache.org/jira/browse/CALCITE-844
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Sean Hsuan-Yi Chu
>            Assignee: Julian Hyde
>
> The lack of Project under LogicalWindow hurts the performance.
> Firstly of all, this issue happens when HepPlanner is used with ProjectToWindowRule.PROJECT rule.
> A simple query like:
> {code}
> select sum(deptno) over(partition by deptno) as sum1 
> from emp
> {code}
> produces
> {code}
> LogicalProject($0=[$9])
>   LogicalWindow(window#0=[window(partition {7} order by [] range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [SUM($7)])])
>     LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> However, from performance standpoint, it is better to have a project between LogicalWindow and LogicalTableScan since only one column is used. Interestingly, when there is an expression in the window function. For example, 
> {code}
> select sum(deptno + 1) over(partition by deptno) as sum1 
> from emp"
> {code}
> produces
> {code}
> LogicalProject($0=[$2])
>   LogicalWindow(window#0=[window(partition {0} order by [] range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [SUM($1)])])
>     LogicalProject(DEPTNO=[$7], $1=[+($7, 1)])
>       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> The LogicalProject below window can trim out useless columns or even be pushed into Scan, which is very important optimization Calcite can exploit. 



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)