Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.80.23.2.2\))
Subject: Re: increased max_parallel_workers_per_gather results in fewer
 workers?
From: Philip Semanchuk <philip@americanefficient.com>
In-Reply-To: <4384167D-196A-4630-A683-B229177F8AFD@swarm64.com>
Date: Thu, 4 Jun 2020 12:41:35 -0400
Cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <89423FD3-0F13-447D-8D9E-EB1722150F94@americanefficient.com>
References: <B2C0B20E-4562-4F0B-A876-922EA4AC22A8@americanefficient.com>
 <20200603211526.GU30144@telsasoft.com>
 <1F407302-1203-41DB-9A9C-69EA0FAFDCC6@americanefficient.com>
 <4384167D-196A-4630-A683-B229177F8AFD@swarm64.com>
To: Sebastian Dressler <sebastian@swarm64.com>
Precedence: bulk


> On Jun 4, 2020, at 2:28 AM, Sebastian Dressler <sebastian@swarm64.com> =
wrote:
>=20
> Hi Philip,
>=20
>> On 4. Jun 2020, at 00:23, Philip Semanchuk =
<philip@americanefficient.com> wrote:
>>=20
>>> I guess you should show an explain analyze, specifically "Workers
>>> Planned/Launched", maybe by linking to explain.depesz.com
>>=20
>> 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=E2=80=99s in the EXPLAIN ANALYZE output:
>>=20
>>        Workers Planned: 1
>>        Workers Launched: 1
>=20
> Can you please verify the amount of max_parallel_workers and =
max_worker_processes? It should be roughly max_worker_processes > =
max_parallel_workers > max_parallel_workers_per_gather, for instance:
>=20
> max_worker_processes =3D 24
> max_parallel_workers =3D 18
> max_parallel_workers_per_gather =3D 6


I changed my settings to these exact values and can still recreate the =
situation where I unexpectedly get a single worker query.


> Also, there are more configuration settings related to parallel =
queries you might want to look into. Most notably:
>=20
> parallel_setup_cost
> parallel_tuple_cost
> min_parallel_table_scan_size
>=20
> Especially the last one is a typical dealbreaker, you can try to set =
it to 0 for the beginning. Good starters for the others are 500 and 0.1 =
respectively.

Aha! By setting min_parallel_table_scan_size=3D0, Postgres uses the 6 =
workers I expect, and the execution time decreases nicely.=20

I posted a clumsily-anonymized plan for the =E2=80=9Cbad=E2=80=9D =
scenario here --
https://gist.github.com/osvenskan/ea00aa71abaa9697ade0ab7c1f3b705b

There are 3 sort nodes in the plan. When I get the =E2=80=9Cbad=E2=80=9D =
behavior, the sorts have one worker, when I get the good behavior, they =
have multiple workers (e.g. 6).

This brings up a couple of questions =E2=80=94
1) I=E2=80=99ve read that this is Postgres=E2=80=99 formula for the max =
# of workers it will consider for a table =E2=80=94

   max_workers =3D log3(table size / min_parallel_table_scan_size)

Does that use the raw table size, or does the planner use statistics to =
estimate the size of the subset of the table that will be read before =
allocating workers?

2) There are 9 tables in this query ranging in size from 72Kb to 17Gb. =
Does Postgres decide on a table-by-table basis to allocate multiple =
workers, or=E2=80=A6?

Thank you so much for the suggestion, I feel un-stuck now that I have an =
idea to experiment with.

Cheers
Philip