Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE tableA(
unique_coords varchar(2) ,
unique_id int  );

insert into tableA values
('a',1),
('b',2),
('c',3),
('d',4);

CREATE TABLE tableB(
coords varchar(2) ,
id int  );

insert into tableB values
('a',null),
('a',null),
('c',null),
('d',null),
('c',null),
('c',null),
('d',null),
('c',null);

CREATE TABLE

INSERT 0 4

CREATE TABLE

INSERT 0 8

select * from tableA;
select * from tableB;

unique_coords
unique_id

a
1

b
2

c
3

d
4

SELECT 4

coords
id

a
null

a
null

c
null

d
null

c
null

c
null

d
null

c
null

SELECT 8

UPDATE tableB
SET id = unique_id
FROM tableA
WHERE tableA.unique_coords = tableB.coords;

UPDATE 8

select * from tableB;

coords
id

a
1

a
1

c
3

c
3

c
3

c
3

d
4

d
4

SELECT 8

coords	id
a	null
a	null
c	null
d	null
c	null
c	null
d	null
c	null