How Do I Improve the Efficiency when the Entire Table Is Associated with UPDATE?

Answer: The efficiency can be improved by eliminating the subplans. There are two update modes. In the first mode, a subplan exists in the plan. In the second mode, the subplan is eliminated, and the modification speed is greatly increased.

• Perform pre-operations: creating tables and inserting data.

  -- Create a table.
  gaussdb=# CREATE TABLE t1(id int, name varchar(50));
  gaussdb=# CREATE TABLE t2(id int, name varchar(50));
  
  -- Insert data.
  gaussdb=# INSERT INTO t1 (id)  VALUES (generate_series(1,10000));
  gaussdb=# INSERT INTO t2 VALUES (generate_series(1,30000),'name'||generate_series(1,30000));

• Method 1:

  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)

• Method 2:

  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)

• Drop a table.

  gaussdb=# DROP TABLE t1,t2;