[jira] [Comment Edited] (CALCITE-7252) Implement ORDER BY Optimization Using Functional Dependencies at SQL-to-Rel Conversion Phase

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

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