Parallel Query Is Getting Better And Better

Back in early November, I reported that the first version of parallel sequential scan had been committed to PostgreSQL 9.6.  I'm pleased to report that a number of significant enhancements have been made since then.  Of those, the two that are by the far the most important are that we now support parallel joins and parallel aggregation - which means that the range of queries that can benefit from parallelism is now far broader than just sequential scans.

For example, I initialized a database using pgbench -i -s 100 and then did this (very slightly edited for clarity):


rhaas=# explain (costs off) select count(*) from pgbench_accounts a, pgbench_branches b where a.bid = b.bid;
                    QUERY PLAN                   
--------------------------------------------------
 Aggregate
   ->  Hash Join
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a
         ->  Hash
               ->  Seq Scan on pgbench_branches b
(6 rows)

rhaas=# select count(*) from pgbench_accounts a, pgbench_branches b where a.bid = b.bid;
  count  
----------
 10000000
(1 row)

Time: 1882.080 ms
rhaas=# set max_parallel_degree = 5;
SET
rhaas=# explain (costs off) select count(*) from pgbench_accounts a, pgbench_branches b where a.bid = b.bid;
                           QUERY PLAN                           
-----------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Number of Workers: 5
         ->  Partial Aggregate
               ->  Hash Join
                     Hash Cond: (a.bid = b.bid)
                     ->  Parallel Seq Scan on pgbench_accounts a
                     ->  Hash
                           ->  Seq Scan on pgbench_branches b
(9 rows)
 
rhaas=# select count(*) from pgbench_accounts a, pgbench_branches b where a.bid = b.bid;
  count  
----------
 10000000
(1 row)

Time: 548.950 ms

That's a very nice speedup and, as you can see from the EXPLAIN output, we succeed in partially parallelizing both the join and the aggregate.  In neither case can we do so completely.  For the join, the scan of pgbench_accounts is fully parallel, but each worker must separately scan pgbench_branches and build its only copy of the hash table.  For the aggregate, each worker can aggregate the rows it sees, but that produces N separate counts; a "finalize aggregate" stage is required to combine those into the final result.  This would be more significant if GROUP BY were present, since we might end up with a separate result in each worker for each group, which would afterwards have to be combined in the leader.

More broadly, some joins and some aggregates cannot yet be done in parallel at all.  We cannot perform a merge join in parallel at all, and not all aggregates currently support parallelism either (though many of those restrictions may be lifted before 9.6 ships).  Nonetheless, the ability to do joins and especially aggregates in parallel is very powerful, and I think many users will benefit greatly from it.  I'd like to thank David Rowley for his leading role in taking the parallel aggregate work forward - it is a great addition to PostgreSQL 9.6!