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;
ERROR:  column "version" does not exist
LINE 1: select @@version;
                 ^


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);
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);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select  distinct on (primary_key_col_1, primary_key_col_2) primary_key_col_1,primary_key_col_2, value_col_1,value_col_2 from 

(select * from 
edit_d 
union all
select primary_key_col_1,primary_key_col_2, null as "primary_key_col_3", value_col_1,value_col_2 from raw_d
order by primary_key_col_1, primary_key_col_2, primary_key_col_3 desc nulls last
)tab


primary_key_col_1
primary_key_col_2
value_col_1
value_col_2


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

primary_key_col_1	primary_key_col_2	value_col_1	value_col_2
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