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?.

create table foo (ID VARCHAR(10), foo_value int, foo_date date);

insert into foo (
          values
          ('234534', 100, '2017-01-01'),
          ('234534', 200, '2017-01-02'),
          ('234534', 300, '2017-01-03'),
          ('234534', 180, '2017-01-08')
        );
4 rows affected

with recursive cte as (
      select f.id, f.foo_value, f.foo_date,
             lead(f.foo_date, 1, f.foo_date) over (partition by f.id order by f.foo_date) - interval '1 day' as next_date
      from foo f
      union all
      select cte.id, cte.foo_value, (cte.foo_date + interval '1 day')::date, cte.next_date
      from cte
      where cte.foo_date < cte.next_date
     )
select *
from cte
order by foo_date


id
foo_value
foo_date
next_date


234534
100
2017-01-01
2017-01-01 00:00:00

234534
200
2017-01-02
2017-01-02 00:00:00

234534
300
2017-01-03
2017-01-07 00:00:00

234534
300
2017-01-04
2017-01-07 00:00:00

234534
300
2017-01-05
2017-01-07 00:00:00

234534
300
2017-01-06
2017-01-07 00:00:00

234534
300
2017-01-07
2017-01-07 00:00:00

234534
180
2017-01-08
2017-01-07 00:00:00


with  f as (
      select f.id, f.foo_value, f.foo_date,
             coalesce(lead(f.foo_date) over (partition by f.id order by f.foo_date) - interval '1 day', f.foo_date) as next_date
      from foo f
     )
select f.id, f.foo_value, gs.dte
from f left join lateral
     generate_series(f.foo_date, f.next_date, interval '1 day') gs(dte)
     on true


id
foo_value
dte


234534
100
2017-01-01 00:00:00

234534
200
2017-01-02 00:00:00

234534
300
2017-01-03 00:00:00

234534
300
2017-01-04 00:00:00

234534
300
2017-01-05 00:00:00

234534
300
2017-01-06 00:00:00

234534
300
2017-01-07 00:00:00

234534
180
2017-01-08 00:00:00

id	foo_value	foo_date	next_date
234534	100	2017-01-01	2017-01-01 00:00:00
234534	200	2017-01-02	2017-01-02 00:00:00
234534	300	2017-01-03	2017-01-07 00:00:00
234534	300	2017-01-04	2017-01-07 00:00:00
234534	300	2017-01-05	2017-01-07 00:00:00
234534	300	2017-01-06	2017-01-07 00:00:00
234534	300	2017-01-07	2017-01-07 00:00:00
234534	180	2017-01-08	2017-01-07 00:00:00