PolarDB:Parallel append

Overview

Parallel querying is an essential capability for modern databases. PolarDB for PostgreSQL provides better parallel querying performance on partitioned tables than common tables.

Usage notes

By default, the parallel append feature is enabled for PolarDB for PostgreSQL clusters.

Parallel append can be inter-partition parallel append, intra-partition parallel append, and hybrid parallel append.

Each parallel append method has its own cost model. The optimizer can select the optimal one.

Inter-partition parallel append

In inter-partition parallel append, each worker queries one partition and multiple workers can query the entire partitioned table 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)

In the preceding example, the prt1 partitioned table contains the prt1_p1, prt1_p2, prt1_p3, prt1_p4, prt1_p5, and prt1_default partitions. Six parallel workers are used for the table. Each worker queries one partition. An operator called Parallel Append is used.

Intra-partition parallel append

In intra-partition parallel append, parallel querying is performed within each partition, but sequential querying is performed between partitions.

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)

In the preceding example, six workers query the prt1_p1, prt1_p2, prt1_p3, prt1_p4, prt1_p5, and prt1_default partitions. Parallel querying is implemented for the entire table, although sequential querying is performed between partitions.

Hybrid parallel append

In hybrid parallel append, parallel querying is performed both within each partition and between partitions. This delivers the highest degree of parallelism.

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 the preceding example, eight workers are used to implement parallel querying both within each partition and between partitions.