Postgres 13

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?.

WITH stock_prices(quote_date, tr, security_id) AS (
  VALUES 
    (TO_DATE('10-Jan-86', 'DD-MON-YY'), null        , 1),
    (TO_DATE('13-Jan-86', 'DD-MON-YY'), -0.026595745, 1),
    (TO_DATE('14-Jan-86', 'DD-MON-YY'), 0.005464481 , 1),
    (TO_DATE('15-Jan-86', 'DD-MON-YY'), -0.016304348, 1),
    (TO_DATE('16-Jan-86', 'DD-MON-YY'), 0           , 1),
    (TO_DATE('17-Jan-86', 'DD-MON-YY'), 0           , 1),
    (TO_DATE('20-Jan-86', 'DD-MON-YY'), 0           , 1),
    (TO_DATE('21-Jan-86', 'DD-MON-YY'), 0.005524862 , 1),
    (TO_DATE('22-Jan-86', 'DD-MON-YY'), -0.005494506, 1),
    (TO_DATE('23-Jan-86', 'DD-MON-YY'), 0           , 1),
    (TO_DATE('24-Jan-86', 'DD-MON-YY'), -0.005524862, 1),
    (TO_DATE('27-Jan-86', 'DD-MON-YY'), 0.005555556 , 1),
    (TO_DATE('28-Jan-86', 'DD-MON-YY'), 0           , 1),
    (TO_DATE('29-Jan-86', 'DD-MON-YY'), 0           , 1),
    (TO_DATE('30-Jan-86', 'DD-MON-YY'), 0           , 1),
    (TO_DATE('31-Jan-86', 'DD-MON-YY'), 0.027624309 , 1),
    (TO_DATE('3-Feb-86' , 'DD-MON-YY'), 0.016129032 , 1),
    (TO_DATE('4-Feb-86' , 'DD-MON-YY'), 0.042328041 , 1),
    (TO_DATE('5-Feb-86' , 'DD-MON-YY'), 0.04568528  , 1)
)
SELECT
  quote_date,
  security_id,
  tr,
  1000 * (EXP(SUM(LN(1 + COALESCE(tr, 0)))
    OVER (PARTITION BY security_id ORDER BY quote_date)))
    AS accum_index
FROM stock_prices;

quote_date	security_id	tr	accum_index
1986-01-10	1	null	1000.0000000000000000
1986-01-13	1	-0.026595745	973.404255000000000000
1986-01-14	1	0.005464481	978.7234040567666550000
1986-01-15	1	-0.016304348	962.7659570812805201000
1986-01-16	1	0	962.7659570812805201000
1986-01-17	1	0	962.7659570812805201000
1986-01-20	1	0	962.7659570812805201000
1986-01-21	1	0.005524862	968.0851061324525177000
1986-01-22	1	-0.005494506	962.7659567082971205000
1986-01-23	1	0	962.7659567082971205000
1986-01-24	1	-0.005524862	957.4468076591858047000
1986-01-27	1	0.005555556	962.7659570161576404000
1986-01-28	1	0	962.7659570161576404000
1986-01-29	1	0	962.7659570161576404000
1986-01-30	1	0	962.7659570161576404000
1986-01-31	1	0.027624309	989.3617013074526973000
1986-02-03	1	0.016129032	1005.3191478474150442000
1986-02-04	1	0.042328041	1047.8723379555854904000
1986-02-05	1	0.04568528	1095.7446791193410408000

SELECT 19