PolarDB:Parallel append

Overview

Executing queries in parallel is an essential feature in most modern database systems, considering the multitude of CPU cores in computing systems. This is also the case in PolarDB for PostgreSQL (Compatible with Oracle), which can process queries on partitions in a paralleled manner to improve perform.

Usage

In PolarDB for PostgreSQL (Compatible with Oracle), the parallel append feature is enabled by default.

The parallel execution can be performed across partitions, within a partition, or in a hybrid manner.

Each of the modes have their cost model, and the optimizer will select the one that is least costly.

Parallel execution across partitions

In this mode, each worker handles a partition, and they work in parallel.

Example:

EXPLAIN (COSTS OFF) select * from prt1;
                  QUERY PLAN                   
-----------------------------------------------
 Gather
   Workers Planned: 6
   ->  Parallel Append
         ->  Seq Scan on prt1_p5
         ->  Seq Scan on prt1_default
         ->  Seq Scan on prt1_p4
         ->  Seq Scan on prt1_p1
         ->  Seq Scan on prt1_p2
         ->  Seq Scan on prt1_p3
(9 rows)

There are six partitions of the prt1 partitioned table, which are prt1_p1, prt1_p2, prt1_p3, prt1_p4, prt1_p5, and prt1_default. Six workers are used in the execution, each of which works on a single partition. The Parallel Append operator in the query plan is the indicator of this parallel execution mode.

Parallel execution within a partition

In this mode, multiple workers simultaneously process the same partition before moving on to the next partition, until the entire partitioned table is processed.

EXPLAIN (COSTS OFF) select * from prt1;
                  QUERY PLAN                   
-----------------------------------------------
 Gather
   Workers Planned: 6
   ->  Append
         ->  Parallel Seq Scan on prt1_p5
         ->  Parallel Seq Scan on prt1_default
         ->  Parallel Seq Scan on prt1_p4
         ->  Parallel Seq Scan on prt1_p1
         ->  Parallel Seq Scan on prt1_p2
         ->  Parallel Seq Scan on prt1_p3
(9 rows)

All six workers work together on one of the prt1_p1, prt1_p2, prt1_p3, prt1_p4, prt1_p5, and prt1_default partitions before moving on to the next.

Hybrid parallel execution

In hybrid mode, the system handles the execution in parallel both within and across partitions.

EXPLAIN (COSTS OFF) select * from prt1;
                  QUERY PLAN                   
-----------------------------------------------
 Gather
   Workers Planned: 8
   ->  Parallel Append
         ->  Parallel Seq Scan on prt1_p5
         ->  Parallel Seq Scan on prt1_default
         ->  Parallel Seq Scan on prt1_p4
         ->  Parallel Seq Scan on prt1_p1
         ->  Parallel Seq Scan on prt1_p2
         ->  Parallel Seq Scan on prt1_p3
(9 rows)

In this example, eight workers are used.