使用平行 SELECT 提升 INSERT SELECT 效能

對查詢的 INSERT INTO ... SELECTSELECT 部分使用平行執行作業，可提升 AlloyDB for PostgreSQL 查詢效能，尤其是分割資料表和複雜查詢。

資料庫會平行處理 SELECT 子計畫，並使用多個工作站程序來擷取資料。資料庫隨後會將擷取的資料插入目標資料表。這種分工方式有助於提升資料庫效能。

PostgreSQL 中的平行查詢方案運作方式

在 PostgreSQL 中，查詢最佳化工具可以為整個查詢或部分查詢建立平行方案。產生平行計畫時，系統會在計畫頂端新增 Gather 或 Gather Merge 節點。

這個節點會協調多個平行工作站程序。每個工作站都會執行部分工作，例如掃描部分資料表。接著，Gather 節點會收集所有工作站的結果，並傳遞至查詢的下一個階段，或傳回給用戶端。

舉例來說，下列查詢計畫顯示 Gather 節點，該節點計畫對資料表 t1 執行平行循序掃描時，使用五個工作站程序。

EXPLAIN SELECT * FROM t1;

                  QUERY PLAN
-------------------------------------------------------------
 Gather  (cost=0.00..143359.76 rows=9999878 width=60)
   Workers Planned: 5
   ->  Parallel Seq Scan on t1  (cost=0.00..143359.76 rows=1999976 width=60)
(3 rows)

INSERT...SELECT 陳述式的平行執行

AlloyDB 會使用多個 worker 程序平行處理 SELECT 陳述式，藉此提升 INSERT INTO ... SELECT 效能。這種分工方式有助於提升資料庫效能。查詢必須是平行安全，且不得有任何條件會阻止平行化。

許多資料庫作業都會使用 INSERT INTO ... SELECT 陳述式，在資料表中新增資料列。通常，陳述式的 SELECT 部分是查詢中最耗用資源的部分。

這項功能是由下列設定參數控管：

• enable_parallel_select_for_insert_select：啟用或停用這項功能。在 PostgreSQL 14 以上版本中，這個參數預設為開啟。
• enable_parallel_select_for_insert_select_into_part_table：啟用或停用可插入分區資料表的功能。在 PostgreSQL 16 以上版本中，這個參數預設為關閉。

查詢規劃工具必須考慮平行計畫，INSERT SELECT 陳述式也必須平行安全。也就是說，作業的任何部分都不得包含無法安全平行執行的函式或運算式。如果查詢的任何部分判定為平行不安全，最佳化工具就會回溯至非平行計畫。詳情請參閱「parallel-safety」。

禁止並行執行的條件

如果 INSERT SELECT 陳述式被視為不安全，查詢最佳化工具就不會使用平行計畫。下列情況會禁止平行化：

• 目標資料表是外部資料表。
• 查詢會使用可修改的一般資料表運算式 (CTE)，其中包含資料修改陳述式，例如 DELETE。
• 目標資料表上的索引使用平行不安全運算式或函式。
• 目標資料表上的資料欄具有預設值，但該值使用平行不安全函式。
• 目標資料表上的觸發程序使用並行不安全的運算式或函式。
• 目標資料表含有 DOMAIN 欄，且具有使用平行不安全函式的 CHECK 限制。
• 目標表格的 CHECK 限制包含平行不安全運算式或函式。
• 目標資料表是分區資料表，且 enable_parallel_select_for_insert_select_into_part_table 已停用。

事前準備

本文假設您已有 AlloyDB 資料庫。如要建立資料庫，請參閱「建立叢集及其主要執行個體」和「建立及管理資料庫」。

必要的角色

如要取得在工作階段層級變更資料庫標記所需的權限，請要求管理員在專案中授予您 AlloyDB 資料庫使用者 (roles/alloydb.databaseUser) 身分與存取權管理 (IAM) 角色。如要進一步瞭解如何授予角色，請參閱「管理專案、資料夾和機構的存取權」。

啟用並行執行參數

如要為分區目標資料表啟用平行執行，請按照下列步驟操作：

1. 前往 Google Cloud 控制台的「Clusters」(叢集) 頁面。

   前往叢集

2. 從清單中選取叢集。

3. 在導覽選單中，按一下「AlloyDB Studio」。

4. 使用資料庫名稱、使用者名稱和密碼登入 AlloyDB Studio。

