[
https://issues.apache.org/jira/browse/CALCITE-7252?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18033590#comment-18033590
]
Zhen Chen edited comment on CALCITE-7252 at 10/28/25 3:45 PM:
--------------------------------------------------------------
In CALCITE-7234, I mentioned that although the SortRemoveDuplicateKeysRule can
generate operators that eliminate redundant ORDER BY columns in the Volcano
planner, these plans cannot be selected. This is because the Collation property
of the RelRoot is already fixed when it enters the Volcano planner and cannot
be modified. Therefore, I intend to implement this logic during the relational
expression (rel) generation phase. This elimination is a positive optimization,
and if it proves stable for some time, it can be enabled by default.
was (Author: jensen):
In CALCITE-7234, I mentioned that although the {{SortRemoveDuplicateKeysRule
}}can generate operators that eliminate redundant ORDER BY columns in the
Volcano planner, these plans cannot be selected. This is because the
{{Collation }}property of the {{RelRoot }}is already fixed when it enters the
Volcano planner and cannot be modified. Therefore, I intend to implement this
logic during the relational expression (rel) generation phase. This elimination
is a positive optimization, and if it proves stable for some time, it can be
enabled by default.
> Implement ORDER BY Optimization Using Functional Dependencies at SQL-to-Rel
> Conversion Phase
> --------------------------------------------------------------------------------------------
>
> Key: CALCITE-7252
> URL: https://issues.apache.org/jira/browse/CALCITE-7252
> Project: Calcite
> Issue Type: New Feature
> Reporter: Zhen Chen
> Assignee: Zhen Chen
> Priority: Minor
> Labels: pull-request-available
>
> This issue implements an optimization that removes redundant columns from
> ORDER BYclauses based on functional dependencies (FDs) during the
> SQL-to-Relational (SQL-to-Rel) conversion process. This approach was chosen
> because a rule-based implementation within the Volcano planner would face
> significant hurdles with trait satisfaction. Specifically, an optimized
> collation (e.g., [0]) cannot satisfy the original, broader collation
> requirement (e.g., [0, 1]) under the standard trait subsumption check, which
> would prevent the optimizer from selecting the more efficient plan, details
> see CALCITE-7234.
> The implementation achieves the following results:
> {code:java}
> -- Original Query (FD: empno(primary key) -> sal)
> select empno, sal from emp order by empno, sal;
> -- Optimized Logical Plan
> LogicalSort(sort0=[$0], dir0=[ASC]) <-- Instead of sort0=[$0], sort1=[$1]
> LogicalProject(EMPNO=[$0], SAL=[$5])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]]) {code}
> Introduces a new optimizeOrderBy configuration parameter to enable the
> optimization. The default value is false.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
