On 20th of January, Robert Haas committed patch:

The core innovation of this patch is the introduction of the concept
of a partial path; that is, a path which if executed in parallel will
generate a subset of the output rows in each process.  Gathering a
partial path produces an ordinary (complete) path.  This allows us to
generate paths for parallel joins by joining a partial path for one
side (which at the baserel level is currently always a Partial Seq
Scan) to an ordinary path on the other side.  This is subject to
various restrictions at present, especially that this strategy seems
unlikely to be sensible for merge joins, so only nested loops and
hash joins paths are generated.
 
This also allows an Append node to be pushed below a Gather node in
the case of a partitioned table.
 
Testing revealed that early versions of this patch made poor decisions
in some cases, which turned out to be caused by the fact that the
original cost model for Parallel Seq Scan wasn't very good.  So this
patch tries to make some modest improvements in that area.
 
There is much more to be done in the area of generating good parallel
plans in all cases, but this seems like a useful step forward.
 
Patch by me, reviewed by Dilip Kumar and Amit Kapila.

This is very cool.

As you know, we currently support only parallel sequential scans.

But now, thanks to author and reviewers, we got ability to join in parallel

I cannot tell you how exactly it works, so let me just show you two explains.

First, without parallelization:

$ explain analyze select t1.id, t2.id, t1.payload from t2 join t1 on t2.t1_id = t1.id where t1.payload < 10000 and t2.id < 100000;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=18143.76..21881.29 rows=9663 width=12) (actual time=74.762..99.274 rows=9889 loops=1)
   Hash Cond: (t2.t1_id = t1.id)
   ->  Index Scan using t2_pkey on t2  (cost=0.42..3269.52 rows=99148 width=8) (actual time=0.010..10.720 rows=99999 loops=1)
         Index Cond: (id < 100000)
   ->  Hash  (cost=16925.12..16925.12 rows=97457 width=8) (actual time=74.695..74.695 rows=99806 loops=1)
         Buckets: 131072  Batches: 1  Memory Usage: 4923kB
         ->  Seq Scan on t1  (cost=0.00..16925.12 rows=97457 width=8) (actual time=0.008..63.300 rows=99806 loops=1)
               Filter: (payload < 10000)
               Rows Removed by Filter: 900204
 Planning time: 0.116 ms
 Execution time: 99.569 ms
(11 rows)

and with set max_parallel_degree = 5;:

$ explain analyze select t1.id, t2.id, t1.payload from t2 join t1 on t2.t1_id = t1.id where t1.payload < 10000 and t2.id < 100000;
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=5508.87..21326.21 rows=9663 width=12) (actual time=30.820..62.216 rows=9889 loops=1)
   Number of Workers: 2
   ->  Hash Join  (cost=4508.87..19359.91 rows=9663 width=12) (actual time=30.964..59.525 rows=3296 loops=3)
         Hash Cond: (t1.id = t2.t1_id)
         ->  Parallel Seq Scan on t1  (cost=0.00..9633.39 rows=40607 width=8) (actual time=0.011..22.746 rows=33269 loops=3)
               Filter: (payload < 10000)
               Rows Removed by Filter: 300068
         ->  Hash  (cost=3269.52..3269.52 rows=99148 width=8) (actual time=30.525..30.525 rows=99999 loops=3)
               Buckets: 131072  Batches: 1  Memory Usage: 4931kB
               ->  Index Scan using t2_pkey on t2  (cost=0.42..3269.52 rows=99148 width=8) (actual time=0.024..16.122 rows=99999 loops=3)
                     Index Cond: (id < 100000)
 Planning time: 0.118 ms
 Execution time: 64.007 ms
(13 rows)

To be honest, I don't know why there were only two workers, maybe because of the fact that I joined only two tables, but still – I got ~ 35% performance increase, in my trivial query.

This looks really, really nice. Thanks guys.

  1. 4 comments

  2. # Tabarnak
    Feb 3, 2016

    How come you get different number of rows ?

  3. Feb 3, 2016

    @Tabarnak:

    not sure what you’re talking about – both queries returned 9889 rows.

  4. # Mark
    Feb 3, 2016

    The number of rows displayed in the example is the number of rows in the EXPLAIN, not the number of rows in the result.

  5. May 19, 2016

    I think either because of max parallel worker setting or because of cost of merging data from more than 2 workers,it would have chosen to go with just 2

Leave a comment