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 my_result( city text, street text,street_num int, val_x int, val_y int);
insert into my_result values('CityA','Street abc',5,11.5,0.5);
1 rows affected

insert into my_result values('CityA' ,'Street abc' ,5,15.4,1.8);
1 rows affected

insert into my_result values('CityB' ,'Street xyz' ,18,15.4,1.8);
1 rows affected

insert into my_result values('CityB' ,'Street xyz' ,18,16.4,3.8);
1 rows affected

insert into my_result values('CityC' ,'Street klm' ,55,14.3,1.9);
1 rows affected

select city, street, street_num, val_x,val_y from (select city, street, street_num, val_x,val_y, row_number() over (partition by city order by city) rn from my_result) as tbl1 where rn=1;


city
street
street_num
val_x
val_y


CityA
Street abc
5
12
1

CityB
Street xyz
18
15
2

CityC
Street klm
55
14
2