Skip to content

3 Enhancements of Parallel Query

Source chapter 3.1 for PostgreSQL 11.
GASource pages: PDF 11-13Original PDF on GitHub

PDF page 11

In PostgreSQL 11, parallel queries can now be used in many situations.

3.1.1 Parallel Hash

Hash joins and hashing can now be processed in parallel. Parallel Hash or Parallel Hash Join is displayed in the execution plan as follows.

Example 4 Parallel Hash
postgres=>  EXPLAIN SELECT COUNT(*) FROM hash1 INNER JOIN hash2 ON
hash1.c1 = hash2.c1 ;
                                   QUERY PLAN
-----------------------------------------------------------------------
Finalize Aggregate  (cost=368663.94..368663.95 rows=1 width=8)
   ->  Gather  (cost=368663.73..368663.94 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=367663.73..367663.74 rows=1
width=8)
               ->  Parallel Hash Join  (cost=164082.00..357247.06
rows=4166667 width=0)
                     Hash Cond: (hash2.c1 = hash1.c1)
                     ->  Parallel Seq Scan on hash2  (cost=0.00..95722.40
rows=4166740 width=6)
                     ->  Parallel Hash  (cost=95721.67..95721.67
rows=4166667 width=6)
                           ->  Parallel Seq Scan on hash1
(cost=0.00..95721.67 rows=4166667 width=6)
(9 rows)

3.1.2 Parallel Append

Append processing can now be executed in parallel. Parallel Append is displayed in the execution plan.

PDF page 12

Example 5 Parallel Append
postgres=> EXPLAIN SELECT COUNT(*) FROM data1 UNION ALL SELECT COUNT(*) FROM
data2 ;
                                QUERY PLAN
----------------------------------------------------------------------------
Gather  (cost=180053.25..180054.25 rows=2 width=8)
   Workers Planned: 2
   ->  Parallel Append  (cost=179053.25..179054.05 rows=1 width=8)
         ->  Aggregate  (cost=179054.02..179054.04 rows=1 width=8)
               ->  Seq Scan on data1  (cost=0.00..154054.22 rows=9999922 width=0)
         ->  Aggregate  (cost=179053.25..179053.26 rows=1 width=8)
               ->  Seq Scan on data2  (cost=0.00..154053.60 rows=9999860 width=0)
(7 rows)

3.1.3 CREATE TABLE AS SELECT statement

The SELECT processing in CREATE TABLE AS SELECT statement can now be executed as a parallel query. It was serial processing on PostgreSQL 10.

Example 6 Parallelization of CREATE TABLE AS SELECT statement
postgres=> EXPLAIN CREATE TABLE para1 AS SELECT COUNT(*) FROM data1 ;
                                       QUERY PLAN
-----------------------------------------------------------------------
 Finalize Aggregate  (cost=11614.55..11614.56 rows=1 width=8)
   ->  Gather  (cost=11614.33..11614.54 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=10614.33..10614.34 rows=1 width=8)
               ->  Parallel Seq Scan on data1  (cost=0.00..9572.67
rows=416667 width=0)
(5 rows)

3.1.4 CREATE MATERIALIZED VIEW statement

The SELECT processing of CREATE MATERIALIZED VIEW statement now works as a parallel query. It was serial running on PostgreSQL 10.

PDF page 13

Example 7 Parallelization of CREATE MATERIALIZED VIEW statement
postgres=> EXPLAIN CREATE MATERIALIZED VIEW mv1 AS SELECT COUNT(*) FROM data1 ;
                                       QUERY PLAN
------------------------------------------------------------------------------
 Finalize Aggregate  (cost=11614.55..11614.56 rows=1 width=8)
   ->  Gather  (cost=11614.33..11614.54 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=10614.33..10614.34 rows=1 width=8)
               ->  Parallel Seq Scan on data1  (cost=0.00..9572.67 rows=416667
width=0)
(5 rows)

3.1.5 SELECT INTO statement

SELECT INTO statement now supports parallel queries.

Example 8 Parallelization of SELECT INTO statement
postgres=> EXPLAIN SELECT COUNT(*) INTO val FROM data1 ;
                                       QUERY PLAN
--------------------------------------------------------------------------
 Finalize Aggregate  (cost=11614.55..11614.56 rows=1 width=8)
   ->  Gather  (cost=11614.33..11614.54 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=10614.33..10614.34 rows=1 width=8)
               ->  Parallel Seq Scan on data1  (cost=0.00..9572.67
rows=416667 width=0)
(5 rows)

3.1.6 CREATE INDEX statement

BTree index creation processing can now be executed in parallel.