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

Hi Jeff,

> IIUC, you resolve the issue by introducing a special zero value, which
> preserves the existing behavior by default, and only an extension can
> set it to a non-zero value. There's no auto-replan, so using an
> extension would get behavior (a) described in the above thread.
> I generally like that approach.

Thanks, and yes, that is exactly it: the field defaults to zero, which
keeps the current behavior and the current work_mem path untouched, and
an extension opts a plan in by setting per-node values, with no
auto-replan.

> I didn't review all the details yet, but if a real extension actually
> uses this, that seems reasonable to me.

One does. I wrote pg_mem_governor on top of the step-1 patch while
developing it, to make sure the field was usable from an extension rather
than just present. It is a shared_preload_libraries extension that hands
out an instance-wide working-memory budget across workload classes. At
plan time it walks the plan and writes each node's share into
Plan.workmem (the cached plan tree included); the executor then honors it
per node. It sizes each node kind from a calibration that measures what
each size actually spills to temp files, keeps a query within its role's
memory class with admission control when the pool is full, and accounts
for parallel workers (the grant has to cover the partial-aggregate
copies). With the field left at zero it is a no-op.

That last part is what is hard to do today: there is no place to put a
per-node number, so an extension cannot give one operator more and
another less. The step-1 field is what makes it possible.

v2 is attached, rebased over current master (the only conflict was in
nodeTableFuncscan.c, where master grew a "reuse the tuplestore across
rescans" guard); its own test module passes. The extension is a working
prototype, not something I'm proposing here -- it builds on this v2 and
its 42 functional tests pass, including the per-kind caps deciding
whether a sort or a hash join spills. I can share it if that is useful
for judging whether the field earns its keep.

Thanks,
Manu