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?.

select version();

version

PostgreSQL 10.23 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit

SELECT 1

create table raw_d(primary_key_col_1 varchar,primary_key_col_2 varchar, value_col_1 int,value_col_2 int);
create table edit_d(primary_key_col_1 varchar,primary_key_col_2 varchar, primary_key_col_3 date, value_col_1 int,value_col_2 int);

CREATE TABLE

CREATE TABLE

insert into raw_d values('src_1','dest_1' ,0,1);
insert into raw_d values('src_2','dest_2' ,5,4);
insert into raw_d values('src_3','dest_3' ,2,2);
insert into raw_d values('src_4','dest_4' ,6,3);
insert into raw_d values('src_5','dest_5' ,9,9);

insert into edit_d values('src_1','dest_1','2020-05-09',7,0);
insert into edit_d values('src_2','dest_2','2020-05-08',6,1);
insert into edit_d values('src_3','dest_3','2020-05-07',5,2);
insert into edit_d values('src_1','dest_1','2020-05-08',3,4);
insert into edit_d values('src_2','dest_2','2020-05-09',2,5);

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

with cte as (
select distinct on (primary_key_col_1,primary_key_col_2) * from edit_d order by primary_key_col_1, primary_key_col_2, primary_key_col_3 desc)

select 
coalesce(t1.primary_key_col_1,t2.primary_key_col_1),
coalesce(t1.primary_key_col_2,t2.primary_key_col_2),
coalesce(t1.value_col_1,t2.value_col_1),
coalesce(t1.value_col_2,t2.value_col_2)
from cte t1 full outer join raw_d t2 on t1.primary_key_col_1=t2.primary_key_col_1 and t1.primary_key_col_2=t2.primary_key_col_2

coalesce
coalesce
coalesce
coalesce

src_1
dest_1
7
0

src_2
dest_2
2
5

src_3
dest_3
5
2

src_4
dest_4
6
3

src_5
dest_5
9
9

SELECT 5

coalesce	coalesce	coalesce	coalesce
src_1	dest_1	7	0
src_2	dest_2	2	5
src_3	dest_3	5	2
src_4	dest_4	6	3
src_5	dest_5	9	9