更新时间:2025-05-29 GMT+08:00
全表关联UPDATE时,如何提升效率?
答:可以通过消除SubPlan的方式提升效率。如下两种UPDATE的方式,方式一的执行计划中存在一个SubPlan,方式二消除了这个SubPlan后修改速度大大增加。
- 前置操作,建表和插入数据。
--建表。 gaussdb=# CREATE TABLE t1(id int, name varchar(50)); gaussdb=# CREATE TABLE t2(id int, name varchar(50)); --插入数据。 gaussdb=# INSERT INTO t1 (id) VALUES (generate_series(1,10000)); gaussdb=# INSERT INTO t2 VALUES (generate_series(1,30000),'name'||generate_series(1,30000));
- 方式一:
gaussdb=# EXPLAIN ANALYZE UPDATE t1 SET name = (SELECT name FROM t2 WHERE id = t1.id); id | operation | A-time | A-rows | E-rows | Peak Memory | A-width | E-width | E-costs ----+------------------------------------------+-----------+--------+--------+-------------+---------+---------+-------------------- 1 | -> Update on t1 | 87650.334 | 10000 | 10000 | 7KB | | 10 | 0.000..4980139.000 2 | -> Seq Scan on t1 | 86720.633 | 10000 | 10000 | 59KB | | 10 | 0.000..4980139.000 3 | -> Seq Scan on t2 [2, SubPlan 1] | 86533.365 | 10000 | 1 | 58KB | | 10 | 0.000..498.000 (3 rows) Predicate Information (identified by plan id) ----------------------------------------------- 3 --Seq Scan on t2 Filter: (id = t1.id) Rows Removed by Filter: 299990000 (3 rows) ====== Query Summary ===== ------------------------------------------ Datanode executor start time: 0.118 ms Datanode executor run time: 87650.360 ms Datanode executor end time: 0.014 ms Planner runtime: 0.424 ms Query Id: 1125899913355073 Total runtime: 87650.520 ms (6 rows)
- 方式二:
gaussdb=# EXPLAIN ANALYZE UPDATE t1 SET t1.name = t2.name FROM t2 WHERE t1.id = t2.id; id | operation | A-time | A-rows | E-rows | Peak Memory | A-width | E-width | E-costs ----+-----------------------------+---------+--------+--------+-------------+---------+---------+------------------ 1 | -> Update on t1 | 133.355 | 10000 | 10000 | 7KB | | 26 | 264.000..899.500 2 | -> Hash Join (3,4) | 41.142 | 10000 | 10000 | 12KB | | 26 | 264.000..899.500 3 | -> Seq Scan on t2 | 11.398 | 30000 | 30000 | 58KB | | 20 | 0.000..423.000 4 | -> Hash | 6.630 | 10000 | 10000 | 745KB | | 10 | 139.000..139.000 5 | -> Seq Scan on t1 | 3.382 | 10000 | 10000 | 57KB | | 10 | 0.000..139.000 (5 rows) Predicate Information (identified by plan id) ----------------------------------------------- 2 --Hash Join (3,4) Hash Cond: (t2.id = t1.id) (2 rows) Memory Information (identified by plan id) -------------------------------------------------- Buckets: 32768 Batches: 1 Memory Usage: 411kB (1 row) ====== Query Summary ===== ---------------------------------------- Datanode executor start time: 0.135 ms Datanode executor run time: 133.393 ms Datanode executor end time: 0.025 ms Planner runtime: 0.391 ms Query Id: 1125899913354385 Total runtime: 133.600 ms (6 rows)
- 删除表
gaussdb=# DROP TABLE t1,t2;
父主题: FAQ