Re: Window Functions & Table Partitions

On Thu, 9 Feb 2023 at 10:45, Benjamin Tingle <ben(at)tingle(dot)org> wrote:
> Basically- window partition functions don't take advantage of existing table partitions. I use window functions as a more powerful GROUP BY clause that preserves row-by-row information- super handy for a lot of things.
>
> In particular, I want to use window functions on already partitioned tables, like the below example:
>
> create table abb (a int, b int, g int) partition by hash(b)
> /* populate table etc... */
> select a, b, min(a) over (partition by b) as g from abb
>
> Ideally with a query plan like this:
>
> Window:
> Append:
> Sort on table_p0
> Sort on table_p1
> Sort on table_p2

There was some effort [1] in version 12 to take advantage of the order
defined by the partitioning scheme. The release notes [2] mention:

"Avoid sorting when partitions are already being scanned in the necessary order"

However, it's not 100% of what you need as there'd have to be a btree
index on abb(b) for the planner to notice.

Likely this could be made better so that add_paths_to_append_rel()
added the pathkeys defined by the partitioned table into
all_child_pathkeys if they didn't exist already. In fact, I've
attached a very quickly hacked together patch against master to do
this. I've given it very little thought and it comes complete with
failing regression tests.

If you're interested in pursuing this then feel free to take the patch
to the pgsql-hackers mailing list and propose it. It's unlikely I'll
get time to do that for a while, but I will keep a branch locally with
it to remind me in case I do at some point in the future.

David

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=959d00e9dbe4cfcf4a63bb655ac2c29a5e579246
[2] https://www.postgresql.org/docs/release/12.0/