Re: simple patch for discussion

On Fri, 18 Jul 2025 at 13:04, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> Have the planner produce two numbers.
>
> 1: This plan needs a minimum of N workers to make the parallelism worthwhile.  Assume that is what we produce today.
> 2:  A number representing how much this plan would benefit from the availability of additional workers beyond the
minimum. This could be the new proposed computation. 
>
> The executor, seeing it has 60 workers to spare right now, is willing to allocate some percentage of them to an
executingplan weighted by the value of the second number. 

Having the ability to do something smarter at executor startup based
on the current server load sounds like something to aspire towards.
That might mean having to generate a serial version of each parallel
plan, and that does mean running the join search at least twice for
all plans that have a parallel portion. One problem with doing that is
that plans can have multiple Gather/GatherMerge nodes, and if one of
those needed some low number of workers and another one needed some
large number of workers, then if you ended up in a scenario that
during execution only a low number of workers were available, then the
best plan might be something in between the serial and parallel plan
(i.e just the Gather with the low worker count). It seems fairly
prohibitive to perform the join search for every combination of
with/without Gather. It is the most expensive part of planning for any
moderately complex join problems.

> I’d much rather avoid configuration and instead improve the executor to minimize idle parallel workers.

That would be nice. Really what we have today with Gather/GatherMerge
spinning up workers is down to the fact that we have a volcano
executor. If we did push-based execution and had threads rather than
processes, or at least if we disconnected backends and sessions, then
you could have the planner post to-be-executed plans to a notice
board, then have a bunch of workers (one per CPU core) come along and
execute those.  For push-based execution, any plan node which is a
scan or has >0 tuples in the input queue can be executed. Each worker
could yield periodically to check for more important work. An
implementation of that is far far away from a weekend project.

> Another approach would be a superuser (grantable) parameter that overrides planning-time heuristics and just says
“usethis many workers where possible”.  This seems like a useful knob even if we do better with dynamic resource
allocationheuristics. 

I don't imagine that would be useful to many people as there would be
nothing to exclude medium-sized tables from using a possibly silly
number of workers. I think the "alter table ... set
(parallel_workers=N);" that we have today is useful. In most
databases, small tables tend to outnumber huge tables, so it doesn't
really seem that prohibitive to do the ALTER TABLE on just the handful
of huge tables. I find it hard to imagine that there'd be many people
around that would benefit from a global
"always_use_this_number_of_parallel_workers" GUC.

David