test=# set min_parallel_table_scan_size = '1kB';
SET
test=# set min_parallel_index_scan_size = '1kB';
SET
test=# set work_mem = '128MB';
SET
test=# set max_parallel_workers_per_gather = 0;
SET
test=# \i explain/17.sql 
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Limit  (cost=23594267.60..23594267.61 rows=1 width=32)
   ->  Aggregate  (cost=23594267.60..23594267.61 rows=1 width=32)
         ->  Nested Loop  (cost=20113710.42..23593903.95 rows=145457 width=8)
               Join Filter: (part.p_partkey = lineitem.l_partkey)
               ->  Hash Join  (cost=20113709.84..22724046.56 rows=14545 width=40)
                     Hash Cond: (lineitem_1.l_partkey = part.p_partkey)
                     ->  HashAggregate  (cost=19581331.82..22002927.78 rows=14949762 width=36)
                           Group Key: lineitem_1.l_partkey
                           Planned Partitions: 64
                           ->  Seq Scan on lineitem lineitem_1  (cost=0.00..12936546.84 rows=450017184 width=9)
                     ->  Hash  (cost=532195.59..532195.59 rows=14594 width=4)
                           ->  Seq Scan on part  (cost=0.00..532195.59 rows=14594 width=4)
                                 Filter: ((p_brand = 'Brand#22'::bpchar) AND (p_container = 'LG BOX'::bpchar))
               ->  Index Scan using idx_lineitem_part_supp on lineitem  (cost=0.57..59.68 rows=10 width=17)
                     Index Cond: (l_partkey = lineitem_1.l_partkey)
                     Filter: (l_quantity < ((0.2 * avg(lineitem_1.l_quantity))))
(16 rows)

test=# set max_parallel_workers_per_gather = 1;
SET
test=# \i explain/17.sql 
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=20155545.75..20155545.76 rows=1 width=32)
   ->  Aggregate  (cost=20155545.75..20155545.76 rows=1 width=32)
         ->  Nested Loop  (cost=18755543.08..20155182.10 rows=145457 width=8)
               Join Filter: (part.p_partkey = lineitem.l_partkey)
               ->  Hash Join  (cost=18755542.51..19285324.71 rows=14545 width=40)
                     Hash Cond: (lineitem_1.l_partkey = part.p_partkey)
                     ->  Finalize HashAggregate  (cost=18313351.98..18654393.43 rows=14949762 width=36)
                           Group Key: lineitem_1.l_partkey
                           Planned Partitions: 64
                           ->  Gather  (cost=14993231.96..17967638.74 rows=14949762 width=36)
                                 Workers Planned: 1
                                 ->  Partial HashAggregate  (cost=14992231.96..16471662.54 rows=14949762 width=36)
                                       Group Key: lineitem_1.l_partkey
                                       Planned Partitions: 64
                                       ->  Parallel Seq Scan on lineitem lineitem_1  (cost=0.00..11083534.91 rows=264715991 width=9)
                     ->  Hash  (cost=442008.10..442008.10 rows=14594 width=4)
                           ->  Gather  (cost=1000.00..442008.10 rows=14594 width=4)
                                 Workers Planned: 1
                                 ->  Parallel Seq Scan on part  (cost=0.00..439548.70 rows=8585 width=4)
                                       Filter: ((p_brand = 'Brand#22'::bpchar) AND (p_container = 'LG BOX'::bpchar))
               ->  Index Scan using idx_lineitem_part_supp on lineitem  (cost=0.57..59.68 rows=10 width=17)
                     Index Cond: (l_partkey = lineitem_1.l_partkey)
                     Filter: (l_quantity < ((0.2 * avg(lineitem_1.l_quantity))))
(23 rows)

