[
https://issues.apache.org/jira/browse/CALCITE-5756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17730363#comment-17730363
]
winds commented on CALCITE-5756:
--------------------------------
[~julianhyde] Thx for your check. RelOptTable#getReferentialConstraints is
really useful function. It can be helpful for many optimization scene, one of
which is the join elimination. I am glad to add this feature to Calcite later.
For your example as following, I think is a really good case.
{code:java}
SELECT Emp.name, Emp.salary
FROM Emp, (SELECT deptno, 2 as TWO FROM Dept)
WHERE Emp.deptno = Dept.dno; {code}
In this case we lost the original intention of the sql if we pull up the
project under join.
Pull up project usually happen in the
[JoinProjectTransposeRule|https://github.com/apache/calcite/blob/821f03be6a99d44d2e9f9380354f28e884430602/core/src/main/java/org/apache/calcite/rel/rules/JoinProjectTransposeRule.java#L64],
then it should be simplified by join removing but not.
To these I have a few thoughts as follows:
# If use this rule in hepPlanner with HepMatchOrder.ARBITRARY, TOP_DOWN or
DEPTH_FIRST.
This will fire ProjectJoinRemoveRule early than JoinProjectTransposeRule to
avoid above case.
# In the rest of the scene such as hepPlanner with HepMatchOrder.BOTTOM_UP or
maybe VolcanoPlanner.
2.1 For the using hepPlanner with HepMatchOrder.BOTTOM_UP scene, i think it
maybe reasonable, as user already do this.
2.2 For the VolcanoPlanner scene, it will generate equivalence RelSubset for
both. Maybe it dosen't seems to be a problem.
> Expand ProjectJoinRemoveRule to support inner join remove by the
> 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. Expand ProjectJoinRemoveRule to support inner join remove by the
> foreign-unique constraint in the catalog.
> 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 inner join
> according to graph inwardEdges,outwardEdges.
> 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)
