Re: Window Functions & Table Partitions

Thanks for the helpful response david! I'll have a shot at getting the
patch to work myself & submitting to pgsql-hackers.

Ben

On Wed, Feb 8, 2023 at 2:36 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> 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/
>

--

Ben(t).