Re: increased max_parallel_workers_per_gather results in fewerworkers? - Mailing list pgsql-performance

> On Jun 3, 2020, at 5:15 PM, Justin Pryzby <pryzby@telsasoft.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@philip-2020-05-19-cluster:wylan>
SET max_parallel_workers_per_gather=12
SET
Time: 0.028s
postgres@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
confirmthat when I set max_parallel_workers_per_gather > 4 and the runtime increases, this is what’s in the EXPLAIN
ANALYZEoutput: 

         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
variationwithin that range correlates only very weakly with max_parallel_workers_per_gather. 


thank you
Philip