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 <dgrowle...@gmail.com> wrote: > On Thu, 9 Feb 2023 at 10:45, Benjamin Tingle <b...@tingle.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).