5. 將 enable_parallel_select_for_insert_select_into_part_table GUC 設為 on。

   SET enable_parallel_select_for_insert_select_into_part_table = on;
   

   啟用 GUC 參數後，如果查詢判斷為並行安全，查詢規劃工具就會自動考量目標為分區資料表的 INSERT INTO ... SELECT 陳述式並行計畫。

   下列範例會產生 EXPLAIN 計畫，其中顯示 Gather 節點，表示 source_table 中的 SELECT 是平行執行。

   -- Create a partitioned table
   CREATE TABLE part_table (a INT, b TEXT) PARTITION BY RANGE (a);
   CREATE TABLE part_table_1 PARTITION OF part_table FOR VALUES FROM (MINVALUE) TO (1000);
   CREATE TABLE part_table_2 PARTITION OF part_table FOR VALUES FROM (1000) TO (MAXVALUE);
   
   -- Create a source table
   CREATE TABLE source_table (c1 INT, c2 TEXT);
   INSERT INTO source_table SELECT i, 'value_' || i FROM generate_series(1, 2000) i;
   ANALYZE source_table;
   
   -- Enable the feature
   SET enable_parallel_select_for_insert_select_into_part_table = on;
   -- Optional for parallelizing the query with a small amount of data
   SET parallel_setup_cost=0;
   SET parallel_tuple_cost=0;
   SET min_parallel_table_scan_size=0;
   
   -- Run the insert with a parallelizable select
   EXPLAIN INSERT INTO part_table SELECT * FROM source_table;
   

   這會傳回下列輸出內容：

   EXPLAIN (COSTS OFF) INSERT INTO part_table SELECT * FROM source_table;
                   QUERY PLAN                   
   -----------------------------------------------
   Insert on part_table
   ->  Gather
           Workers Planned: 2
           ->  Parallel Seq Scan on source_table
   (4 rows)
   

停用平行執行參數

如要停用參數，請按照下列步驟操作：

1. 前往 Google Cloud 控制台的「Clusters」(叢集) 頁面。

   前往叢集

2. 從清單中選取叢集。

3. 在導覽選單中，按一下「AlloyDB Studio」。

4. 使用資料庫名稱、使用者名稱和密碼登入 AlloyDB Studio。

5. 如要停用 enable_parallel_select_for_insert_select 參數，請執行下列 SQL 指令：

   SET enable_parallel_select_for_insert_select = OFF;
   

6. 如要停用 enable_parallel_select_for_insert_select_into_part_table 參數，請執行下列 SQL 指令：

   SET enable_parallel_select_for_insert_select_into_part_table = OFF;
   

驗證平行方案

您可以使用 EXPLAIN 指令，確認最佳化工具是否使用平行計畫。在查詢計畫中尋找 Gather 節點和 Workers Planned 或 Workers Launched 屬性。

標準資料表範例

在下列計畫範例中，系統會啟動六個工作程序，以執行平行雜湊聯結的 SELECT 陳述式。

EXPLAIN (ANALYZE)
INSERT INTO t1 SELECT t2.* FROM t1, t2 WHERE t1.c1 != t2.c1 AND t1.c2 = t2.c2;

                                 QUERY PLAN
------------------------------------------------------------------------------------
 Insert on t1  (cost=1209138.00..12801765.49 rows=0 width=0) (actual time=16812.677..19337.150 rows=0 loops=1)
   ->  Gather  (cost=1209138.00..12801765.49 rows=99995407 width=24) (actual time=16812.674..19337.144 rows=0 loops=1)
         Workers Planned: 6
         Workers Launched: 6
         ->  Parallel Hash Join  (...)
(...)

分區資料表範例

以下範例顯示，為已分割的資料表啟用這項功能後，計畫會顯示 Gather 節點，並為 SELECT 規劃四個工作站。

-- First, enable the feature for partitioned tables
SET enable_parallel_select_for_insert_select_into_part_table = ON;

-- Then, explain the query
EXPLAIN (COSTS OFF) INSERT INTO part_table SELECT * FROM source_table;
                  QUERY PLAN                   
-----------------------------------------------
 Insert on part_table
   ->  Gather
         Workers Planned: 2
         ->  Parallel Seq Scan on source_table
(4 rows)

後續步驟

• 進一步瞭解並行安全。