Re: Monotonic WindowFunc support for ntile(), percent_rank() and cume_dist()

On Wed, 25 Jan 2023 at 02:39, Melanie Plageman
<melanieplageman@gmail.com> wrote:
>
> On Tue, Jan 24, 2023 at 02:00:33PM +1300, David Rowley wrote:
> > If you feel strongly about that, then feel free to show me what you
> > have in mind in more detail so I can think harder about it.
>
> Nah, I don't feel strongly. I think it was because looking at the patch
> in isolation, the repetition stands out but in the context of the rest
> of the code it doesn't.

I played around with this patch again and the performance gains for
the best case are not quite as good as we got for row_number(), rank()
and dense_rank() in the original commit.  The reasons for this is that
ntile() and co all require getting a count of the total rows in the
partition so it can calculate the result. ntile() needs to know how
large the tiles are, for example. That obviously requires pulling all
tuples into the tuplestore.

Despite this, the performance with the patch is still much better than
without.  Here's master:

explain (analyze, timing off, costs off) select * from (select
a,ntile(10) over (order by a) nt from a) where nt < 1;

 Subquery Scan on unnamed_subquery (actual rows=0 loops=1)
   Filter: (unnamed_subquery.nt < 1)
   Rows Removed by Filter: 1000000
   ->  WindowAgg  (actual rows=1000000 loops=1)
         ->  Index Only Scan using a_a_idx on a  (actual rows=1000000 loops=1)
               Heap Fetches: 0
 Planning Time: 0.073 ms
 Execution Time: 254.118 ms
(8 rows)

and with the patch:

 WindowAgg  (actual rows=0 loops=1)
   Run Condition: (ntile(10) OVER (?) < 1)
   ->  Index Only Scan using a_a_idx on a (actual rows=1000000 loops=1)
         Heap Fetches: 0
 Planning Time: 0.072 ms
 Execution Time: 140.023 ms
(6 rows)

Here's with row_number() for reference:

explain (analyze, timing off, costs off) select * from (select
a,row_number() over (order by a) rn from a) where rn < 1;

 WindowAgg (actual rows=0 loops=1)
   Run Condition: (row_number() OVER (?) < 1)
   ->  Index Only Scan using a_a_idx on a (actual rows=1 loops=1)
         Heap Fetches: 0
 Planning Time: 0.089 ms
 Execution Time: 0.054 ms
(6 rows)

you can clearly see the difference with the number of rows pulled out
of the index only scan.

This is just a 1 million row table with a single INT column and an
index on that column.

Anyway, all seems like clear wins and low controversy so I've now pushed it.

David