winds created CALCITE-5756:
------------------------------
Summary: 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
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)
