标签

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