On Fri, 10 Feb 2023 at 06:40, Benjamin Tingle <ben@xxxxxxxxxx> wrote: > Thanks for the helpful response david! I'll have a shot at getting the patch to work myself & submitting to pgsql-hackers. I took some time today for this and fixed up a few mistakes in the patch and added it to the March commitfest [1]. Time is ticking away for v16, so given this is a fairly trivial patch, I thought it might be nice to have it. Any discussion on the patch can be directed at [2] David [1] https://commitfest.postgresql.org/42/4198/ [2] https://www.postgresql.org/message-id/flat/CAApHDvojKdBR3MR59JXmaCYbyHB6Q_5qPRU+dy93En8wm+XiDA@xxxxxxxxxxxxxx > Ben > > On Wed, Feb 8, 2023 at 2:36 PM David Rowley <dgrowleyml@xxxxxxxxx> wrote: >> >> On Thu, 9 Feb 2023 at 10:45, Benjamin Tingle <ben@xxxxxxxxxx> 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).> > Ben > > On Wed, Feb 8, 2023 at 2:36 PM David Rowley <dgrowleyml@xxxxxxxxx> wrote: >> >> On Thu, 9 Feb 2023 at 10:45, Benjamin Tingle <ben@xxxxxxxxxx> 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).
