Re: Query on partitioned table needs memory n_partitions * work_mem

On Fri, 12 Jul 2024 at 02:08, Dimitrios Apostolou <jimis(at)gmx(dot)net> wrote:
> I can't help but see this as a bug. I see many issues:
>
> * postgres is not reading from partitions in parallel, but one after the
> other. It shouldn't need all this memory simultaneously.

I don't know for Hash Aggregate, but for nodes like Sort, we still
hold onto the tuplestore after returning the last tuple as a rescan
might want to read those tuples again. There's also a mark/restore
that might want to rewind a little to match up to the next outer tuple
of a Merge Join.

It might be possible to let go of the memory sooner in plans when
returning the final tuple means we'll never need the memory again, but
that would require figuring out all the cases where that could happen
and ensuring we don't ever release memory when it's required again.

> * The memory is unnecessarily allocated early on, before any partitions
> are actually aggregated. I know this because I/O is slow on this device
> and the table sizes are huge, it's simply not possible that postgres
> went through all partitions and blew up the memory. That would take
> hours, but the OOM happens seconds after I start the query.

That's interesting. Certainly, there is some memory allocated during
executor startup, but that amount should be fairly small. Are you
able to provide a self-contained test case that shows the memory
blowing up before execution begins?

> Having wasted long time in that, the minimum I can do is submit a
> documentation patch. At enable_partitionwise_aggregate someting like
> "WARNING it can increase the memory usage by at least
> n_partitions * work_mem". How do I move on for such a patch? Pointers
> would be appreciated. :-)

I think mentioning something about this in enable_partitionwise_join
and enable_partitionwise_aggregate is probably wise. I'll propose a
patch on pgsql-hackers.

David