Hi Anjali, For the first plan it seems that the Union is redundant since both inputs are identical and the operator removes duplicates. I guess the plan could be simplified to:
LogicalProject(EMPNO=[$0]) LogicalFilter(condition=[>=($0, 7369)]) LogicalTableScan(table=[[scott, EMP]]) I don't remember seeing a rule that does this so possibly we could add a new one in PruneEmptyRules [1]. Apart from that and as others have mentioned there is a way to model the materialisation of the common sub-expression using spools. The second plan that you provided could look like below: LogicalRepeatUnion(all=[true]) LogicalTableSpool(readType=[LAZY], writeType=[EAGER], table=[[D]]) LogicalProject(EMPNO=[$0]) LogicalFilter(condition=[>=($0, 7369)]) LogicalTableScan(table=[[scott, EMP]]) LogicalFilter(condition=[>=($0, 8000)]) LogicalTableScan(table=[[D]]) // This is a transient table Currently there are no rules in Calcite that are going to perform such transformation for using the spools. Nevertheless there are rules/code who perform view based rewriting (e.g., in [2]) which might give you an idea of how can this be done. Best, Stamatis [1] https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java [2] https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java On Thu, Feb 20, 2020 at 3:54 PM Christian Beikov <christian.bei...@gmail.com> wrote: > I guess what you are looking for is something like the SubPlan node of > PostgreSQL? Such a node would materialize the results to be used by > multiple other nodes or in a nested loop context to avoid accessing a > source relation multiple times. > > Would be cool if Calcite could create a SubPlan and let the union all node > use that instead. Something like this > > Materialize(name=Sub1) > > LogicalProject(EMPNO=[$0]) > > LogicalFilter(condition=[OR(>=($0, 7369), >=($0, 7369))]) > > LogicalTableScan(table=[[scott, EMP]]) > > LogicalUnion(all=[true]) > > LogicalProject(EMPNO=[$0]) > > LogicalFilter(condition=[>=($0, 7369)]) > > LogicalTableScan(table=[[Sub1]]) > > LogicalProject(EMPNO=[$0]) > > LogicalFilter(condition=[>=($0, 7369)]) > > LogicalTableScan(table=[[Sub1]]) > > Of course there are further optimizations that could be done like removing > filters etc. but that's just a general example. > > Not sure if the Spool operator does that, but such a translation could also > come in handy for distributed queries where every row access to a remote > system has protocol overhead. > > Danny Chan <yuzhao....@gmail.com> schrieb am Do., 20. Feb. 2020, 15:27: > > > Calcite has a Spool operator, maybe you can check that. > > > > Anjali Shrishrimal <anjali.shrishri...@1eq.com.invalid>于2020年2月20日 > > 周四下午3:20写道: > > > > > Hi everybody, > > > > > > I would like to have your suggestions on CALCITE-3806. > > > > > > Asking it here as suggested by Julian. > > > > > > > > > > > > > > > > > > If RelNode tree contains a subtree whose result can be obtained by some > > > other part of the same tree, > > > > > > can we optimize it ? and how to express it in plan ? > > > > > > > > > > > > For example, > > > > > > Let's say input structure looks like this : > > > > > > > > > > > > LogicalUnion(all=[true]) > > > > > > LogicalProject(EMPNO=[$0]) > > > > > > LogicalFilter(condition=[>=($0, 7369)]) > > > > > > LogicalTableScan(table=[[scott, EMP]]) > > > > > > LogicalProject(EMPNO=[$0]) > > > > > > LogicalFilter(condition=[>=($0, 7369)]) > > > > > > LogicalTableScan(table=[[scott, EMP]]) > > > > > > > > > > > > > > > > > > In this case, > > > > > > > > > > > > LogicalProject(EMPNO=[$0]) > > > > > > LogicalFilter(condition=[>=($0, 7369)]) > > > > > > LogicalTableScan(table=[[scott, EMP]]) > > > > > > > > > > > > is repeated. It is going to fetch same data twice. > > > > > > Can we save one fetch? Can we somehow tell 2nd input of union to make > use > > > of union's 1st input. Is there any way to express that in plan? > > > > > > > > > > > > Also, > > > If the structure was like this : > > > > > > > > > > > > LogicalUnion(all=[true]) > > > > > > LogicalProject(EMPNO=[$0]) > > > > > > LogicalFilter(condition=[>=($0, 7369)]) > > > > > > LogicalTableScan(table=[[scott, EMP]]) > > > > > > LogicalProject(EMPNO=[$0]) > > > > > > LogicalFilter(condition=[>=($0, 8000)]) > > > > > > LogicalTableScan(table=[[scott, EMP]]) > > > > > > > > > > > > Second part of union can perform filtering on fetched data of 1st part. > > > (As second's output is subset of first's output) > > > > > > > > > > > > Does calcite provide such kind of optimizations ? > > > > > > If not, what are the challenges to do so? > > > > > > > > > > > > > > > > > > > > > > > > Would love to hear your thoughts. > > > > > > > > > > > > > > > Thank you, > > > Anjali Shrishrimal > > > > > >