Create Tables and Refresh Views in Parallel

SynxDB supports using the CREATE TABLE AS statement to create Append-Optimized (AO) tables and Append-Optimized Column Oriented (AOCO) tables in parallel, and also supports parallel refreshing of materialized views based on such tables, thereby accelerating table creation and materialized view refreshing.

To use this parallel functionality, you need to set the system parameter enable_parallel to ON first. This parameter defaults to off and can be set within a session using SET, without restarting the database.

What determines the degree of parallelism

Once enable_parallel is on, the number of parallel workers actually started on each segment is the smaller of the following two values:

The parallel_workers storage option of the table, specified with WITH at table creation time.
The configuration parameter max_parallel_workers_per_gather, whose default value is 2.

Raising parallel_workers alone therefore does not increase the degree of parallelism; you also need to raise max_parallel_workers_per_gather.

parallel_workers is not required. A table without this option can still get a parallel plan, in which case the optimizer derives the degree of parallelism from the size of the table. Specifying the option sets an upper bound on the degree of parallelism for that table.

Usage examples

Create AO/AOCO tables in parallel

Create the base table t_p2, using WITH in the table creation statement to specify the upper bound on the degree of parallelism for the table.
```
CREATE TABLE t_p2(c1 INT, c2 INT) WITH (parallel_workers=2) DISTRIBUTED BY (c1);
```

Insert data into the table and collect statistics on table t_p2.

INSERT INTO t_p2 SELECT i, i+1 FROM generate_series(1, 10000000) i;
ANALYZE t_p2;

Enable parallel processing.
```
SET enable_parallel = ON;
SET optimizer = OFF;
```
Note

Parallelism does not depend on disabling GPORCA; both optimizers can produce parallel plans. GPORCA is disabled here only to keep the execution plan output below concise.

Use CREATE TABLE AS to create a table based on t_p2. Parallel operators appear in the execution plan, indicating that the table is created in parallel.

Create an AO table:

EXPLAIN(COSTS OFF) CREATE TABLE ctas_ao USING ao_row AS SELECT SUM(a.c2) AS c2, AVG(b.c1) AS c1 FROM t_p2 a JOIN t_p2 b ON a.c1 = b.c1 DISTRIBUTED BY (c2);

                           QUERY PLAN
-----------------------------------------------------------------
 Redistribute Motion 1:4  (slice1; segments: 1)
   Hash Key: (sum(a.c2))
   ->  Finalize Aggregate
         ->  Gather Motion 8:1  (slice2; segments: 8)
               ->  Partial Aggregate
                     ->  Parallel Hash Join
                           Hash Cond: (a.c1 = b.c1)
                           ->  Parallel Seq Scan on t_p2 a
                           ->  Parallel Hash
                                 ->  Parallel Seq Scan on t_p2 b
 Optimizer: Postgres query optimizer
(11 rows)

Create an AOCO table:

EXPLAIN(COSTS OFF) CREATE TABLE ctas_aoco USING ao_column AS SELECT SUM(a.c2) AS c2, AVG(b.c1) AS c1 FROM t_p2 a JOIN t_p2 b ON a.c1 = b.c1 DISTRIBUTED BY (c2);

                           QUERY PLAN
-----------------------------------------------------------------
 Redistribute Motion 1:4  (slice1; segments: 1)
   Hash Key: (sum(a.c2))
   ->  Finalize Aggregate
         ->  Gather Motion 8:1  (slice2; segments: 8)
               ->  Partial Aggregate
                     ->  Parallel Hash Join
                           Hash Cond: (a.c1 = b.c1)
                           ->  Parallel Seq Scan on t_p2 a
                           ->  Parallel Hash
                                 ->  Parallel Seq Scan on t_p2 b
 Optimizer: Postgres query optimizer
(11 rows)

Refresh materialized views in parallel

Create the base table t_p, using WITH in the table creation statement to specify the upper bound on the degree of parallelism for the table.
```
CREATE TABLE t_p(c1 INT, c2 INT) WITH (parallel_workers=8) DISTRIBUTED BY (c1);
```

Insert data into the table and collect statistics on table t_p.

INSERT INTO t_p SELECT i, i+1 FROM generate_series(1, 10000000) i;
ANALYZE t_p;

Create the materialized view matv, whose content is an aggregated query result derived from table t_p. Use USING to specify the storage the materialized view uses. Run one of the following two statements, not both.

Row storage (AO Row):

CREATE MATERIALIZED VIEW matv USING ao_row AS SELECT SUM(a.c2) AS c2, AVG(b.c1) AS c1 FROM t_p a JOIN t_p b ON a.c1 = b.c1 WITH NO DATA DISTRIBUTED BY (c2);

Column storage (AO Column):

CREATE MATERIALIZED VIEW matv USING ao_column AS SELECT SUM(a.c2) AS c2, AVG(b.c1) AS c1 FROM t_p a JOIN t_p b ON a.c1 = b.c1 WITH NO DATA DISTRIBUTED BY (c2);

Enable parallel processing and raise max_parallel_workers_per_gather to a value that matches the parallel_workers specified at table creation time.
```
SET enable_parallel = ON;
SET max_parallel_workers_per_gather = 4;
SET optimizer = OFF;
```
Refresh the materialized view.
```
REFRESH MATERIALIZED VIEW matv;
```

Performance improvement demonstration

The table below shows one set of timings for a self-join of a 10-million-row base table followed by an aggregation, for reference. The actual speedup depends on data volume, query complexity, and the degree of parallelism.

Operation	Non-parallel	Parallel	Speedup
Refresh AO table materialized view	2270 ms	948 ms	2.4×
Refresh AOCO table materialized view	2050 ms	995 ms	2.1×
CTAS create AO table	2309 ms	1630 ms	1.4×
CTAS create AOCO table	2042 ms	1489 ms	1.4×