Thread: Window Functions & Table Partitions
Hell postgres people!
This is not an issue report so much as a gripe. I'm on postgres 12.2, so it is entirely possible that the issue I describe is fixed in a later version. If so, it is not described in the docs or any posts I can find archived on pgsql-performance. (I am not brave enough to delve into pgsql-developer, where I'm sure this has been brought up at some point)
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
Instead, I get this:
Window:
Sort:
Append:
Parallel seq scan on table_p0
Parallel seq scan on table_p1
Parallel seq scan on table_p2
Which is a BIG no-no, as there could potentially be thousands of partitions and BILLIONS of rows per table. This can be solved by manually implementing the first query plan via scripting, e.g:
do $$
declare i int;
begin
for i in 0..get_npartitions() loop
execute('select a, b, min(a) over (partition by b) as g from abb_p%', i);
end loop;
end $$ language plpgsql;
This is not ideal, but perfectly workable. I'm sure you guys are already aware of this, it just seems like a really simple fix to me- if the window function partition scheme exactly matches the partition scheme of the table it queries, it should take advantage of those partitions.
Thanks,
Ben
On Thu, 9 Feb 2023 at 10:45, Benjamin Tingle <ben@tingle.org> wrote: > Basically- window partition functions don't take advantage of existing table partitions. I use window functions as a morepowerful 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/
Attachment
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@gmail.com> wrote:
On Thu, 9 Feb 2023 at 10:45, Benjamin Tingle <ben@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).
On Fri, 10 Feb 2023 at 06:40, Benjamin Tingle <ben@tingle.org> 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@mail.gmail.com > Ben > > On Wed, Feb 8, 2023 at 2:36 PM David Rowley <dgrowleyml@gmail.com> wrote: >> >> On Thu, 9 Feb 2023 at 10:45, Benjamin Tingle <ben@tingle.org> wrote: >> > Basically- window partition functions don't take advantage of existing table partitions. I use window functions as amore 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@gmail.com> wrote: >> >> On Thu, 9 Feb 2023 at 10:45, Benjamin Tingle <ben@tingle.org> wrote: >> > Basically- window partition functions don't take advantage of existing table partitions. I use window functions as amore 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).