On Fri, May 20, 2022 at 7:09 PM Tomas Vondra
<tomas.vondra@enterprisedb.com> wrote:
> I wonder if we might eventually use this to define memory budgets. One
> of the common questions I get is how do you restrict the user from
> setting work_mem too high or doing too much memory-hungry things.
> Currently there's no way to do that, because we have no way to limit
> work_mem values, and even if we had the user could construct a more
> complex query with more memory-hungry operations.
>
> But I think it's also that we weren't sure what to do after hitting a
> limit - should we try replanning the query with lower work_mem value, or
> what?
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.
My general feeling about this topic is that, in cases where PostgreSQL
today uses more memory than is desirable, it's probably only
moderately difficult to make it fail with a nice error message
instead. Making it succeed by altering its behavior to use less memory
seems likely to be a lot harder -- which is not to say that we
shouldn't try to do it. It's an important problem. Just not an easy
one.
--
Robert Haas
EDB: http://www.enterprisedb.com