Postgres 10

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 table1 (id int, value int);
create table table2 (id int, value int);
insert into table1 values 
(1,10),
(2,20),
(3,30);
insert into table2 values 
(1,15),
(3,15),
(5,50);

CREATE TABLE

CREATE TABLE

INSERT 0 3

INSERT 0 3

select
    coalesce(t1.id, t2.id) as id,
    coalesce(t2.value, 0) - coalesce(t1.value, 0) as diff
from table1 as t1
     full join table2 as t2
     on t1.id = t2.id ;

id
diff

1
5

2
-20

3
-15

5
50

SELECT 4

select
    id,
    coalesce(t2.value, 0) - coalesce(t1.value, 0) as diff
from table1 as t1
     full join table2 as t2
     using (id) ;

id
diff

1
5

2
-20

3
-15

5
50

SELECT 4