[jira] [Commented] (CALCITE-3181) Support limit per group in Window

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3181?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17377599#comment-17377599 ] 

Julian Hyde commented on CALCITE-3181:
--------------------------------------

[~hyuan], I just logged CALCITE-4687, which is another way of posing queries that have "per-key limits". Please take a look, and let me know what you think.

The important insight, I think, is that computing the rank is expensive, but filtering on the rank (say {{rn <= 3}}) may be much cheaper, as long as you don't return the rank column in your result.

Since the two formulations are equivalent, it would be possible to define rules that transform one into the other.

> Support limit per group in Window
> ---------------------------------
>
>                 Key: CALCITE-3181
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3181
>             Project: Calcite
>          Issue Type: Improvement
>          Components: core
>            Reporter: Haisheng Yuan
>            Priority: Major
>
> We have a lot of queries like the following to retrieve top N tuples per group:
> {code:java}
> SELECT x, y FROM
>      (SELECT x, y, ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) 
>      AS rn FROM t1) t2 WHERE rn <= 3;
> {code}
> The performance is not good if each group has a lot more tuples than wanted, because we will retrieve and sort all the tuples, instead of just doing a top-N heap sort.
> In order to do optimization for this kind of query, we need to extend window to support limit, if and only if there is only 1 window function, and it is {{row_number()}}. We also need a substitute rule to push the limit into window. Of course, we also need to modify executor to support this optimization (can be later).
> {code:java}
> Filter (rn <= 3)
>   +- Window (window#0={Partition by x order by y ROW_NUMBER()})
> {code}
> to
> {code:java}
> Filter (rn <= 3)
>   +- Window (window#0={Partition by x order by y limit 3 ROW_NUMBER()})
> {code}
> Thoughts? Objections?



--
This message was sent by Atlassian Jira
(v8.3.4#803005)