Re: Proposal: "query_work_mem" GUC, to distribute working memory to the query's individual operators

On Fri, Jan 24, 2025 at 5:48 PM Jeff Davis <pgsql@j-davis.com> wrote:
>
> On Fri, 2025-01-24 at 17:04 -0800, James Hunter wrote:
> > Generating "high memory" vs. "low memory" paths would be tricky,
> > because the definition of "high" vs. "low" depends on the entire path
> > tree, not just on a single path node. So I think it would quickly
> > lead
> > to a state-space explosion, as you mention.
>
> At first, it appears to lead to an explosion, but there are a lot of
> ways to prune early. ...
>
> Maybe my idea doesn't work out, but I think it's too early to dismiss
> it.

I think it makes sense to split the work into two parts: one part that
improves SQL execution, and a second part that improves the optimizer,
to reflect the improvements to execution.

It seems better to me to wait until we have the ability to enforce
memory limits, before worrying about ways to generate different paths
with different memory limits. Then we would be able to tune the
optimizer heuristics based on the actual executor, instead of
extrapolating how the executor would behave under different memory
limits.

James