[
https://issues.apache.org/jira/browse/CALCITE-5940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17757064#comment-17757064
]
Julian Hyde edited comment on CALCITE-5940 at 8/21/23 7:06 PM:
---------------------------------------------------------------
A word of caution. Since logical RelNodes are not sorted, we should be careful
about inferring sort order when we combine LogicalSort instances.
In physical land we can make such assumptions, and they are helpful. For
example, consider the query "select * from (select * from t order by x, y limit
100) where z > 0 order by x limit 10". In Enumerable convention, it is valid to
assume that the output of "sort x, y limit 100" is ordered by x, y, and
therefore if the output goes into "filter z > 0" and then "sort x limit 10",
the second sort-limit can be optimized to avoid a sort.
Those kinds of optimizations should probably be done as part of CALCITE-2540,
not here.
was (Author: julianhyde):
A word of caution. Since logical RelNodes are not sorted, we should be careful
about inferring sort order when we combine LogicalSort instances.
In physical land we can make such assumptions, and they are helpful. For
example, consider the query "select * from (select * from t order by x, y limit
100) where z > 0 order by x limit 10". In Enumerable convention, it is valid to
assume that the output of "sort x, y limit 100" is ordered by x, y, and
therefore if the output goes into "filter z > 0" and then "sort x limit 10",
the second sort-limit can be optimized to avoid a sort.
> Add the Rules to optimize Limit
> -------------------------------
>
> Key: CALCITE-5940
> URL: https://issues.apache.org/jira/browse/CALCITE-5940
> Project: Calcite
> Issue Type: New Feature
> Reporter: LakeShen
> Priority: Major
>
> Now in calcite,the Limit will be represented using
> LogicalSort(fetch=[xx]),but there are few rules to optimize Limit.
> In trino and presto,there are many optimization rules to optimize Limit.
> For example,the sql:
> {code:java}
> select * from nation limit 0 {code}
> The limit 0 will use empty ValuesNode(Calcite LogicalValues) to optimize,so
> the SQL is not delivered to the Worker compute,the rule could see:
> [EvaluateZeroLimit|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/EvaluateZeroLimit.java#L28C1-L28C31]
> The sql:
> {code:java}
> select concat('-',N_REGIONKEY) from (select * from nation limit 10000) limit
> 10 {code}
> It would be optimized by
> [MergeLimits|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/MergeLimits.java#L26]
> rule to:
> {code:java}
> select concat('-',N_REGIONKEY) from nation limit 10 {code}
> The value of limit takes the minimum of the outer limit and the inner limit.
> The sql:
> {code:java}
> select concat('-',N_REGIONKEY) from (SELECT * FROM nation order BY
> N_REGIONKEY DESC LIMIT 10000) limit 10 {code}
> It would be optimized by
> [MergeLimitWithTopN|https://github.com/prestodb/presto/blob/fea80c96ddfe4dc42f79c3cff9294b88595275ce/presto-main/src/main/java/com/facebook/presto/sql/planner/iterative/rule/MergeLimitWithTopN.java#L28C1-L28C31]
> rule to:
> {code:java}
> SELECT concat('-',N_REGIONKEY) FROM nation order BY N_REGIONKEY DESC LIMIT
> 10{code}
> So I propose to add these three rules to Calcite as well, to optimize the
> Limit case.
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
