Re: parallel joins, and better parallel explain

On Tue, Dec 15, 2015 at 7:31 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Mon, Dec 14, 2015 at 8:38 AM, Amit Kapila <amit.kapila16@gmail.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