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, 1)))
    OVER (PARTITION BY security_id ORDER BY quote_date)) - 1)
    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	946.808509999999982000
1986-01-14	1	0.005464481	957.4468081135332916000
1986-01-15	1	-0.016304348	925.5319141625610220000
1986-01-16	1	0	925.5319141625610220000
1986-01-17	1	0	925.5319141625610220000
1986-01-20	1	0	925.5319141625610220000
1986-01-21	1	0.005524862	936.1702122649050172000
1986-01-22	1	-0.005494506	925.5319134165942229000
1986-01-23	1	0	925.5319134165942229000
1986-01-24	1	-0.005524862	914.8936153183715914000
1986-01-27	1	0.005555556	925.5319140323152626000
1986-01-28	1	0	925.5319140323152626000
1986-01-29	1	0	925.5319140323152626000
1986-01-30	1	0	925.5319140323152626000
1986-01-31	1	0.027624309	978.7234026149053760000
1986-02-03	1	0.016129032	1010.6382956948300694000
1986-02-04	1	0.042328041	1095.7446759111709610000
1986-02-05	1	0.04568528	1191.4893582386820610000