Optimizing Performance of Importing Data to Partitions

Scenario

When data is inserted into a partitioned table, if the inserted data is of simple types such as constants, parameters, and expressions, the INSERT operator is automatically optimized (FastPath). You can determine whether the operator optimization is triggered based on the execution plan. When the operator optimization is triggered, the keyword FastPath is added before the INSERT plan.

Example

gaussdb=# CREATE TABLE fastpath_t1
(
    col1 int,
    col2 text
) 
PARTITION BY RANGE(col1)
(
    PARTITION p1 VALUES LESS THAN(10),
    PARTITION p2 VALUES LESS THAN(MAXVALUE)
);

-- Insert a constant and execute FastPath.
gaussdb=# EXPLAIN INSERT into fastpath_t1 values (0, 'test_insert');
                            QUERY PLAN                            
------------------------------------------------------------------
 FastPath Insert on fastpath_t1  (cost=0.00..0.01 rows=1 width=0)
   ->  Result  (cost=0.00..0.01 rows=1 width=0)
(2 rows)

-- Insert an expression with parameters or a simple expression and execute FastPath.
gaussdb=# prepare insert_t1 as insert into fastpath_t1 values($1 + 1 + $2, $2);
PREPARE
gaussdb=# explain execute insert_t1(10, '0');
                            QUERY PLAN                            
------------------------------------------------------------------
 FastPath Insert on fastpath_t1  (cost=0.00..0.02 rows=1 width=0)
   ->  Result  (cost=0.00..0.02 rows=1 width=0)
(2 rows)

-- Insert a subquery. FastPath cannot be executed. The standard executor is used. 
gaussdb=# create table test_1(col1 int, col3 text);
gaussdb=# explain insert into fastpath_t1 select * from test_1;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Insert on fastpath_t1  (cost=0.00..22.38 rows=1238 width=36)
   ->  Seq Scan on test_1  (cost=0.00..22.38 rows=1238 width=36)
(2 rows)

-- Cleanup example
gaussdb=# DROP TABLE fastpath_t1;
gaussdb=# DROP TABLE test_1;

Precautions and Constraints

1. FastPath can only be executed under the INSERT VALUES statement, and the data following the VALUES clause must be of the constant, parameter, or expression type.
2. FastPath can only be executed for row-store tables.
3. FastPath does not support triggers.
4. FastPath cannot be executed under the UPSERT statement.
5. In case of CPU resource bottleneck, the performance is improved. In the typical MetaERP data import scenario (16 cores and 256 GB memory), 37 columns are imported by using 64 concurrent threads, improving the performance by more than 30%.