Message-ID: <0beedccb92ba5a1db386bd09b44e15b20c1ac1fc.camel@j-davis.com>
Subject: Re: Proposal: "query_work_mem" GUC, to distribute working memory to
 the query's individual operators
From: Jeff Davis <pgsql@j-davis.com>
To: James Hunter <james.hunter.pg@gmail.com>, 
	"pgsql-hackers@lists.postgresql.org"
	 <pgsql-hackers@lists.postgresql.org>
Date: Tue, 21 Jan 2025 13:26:52 -0800
In-Reply-To: <CAJVSvF6s1LgXF6KB2Cz68sHzk+v+O_vmwEkaon=H8O9VcOr-tQ@mail.gmail.com>
References: 
	<CAJVSvF6s1LgXF6KB2Cz68sHzk+v+O_vmwEkaon=H8O9VcOr-tQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.44.4-0ubuntu2 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/0beedccb92ba5a1db386bd09b44e15b20c1ac1fc.camel%40j-davis.com>
Precedence: bulk

On Fri, 2025-01-10 at 10:00 -0800, James Hunter wrote:
> How should =E2=80=9Cquery_work_mem=E2=80=9D work? Let=E2=80=99s start wit=
h an example:
> suppose
> we have an OLAP query that has 2 Hash Joins, and no other operators
> that use work_mem.

So we plan first, and then assign available memory afterward? If we do
it that way, then the costing will be inaccurate, because the original
costs are based on the original work_mem.

It may be better than killing the query, but not ideal.

> -- Second, we could say, instead: the small Hash Join is *highly
> unlikely* to use > 1 MB, so let=E2=80=99s just give both Hash Joins 1023 =
MB,
> expecting that the small Hash Join won=E2=80=99t use more than 1 MB of it=
s
> 1023 MB allotment anyway, so we won=E2=80=99t run OOM. In effect, we=E2=
=80=99re
> oversubscribing, betting that the small Hash Join will just stay
> within some smaller, =E2=80=9Cunenforced=E2=80=9D memory limit.
>=20
> In this example, this bet is probably fine =E2=80=94 but it won=E2=80=99t=
 work in
> general. I don=E2=80=99t want to be in the business of gambling with cust=
omer
> resources: if the small Hash Join is unlikely to use more than 1 MB,
> then let=E2=80=99s just assign it 1 MB of work_mem.

I like this idea. Operators that either know they don't need much
memory, or estimate that they don't need much memory, can constrain
themselves. That would protect against misestimations and advertise to
the higher levels of the planner how much memory the operator actually
wants. Right now, the planner doesn't know which operators need a lot
of memory and which ones don't need any significant amount at all.

The challenge, of course, is what the higher levels of the planner
would do with that information, which goes to the rest of your
proposal. But tracking the information seems very reasonable to me.

> I propose that we add a =E2=80=9Cquery_work_mem=E2=80=9D GUC, which works=
 by
> distributing (using some algorithm to be described in a follow-up
> email) the entire =E2=80=9Cquery_work_mem=E2=80=9D to the query=E2=80=99s=
 operators. And then
> each operator will spill when it exceeds its own work_mem limit. So
> we=E2=80=99ll preserve the existing =E2=80=9Cspill=E2=80=9D logic as much=
 as possible.

The description above sounds too "top-down" to me. That may work, but
has the disadvantage that costing has already happened. We should also
consider:

* Reusing the path generation infrastructure so that both "high memory"
and "low memory" paths can be considered, and if a path requires too
much memory in aggregate, then it would be rejected in favor of a path
that uses less memory. This feels like it fits within the planner
architecture the best, but it also might lead to a path explosion, so
we may need additional controls.

* Some kind of negotiation where the top level of the planner finds
that the plan uses too much memory, and replans some or all of it. (I
think is similar to what you described as the "feedback loop" later in
your email.) I agree that this is complex and may not have enough
benefit to justify.

Regards,
	Jeff Davis