> On Jun 4, 2020, at 1:45 PM, Sebastian Dressler <sebastian@xxxxxxxxxxx> wrote: > > Hi Philip, > >> On 4. Jun 2020, at 18:41, Philip Semanchuk <philip@xxxxxxxxxxxxxxxxxxxxx> wrote: >> [...] >> >>> 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). > > I also think, what Luis pointed out earlier might be a good option for you, i.e. setting > > parallel_leader_participation = off; > > And by the way, this 1 worker turns actually into 2 workers in total with leader participation enabled. I’ll try that out, thanks. > >> 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? > > "table size" is the number of PSQL pages, i.e. relation-size / 8 kB. This comes from statistics. OK, so it sounds like the planner does *not* use the values in pg_stats when planning workers, true? I’m still trying to understand one thing I’ve observed. I can run the query that produced the plan in the gist I linked to above with max_parallel_workers_per_gather=6 and the year param = 2018, and I get 6 workers. When I set the year param=2022 I get only one worker. Same tables, same query, different parameter. That suggests to me that the planner is using pg_stats when allocating workers, but I can imagine there might be other things going on that I don’t understand. (I haven’t ruled out that this might be an AWS-specific quirk, either.) Cheers Philip
