Postgres 10

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 daily_ohlc (
 cdate date,
 open numeric(8,2),
 high numeric(8,2),
 low numeric(8,2),
 close numeric(8,2),
 sma8 numeric(8,2)
);

CREATE TABLE


create or replace function update_sma8() RETURNS TRIGGER AS
$$
 BEGIN
 
UPDATE daily_ohlc d SET sma8 = s.simple_mov_avg 
FROM
(
 SELECT  sec.cdate,AVG(sec.close)  
   OVER(ORDER BY sec.cdate ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS 
    simple_mov_avg FROM daily_ohlc sec
)s where s.cdate = NEW.cdate
     AND d.cdate = s.cdate;
RETURN NULL;
    
END $$ language plpgsql;

CREATE FUNCTION


CREATE TRIGGER check_update
    AFTER INSERT ON daily_ohlc
    FOR EACH ROW
EXECUTE PROCEDURE update_sma8();

CREATE TRIGGER


INSERT INTO daily_ohlc (cdate, open, high, low, close) values ('2019-01-10', 101, 110, 95, 108);
INSERT INTO daily_ohlc (cdate, open, high, low, close) values ('2019-01-11', 101, 110, 95, 109);
INSERT INTO daily_ohlc (cdate, open, high, low, close) values ('2019-01-13', 101, 110, 95, 110);
INSERT INTO daily_ohlc (cdate, open, high, low, close) values ('2019-01-14', 101, 110, 95, 112);
INSERT INTO daily_ohlc (cdate, open, high, low, close) values ('2019-01-16', 101, 110, 95, 113);
INSERT INTO daily_ohlc (cdate, open, high, low, close) values ('2019-01-17', 101, 110, 95, 115);
INSERT INTO daily_ohlc (cdate, open, high, low, close) values ('2019-01-18', 101, 110, 95, 117);
INSERT INTO daily_ohlc (cdate, open, high, low, close) values ('2019-01-20', 101, 110, 95, 120);


INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


SELECT  sec.cdate,AVG(sec.close)  
   OVER(ORDER BY sec.cdate ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS 
simple_mov_avg FROM daily_ohlc sec


cdate
simple_mov_avg


2019-01-10
108.0000000000000000

2019-01-11
108.5000000000000000

2019-01-13
109.0000000000000000

2019-01-14
109.7500000000000000

2019-01-16
110.4000000000000000

2019-01-17
111.1666666666666667

2019-01-18
112.0000000000000000

2019-01-20
113.0000000000000000



SELECT 8


select * from daily_ohlc;


cdate
open
high
low
close
sma8


2019-01-10
101.00
110.00
95.00
108.00
108.00

2019-01-11
101.00
110.00
95.00
109.00
108.50

2019-01-13
101.00
110.00
95.00
110.00
109.00

2019-01-14
101.00
110.00
95.00
112.00
109.75

2019-01-16
101.00
110.00
95.00
113.00
110.40

2019-01-17
101.00
110.00
95.00
115.00
111.17

2019-01-18
101.00
110.00
95.00
117.00
112.00

2019-01-20
101.00
110.00
95.00
120.00
113.00



SELECT 8

cdate	simple_mov_avg
2019-01-10	108.0000000000000000
2019-01-11	108.5000000000000000
2019-01-13	109.0000000000000000
2019-01-14	109.7500000000000000
2019-01-16	110.4000000000000000
2019-01-17	111.1666666666666667
2019-01-18	112.0000000000000000
2019-01-20	113.0000000000000000

cdate	open	high	low	close	sma8
2019-01-10	101.00	110.00	95.00	108.00	108.00
2019-01-11	101.00	110.00	95.00	109.00	108.50
2019-01-13	101.00	110.00	95.00	110.00	109.00
2019-01-14	101.00	110.00	95.00	112.00	109.75
2019-01-16	101.00	110.00	95.00	113.00	110.40
2019-01-17	101.00	110.00	95.00	115.00	111.17
2019-01-18	101.00	110.00	95.00	117.00	112.00
2019-01-20	101.00	110.00	95.00	120.00	113.00