test=# set max_parallel_workers_per_gather = 2;
SET
test=# \i explain/17.sql 
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=19785306.14..19785306.15 rows=1 width=32)
   ->  Aggregate  (cost=19785306.14..19785306.15 rows=1 width=32)
         ->  Nested Loop  (cost=18268508.46..19784942.50 rows=145457 width=8)
               Join Filter: (part.p_partkey = lineitem.l_partkey)
               ->  Hash Join  (cost=18268507.89..18915085.11 rows=14545 width=40)
                     Hash Cond: (lineitem_1.l_partkey = part.p_partkey)
                     ->  Finalize HashAggregate  (cost=17864920.23..18322756.69 rows=14949762 width=36)
                           Group Key: lineitem_1.l_partkey
                           Planned Partitions: 64
                           ->  Gather  (cost=13081107.01..17173493.74 rows=29899524 width=36)
                                 Workers Planned: 2
                                 ->  Partial HashAggregate  (cost=13080107.01..14182541.34 rows=14949762 width=36)
                                       Group Key: lineitem_1.l_partkey
                                       Planned Partitions: 64
                                       ->  Parallel Seq Scan on lineitem lineitem_1  (cost=0.00..10311446.60 rows=187507160 width=9)
                     ->  Hash  (cost=403405.23..403405.23 rows=14594 width=4)
                           ->  Gather  (cost=1000.00..403405.23 rows=14594 width=4)
                                 Workers Planned: 2
                                 ->  Parallel Seq Scan on part  (cost=0.00..400945.83 rows=6081 width=4)
                                       Filter: ((p_brand = 'Brand#22'::bpchar) AND (p_container = 'LG BOX'::bpchar))
               ->  Index Scan using idx_lineitem_part_supp on lineitem  (cost=0.57..59.68 rows=10 width=17)
                     Index Cond: (l_partkey = lineitem_1.l_partkey)
                     Filter: (l_quantity < ((0.2 * avg(lineitem_1.l_quantity))))
(23 rows)

test=# set max_parallel_workers_per_gather = 5;
SET
test=# \i explain/17.sql 
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=22718176.57..22718176.59 rows=1 width=32)
   ->  Aggregate  (cost=22718176.57..22718176.59 rows=1 width=32)
         ->  Nested Loop  (cost=20850993.85..22717812.93 rows=145457 width=8)
               Join Filter: (part.p_partkey = lineitem.l_partkey)
               ->  Hash Join  (cost=20850993.27..21847955.54 rows=14545 width=40)
                     Hash Cond: (lineitem_1.l_partkey = part.p_partkey)
                     ->  Finalize HashAggregate  (cost=20496155.53..21304377.04 rows=14949762 width=36)
                           Group Key: lineitem_1.l_partkey
                           Planned Partitions: 64
                           ->  Gather  (cost=10666366.37..18767589.30 rows=74748810 width=36)
                                 Workers Planned: 5
                                 ->  Partial HashAggregate  (cost=10665366.37..11291708.30 rows=14949762 width=36)
                                       Group Key: lineitem_1.l_partkey
                                       Planned Partitions: 64
                                       ->  Parallel Seq Scan on lineitem lineitem_1  (cost=0.00..9336409.37 rows=90003437 width=9)
                     ->  Hash  (cost=354655.32..354655.32 rows=14594 width=4)
                           ->  Gather  (cost=1000.00..354655.32 rows=14594 width=4)
                                 Workers Planned: 5
                                 ->  Parallel Seq Scan on part  (cost=0.00..352195.92 rows=2919 width=4)
                                       Filter: ((p_brand = 'Brand#22'::bpchar) AND (p_container = 'LG BOX'::bpchar))
               ->  Index Scan using idx_lineitem_part_supp on lineitem  (cost=0.57..59.68 rows=10 width=17)
                     Index Cond: (l_partkey = lineitem_1.l_partkey)
                     Filter: (l_quantity < ((0.2 * avg(lineitem_1.l_quantity))))
(23 rows)

test=# set max_parallel_workers_per_gather = 6;
SET
test=# \i explain/17.sql 
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=23063639.26..23063639.27 rows=1 width=32)
   ->  Finalize Aggregate  (cost=23063639.26..23063639.27 rows=1 width=32)
         ->  Gather  (cost=23063638.62..23063639.23 rows=6 width=32)
               Workers Planned: 6
               ->  Partial Aggregate  (cost=23062638.62..23062638.63 rows=1 width=32)
                     ->  Nested Loop  (cost=22456094.00..23062578.01 rows=24243 width=8)
                           Join Filter: (part.p_partkey = lineitem.l_partkey)
                           ->  Hash Join  (cost=22456093.43..22917611.75 rows=2424 width=40)
                                 Hash Cond: (part.p_partkey = lineitem_1.l_partkey)
                                 ->  Parallel Seq Scan on part  (cost=0.00..344695.93 rows=2432 width=4)
                                       Filter: ((p_brand = 'Brand#22'::bpchar) AND (p_container = 'LG BOX'::bpchar))
                                 ->  Hash  (cost=22152425.40..22152425.40 rows=14949762 width=36)
                                       ->  HashAggregate  (cost=19581331.82..22002927.78 rows=14949762 width=36)
                                             Group Key: lineitem_1.l_partkey
                                             Planned Partitions: 64
                                             ->  Seq Scan on lineitem lineitem_1  (cost=0.00..12936546.84 rows=450017184 width=9)
                           ->  Index Scan using idx_lineitem_part_supp on lineitem  (cost=0.57..59.68 rows=10 width=17)
                                 Index Cond: (l_partkey = lineitem_1.l_partkey)
                                 Filter: (l_quantity < ((0.2 * avg(lineitem_1.l_quantity))))
