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

Hi Jeff, James,

> For a first step, I think it would be useful to create a new field for
> each Plan node, and use that to enforce the execution-time memory
> limit.

The thread has been quiet for a while, so I tried that first step.
Patch attached, building on James's work.

Each Plan node gets a workmem field. Zero, which is what the planner
leaves in it, means work_mem, so nothing changes unless a planner hook
sets it. The executor uses the node's value, and so does the code that
runs for a node: materialize-mode set-returning functions and
ordered-set aggregates. EXPLAIN VERBOSE shows it when it is set. The
test_plan_workmem module sets it from a hook and checks every kind of
node that uses working memory, with the limit below and above work_mem.

I first tried copying work_mem into the field at plan time. Then SET
LOCAL work_mem inside a PL/pgSQL function no longer reached a query
whose plan was already cached: it wrote a 13 MB temporary file that
master does not. Replanning cached plans when work_mem changes fixes
that, but cost 47% of the tps in a pgbench run that changes work_mem in
every transaction before a prepared 3-way join. With zero meaning
work_mem, behavior does not change and I could not measure a cost (all
three runs within noise, 95% CI about +/-2%).

Parallel workers get the limit with the plan. Parallel Hash uses it the
way it uses work_mem today, including the combined budget of all
participants.

Known limits:
- One limit per node: an Agg's hash table and its sorts share it.
- Limits set by a hook stay with cached plans.
- The planner still costs with work_mem.
- Not covered: GIN partial-match bitmaps, array_sort(), and the hold
store of WITH HOLD cursors, which do not run for a plan node.
- ExecChooseHashTableSize() and hash_agg_set_limits() take the limit
as a new argument.

This is not a cap on a backend's memory, only the hook point. As a
check that it is enough for a per-query budget, a small extension that
splits a budget among the nodes kept a query that used 13 MB at
work_mem = 8MB within a 4 MB budget.

Regards,
Manu