KingbaseES 两表关联Update的两种写法与性能

 熟悉oracle 的人都知道，对于两表的关联更新，其执行计划主要有 Filter 和 Outer Join 两种方式。对于大批量数据的update，Join方式明显是更优的选择。KingbaseES 和 Postgresql 也支持两种方式的关联update，语法上采用两种不同的写法。

以下以例子的形式展示两种写法及性能上的差异。这些例子同时通过KingbaseES V8R6和 Postgresql  12.3 环境验证。

一、准备测试数据

?

1 2 3 4 5 6 7 8 9 10 11

create table t1(id1 integer,name1 varchar(200)); create table t2(id2 integer,name2 varchar(200));   insert into t1 select * from (select generate_series(1,1000000),repeat('a',50)) as a order by random(); insert into t2 select * from (select generate_series(1,1000000),repeat('b',50)) as a order by random();   create index ind_t1_id1 on t1(id1); create index ind_t2_id2 on t2(id2);   analyze t1; analyze t2;

二、性能测试

1、语法一

采用类似oracle filter 方式，逐条处理t1 表的每条记录。对于t1表的每条记录，都需要访问t2表。

?

1 2 3 4 5 6 7 8 9 10 11

test=# explain analyze update t1 set name1=(select name2 from t2 where id1=id2);                                                              QUERY PLAN                                                             ------------------------------------------------------------------------------------------------------------------------------------  Update on t1  (cost=0.00..8462810.00 rows=1000000 width=428) (actual time=13072.720..13072.721 rows=0 loops=1)    ->  Seq Scan on t1  (cost=0.00..8462810.00 rows=1000000 width=428) (actual time=0.035..6620.732 rows=1000000 loops=1)          SubPlan 1            ->  Index Scan using ind_t2_id2 on t2  (cost=0.42..8.44 rows=1 width=51) (actual time=0.006..0.006 rows=1 loops=1000000)                  Index Cond: (id2 = t1.id1)  Planning Time: 0.116 ms  Execution Time: 13072.780 ms (7 rows)

 2、语法二

采用hash join，大批量的update 效率更高。

?

1 2 3 4 5 6 7 8 9 10 11 12

test=# explain analyze update t1 set name1=name2 from t2 where id1=id2;                                                            QUERY PLAN                                                            ---------------------------------------------------------------------------------------------------------------------------------  Update on t1  (cost=37693.00..98122.00 rows=1000000 width=67) (actual time=8197.309..8197.312 rows=0 loops=1)    ->  Hash Join  (cost=37693.00..98122.00 rows=1000000 width=67) (actual time=349.817..1633.896 rows=1000000 loops=1)          Hash Cond: (t2.id2 = t1.id1)          ->  Seq Scan on t2  (cost=0.00..20310.00 rows=1000000 width=61) (actual time=0.021..191.730 rows=1000000 loops=1)          ->  Hash  (cost=20310.00..20310.00 rows=1000000 width=10) (actual time=348.798..348.798 rows=1000000 loops=1)                Buckets: 131072  Batches: 16  Memory Usage: 3594kB                ->  Seq Scan on t1  (cost=0.00..20310.00 rows=1000000 width=10) (actual time=0.034..153.882 rows=1000000 loops=1)  Planning Time: 0.780 ms  Execution Time: 8197.543 ms

三、结论

对于大批量数据update，基于hash join 的update方法效率上要高效很多。