Postgres 9.4

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 dw_dim_date
AS
SELECT *
FROM generate_series('2019-01-01'::date,'2019-01-10'::date, '1 day'::interval) s(date)

SELECT 10


CREATE TABLE score_history(id ,happened_at, location_id,score)
AS
      SELECT 1, '2019-01-01:10:13:01'::timestamp, 100, 5.0
UNION SELECT 2, '2019-01-05:20:00:01', 100, 5.8
UNION SELECT 3, '2019-01-05:23:01:22', 100, 6.2
UNION SELECT 4, '2019-01-03:23:01:22', 200, 10
UNION SELECT 5, '2019-01-09:23:01:22', 200, 11

SELECT 5


SELECT sub.date, sub.location_id, score
FROM (SELECT * FROM dw_dim_date
      CROSS JOIN (SELECT DISTINCT location_id FROM score_history) s
      WHERE date >= '2019-01-01'::date) sub
,LATERAL (SELECT score FROM score_history sc 
         WHERE sc.happened_at::date <= sub.date
            AND sc.location_id = sub.location_id
         ORDER BY happened_at DESC LIMIT 1) l
,LATERAL(SELECT MIN(happened_at::date) m1, MAX(happened_at::date) m2 
         FROM score_history sc
         WHERE sc.location_id = sub.location_id) lm
WHERE sub.date BETWEEN lm.m1 AND lm.m2
ORDER BY location_id, date


date
location_id
score


2019-01-01 00:00:00+00
100
5.0

2019-01-02 00:00:00+00
100
5.0

2019-01-03 00:00:00+00
100
5.0

2019-01-04 00:00:00+00
100
5.0

2019-01-05 00:00:00+00
100
6.2

2019-01-03 00:00:00+00
200
10

2019-01-04 00:00:00+00
200
10

2019-01-05 00:00:00+00
200
10

2019-01-06 00:00:00+00
200
10

2019-01-07 00:00:00+00
200
10

2019-01-08 00:00:00+00
200
10

2019-01-09 00:00:00+00
200
11



SELECT 12

date	location_id	score
2019-01-01 00:00:00+00	100	5.0
2019-01-02 00:00:00+00	100	5.0
2019-01-03 00:00:00+00	100	5.0
2019-01-04 00:00:00+00	100	5.0
2019-01-05 00:00:00+00	100	6.2
2019-01-03 00:00:00+00	200	10
2019-01-04 00:00:00+00	200	10
2019-01-05 00:00:00+00	200	10
2019-01-06 00:00:00+00	200	10
2019-01-07 00:00:00+00	200	10
2019-01-08 00:00:00+00	200	10
2019-01-09 00:00:00+00	200	11