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
