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 xx ( id serial, amount int );
insert into xx ( id, amount ) values ( 1, 5  ), ( 1, 5), ( 2, 4) , ( 2, 4), (2,4), (3,7), (3,7), (4,5), (4,5);
9 rows affected

select * from xx


id
amount


1
5

1
5

2
4

2
4

2
4

3
7

3
7

4
5

4
5


select id, amount, (select sum( x ) from ( select sum( distinct amount ) as x from xx group by id) t ) as total from xx;

-- sum( distict amount ) over ( order by id range between unbounded preceding and unbounded following )
-- Here I want to sum: 5 + 4 + 7 + 5

-- Order BY id will make values 55, 444, 77, 55 as peers. 
-- DISTINCT will force SUM to take only one value from EACH peers



id
amount
total


1
5
21

1
5
21

2
4
21

2
4
21

2
4
21

3
7
21

3
7
21

4
5
21

4
5
21


select id, amount, 
  sum( amount ) over ( order by id range between unbounded preceding and unbounded following )
from xx


id
amount
sum


1
5
46

1
5
46

2
4
46

2
4
46

2
4
46

3
7
46

3
7
46

4
5
46

4
5
46