I had hoped and expected that it would do B (push down project and
filter expressions) or C (push down project expressions) but in fact
it does A (keep expressions in the same place, but add a project to
the input that just narrows the columns to just those needed.
I agree with Stamatis' that for optimization, A is usually superior.
It narrows the set of fields early (therefore reducing copying & IO)
but doesn't pay the price of computation until after the Filter has
reduced the number of rows.
I also agree with Stamatis that B usually fattens tuples. But there is
a case where B can slim tuples - if you don't need x and y but only
the expression "x + y", then if you compute "x + y" early, you have
made the tuple slimmer (but paid the price of computing a value that
might be thrown away by the Filter).
However, I do think there is a use for cases B and C; I plan to build
them as part of https://issues.apache.org/jira/browse/CALCITE-3975,
adding some parameters to ProjectFilterTransposeRule.
Julian
On Wed, May 27, 2020 at 4:05 PM Stamatis Zampetakis wrote:
>
> Hello,
>
> I looked into the rule recently but to be honest I already forgot what it
> really does.
>
> Expect: B
> Hope: A
> Comments:
> Judging by the name I would expect B, pushing as much as possible from the
> top project but not altering the filter.
> In the end it is just a transpose.
>
> On the other hand, I would use the rule mostly as an alternative to
> RelFieldTrimmer (along with other rules) in order
> to prune out unnecessary columns as early as possible. Thus, I think option
> A is what I would like.
>
> If it works like B then in some cases it could have also the opposite
> effect of what I would like; fattening the tuples
> instead of slimming them down.
>
> SELECT sal, comm, sal + comm AS remuneration, sal * 0.45 AS incometax, sal
> * 0.03 AS surchargetax
> FROM Emp
> WHERE deptno = 10
>
> SELECT sal, comm, renumeration, incometax, surchargetax
> FROM (
> SELECT sal, comm, sal + comm AS remuneration, sal * 0.45 As incometax,
> sal * 0.03 AS surchargetax, deptno
> FROM Emp)
> WHERE deptno = 10
>
> Best,
> Stamatis
>
> PS. I will go now and check what the rule does :)
>
>
> On Wed, May 27, 2020 at 8:45 PM Julian Hyde wrote:
>
> > As part of https://issues.apache.org/jira/browse/CALCITE-3975, I've
> > been looking into what ProjectFilterTransposeRule does with
> > expressions. It's one of our oldest rules, and I thought I knew what
> > it did. But the rule does not do what I expect. I've like to conduct a
> > straw poll to see what other people think.
> >
> > Consider the following query (a Project on top of a Filter on a Scan).
> >
> > SELECT sal + comm AS remuneration
> > FROM Emp
> > WHERE deptno = 10
> >
> > After applying ProjectFilterTransposeRule, what result would you
> > expect? What would the ideal result be? Are there any extreme cases
> > (large project lists, complex expressions, expensive filter
> > expressions) where the rule should do something different?
> >
> > Please fill out the following:
> >
> > Expect:
> > Hope:
> > Comments:
> >
> > Option A. Push down references to the fields needed by filter and
> > project expressions
> >
> > SELECT sal + comm AS remuneration
> > FROM (
> > SELECT sal, comm, deptno
> > FROM Emp)
> > WHERE deptno = 10
> >
> > B. Push down the project expression, and references to the fields
> > needed by the filter
> >
> > SELECT remuneration
> > FROM (
> > SELECT sal + comm AS remuneration, deptno
> > FROM Emp)
> > WHERE deptno = 10
> >
> > C. Push down the project and filter expressions
> >
> > SELECT remuneration
> > FROM (
> > SELECT sal + comm AS remuneration, deptno = 10 AS predicate
> > FROM Emp)
> > WHERE predicate
> >
> > Julian
> >
