Re: parallel joins, and better parallel explain

On Tue, Dec 15, 2015 at 7:31 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Mon, Dec 14, 2015 at 8:38 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
wrote:
> > set enable_hashjoin=off;
> > set enable_mergejoin=off;
>
> [ ... ]
>
>
> > Now here the point to observe is that non-parallel case uses both less
> > Execution time and Planning time to complete the statement. There
> > is a considerable increase in planning time without any benefit in
> > execution.
>
> So, you forced the query planner to give you a bad plan, and then
> you're complaining that the plan is bad?
>

Oh no, I wanted to check the behaviour of parallel vs. non-parallel
execution of joins. I think even if hash and merge join are set to
off, it should have picked up non-parallel NestLoop plan. In any case,
I have done some more investigation of the patch and found that even
without changing query planner related parameters, it seems to give
bad plans (as in example below [1]). I think here the costing of rework
each
worker has to do seems to be missing which is causing bad plans to
be selected over good plans. Another point is that with patch, the number
of
paths that we explore to get the cheapest path have increased, do you think
we should try to evaluate it? One way is we run some queries where there
are more number of joins and see the impact on planner time and other is we
try to calculate the increase in number of paths that planner explores.

[1] -
CREATE TABLE t1(c1, c2) AS SELECT g, repeat('x', 5) FROM
generate_series(1, 10000000) g;

CREATE TABLE t2(c1, c2) AS SELECT g, repeat('x', 5) FROM
generate_series(1, 3000000) g;

Analyze t1;
Analyze t2;

Non-parallel case

postgres=# Explain Analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 = t2.c1
AND t1.c1 BETWEEN 100 AND 200;
QUERY PLAN

-------------------------------------------------------------------------------------------------------------
-------------
Aggregate (cost=261519.93..261519.94 rows=1 width=0) (actual
time=2779.965..2779.965 rows=1 loops=1)
-> Hash Join (cost=204052.91..261519.92 rows=1 width=0) (actual
time=2017.241..2779.947 rows=101
loops=1)
Hash Cond: (t2.c1 = t1.c1)
-> Seq Scan on t2 (cost=0.00..46217.00 rows=3000000 width=4)
(actual time=0.073..393.479
rows=3000000 loops=1)
-> Hash (cost=204052.90..204052.90 rows=1 width=4) (actual
time=2017.130..2017.130 rows=101
loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Seq Scan on t1 (cost=0.00..204052.90 rows=1 width=4)
(actual time=0.038..2017.105
rows=101 loops=1)
Filter: ((c1 >= 100) AND (c1 <= 200))
Rows Removed by Filter: 9999899
Planning time: 0.113 ms
Execution time: 2780.000 ms
(11 rows)

Parallel-case
set max_parallel_degree=4;

postgres=# Explain Analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 = t2.c1
AND t1.c1 BETWEEN 100 AND 200;
QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=100895.52..100895.53 rows=1 width=0) (actual
time=67871.443..67871.443 rows=1 loops=1)
-> Gather (cost=1000.00..100895.52 rows=1 width=0) (actual
time=0.653..67871.287 rows=101 loops=1)
Number of Workers: 4
-> Nested Loop (cost=0.00..99895.42 rows=1 width=0) (actual
time=591.408..16455.731 rows=20 loops=5)
Join Filter: (t1.c1 = t2.c1)
Rows Removed by Join Filter: 60599980
-> Parallel Seq Scan on t1 (cost=0.00..45345.09 rows=0
width=4) (actual time=433.350..433.386 rows=20 loops=5)
Filter: ((c1 >= 100) AND (c1 <= 200))
Rows Removed by Filter: 1999980
-> Seq Scan on t2 (cost=0.00..46217.00 rows=3000000
width=4) (actual time=0.005..395.480 rows=3000000 loops=101)
Planning time: 0.114 ms
Execution time: 67871.584 ms
(12 rows)

Without patch, parallel case

set max_parallel_degree=4;

Explain Analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 = t2.c1 AND t1.c1
BETWEEN 100 AND 200;
QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=103812.21..103812.22 rows=1 width=0) (actual
time=1207.043..1207.043 rows=1 loops=1)
-> Hash Join (cost=46345.20..103812.21 rows=1 width=0) (actual
time=428.632..1207.027 rows=101 loops=1)
Hash Cond: (t2.c1 = t1.c1)
-> Seq Scan on t2 (cost=0.00..46217.00 rows=3000000 width=4)
(actual time=0.034..375.670 rows=3000000 loops=1)
-> Hash (cost=46345.19..46345.19 rows=1 width=4) (actual
time=428.557..428.557 rows=101 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Gather (cost=1000.00..46345.19 rows=1 width=4) (actual
time=0.287..428.476 rows=101 loops=1)
Number of Workers: 4
-> Parallel Seq Scan on t1 (cost=0.00..45345.09
rows=1 width=4) (actual time=340.139..425.591 rows=20 loops=5)
Filter: ((c1 >= 100) AND (c1 <= 200))
Rows Removed by Filter: 1999980
Planning time: 0.116 ms
Execution time: 1207.196 ms
(13 rows)

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com