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 day (
symbol varchar(20),
date date,
open NUMERIC(8,2),
high NUMERIC(8,2),
low  NUMERIC(8,2),
close NUMERIC(8,2),
sma8 NUMERIC(8,2),
PRIMARY KEY(symbol, date));
create or replace function update_sma8() RETURNS TRIGGER AS
$$
 BEGIN

 UPDATE day d SET sma8 = s.simple_mov_avg 
 FROM
 (
  SELECT  sec.date,sec.symbol,AVG(sec.close)  
    OVER( partition by symbol ORDER BY sec.date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS 
     simple_mov_avg FROM day sec
 )s where s.date = NEW.date  --The newly inserted date
      AND d.date = s.date   
      AND d.symbol = s.symbol;
 RETURN NULL;

 END $$ language plpgsql;
CREATE TRIGGER check_update
    AFTER INSERT ON day
    FOR EACH ROW
EXECUTE PROCEDURE update_sma8();
INSERT INTO day VALUES('ABC', '2019-03-19', 102.3, 110.0, 125.3, 120.4, 0);
INSERT INTO day VALUES('ABC', '2019-03-20', 112.3, 109.0, 119.3, 118.4, 0);
INSERT INTO day VALUES('DEF', '2019-03-19', 1112.3, 1100.0, 1155.3, 1120.4, 0);
INSERT INTO day VALUES('DEF', '2019-03-20', 1202.3, 1180.0, 1205.3, 1190.4, 0);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

select * from day order by symbol,date;


symbol
date
open
high
low
close
sma8


ABC
2019-03-19
102.30
110.00
125.30
120.40
120.40

ABC
2019-03-20
112.30
109.00
119.30
118.40
119.40

DEF
2019-03-19
1112.30
1100.00
1155.30
1120.40
1120.40

DEF
2019-03-20
1202.30
1180.00
1205.30
1190.40
1155.40

symbol	date	open	high	low	close	sma8
ABC	2019-03-19	102.30	110.00	125.30	120.40	120.40
ABC	2019-03-20	112.30	109.00	119.30	118.40	119.40
DEF	2019-03-19	1112.30	1100.00	1155.30	1120.40	1120.40
DEF	2019-03-20	1202.30	1180.00	1205.30	1190.40	1155.40