Enable parallel SELECT for "INSERT INTO ... SELECT ...".
Amit Kapila <akapila@postgresql.org>
Enable parallel SELECT for "INSERT INTO ... SELECT ...". Parallel SELECT can't be utilized for INSERT in the following cases: - INSERT statement uses the ON CONFLICT DO UPDATE clause - Target table has a parallel-unsafe: trigger, index expression or predicate, column default expression or check constraint - Target table has a parallel-unsafe domain constraint on any column - Target table is a partitioned table with a parallel-unsafe partition key expression or support function The planner is updated to perform additional parallel-safety checks for the cases listed above, for determining whether it is safe to run INSERT in parallel-mode with an underlying parallel SELECT. The planner will consider using parallel SELECT for "INSERT INTO ... SELECT ...", provided nothing unsafe is found from the additional parallel-safety checks, or from the existing parallel-safety checks for SELECT. While checking parallel-safety, we need to check it for all the partitions on the table which can be costly especially when we decide not to use a parallel plan. So, in a separate patch, we will introduce a GUC and or a reloption to enable/disable parallelism for Insert statements. Prior to entering parallel-mode for the execution of INSERT with parallel SELECT, a TransactionId is acquired and assigned to the current transaction state. This is necessary to prevent the INSERT from attempting to assign the TransactionId whilst in parallel-mode, which is not allowed. This approach has a disadvantage in that if the underlying SELECT does not return any rows, then the TransactionId is not used, however that shouldn't happen in practice in many cases. Author: Greg Nancarrow, Amit Langote, Amit Kapila Reviewed-by: Amit Langote, Hou Zhijie, Takayuki Tsunakawa, Antonin Houska, Bharath Rupireddy, Dilip Kumar, Vignesh C, Zhihong Yu, Amit Kapila Tested-by: Tang, Haiying Discussion: https://postgr.es/m/CAJcOf-cXnB5cnMKqWEp2E2z7Mvcd04iLVmV=qpFJrR3AcrTS3g@mail.gmail.com Discussion: https://postgr.es/m/CAJcOf-fAdj=nDKMsRhQzndm-O13NY4dL6xGcEvdX5Xvbbi0V7g@mail.gmail.com
Files
| Path | Change | +/− |
|---|---|---|
| doc/src/sgml/parallel.sgml | modified | +3 −1 |
| src/backend/access/transam/xact.c | modified | +26 −0 |
| src/backend/executor/execMain.c | modified | +3 −0 |
| src/backend/nodes/copyfuncs.c | modified | +1 −0 |
| src/backend/nodes/outfuncs.c | modified | +2 −0 |
| src/backend/nodes/readfuncs.c | modified | +1 −0 |
| src/backend/optimizer/plan/planner.c | modified | +26 −11 |
| src/backend/optimizer/util/clauses.c | modified | +543 −7 |
| src/backend/utils/cache/plancache.c | modified | +32 −1 |
| src/include/access/xact.h | modified | +15 −0 |
| src/include/nodes/pathnodes.h | modified | +2 −0 |
| src/include/nodes/plannodes.h | modified | +2 −0 |
| src/include/optimizer/clauses.h | modified | +2 −1 |
| src/test/regress/expected/insert_parallel.out | added | +536 −0 |
| src/test/regress/parallel_schedule | modified | +1 −0 |
| src/test/regress/serial_schedule | modified | +1 −0 |
| src/test/regress/sql/insert_parallel.sql | added | +335 −0 |
Documentation touched
Discussion
- Bug in query rewriter - hasModifyingCTE not getting set 14 messages · 2021-02-06 → 2021-09-08
- Parallel INSERT (INTO ... SELECT ...) 363 messages · 2020-09-22 → 2021-04-01