Question of Parallel Hash Join on TPC-H Benchmark

Hi everyone,

Considering the query 2 in TPC-H benchmark:

explain analyze
select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment from PART, SUPPLIER, PARTSUPP, NATION, REGION where p_partkey = ps_partkey and s_suppkey = ps_suppkey and p_size = 30 and p_type like '%STEEL' and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'ASIA' and ps_supplycost = ( select min(ps_supplycost) from PARTSUPP, SUPPLIER, NATION, REGION where p_partkey = ps_partkey and s_suppkey = ps_suppkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'ASIA' ) order by s_acctbal desc, n_name, s_name, p_partkey limit 100;

I run it on PostgreSQL and got this query plan:

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=66839.16..66839.17 rows=1 width=195) (actual time=267.054..267.114 rows=100 loops=1)
-> Sort (cost=66839.16..66839.17 rows=1 width=195) (actual time=267.053..267.108 rows=100 loops=1)
Sort Key: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
Sort Method: top-N heapsort Memory: 71kB
-> Hash Join (cost=37874.96..66839.15 rows=1 width=195) (actual time=227.273..266.816 rows=485 loops=1)
Hash Cond: ((part.p_partkey = partsupp.ps_partkey) AND ((SubPlan 1) = partsupp.ps_supplycost))
-> Gather (cost=1000.00..6458.40 rows=804 width=30) (actual time=0.567..0.733 rows=826 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on part (cost=0.00..5378.00 rows=335 width=30) (actual time=0.156..15.960 rows=275 loops=3)
Filter: (((p_type)::text ~~ '%STEEL'::text) AND (p_size = 30))
Rows Removed by Filter: 66391
-> Hash (cost=30567.96..30567.96 rows=160000 width=175) (actual time=225.816..225.820 rows=160240 loops=1)
Buckets: 65536 Batches: 8 Memory Usage: 4648kB
-> Hash Join (cost=407.96..30567.96 rows=160000 width=175) (actual time=5.200..162.555 rows=160240 loops=1)
Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey)
-> Seq Scan on partsupp (cost=0.00..25560.00 rows=800000 width=14) (actual time=0.016..59.835 rows=800000 loops=1)
-> Hash (cost=382.96..382.96 rows=2000 width=169) (actual time=5.169..5.172 rows=2003 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 413kB
-> Hash Join (cost=2.46..382.96 rows=2000 width=169) (actual time=0.108..4.336 rows=2003 loops=1)
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Seq Scan on supplier (cost=0.00..323.00 rows=10000 width=144) (actual time=0.013..2.478 rows=10000 loops=1)
-> Hash (cost=2.40..2.40 rows=5 width=33) (actual time=0.069..0.071 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Join (cost=1.07..2.40 rows=5 width=33) (actual time=0.060..0.066 rows=5 loops=1)
Hash Cond: (nation.n_regionkey = region.r_regionkey)
-> Seq Scan on nation (cost=0.00..1.25 rows=25 width=37) (actual time=0.011..0.013 rows=25 loops=1)
-> Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.029..0.029 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on region (cost=0.00..1.06 rows=1 width=4) (actual time=0.014..0.015 rows=1 loops=1)
Filter: (r_name = 'ASIA'::bpchar)
Rows Removed by Filter: 4
SubPlan 1
-> Aggregate (cost=48.70..48.71 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=1311)
-> Nested Loop (cost=0.85..48.70 rows=1 width=6) (actual time=0.014..0.018 rows=1 loops=1311)
Join Filter: (region_1.r_regionkey = nation_1.n_regionkey)
Rows Removed by Join Filter: 3
-> Seq Scan on region region_1 (cost=0.00..1.06 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1311)
Filter: (r_name = 'ASIA'::bpchar)
Rows Removed by Filter: 4
-> Nested Loop (cost=0.85..47.58 rows=4 width=10) (actual time=0.010..0.017 rows=4 loops=1311)
-> Nested Loop (cost=0.71..46.96 rows=4 width=10) (actual time=0.009..0.013 rows=4 loops=1311)
-> Index Scan using partsupp_pkey on partsupp partsupp_1 (cost=0.42..13.75 rows=4 width=10) (actual time=0.007..0.008 rows=4 loops=1311)
Index Cond: (ps_partkey = part.p_partkey)
-> Index Scan using supplier_pkey on supplier supplier_1 (cost=0.29..8.30 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=5244)
Index Cond: (s_suppkey = partsupp_1.ps_suppkey)
-> Index Scan using nation_pkey on nation nation_1 (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=5244)
Index Cond: (n_nationkey = supplier_1.s_nationkey)
(50 rows)

I am wondering why the HASH JOIN in the fifth line is not in parallel?
According to this document https://www.postgresql.org/docs/current/parallel-safety.html, I understand it should be in parallel, and we can get a more efficient query plan as follows:

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=42019.25..42019.25 rows=1 width=195) (actual time=134.698..142.258 rows=100 loops=1)
-> Sort (cost=42019.25..42019.25 rows=1 width=195) (actual time=134.697..142.252 rows=100 loops=1)
Sort Key: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
Sort Method: top-N heapsort Memory: 69kB
-> Gather (cost=26845.97..42019.24 rows=1 width=195) (actual time=116.843..142.014 rows=485 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Hash Join (cost=25845.97..41019.14 rows=1 width=195) (actual time=120.427..130.569 rows=162 loops=3)
Hash Cond: ((part.p_partkey = partsupp.ps_partkey) AND ((SubPlan 1) = partsupp.ps_supplycost))
-> Parallel Seq Scan on part (cost=0.00..5378.00 rows=335 width=30) (actual time=0.102..9.739 rows=275 loops=3)
Filter: (((p_type)::text ~~ '%STEEL'::text) AND (p_size = 30))
Rows Removed by Filter: 66391
-> Parallel Hash (cost=23217.97..23217.97 rows=66667 width=175) (actual time=97.833..97.837 rows=53413 loops=3)
Buckets: 65536 Batches: 8 Memory Usage: 4800kB
-> Hash Join (cost=407.96..23217.97 rows=66667 width=175) (actual time=4.596..69.280 rows=53413 loops=3)
Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey)
-> Parallel Seq Scan on partsupp (cost=0.00..20893.33 rows=333333 width=14) (actual time=0.039..26.690 rows=266667 loops=3)
-> Hash (cost=382.96..382.96 rows=2000 width=169) (actual time=4.543..4.545 rows=2003 loops=3)
Buckets: 2048 Batches: 1 Memory Usage: 413kB
-> Hash Join (cost=2.46..382.96 rows=2000 width=169) (actual time=0.106..3.658 rows=2003 loops=3)
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Seq Scan on supplier (cost=0.00..323.00 rows=10000 width=144) (actual time=0.015..1.071 rows=10000 loops=3)
-> Hash (cost=2.40..2.40 rows=5 width=33) (actual time=0.070..0.072 rows=5 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Join (cost=1.07..2.40 rows=5 width=33) (actual time=0.061..0.067 rows=5 loops=3)
Hash Cond: (nation.n_regionkey = region.r_regionkey)
-> Seq Scan on nation (cost=0.00..1.25 rows=25 width=37) (actual time=0.009..0.011 rows=25 loops=3)
-> Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.031..0.031 rows=1 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on region (cost=0.00..1.06 rows=1 width=4) (actual time=0.014..0.016 rows=1 loops=3)
Filter: (r_name = 'ASIA'::bpchar)
Rows Removed by Filter: 4
SubPlan 1
-> Aggregate (cost=48.70..48.71 rows=1 width=32) (actual time=0.017..0.017 rows=1 loops=3810)
-> Nested Loop (cost=0.85..48.70 rows=1 width=6) (actual time=0.012..0.016 rows=1 loops=3810)
Join Filter: (region_1.r_regionkey = nation_1.n_regionkey)
Rows Removed by Join Filter: 3
-> Seq Scan on region region_1 (cost=0.00..1.06 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=3810)
Filter: (r_name = 'ASIA'::bpchar)
Rows Removed by Filter: 4
-> Nested Loop (cost=0.85..47.58 rows=4 width=10) (actual time=0.006..0.015 rows=4 loops=3810)
-> Nested Loop (cost=0.71..46.96 rows=4 width=10) (actual time=0.005..0.011 rows=4 loops=3810)
-> Index Scan using partsupp_pkey on partsupp partsupp_1 (cost=0.42..13.75 rows=4 width=10) (actual time=0.003..0.004 rows=4 loops=3810)
Index Cond: (ps_partkey = part.p_partkey)
-> Index Scan using supplier_pkey on supplier supplier_1 (cost=0.29..8.30 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=15240)
Index Cond: (s_suppkey = partsupp_1.ps_suppkey)
-> Index Scan using nation_pkey on nation nation_1 (cost=0.14..0.16 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=15240)
Index Cond: (n_nationkey = supplier_1.s_nationkey)
(50 rows)

Best regards,

Jinsheng Ba

Notice: This email is generated from the account of an NUS alumnus. Contents, views, and opinions therein are solely those of the sender.