[jira] [Updated] (CALCITE-5756) Expand ProjectJoinRemoveRule to support inner join remove by the foreign-unique constraint in the catalog

["winds (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-5756?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

winds updated CALCITE-5756:
---------------------------
    Description: 
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.

 

 

 

 

 

 

  was:
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.

 

 

 

 

 

 

        Summary: Expand ProjectJoinRemoveRule to support inner join remove by the foreign-unique constraint in the catalog  (was: Add Join elimination optimization according to foreign-unique constraint in the catalog)

> 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)