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=# set enable_hashagg = off; SET test=# \i explain/17.sql QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Limit (cost=88495966.59..88495966.60 rows=1 width=32) -> Aggregate (cost=88495966.59..88495966.60 rows=1 width=32) -> Merge Join (cost=83109553.57..88495602.94 rows=145457 width=8) Merge Cond: (lineitem_1.l_partkey = part.p_partkey) Join Filter: (lineitem.l_quantity < ((0.2 * avg(lineitem_1.l_quantity)))) -> GroupAggregate (cost=83109552.56..86708927.87 rows=14949762 width=36) Group Key: lineitem_1.l_partkey -> Sort (cost=83109552.56..84234595.52 rows=450017184 width=9) Sort Key: lineitem_1.l_partkey -> Seq Scan on lineitem lineitem_1 (cost=0.00..12936546.84 rows=450017184 width=9) -> Materialize (cost=1.01..1593253.80 rows=437838 width=21) -> Nested Loop (cost=1.01..1592159.21 rows=437838 width=21) -> Index Scan using part_pkey on part (cost=0.43..689458.90 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..61.55 rows=30 width=17) Index Cond: (l_partkey = part.p_partkey) (16 rows) test=# set max_parallel_workers_per_gather = 1; SET test=# \i explain/17.sql QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=56912954.14..56912954.15 rows=1 width=32) -> Aggregate (cost=56912954.14..56912954.15 rows=1 width=32) -> Merge Join (cost=51350528.65..56912590.49 rows=145457 width=8) Merge Cond: (lineitem_1.l_partkey = part.p_partkey) Join Filter: (lineitem.l_quantity < ((0.2 * avg(lineitem_1.l_quantity)))) -> Finalize GroupAggregate (cost=51349527.63..55539987.46 rows=14949762 width=36) Group Key: lineitem_1.l_partkey -> Gather Merge (cost=51349527.63..55203617.82 rows=14949762 width=36) Workers Planned: 1 -> Partial GroupAggregate (cost=51348527.62..53520769.58 rows=14949762 width=36) Group Key: lineitem_1.l_partkey -> Sort (cost=51348527.62..52010317.60 rows=264715991 width=9) Sort Key: lineitem_1.l_partkey -> Parallel Seq Scan on lineitem lineitem_1 (cost=0.00..11083534.91 rows=264715991 width=9) -> Materialize (cost=1001.02..1179181.76 rows=437838 width=21) -> Gather Merge (cost=1001.02..1178087.17 rows=437838 width=21) Workers Planned: 1 -> Nested Loop (cost=1.01..1127830.38 rows=257552 width=21) -> Parallel Index Scan using part_pkey on part (cost=0.43..596812.00 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..61.55 rows=30 width=17) Index Cond: (l_partkey = part.p_partkey) (22 rows) test=# set max_parallel_workers_per_gather = 5; SET test=# \i explain/17.sql QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Limit (cost=33914092.61..33914092.62 rows=1 width=32) -> Aggregate (cost=33914092.61..33914092.62 rows=1 width=32) -> Merge Join (cost=22328104.16..33913728.97 rows=145457 width=8) Merge Cond: (lineitem_1.l_partkey = part.p_partkey) Join Filter: (lineitem.l_quantity < ((0.2 * avg(lineitem_1.l_quantity)))) -> Finalize GroupAggregate (cost=22327103.07..32975474.81 rows=14949762 width=36) Group Key: lineitem_1.l_partkey -> Gather Merge (cost=22327103.07..32190612.31 rows=74748810 width=36) Workers Planned: 5 -> Partial GroupAggregate (cost=22326103.00..23188000.80 rows=14949762 width=36) Group Key: lineitem_1.l_partkey -> Sort (cost=22326103.00..22551111.59 rows=90003437 width=9) Sort Key: lineitem_1.l_partkey -> Parallel Seq Scan on lineitem lineitem_1 (cost=0.00..9336409.37 rows=90003437 width=9) -> Materialize (cost=1001.09..744832.88 rows=437838 width=21) -> Gather Merge (cost=1001.09..743738.29 rows=437838 width=21) Workers Planned: 5 -> Nested Loop (cost=1.01..690011.65 rows=87568 width=21) -> Parallel Index Scan using part_pkey on part (cost=0.43..509459.22 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..61.55 rows=30 width=17) Index Cond: (l_partkey = part.p_partkey) (22 rows) test=# set max_parallel_workers_per_gather = 15; SET test=# \i explain/17.sql QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Limit (cost=44566651.53..44566651.54 rows=1 width=32) -> Aggregate (cost=44566651.53..44566651.54 rows=1 width=32) -> Merge Join (cost=12830532.44..44566287.88 rows=145457 width=8) Merge Cond: (lineitem_1.l_partkey = part.p_partkey) Join Filter: (lineitem.l_quantity < ((0.2 * avg(lineitem_1.l_quantity)))) -> Finalize GroupAggregate (cost=12829531.24..43738926.34 rows=14949762 width=36) Group Key: lineitem_1.l_partkey -> Gather Merge (cost=12829531.24..41832831.69 rows=224246430 width=36) Workers Planned: 15 -> Partial GroupAggregate (cost=12828530.92..13240411.54 rows=14949762 width=36) Group Key: lineitem_1.l_partkey -> Sort (cost=12828530.92..12903533.79 rows=30001146 width=9) Sort Key: lineitem_1.l_partkey -> Parallel Seq Scan on lineitem lineitem_1 (cost=0.00..8736386.46 rows=30001146 width=9) -> Materialize (cost=1001.20..633940.27 rows=437838 width=21) -> Gather Merge (cost=1001.20..632845.68 rows=437838 width=21) Workers Planned: 10 -> Nested Loop (cost=1.01..577204.55 rows=43784 width=21) -> Parallel Index Scan using part_pkey on part (cost=0.43..486959.26 rows=1459 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..61.55 rows=30 width=17) Index Cond: (l_partkey = part.p_partkey) (22 rows)