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 <zabe...@gmail.com> 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 <jh...@apache.org> 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: <option A, B or C, or a query> > > Hope: <option A, B or C, or a query> > > 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 > >
