Tests:
create table mytab(x int,x1 char(9),x2 varchar(9));
create table mytab1(y int,y1 char(9),y2 varchar(9));
insert into mytab values (generate_series(1,50000),'aa','aaa');
insert into mytab1 values (generate_series(1,10000),'aa','aaa');
insert into mytab values (generate_series(1,500000),'aa','aaa');
insert into mytab values (generate_series(1,500000),'aa','aaa');
analyze mytab;
analyze mytab1;
vacuum mytab;
vacuum mytab1;
set max_parallel_degree=0;
SET
df=# SELECT count(*) FROM mytab RIGHT OUTER JOIN mytab1
ON mytab.x = mytab1.y;
count
-------
30000
(1 row)
# set max_parallel_degree=5;
SET
df=# SELECT count(*) FROM mytab RIGHT OUTER JOIN mytab1
ON mytab.x = mytab1.y;
count
-------
39089
(1 row)
Casue:
======
Normal plan
==========
explain SELECT count(*) FROM mytab RIGHT OUTER JOIN mytab1
ON mytab.x = mytab1.y;postgres-#
QUERY PLAN
------------------------------------------------------------------------------
Aggregate (cost=21682.71..21682.72 rows=1 width=8)
-> Hash Right Join (cost=289.00..21629.07 rows=21457 width=0)
Hash Cond: (mytab.x = mytab1.y)
-> Seq Scan on mytab (cost=0.00..17188.00 rows=1050000 width=4)
-> Hash (cost=164.00..164.00 rows=10000 width=4)
-> Seq Scan on mytab1 (cost=0.00..164.00 rows=10000 width=4)
=================================================================
Parallel plan.
==========
explain SELECT count(*) FROM mytab RIGHT OUTER JOIN mytab1
ON mytab.x = mytab1.y;postgres-#
QUERY PLAN
-----------------------------------------------------------------------------------------------
Finalize Aggregate (cost=14135.88..14135.89 rows=1 width=8)
-> Gather (cost=14135.67..14135.88 rows=2 width=8)
Number of Workers: 2
-> Partial Aggregate (cost=13135.67..13135.68 rows=1 width=8)
-> Hash Right Join (cost=289.00..13082.02 rows=21457 width=0)
Hash Cond: (mytab.x = mytab1.y)
-> Parallel Seq Scan on mytab (cost=0.00..11063.00 rows=437500 width=4)
-> Hash (cost=164.00..164.00 rows=10000 width=4)
-> Seq Scan on mytab1 (cost=0.00..164.00 rows=10000 width=4)
As above Right and Full join paths cannot be parallel as they can produce false null extended rows because outer table is partial path and not completely visible.
Adding a patch to fix same.
--
Thanks and Regards
Mithun C Y
