Re: parallel joins, and better parallel explain

On Tue, Dec 22, 2015 at 4:14 AM, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> On Fri, Dec 18, 2015 at 8:47 PM Robert Wrote,
>>> Yes, you are right, that create_gather_path() sets parallel_safe to false
>>> unconditionally but whenever we are building a non partial path, that
>>> time
>>> we should carry forward the parallel_safe state to its parent, and it
>>> seems
>>> like that part is missing here..
>
>>Ah, right. Woops. I can't exactly replicate your results, but I've
>>attempted to fix this in a systematic way in the new version attached
>>here (parallel-join-v3.patch).
>
> I Have tested with the latest patch, problem is solved..
>
> During my testing i observed one more behaviour in the hash join, where
> Parallel hash join is taking more time compared to Normal hash join,

I think the gather-reader-order patch will fix this. Here's a test
with all three patches.

rhaas=# SET max_parallel_degree=0;SELECT count(*) FROM t1 JOIN t2 ON
t1.c1 = t2.c1 AND t2.c2 + t1.c1 > 100;
SET
Time: 0.192 ms
count
---------
2999950
(1 row)

Time: 11331.425 ms
rhaas=# SET max_parallel_degree=1;SELECT count(*) FROM t1 JOIN t2 ON
t1.c1 = t2.c1 AND t2.c2 + t1.c1 > 100;
SET
Time: 0.185 ms
count
---------
2999950
(1 row)

Time: 8796.190 ms
rhaas=# SET max_parallel_degree=2;SELECT count(*) FROM t1 JOIN t2 ON
t1.c1 = t2.c1 AND t2.c2 + t1.c1 > 100;
SET
Time: 0.192 ms
count
---------
2999950
(1 row)

Time: 8153.258 ms
rhaas=# SET max_parallel_degree=3;SELECT count(*) FROM t1 JOIN t2 ON
t1.c1 = t2.c1 AND t2.c2 + t1.c1 > 100;
SET
Time: 0.187 ms
count
---------
2999950
(1 row)

Time: 6198.163 ms
rhaas=# SET max_parallel_degree=4;SELECT count(*) FROM t1 JOIN t2 ON
t1.c1 = t2.c1 AND t2.c2 + t1.c1 > 100;
SET
Time: 0.190 ms
count
---------
2999950
(1 row)

Time: 7511.970 ms
rhaas=# SET max_parallel_degree=5;SELECT count(*) FROM t1 JOIN t2 ON
t1.c1 = t2.c1 AND t2.c2 + t1.c1 > 100;
SET
Time: 0.152 ms
count
---------
2999950
(1 row)

Time: 7651.862 ms
rhaas=# SET max_parallel_degree=6;SELECT count(*) FROM t1 JOIN t2 ON
t1.c1 = t2.c1 AND t2.c2 + t1.c1 > 100;
SET
Time: 0.195 ms
count
---------
2999950
(1 row)

Time: 7584.073 ms

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company