MIME-Version: 1.0
References: <CAJVSvF6s1LgXF6KB2Cz68sHzk+v+O_vmwEkaon=H8O9VcOr-tQ@mail.gmail.com>
 <0beedccb92ba5a1db386bd09b44e15b20c1ac1fc.camel@j-davis.com>
 <CAJVSvF7B+z0i=jMvvcWnq=Vh8AHZwe02BSBmOfmGjiu1Nf4_wQ@mail.gmail.com> <6b134f12c727fdb3a03849deab585dea0dfb30aa.camel@j-davis.com>
In-Reply-To: <6b134f12c727fdb3a03849deab585dea0dfb30aa.camel@j-davis.com>
From: James Hunter <james.hunter.pg@gmail.com>
Date: Mon, 10 Feb 2025 19:09:25 -0800
Message-ID: <CAJVSvF68TGsyrvMpjmPviNvR48UYkD6AN-HLQXOu2uj3HCHHGA@mail.gmail.com>
Subject: Re: Proposal: "query_work_mem" GUC, to distribute working memory to
 the query's individual operators
To: Jeff Davis <pgsql@j-davis.com>
Cc: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAJVSvF68TGsyrvMpjmPviNvR48UYkD6AN-HLQXOu2uj3HCHHGA%40mail.gmail.com>
Precedence: bulk

On Fri, Jan 24, 2025 at 5:48=E2=80=AFPM Jeff Davis <pgsql@j-davis.com> wrot=
e:
>
> 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