Postgres 12

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 mytable (id int,cars int,price int)
insert into mytable values(1, 2, 200)
1 rows affected

insert into mytable values(1, 3, 200)
1 rows affected

insert into mytable values(1, 1, 500)
1 rows affected

insert into mytable values(2, 4, 200)
1 rows affected

select round((price::decimal/sum(price) over (partition by id)),4) * 100 || ' %' as share_of_value_of_owner
, round((price::decimal/sum(price) over()), 4) * 100 || ' %' as share_of_value_of_cars
from mytable
group by id, cars, price


share_of_value_of_owner
share_of_value_of_cars


22.2200 %
18.1800 %

55.5600 %
45.4500 %

22.2200 %
18.1800 %

100.0000 %
18.1800 %


create table owners (id int,cars int,price int)
insert into owners values(1, 1, 10)
1 rows affected

insert into owners values(1, 2, 10)
1 rows affected

insert into owners values(2, 3, 10)
1 rows affected

select round((price::decimal/sum(price) over (partition by id)),4) * 100 || ' %' as share_of_value_of_owner
, round((price::decimal/sum(price) over()), 4) * 100 || ' %' as share_of_value_of_cars
from owners
group by id, cars, price


share_of_value_of_owner
share_of_value_of_cars


50.0000 %
33.3300 %

50.0000 %
33.3300 %

100.0000 %
33.3300 %

share_of_value_of_owner	share_of_value_of_cars
22.2200 %	18.1800 %
55.5600 %	45.4500 %
22.2200 %	18.1800 %
100.0000 %	18.1800 %