Monday, March 21, 2016

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!

8 comments:

  1. Great work so far. Parallelizing is a big step for PG.
    Thanks!

    ReplyDelete
  2. This is great work!

    I have a question regarding this. From what I understand from the code, a requirement for parallel aggregate functions is that they have a combine function.

    Currently a lot of builtin aggregate functions don't have a combine function, even where such a function would be simple (I assume because it is not needed when aggregating in a single thread). For example average or bigint->numeric sum.

    Is it planned to do work to introduce such combine functions for at least the easy ones?

    ReplyDelete
  3. Hi! It is very good news!
    Does this feature support parallel scan on CTE result?

    ReplyDelete
  4. Hi Robert,
    Thanks for your work. Can the PostGIS extension make use of this feature?

    Thanks,

    ReplyDelete