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 相关资源：python入门教程(PDF版)