文档首页/ 云数据库 GaussDB/ 开发指南(集中式_V2.0-8.x)/ FAQ/ 全表关联UPDATE时,如何提升效率?
更新时间: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;