Skip to content

6 Enhancement of Parallel Query

Source chapter 3.4 for PostgreSQL 10.
Beta 1Source pages: PDF 40-43Original PDF on GitHub

PDF page 40

In PostgreSQL 10, the processing that can use parallel queries has been expanded.

3.4.1 PREPARE / EXECUTE statement

Parallel queries can now be executed even in search processing using PREPARE and EXECUTE statements. Parallel processing was not executed in that process in PostgreSQL 9.6.

Example 44 Parallel query with PREPARE and EXECUTE statements
postgres=> EXPLAIN SELECT COUNT(*) FROM large1 ;
                                       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 large1  (cost=0.00..9572.67 rows=416667
width=0)
(5 rows)
postgres=> PREPARE p1 AS SELECT COUNT(*) FROM large1 ;
PREPARE
postgres=> EXPLAIN EXECUTE p1 ;
                                       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 large1  (cost=0.00..9572.67 rows=416667
width=0)
(5 rows)

PDF page 41

3.4.2 Parallel Index Scan

Parallel queries are now also used for Index Scan and Index Only Scan.

Example 45 Parallel Index Scan
postgres=> EXPLAIN SELECT * FROM large1 WHERE c1 BETWEEN 10000 AND 20000000 ;
                               QUERY PLAN
-------------------------------------------------------------------------------
 Gather  (cost=0.43..369912.83 rows=7917410 width=12)
   Workers Planned: 2
   ->  Parallel Index Scan using idx1_large1 on large1
(cost=0.43..369912.83 rows=3298921 width=12)
         Index Cond: ((c1 >= '10000'::numeric) AND (c1 <= '20000000'::numeric))
(4 rows)
Example 46 Parallel Index Only Scan
postgres=>  EXPLAIN SELECT COUNT(c1) FROM large1 WHERE c1 BETWEEN 1000 AND
10000000 ;
                             QUERY PLAN
-------------------------------------------------------------------------------
Finalize Aggregate  (cost=316802.38..316802.39 rows=1 width=8)
   ->  Gather  (cost=316802.17..316802.38 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=315802.17..315802.18 rows=1 width=8)
               ->  Parallel Index Only Scan using idx1_large1 on
large1  (cost=0.43..305386.50 rows=4166267 width=6)
                     Index
Cond:
((c1
>=
'1000'::numeric)
AND
(c1
<=
'10000000'::numeric))
(6 rows)

PDF page 42

3.4.3 SubPlan

Parallel queries can now be used with SELECT statements that has SubPlan.

Example 47 SubPlan and parallel query
postgres=>  EXPLAIN SELECT * FROM large1 l1 WHERE l1.c1 NOT IN (SELECT l2.c1
FROM large2 l2 WHERE l2.c1 in (1000,2000,3000)) ;
                                     QUERY PLAN
-------------------------------------------------------------------------------
 Seq Scan on large1 l1  (cost=23269.95..59080.95 rows=1000000 width=11)
   Filter: (NOT (hashed SubPlan 1))
   SubPlan 1
     ->  Gather  (cost=1000.00..23269.93 rows=6 width=6)
           Workers Planned: 2
           ->  Parallel Seq Scan on large2 l2  (cost=0.00..22269.33 rows=2
width=6)
                 Filter: (c1 = ANY ('{1000,2000,3000}'::numeric[]))
(7 rows)

3.4.4 Parallel Merge Join / Gather Merge

Parallel queries are now available even when Merge Join is chosen. Gather Merge which gathers results while Merge by parallel processing is now available.

PDF page 43

Example 48 Parallel Merge Join
postgres=> EXPLAIN SELECT COUNT(*) FROM large1 INNER JOIN large2 ON large1.c1 =
large2.c1 ;
                                              QUERY PLAN
-------------------------------------------------------------------------------
Finalize Aggregate  (cost=447792.07..447792.08 rows=1 width=8)
   ->  Gather  (cost=447791.86..447792.07 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=446791.86..446791.87 rows=1 width=8)
               ->  Merge Join  (cost=407305.94..442727.96 rows=1625561 width=0)
                     Merge Cond: (large2.c1 = large1.c1)
                     ->  Sort  (cost=112492.52..114575.86 rows=833333 width=6)
                           Sort Key: large2.c1
                           ->  Parallel Seq Scan on large2  (cost=0.00..19144.33
 rows=833333 width=6)
                     ->  Materialize  (cost=294813.42..304813.25 rows=1999965 wi
…

3.4.5 Parallel bitmap heap scan

Bitmap Heap Scan has supported parallel query.

Example 49 Parallel Bitmap Heap Scan
postgres=> EXPLAIN SELECT COUNT(c1) FROM large1 WHERE c1 BETWEEN 100000 AND 200000 ;
                                             QUERY PLAN
-------------------------------------------------------------------------------
 Finalize Aggregate  (cost=18500.74..18500.75 rows=1 width=8)
   ->  Gather  (cost=18500.52..18500.73 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=17500.52..17500.53 rows=1 width=8)
               ->  Parallel Bitmap Heap Scan on large1  …
                     Recheck Cond: ((c1 >= '100000'::numeric) …
                     ->  Bitmap Index Scan on idx1_large1 …
                           Index Cond: ((c1 >= '100000'::numeric) …
(8 rows)