Postgres 8.4

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 measures(provider varchar(50),mcost int, percent int);
insert into measures values('Police',30000,80);
insert into measures values('Fire Department',50000,40);
insert into measures values('Military',40000,50);
insert into measures values('Medical',45000,70);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

select * from (
         select row_number() over(order by x.max_percent desc) as rnk
               ,x.*
           from (
         select a.provider
                ,b.provider
                ,greatest(a.provider,b.provider) as combo_pair
                ,a.percent+b.percent as summed_percent
                ,a.mcost+b.mcost as summed_mcost
                ,max(a.percent+b.percent) over(partition by greatest(a.provider,b.provider)) as max_percent
           from measures a
           join measures b
             on 1=1
         where a.provider <> b.provider    
           and a.mcost+b.mcost <100000 /*Check for the combined cost to be <100000*/
         )x
         where x.max_percent=x.summed_percent
)y
where y.rnk=1


rnk
provider
provider
combo_pair
summed_percent
summed_mcost
max_percent


1
Police
Medical
Police
150
75000
150