Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options - Mailing list pgsql-hackers

On 10/20/22 22:02, David Rowley wrote:
> On Thu, 13 Oct 2022 at 13:34, David Rowley <dgrowleyml@gmail.com> wrote:
>> So it looks like the same can be done for rank() and dense_rank() too.
>> I've added support for those in the attached.
> 
> The attached adds support for percent_rank(), cume_dist() and ntile().


Shouldn't it be able to detect that these two windows are the same and 
only do one WindowAgg pass?


explain (verbose, costs off)
select row_number() over w1,
        lag(amname) over w2
from pg_am
window w1 as (order by amname),
        w2 as (w1 rows unbounded preceding)
;


                            QUERY PLAN
-----------------------------------------------------------------
  WindowAgg
    Output: (row_number() OVER (?)), lag(amname) OVER (?), amname
    ->  WindowAgg
          Output: amname, row_number() OVER (?)
          ->  Sort
                Output: amname
                Sort Key: pg_am.amname
                ->  Seq Scan on pg_catalog.pg_am
                      Output: amname
(9 rows)

-- 
Vik Fearing