PostgreSQL update tbl1 from tbl2 一对多的注意事项(到底匹配哪条)

标签

PostgreSQL , update from , 一对多

背景

首先A表和B表需要有关联的列, 关联之后A表和B表应该是多对一或者一对一的关系, 一对一的话，很好理解。

如果是一对多会怎么样呢? 任何数据库都会给你一个不确定的答案（与执行计划数据的扫描方法有关）

测试如下 :

sar=> create table a (id int primary key, info text);  
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a"  
CREATE TABLE  
sar=> create table b (id int, info text);  
CREATE TABLE  
sar=> insert into a select generate_series(1,10),'digoal';  
INSERT 0 10  
sar=> insert into b select generate_series(1,10),'Digoal';  
INSERT 0 10  
sar=> insert into b select generate_series(1,10),'DIGOAL';  
INSERT 0 10  
sar=> select * from a where id=1;  
 id |  info    
----+--------  
  1 | digoal  
(1 row)  
  
sar=> select * from b where id=1;  
 id |  info    
----+--------  
  1 | Digoal  
  1 | DIGOAL  
(2 rows)  

执行如下更新之后, a.id 会等于什么呢? 是Digoal, 还是DIGOAL呢？

看第一个执行计划的结果

b表还没有建索引，使用了nestloop+全表扫描  
  
postgres=# explain update a set info=b.info from b where a.id=b.id and a.id=1;  
                                 QUERY PLAN                                    
-----------------------------------------------------------------------------  
 Update on a  (cost=0.15..28.70 rows=6 width=48)  
   ->  Nested Loop  (cost=0.15..28.70 rows=6 width=48)  
         ->  Index Scan using a_pkey on a  (cost=0.15..2.77 rows=1 width=10)  
               Index Cond: (id = 1)  
         ->  Seq Scan on b  (cost=0.00..25.88 rows=6 width=42)  
               Filter: (id = 1)  
(6 rows)  
  
全表扫描时Digoal这条在前面命中  
  
postgres=# select * from b where id=1 limit 1;  
 id |  info    
----+--------  
  1 | Digoal  
(1 row)  
  
更新拿到了第一条命中的b.info  
  
sar=> update a set info=b.info from b where a.id=b.id and a.id=1;  
UPDATE 1  
sar=> select * from a where id=1;  
 id |  info    
----+--------  
  1 | Digoal  
(1 row)  

看第二个执行计划，使用nestloop+索引扫描

创建一个复合索引，这样可以让索引扫描时， DIGOAL这条记录排到前面  
postgres=# create index idx_b_id on b(id, info);  
CREATE INDEX  
  
  
postgres=# set enable_seqscan=off;  
SET  
  
postgres=# select * from b where id=1 limit 1;  
 id |  info    
----+--------  
  1 | DIGOAL  
(1 row)  
  
现在执行计划，B表使用索引了  
postgres=# explain update a set info=b.info from b where a.id=b.id and a.id=1;  
                                  QUERY PLAN                                     
-------------------------------------------------------------------------------  
 Update on a  (cost=0.29..5.53 rows=1 width=48)  
   ->  Nested Loop  (cost=0.29..5.53 rows=1 width=48)  
         ->  Index Scan using a_pkey on a  (cost=0.15..2.77 rows=1 width=10)  
               Index Cond: (id = 1)  
         ->  Index Scan using idx_b_id on b  (cost=0.14..2.75 rows=1 width=42)  
               Index Cond: (id = 1)  
(6 rows)  
  
现在更新，就变成DIGOAL了。  
postgres=# update a set info=b.info from b where a.id=b.id and a.id=1 returning a.ctid,*;  
  ctid  | id |  info  | id |  info    
--------+----+--------+----+--------  
 (0,11) |  1 | DIGOAL |  1 | DIGOAL  
(1 row)  
  
UPDATE 1