large-plans-hash.txt
text/plain
Filename: large-plans-hash.txt
Type: text/plain
Part: 1
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)