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:

  -- Part of the execution plan in method 1 is as follows:
  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 | ->  Streaming (type: GATHER)                  | 36683.710             |         0 |      1 | 70KB          |         |      14 | 325.34
    2 |    ->  Update on t1                           | [26777.011,36611.150] |     10000 |     30 | [448KB,448KB] |         |      14 | 325.21
    3 |       ->  Seq Scan on t1                      | [26599.153,36335.775] |     10000 |     30 | [56KB,56KB]   |         |      14 | 267.20
    4 |          ->  Result  [3, SubPlan 1]           | [26554.043,36252.543] |     10000 |     90 | [5KB,5KB]     |         |      47 | 18.08
    5 |             ->  Materialize                   | [8166.443,11354.492]  | 300090000 |     90 | [2MB,2MB]     | [31,31] |      47 | 18.08
    6 |                ->  Streaming(type: BROADCAST) | [18.987,33.671]       |     90000 |     90 | [69KB,69KB]   |         |      47 | 17.93
    7 |                   ->  Seq Scan on t2          | [3.000,14.795]        |     30000 |     30 | [53KB,53KB]   |         |      47 | 14.14
  (7 rows)
  
   Predicate Information (identified by plan id) 
  -----------------------------------------------
     4 --Result
           Filter: (t2.id = t1.id)
  (2 rows)
  ......
   Coordinator executor start time: 0.181 ms
   Coordinator executor run time: 36683.738 ms
   Coordinator executor end time: 0.065 ms
   Planner runtime: 0.382 ms
   Plan size: 4831 byte
   Query Id: 72339069017485048
   Total runtime: 36684.016 ms
  (10 rows)

• Method 2:

  -- Part of the execution plan in method 2 is as follows:
  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 | ->  Streaming (type: GATHER)   | 68.460          |      0 |      1 | 70KB          |         |      63 | 70.73
    2 |    ->  Update on t1            | [26.512,35.530] |  10000 |     30 | [448KB,448KB] |         |      63 | 70.60
    3 |       ->  Hash Join (4,5)      | [9.789,12.977]  |  10000 |     30 | [11KB,11KB]   |         |      63 | 28.59
    4 |          ->  Seq Scan on t1    | [1.474,1.901]   |  10000 |     30 | [54KB,54KB]   |         |      14 | 14.14
    5 |          ->  Hash              | [5.959,7.931]   |  30000 |     29 | [806KB,838KB] | [35,35] |      53 | 14.14
    6 |             ->  Seq Scan on t2 | [3.068,4.069]   |  30000 |     30 | [54KB,54KB]   |         |      53 | 14.14
  (6 rows)
  
   Predicate Information (identified by plan id) 
  -----------------------------------------------
     3 --Hash Join (4,5)
           Hash Cond: (t1.id = t2.id)
  (2 rows)
  ......
   Coordinator executor start time: 0.241 ms
   Coordinator executor run time: 68.477 ms
   Coordinator executor end time: 0.034 ms
   Planner runtime: 0.462 ms
   Plan size: 4427 byte
   Query Id: 72339069017486440
   Total runtime: 68.785 ms
  (10 rows)

• Drop a table.

  gaussdb=# DROP TABLE t1,t2;