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 invoice ( invoice_id int, group_id int, qty numeric(10,6), price numeric( 10,6 ) );
insert into invoice ( invoice_id, group_id, qty, price ) values 
 ( 1, 1, 525,   6.051280 ),
 ( 1, 1,   1,  60.512800 ),
 ( 1, 1,  32, 302.564000 ),
 ( 1, 1,  16,  75.641000 ),

 ( 1, 2, 400,   6.051280 ),
 ( 1, 2,   1,  60.512800 ),
 ( 1, 2,  24, 302.564000 ),
 ( 1, 2,  26,  75.641000 ),

 ( 1, 3,  50,   6.051280 ),
 ( 1, 3,   1,  60.512800 ),
 ( 1, 3,   4, 302.564000 ),
 ( 1, 3,   2,  75.641000 ),

 ( 1, 4,  50,   6.051280 ),
 ( 1, 4,   1,  60.512800 ),
 ( 1, 4,   8, 302.564000 ),
 ( 1, 4,   2,  75.641000 );
 

CREATE TABLE



INSERT 0 16


select * from invoice


invoice_id
group_id
qty
price


1
1
525.000000
6.051280

1
1
1.000000
60.512800

1
1
32.000000
302.564000

1
1
16.000000
75.641000

1
2
400.000000
6.051280

1
2
1.000000
60.512800

1
2
24.000000
302.564000

1
2
26.000000
75.641000

1
3
50.000000
6.051280

1
3
1.000000
60.512800

1
3
4.000000
302.564000

1
3
2.000000
75.641000

1
4
50.000000
6.051280

1
4
1.000000
60.512800

1
4
8.000000
302.564000

1
4
2.000000
75.641000



SELECT 16


with sub_total as (
select 
 *,
 sum( qty*price ) OVER( PARTITION BY invoice_id, group_id )      AS order_cost
from invoice
)

-- Here how I get is expected result:
select *,
  (SELECT sum(x) from (SELECT sum( DISTINCT order_cost ) AS x FROM sub_total sub_i GROUP BY invoice_id, group_id) t) as total_cost
  -- Here is desired functionality:  is this possible?
  -- sum( DISTINCT order_cost ) OVER ( PARTITION BY invoice_id ORDER BY invoice_id, group_id RANGE unbound preceeding and unbound following )
from sub_total;




invoice_id
group_id
qty
price
order_cost
total_cost


1
1
525.000000
6.051280
14129.738800000000
30498.451200000000

1
1
1.000000
60.512800
14129.738800000000
30498.451200000000

1
1
32.000000
302.564000
14129.738800000000
30498.451200000000

1
1
16.000000
75.641000
14129.738800000000
30498.451200000000

1
2
400.000000
6.051280
11709.226800000000
30498.451200000000

1
2
1.000000
60.512800
11709.226800000000
30498.451200000000

1
2
24.000000
302.564000
11709.226800000000
30498.451200000000

1
2
26.000000
75.641000
11709.226800000000
30498.451200000000

1
3
50.000000
6.051280
1724.614800000000
30498.451200000000

1
3
1.000000
60.512800
1724.614800000000
30498.451200000000

1
3
4.000000
302.564000
1724.614800000000
30498.451200000000

1
3
2.000000
75.641000
1724.614800000000
30498.451200000000

1
4
50.000000
6.051280
2934.870800000000
30498.451200000000

1
4
1.000000
60.512800
2934.870800000000
30498.451200000000

1
4
8.000000
302.564000
2934.870800000000
30498.451200000000

1
4
2.000000
75.641000
2934.870800000000
30498.451200000000



SELECT 16

invoice_id	group_id	qty	price
1	1	525.000000	6.051280
1	1	1.000000	60.512800
1	1	32.000000	302.564000
1	1	16.000000	75.641000
1	2	400.000000	6.051280
1	2	1.000000	60.512800
1	2	24.000000	302.564000
1	2	26.000000	75.641000
1	3	50.000000	6.051280
1	3	1.000000	60.512800
1	3	4.000000	302.564000
1	3	2.000000	75.641000
1	4	50.000000	6.051280
1	4	1.000000	60.512800
1	4	8.000000	302.564000
1	4	2.000000	75.641000

invoice_id	group_id	qty	price	order_cost	total_cost
1	1	525.000000	6.051280	14129.738800000000	30498.451200000000
1	1	1.000000	60.512800	14129.738800000000	30498.451200000000
1	1	32.000000	302.564000	14129.738800000000	30498.451200000000
1	1	16.000000	75.641000	14129.738800000000	30498.451200000000
1	2	400.000000	6.051280	11709.226800000000	30498.451200000000
1	2	1.000000	60.512800	11709.226800000000	30498.451200000000
1	2	24.000000	302.564000	11709.226800000000	30498.451200000000
1	2	26.000000	75.641000	11709.226800000000	30498.451200000000
1	3	50.000000	6.051280	1724.614800000000	30498.451200000000
1	3	1.000000	60.512800	1724.614800000000	30498.451200000000
1	3	4.000000	302.564000	1724.614800000000	30498.451200000000
1	3	2.000000	75.641000	1724.614800000000	30498.451200000000
1	4	50.000000	6.051280	2934.870800000000	30498.451200000000
1	4	1.000000	60.512800	2934.870800000000	30498.451200000000
1	4	8.000000	302.564000	2934.870800000000	30498.451200000000
1	4	2.000000	75.641000	2934.870800000000	30498.451200000000