Re: increased max_parallel_workers_per_gather results in fewer workers?

> On Jun 3, 2020, at 5:15 PM, Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
>
> On Wed, Jun 03, 2020 at 04:04:13PM -0400, Philip Semanchuk wrote:
>> Can anyone help me understand why this happens, or where I might look for clues?
>
> What version postgres ?

Sorry, I should have posted that in my initial email.

select version();
+-----------------------------------------------------------------------------+
| version |
|-----------------------------------------------------------------------------|
| PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit |
+-----------------------------------------------------------------------------+

This is AWS’ version of Postgres 11.6 (“Aurora”) which of course might make a difference.

> Can you reproduce if you do:
> ALTER SYSTEM SET max_parallel_workers_per_gather=0; SELECT pg_reload_conf();
> .. and then within the session do: SET max_parallel_workers_per_gather=12;

Unfortunately under Aurora I’m not superuser so I can’t run ALTER SYSTEM, but I can change the config via AWS’ config interface, so I set max_parallel_workers_per_gather=0 there.

show max_parallel_workers_per_gather
+-----------------------------------+
| max_parallel_workers_per_gather |
|-----------------------------------|
| 0 |
+-----------------------------------+
SHOW
Time: 0.034s
postgres(at)philip-2020-05-19-cluster:wylan>
SET max_parallel_workers_per_gather=12
SET
Time: 0.028s
postgres(at)philip-2020-05-19-cluster:wylan>
show max_parallel_workers_per_gather
+-----------------------------------+
| max_parallel_workers_per_gather |
|-----------------------------------|
| 12 |
+-----------------------------------+
SHOW

I then ran the EXPLAIN ANALYZE and got the same slow runtime (1473s) and 1 worker in the EXPLAIN ANALYZE output.

> I guess you should show an explain analyze, specifically "Workers
> Planned/Launched", maybe by linking to explain.depesz.com

Out of an abundance of caution, our company has a policy of not pasting our plans to public servers. However, I can confirm that when I set max_parallel_workers_per_gather > 4 and the runtime increases, this is what’s in the EXPLAIN ANALYZE output:

Workers Planned: 1
Workers Launched: 1

FWIW, the Planning Time reported in EXPLAIN ANALYZE output doesn’t vary significantly, only from 411-443ms, and the variation within that range correlates only very weakly with max_parallel_workers_per_gather.

thank you
Philip