Re: Use incremental sort paths for window functions

On Wed, Jul 08, 2020 at 04:57:21PM +1200, David Rowley wrote:
>Over on [1] someone was asking about chained window paths making use
>of already partially sorted input.  (The thread is on -general, so I
>guessed they're not using PG13.)
>
>However, On checking PG13 to see if incremental sort would help their
>case, I saw it didn't. Looking at the code I saw that
>create_window_paths() and create_one_window_path() don't make any use
>of incremental sort paths.
>
>I quickly put together the attached. It's only about 15 mins of work,
>but it seems worth looking at a bit more for some future commitfest.
>Yeah, I'll need to add some tests as I see nothing failed by changing
>this.
>

Yeah, I'm sure there are a couple other places that might benefit from
incremental sort but were not included in the PG13 commit. The patch
seems correct - did it help in the reported thread? How much?

I suppose this might benefit from an optimization similar to the GROUP
BY reordering discussed in [1]. For example, with

    max(a) over (partition by b,c)

I think we could use index on (c) and consider incremental sort by c,b,
i.e. with the inverted pathkeys. But that's a completely independent
topic, I believe.

[1] https://www.postgresql.org/message-id/7c79e6a5-8597-74e8-0671-1c39d124c9d6%40sigaev.ru

>I'll just park this here until then so I don't forget.
>

OK, thanks for looking into this!

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services