Postgres 9.6

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 t1 (a integer, b integer);
create table t2 (a integer, b integer);
insert into t1 (a,b) values (1,1);
insert into t1 (a,b) values (2,1);
insert into t1 (a,b) values (3,2);
insert into t1 (a,b) values (4,2);

insert into t2 (a,b) values (1,1);
insert into t2 (a,b) values (2,null);
insert into t2 (a,b) values (4,null);



1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select * from t2;


a
b


1
1

2
null

4
null


update t2 set b = t1.b 
from t1 
where t1.a = t2.a;
3 rows affected

select * from t2;


a
b


1
1

2
1

4
2

a	b
1	1
2	null
4	null