(19 rows)

test=# set max_parallel_workers_per_gather = 10;
SET
test=# \i explain/17.sql 
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=22990594.48..22990594.50 rows=1 width=32)
   ->  Finalize Aggregate  (cost=22990594.48..22990594.50 rows=1 width=32)
         ->  Gather  (cost=22990593.42..22990594.43 rows=10 width=32)
               Workers Planned: 10
               ->  Partial Aggregate  (cost=22989593.42..22989593.43 rows=1 width=32)
                     ->  Nested Loop  (cost=22456094.00..22989557.05 rows=14546 width=8)
                           Join Filter: (part.p_partkey = lineitem.l_partkey)
                           ->  Hash Join  (cost=22456093.43..22902601.22 rows=1454 width=40)
                                 Hash Cond: (part.p_partkey = lineitem_1.l_partkey)
                                 ->  Parallel Seq Scan on part  (cost=0.00..329695.96 rows=1459 width=4)
                                       Filter: ((p_brand = 'Brand#22'::bpchar) AND (p_container = 'LG BOX'::bpchar))
                                 ->  Hash  (cost=22152425.40..22152425.40 rows=14949762 width=36)
                                       ->  HashAggregate  (cost=19581331.82..22002927.78 rows=14949762 width=36)
                                             Group Key: lineitem_1.l_partkey
                                             Planned Partitions: 64
                                             ->  Seq Scan on lineitem lineitem_1  (cost=0.00..12936546.84 rows=450017184 width=9)
                           ->  Index Scan using idx_lineitem_part_supp on lineitem  (cost=0.57..59.68 rows=10 width=17)
                                 Index Cond: (l_partkey = lineitem_1.l_partkey)
                                 Filter: (l_quantity < ((0.2 * avg(lineitem_1.l_quantity))))
(19 rows)

test=# set max_parallel_workers_per_gather = 32;
SET
test=# \i explain/17.sql 
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=22972363.30..22972363.31 rows=1 width=32)
   ->  Finalize Aggregate  (cost=22972363.30..22972363.31 rows=1 width=32)
         ->  Gather  (cost=22972362.02..22972363.23 rows=12 width=32)
               Workers Planned: 12
               ->  Partial Aggregate  (cost=22971362.02..22971362.03 rows=1 width=32)
                     ->  Nested Loop  (cost=22456094.00..22971331.72 rows=12121 width=8)
                           Join Filter: (part.p_partkey = lineitem.l_partkey)
                           ->  Hash Join  (cost=22456093.43..22898848.59 rows=1212 width=40)
                                 Hash Cond: (part.p_partkey = lineitem_1.l_partkey)
                                 ->  Parallel Seq Scan on part  (cost=0.00..325945.97 rows=1216 width=4)
                                       Filter: ((p_brand = 'Brand#22'::bpchar) AND (p_container = 'LG BOX'::bpchar))
                                 ->  Hash  (cost=22152425.40..22152425.40 rows=14949762 width=36)
                                       ->  HashAggregate  (cost=19581331.82..22002927.78 rows=14949762 width=36)
                                             Group Key: lineitem_1.l_partkey
                                             Planned Partitions: 64
                                             ->  Seq Scan on lineitem lineitem_1  (cost=0.00..12936546.84 rows=450017184 width=9)
                           ->  Index Scan using idx_lineitem_part_supp on lineitem  (cost=0.57..59.68 rows=10 width=17)
                                 Index Cond: (l_partkey = lineitem_1.l_partkey)
                                 Filter: (l_quantity < ((0.2 * avg(lineitem_1.l_quantity))))
(19 rows)