> On Jun 3, 2020, at 5:15 PM, Justin Pryzby <pry...@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 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
