# Enhancement of Parallel Query

> Source chapter 3.4 for PostgreSQL 10.

---

LLMS index: [llms.txt](/en/llms.txt)

---

<div class="feature-source-note"><span class="feature-edition-badge">Beta 1</span><span><strong>Source pages:</strong> PDF 40-43</span><span><a href="https://github.com/nori-shinoda/documents/blob/1773200c5362476c4a549222b7d17fa91f546cae/postgresql10_beta1_new_features_en_20170522-1.pdf">Original PDF on GitHub</a></span></div>

<p class="feature-page-marker" id="pgfeat-10-parallel-pdf-page-40">PDF page 40</p>

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

#### 3.4.1 PREPARE / EXECUTE statement {#pg10-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

```text
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)
```

<p class="feature-page-marker" id="pgfeat-10-parallel-pdf-page-41">PDF page 41</p>

#### 3.4.2 Parallel Index Scan {#pg10-3-4-2-parallel-index-scan}

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

**Example 45.** Parallel Index Scan

```text
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

```text
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)
```

<p class="feature-page-marker" id="pgfeat-10-parallel-pdf-page-42">PDF page 42</p>

#### 3.4.3 SubPlan {#pg10-3-4-3-subplan}

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

**Example 47.** SubPlan and parallel query

```text
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 {#pg10-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.

<p class="feature-page-marker" id="pgfeat-10-parallel-pdf-page-43">PDF page 43</p>

**Example 48.** Parallel Merge Join

```text
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 {#pg10-3-4-5-parallel-bitmap-heap-scan}

Bitmap Heap Scan has supported parallel query.

**Example 49.** Parallel Bitmap Heap Scan

```text
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)
```