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 (POLICY_ID int, PAYMENT_DATE DATE, PREMIUM int, YEARLY_PAYMENT_FREQ int,MONTHLY_PREMIUM_DESIRED int);                                  
INSERT INTO MYTABLE VALUES (1, DATE '2014-10-01',120,12,120);
INSERT INTO MYTABLE VALUES (1, DATE  '2014-11-01',360,4,120);
INSERT INTO MYTABLE VALUES (1, DATE '2014-12-01',0,4,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-01-01',0,4,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-02-01',360,4,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-03-01',0,4,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-04-01',0,4,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-05-01',720,2,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-06-01',0,2,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-07-01',0,2,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-08-01',0,2,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-09-01',0,2,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-10-01',0,2,120);
INSERT INTO MYTABLE VALUES (1, DATE '2015-11-01',120,12,120);
INSERT INTO MYTABLE VALUES (2, DATE '2015-01-01',60,3,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-02-01',0,3,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-03-01',0,3,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-04-01',0,3,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-05-01',180,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-06-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-07-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-08-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-09-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-10-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-11-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2015-12-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2016-01-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2016-02-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2016-03-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2016-04-01',0,1,15);
INSERT INTO MYTABLE VALUES (2, DATE '2016-05-01',15,12,15);
INSERT INTO MYTABLE VALUES (2, DATE '2016-06-01',15,12,15);

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select t.*,
       ( max(premium) over (partition by policy_id, grp) /
         (12 / yearly_payment_freq)
       ) as monthly_premium_calculated
from (select t.*,
             sum(case when premium > 0 then 1 else 0 end) over (partition by policy_id order by payment_date) as grp
      from mytable t
     ) t


policy_id
payment_date
premium
yearly_payment_freq
monthly_premium_desired
grp
monthly_premium_calculated


1
2014-10-01
120
12
120
1
120

1
2014-11-01
360
4
120
2
120

1
2014-12-01
0
4
120
2
120

1
2015-01-01
0
4
120
2
120

1
2015-02-01
360
4
120
3
120

1
2015-03-01
0
4
120
3
120

1
2015-04-01
0
4
120
3
120

1
2015-05-01
720
2
120
4
120

1
2015-06-01
0
2
120
4
120

1
2015-07-01
0
2
120
4
120

1
2015-08-01
0
2
120
4
120

1
2015-09-01
0
2
120
4
120

1
2015-10-01
0
2
120
4
120

1
2015-11-01
120
12
120
5
120

2
2015-01-01
60
3
15
1
15

2
2015-02-01
0
3
15
1
15

2
2015-03-01
0
3
15
1
15

2
2015-04-01
0
3
15
1
15

2
2015-05-01
180
1
15
2
15

2
2015-06-01
0
1
15
2
15

2
2015-07-01
0
1
15
2
15

2
2015-08-01
0
1
15
2
15

2
2015-09-01
0
1
15
2
15

2
2015-10-01
0
1
15
2
15

2
2015-11-01
0
1
15
2
15

2
2015-12-01
0
1
15
2
15

2
2016-01-01
0
1
15
2
15

2
2016-02-01
0
1
15
2
15

2
2016-03-01
0
1
15
2
15

2
2016-04-01
0
1
15
2
15

2
2016-05-01
15
12
15
3
15

2
2016-06-01
15
12
15
4
15

policy_id	payment_date	premium	yearly_payment_freq	monthly_premium_desired	grp	monthly_premium_calculated
1	2014-10-01	120	12	120	1	120
1	2014-11-01	360	4	120	2	120
1	2014-12-01	0	4	120	2	120
1	2015-01-01	0	4	120	2	120
1	2015-02-01	360	4	120	3	120
1	2015-03-01	0	4	120	3	120
1	2015-04-01	0	4	120	3	120
1	2015-05-01	720	2	120	4	120
1	2015-06-01	0	2	120	4	120
1	2015-07-01	0	2	120	4	120
1	2015-08-01	0	2	120	4	120
1	2015-09-01	0	2	120	4	120
1	2015-10-01	0	2	120	4	120
1	2015-11-01	120	12	120	5	120
2	2015-01-01	60	3	15	1	15
2	2015-02-01	0	3	15	1	15
2	2015-03-01	0	3	15	1	15
2	2015-04-01	0	3	15	1	15
2	2015-05-01	180	1	15	2	15
2	2015-06-01	0	1	15	2	15
2	2015-07-01	0	1	15	2	15
2	2015-08-01	0	1	15	2	15
2	2015-09-01	0	1	15	2	15
2	2015-10-01	0	1	15	2	15
2	2015-11-01	0	1	15	2	15
2	2015-12-01	0	1	15	2	15
2	2016-01-01	0	1	15	2	15
2	2016-02-01	0	1	15	2	15
2	2016-03-01	0	1	15	2	15
2	2016-04-01	0	1	15	2	15
2	2016-05-01	15	12	15	3	15
2	2016-06-01	15	12	15	4	15