Postgres 11

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(
    my_date date,
    value int,
    region_id int
);

INSERT INTO mytable VALUES
('2016-09-01', 11, 1),
('2016-10-01', 22, 1),
('2016-11-01', 33, 1),
('2016-12-01', 44, 1),
('2017-01-01', 55, 1),
('2017-02-01', 66, 1),
('2017-03-01', 77, 1),
('2017-04-01', 88, 1),
('2017-05-01', 99, 1),
('2017-06-01', 11, 1),
('2017-07-01', 22, 1),
('2017-08-01', 33, 1),
('2017-09-01', 44, 1),
('2017-10-01', 55, 1);
14 rows affected

SELECT
    *,
    AVG(value) OVER (
        PARTITION BY region_id 
        ORDER BY my_date 
        RANGE BETWEEN interval '11 months' PRECEDING AND CURRENT ROW)
FROM mytable


my_date
value
region_id
avg


2016-09-01
11
1
11.0000000000000000

2016-10-01
22
1
16.5000000000000000

2016-11-01
33
1
22.0000000000000000

2016-12-01
44
1
27.5000000000000000

2017-01-01
55
1
33.0000000000000000

2017-02-01
66
1
38.5000000000000000

2017-03-01
77
1
44.0000000000000000

2017-04-01
88
1
49.5000000000000000

2017-05-01
99
1
55.0000000000000000

2017-06-01
11
1
50.6000000000000000

2017-07-01
22
1
48.0000000000000000

2017-08-01
33
1
46.7500000000000000

2017-09-01
44
1
49.5000000000000000

2017-10-01
55
1
52.2500000000000000


SELECT
    *,
    CASE WHEN my_date >= '2017-08-01' THEN
        AVG(value) OVER (
            PARTITION BY region_id 
            ORDER BY my_date 
            RANGE BETWEEN interval '11 months' PRECEDING AND CURRENT ROW)
    ELSE NULL END
FROM mytable


my_date
value
region_id
case


2016-09-01
11
1
null

2016-10-01
22
1
null

2016-11-01
33
1
null

2016-12-01
44
1
null

2017-01-01
55
1
null

2017-02-01
66
1
null

2017-03-01
77
1
null

2017-04-01
88
1
null

2017-05-01
99
1
null

2017-06-01
11
1
null

2017-07-01
22
1
null

2017-08-01
33
1
46.7500000000000000

2017-09-01
44
1
49.5000000000000000

2017-10-01
55
1
52.2500000000000000

my_date	value	region_id	avg
2016-09-01	11	1	11.0000000000000000
2016-10-01	22	1	16.5000000000000000
2016-11-01	33	1	22.0000000000000000
2016-12-01	44	1	27.5000000000000000
2017-01-01	55	1	33.0000000000000000
2017-02-01	66	1	38.5000000000000000
2017-03-01	77	1	44.0000000000000000
2017-04-01	88	1	49.5000000000000000
2017-05-01	99	1	55.0000000000000000
2017-06-01	11	1	50.6000000000000000
2017-07-01	22	1	48.0000000000000000
2017-08-01	33	1	46.7500000000000000
2017-09-01	44	1	49.5000000000000000
2017-10-01	55	1	52.2500000000000000

my_date	value	region_id	case
2016-09-01	11	1	null
2016-10-01	22	1	null
2016-11-01	33	1	null
2016-12-01	44	1	null
2017-01-01	55	1	null
2017-02-01	66	1	null
2017-03-01	77	1	null
2017-04-01	88	1	null
2017-05-01	99	1	null
2017-06-01	11	1	null
2017-07-01	22	1	null
2017-08-01	33	1	46.7500000000000000
2017-09-01	44	1	49.5000000000000000
2017-10-01	55	1	52.2500000000000000