Re: Limiting memory allocation

On Tue, May 24, 2022 at 11:49:27AM -0400, Robert Haas wrote:
> It's always seemed to me that the principled thing to do would be to
> make work_mem a per-query budget rather than a per-node budget, and
> have add_path() treat memory usage as an independent figure of merit
> -- and also discard any paths that went over the memory budget. Thus
> we might keep more expensive paths if they use less memory to produce
> the result. For this to work well, memory-hungry nodes would probably
> need to add multiple paths - especially nodes that do hashing, which
> is likely to have breakpoints where the estimated cost changes sharply
> (and the actual cost does too, if the row counts are accurate).
>
> I've also wondered whether we could maybe do something unprincipled
> instead, because that all sounds not only complicated but also
> potentially expensive, if it results in us keeping extra paths around
> compared to what we keep today. It might be worth it, though.
> Generating query plans infinitely fast is no good if the plans suck,
> and running the machine out of RAM definitely counts as sucking.

If the plan output is independent of work_mem, I always wondered why we
didn't just determine the number of simultaneous memory requests in the
plan and just allocate accordingly, e.g. if there are four simultaneous
memory requests in the plan, each gets work_mem/4.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson