[
https://issues.apache.org/jira/browse/CALCITE-5756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17730446#comment-17730446
]
Julian Hyde commented on CALCITE-5756:
--------------------------------------
I think you should focus on the simple case (without the {{Project}}) first. It
is hard enough, and valuable enough. Mark this case "done" when you've solved
that. Then come back and think about the harder cases.
Regarding making the rule work 'through' projects. One way, as you suggest, is
to use transformation rules to move relational operators around. But another
way might be to leave the relational operators in place and propagate some
"property" (a kind of metadata, made available via {{RelMetadataQuery}}).
I don't have any more concrete ideas, and don't even know what this metadata
would look like. But this dual pattern crops up frequently - e.g. you can
either pull up {{Filter}} operators or you can leave them in place and instead
propagate the {{RelOptPredicateList}} that they make possible - and the
metadata approach is usually better.
> 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)
