摘要：本文就針對因USING子句的書寫方式可能導致MERGE INTO陳述句的執行不下推的場景，對USING子句的SQL陳述句進行改寫一遍，整個SQL陳述句可以下推，
本文分享自華為云社區《GaussDB(DWS)運維 -- values子句做MERGE資料源導致SQL執行不下推的改寫方案》，作者： 譡里個檔，
現網做實時接入的時候，有的時候會使用MERGE INTO陳述句實作類似UPSERT的功能，這種場景下MERGE INTO陳述句的USING部分的資料位VALUES子句，為了后續的SQL陳述句中描述方便，需要對VALUES子句的輸出命名別名，USING子句的書寫方式可能導致MERGE INTO陳述句的執行不下推，本文就針對因此導致的不下推的場景，對USING子句的SQL陳述句進行改寫一遍，整個SQL陳述句可以下推，
預置條件
CREATE TABLE t1(name text, id INT) DISTRIBUTE BY HASH(id);
原始陳述句
MERGE INTO t1 USING ( SELECT * FROM (VALUES ('json', 1), ('sam', 2)) AS val(name, id) ) tmp ON (t1.id = tmp.id) WHEN MATCHED THEN UPDATE SET t1.name = tmp.name WHEN NOT MATCHED THEN INSERT (name, id) VALUES(tmp.name, tmp.id);
SQL陳述句不下推，導致執行低效
postgres=# EXPLAIN VERBOSE MERGE INTO t1 USING ( postgres(# SELECT * postgres(# FROM (VALUES ('json', 1), ('sam', 2)) AS val(name, id) postgres(# ) tmp ON (t1.id = tmp.id) postgres-# WHEN MATCHED THEN postgres-# UPDATE SET t1.name = tmp.name postgres-# WHEN NOT MATCHED THEN postgres-# INSERT (name, id) VALUES(tmp.name, tmp.id); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------- id | operation | E-rows | E-distinct | E-width | E-costs ----+-------------------------------------------------------+--------+------------+---------+--------- 1 | -> Merge on public.t1 | 2 | | 54 | 0.08 2 | -> Nested Loop Left Join (3, 4) | 2 | | 54 | 0.08 3 | -> Values Scan on "*VALUES*" | 2 | | 36 | 0.03 4 | -> Data Node Scan on t1 "_REMOTE_TABLE_QUERY_" | 2 | | 18 | 0.00 SQL Diagnostic Information ------------------------------------------------------------ SQL is not plan-shipping reason: Type of Record in non-real table can not be shipped Predicate Information (identified by plan id) ------------------------------------------------- 1 --Merge on public.t1 Node expr: : $10 2 --Nested Loop Left Join (3, 4) Join Filter: (t1.id = "*VALUES*".column2) Targetlist Information (identified by plan id) ----------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 --Merge on public.t1 Node/s: All datanodes Remote query: UPDATE ONLY public.t1 SET name = $7, id = $8 WHERE t1.ctid = $5 AND t1.xc_node_id = $6 Node/s: All datanodes Remote query: INSERT INTO public.t1 (name, id) VALUES ($9, $10) 2 --Nested Loop Left Join (3, 4) Output: "*VALUES*".column1, "*VALUES*".column2, t1.name, t1.id, t1.ctid, t1.xc_node_id, "*VALUES*".column1, t1.id, "*VALUES*".column1, "*VALUES*".column2 3 --Values Scan on "*VALUES*" Output: "*VALUES*".column1, "*VALUES*".column2 4 --Data Node Scan on t1 "_REMOTE_TABLE_QUERY_" Output: t1.name, t1.id, t1.ctid, t1.xc_node_id Node/s: All datanodes Remote query: SELECT name, id, ctid, xc_node_id FROM ONLY public.t1 WHERE true ====== Query Summary ===== -------------------------- Parser runtime: 0.079 ms Planner runtime: 1.392 ms Unique SQL Id: 1657855173 (40 rows)
改寫方案
MERGE INTO t1 USING ( WITH val(name, id) AS( VALUES ('json', 1), ('sam', 2) ) SELECT * FROM val ) tmp ON (t1.id = tmp.id) WHEN MATCHED THEN UPDATE SET t1.name = tmp.name WHEN NOT MATCHED THEN INSERT (name, id) VALUES(tmp.name, tmp.id);
改寫后下推
postgres=# EXPLAIN VERBOSE MERGE INTO t1 USING ( postgres(# WITH val(name, id) AS( postgres(# VALUES ('json', 1), ('sam', 2) postgres(# ) postgres(# SELECT * FROM val postgres(# ) tmp ON (t1.id = tmp.id) postgres-# WHEN MATCHED THEN postgres-# UPDATE SET t1.name = tmp.name postgres-# WHEN NOT MATCHED THEN postgres-# INSERT (name, id) VALUES(tmp.name, tmp.id); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------ id | operation | E-rows | E-distinct | E-memory | E-width | E-costs ----+----------------------------------------------+--------+------------+----------+---------+--------- 1 | -> Streaming (type: GATHER) | 1 | | | 54 | 1.56 2 | -> Merge on public.t1 | 2 | | | 54 | 1.15 3 | -> Streaming(type: REDISTRIBUTE) | 2 | | 2MB | 54 | 1.15 4 | -> Nested Loop Left Join (5, 7) | 2 | | 1MB | 54 | 1.11 5 | -> Subquery Scan on tmp | 2 | | 1MB | 36 | 0.08 6 | -> Values Scan on "*VALUES*" | 24 | | 1MB | 36 | 0.03 7 | -> Seq Scan on public.t1 | 2 | | 1MB | 18 | 1.01 Predicate Information (identified by plan id) --------------------------------------------- 4 --Nested Loop Left Join (5, 7) Join Filter: (t1.id = tmp.id) 5 --Subquery Scan on tmp Filter: (Hash By tmp.id) Targetlist Information (identified by plan id) ---------------------------------------------------------------------------------------------------------------------------------------------------- 1 --Streaming (type: GATHER) Node/s: All datanodes 3 --Streaming(type: REDISTRIBUTE) Output: tmp.name, tmp.id, t1.name, t1.id, t1.ctid, t1.xc_node_id, tmp.name, tmp.id, (CASE WHEN (t1.ctid IS NULL) THEN tmp.id ELSE t1.id END) Distribute Key: (CASE WHEN (t1.ctid IS NULL) THEN tmp.id ELSE t1.id END) Spawn on: All datanodes Consumer Nodes: All datanodes 4 --Nested Loop Left Join (5, 7) Output: tmp.name, tmp.id, t1.name, t1.id, t1.ctid, t1.xc_node_id, tmp.name, tmp.id, CASE WHEN (t1.ctid IS NULL) THEN tmp.id ELSE t1.id END 5 --Subquery Scan on tmp Output: tmp.name, tmp.id 6 --Values Scan on "*VALUES*" Output: "*VALUES*".column1, "*VALUES*".column2 7 --Seq Scan on public.t1 Output: t1.name, t1.id, t1.ctid, t1.xc_node_id Distribute Key: t1.id ====== Query Summary ===== ------------------------------- System available mem: 3112960KB Query Max mem: 3112960KB Query estimated mem: 6336KB Parser runtime: 0.107 ms Planner runtime: 1.185 ms Unique SQL Id: 780461632 (44 rows)
點擊關注，第一時間了解華為云新鮮技術~
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/547719.html
標籤：其他
上一篇：什么是主資料？（轉載）