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.
How come you get different number of rows ?
@Tabarnak:
not sure what you’re talking about – both queries returned 9889 rows.
The number of rows displayed in the example is the number of rows in the EXPLAIN, not the number of rows in the result.
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
Excuse me, I would like to know whether parallel joins is invalid for a temporary table.
Through my experiment, I found that the temporary table can not support the parallel operation.
I want to know the reason ?
Please give me some guidance.
Thank you~
@youyou:
my initial guess is that temporary tables are visible only to single backend.
But – I guess it would be better to ask devs in official support channel – like pgsql-general mailing list.