Label

PostgreSQL, update from, one-to-many

background

First of all, table A and table B need related columns. After association, table A and table B should have a one-to-one or one-to-one relationship. If one-to-one, it is easy to understand.

What if it's one-to-many? Any database will give you an uncertain answer (related to the scanning method of execution plan data).

The tests are as follows:

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)  

What does a.id mean after the following updates? Is it Digoal or DIGOAL?

Look at the results of the first implementation plan

b The table has not yet been indexed and is used nestloop+Full table scan  
  
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 hits ahead in full table scan  
  
postgres=# select * from b where id=1 limit 1;  
 id |  info    
----+--------  
  1 | Digoal  
(1 row)  
  
//Update got the first hit 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)  

Look at the second execution plan, using nestloop + index scan

Create a composite index so that when the index is scanned, DIGOAL This record goes to the front.  
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)  
  
//Now execute the plan. Table B uses the index.  
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)  
  
//Now update, it will become 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