Re: increased max_parallel_workers_per_gather results in fewer workers?

> On Jun 4, 2020, at 2:28 AM, Sebastian Dressler <sebastian(at)swarm64(dot)com> wrote:
>
> Hi Philip,
>
>> On 4. Jun 2020, at 00:23, Philip Semanchuk <philip(at)americanefficient(dot)com> wrote:
>>
>>> 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
>
> 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:
>
> max_worker_processes = 24
> max_parallel_workers = 18
> max_parallel_workers_per_gather = 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:
>
> parallel_setup_cost
> parallel_tuple_cost
> min_parallel_table_scan_size
>
> 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=0, Postgres uses the 6 workers I expect, and the execution time decreases nicely.

I posted a clumsily-anonymized plan for the “bad” scenario here --
https://gist.github.com/osvenskan/ea00aa71abaa9697ade0ab7c1f3b705b

There are 3 sort nodes in the plan. When I get the “bad” 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 —
1) I’ve read that this is Postgres’ formula for the max # of workers it will consider for a table —

max_workers = 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…?

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

Cheers
Philip