作者
digoal
日期
2017-03-14
标签
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
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
