[
https://issues.apache.org/jira/browse/CALCITE-5756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17730040#comment-17730040
]
winds commented on CALCITE-5756:
--------------------------------
[~rkondakov] Thx for your comment.
I read [ProjectJoinRemoveRule|
https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/rel/rules/ProjectJoinRemoveRule.java]
code, found that it only support left or right outer join.
And it's logic is that make sure that the join key in right side are unique
when left outer join，
make sure that the join key in left side are unique when right outer join.
This operation guarantee
that join remove without affecting the resultset.
To be precise, this improvment issues is to expand ProjectJoinRemoveRule to
support inner join by
the foreign-unique constraint in the catalog.
> Add Join elimination optimization according to foreign-unique constraint in
> the catalog
> ---------------------------------------------------------------------------------------
>
> Key: CALCITE-5756
> URL: https://issues.apache.org/jira/browse/CALCITE-5756
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Reporter: winds
> Priority: Minor
>
> Join elimination is a useful optmize improvement.
> Consider a query that joins the two tables but does not make use of the Dept
> columns:
> {code:java}
> SELECT Emp.name, Emp.salary
> FROM Emp, Dept
> WHERE Emp.deptno = Dept.dno {code}
> Assuming Emp.deptno is the foreign-key and is non-null, Dept.dno is the
> unique-key. The sql above can be rewritten as following. remove the Dept
> table without affecting the resultset.
> {code:java}
> SELECT Emp.name, Emp.salary
> FROM Emp {code}
> Without redundant join elimination, this query execution may perform poorly.
> The optimize improvement is also available in SQL Server, Oracle and
> Snowflake and so on.
> In Calcite, i think that is also useful. The infrastructure that join
> elimination depend on is already available.
> 1. Join elimination should be a sub class of TransformationRule and support
> inner join currently.
> 2. Should use
> org.apache.calcite.rel.metadata.RelMetadataQuery#getTableReferences acquire
> Set<RelTableRef>.
> 3. Get constraints by
> org.apache.calcite.plan.RelOptTable#getReferentialConstraints.
> 4. Build the graph by constraints and then remove the redundant join
> according to graph inwardEdges,outwardEdges.
>
> It is very easy to prevent join elimination by not giving the optimizer
> foreign-unique constraint information or don not use the join elimination
> TransformationRule.
>
> Please help me to check the improvement whether is useful or not.
> And i would like to add this improvement to Calcite.
>
>
>
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
