Postgres 13

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 df_1 (job_id int, worker_id int, random_id text);
insert into df_1 (job_id, worker_id, random_id)
values
  (1711418, 45430, 'random'),
  (1711418, 46353, 'random'),
  (1711418, 45158, 'random');
3 rows affected

create table df_2 (job_id int, worker_id int);
insert into df_2 (job_id, worker_id)
values
  (1711418, 45430),
  (1711419, 46117),
  (1711419, 46322),
  (1711419, 45926);
4 rows affected

select job_id, worker_id, random_id
from df_1
union all
select job_id, worker_id, 'NaN'
from df_2 d2
where not exists (select *
                  from df_1 d1
                  where d1.job_id = d2.job_id
                    and d1.worker_id = d2.worker_id);


job_id
worker_id
random_id


1711418
45430
random

1711418
46353
random

1711418
45158
random

1711419
46117
NaN

1711419
46322
NaN

1711419
45926
NaN


select job_id, worker_id, random_id
from df_1
union all
select job_id, worker_id, 'NaN'
from 
(
  select job_id, worker_id
  from df_2 d2
  except
  select job_id, worker_id
  from df_1
) t;


job_id
worker_id
random_id


1711418
45430
random

1711418
46353
random

1711418
45158
random

1711419
46322
NaN

1711419
46117
NaN

1711419
45926
NaN

job_id	worker_id	random_id
1711418	45430	random
1711418	46353	random
1711418	45158	random
1711419	46117	NaN
1711419	46322	NaN
1711419	45926	NaN