Re: how to limit statement memory allocation

On Tue, Mar 9, 2021 at 6:53 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Radoslav Nedyalkov <rnedyalkov(at)gmail(dot)com> writes:
> > On Tue, Mar 9, 2021 at 6:03 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> Perhaps the accumulation is happening on the client side? libpq doesn't
> >> have any provision for spilling a result set to disk.
>
> > Ah, I named it result set wrongly perhaps.
> > These are queries , part of a larger ETL function or statement which at
> the
> > end just write to a table.
> > The huge join is an intermediate step.
>
> Hm. What's the query plan look like?
>
> The only thing I'm aware of that could consume unbounded memory
> on the server side is hash aggregation. (v13 has improved that
> situation, but I'm guessing you are running some older version.)
> The planner won't choose hash aggregation if it estimates that
> the hash table would need to exceed work_mem, but its estimate
> could be wrong. If that's the scenario, you could back off
> work_mem until hash aggregation isn't used, or you could try to
> improve the planner's estimates. If your ETL process involves
> intermediate tables, you might need to explicitly ANALYZE those
> after you fill them so that the planner has a better idea how
> to plan the later steps.
>
> regards, tom lanea

Thanks Tom,
Query and plan attached.

Rado