PostgreSQL update tbl1 from tbl2 一对多的注意事项(到底匹配哪条)
背景
首先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