Avoid parallel full and right join paths.

From: Mithun Cy <mithun(dot)cy(at)enterprisedb(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>
Subject: Avoid parallel full and right join paths.
Date: 2016-04-19 14:21:09
Message-ID: CAD__Ouh_PB4y37K1PJJTKjZnM5UN_L2NtpQXL5+kqWdDNMDj8g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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
EnterpriseDB: http://www.enterprisedb.com

Attachment Content-Type Size
avoid_parallel_full_right_join.patch application/octet-stream 3.4 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alexander Korotkov 2016-04-19 14:26:47 Re: Declarative partitioning
Previous Message Andres Freund 2016-04-19 14:07:09 Re: Parser extensions (maybe for 10?)