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 if not EXISTS issues (
  id int,
  created_at DATE,
  deleted_at DATE
);

INSERT INTO issues VALUES
(1, '2020-01-01', '2020-01-02'),
(2, '2020-01-02', '2020-01-05'),
(3, '2020-01-04', '2020-01-03'),
(4, '2020-01-04', '2020-01-07'),
(5, '2020-01-04', '2020-01-07');
5 rows affected

select gs.dte, count(*)
from issues i cross join lateral
     generate_series(i.created_at, i.deleted_at, interval '1 day') gs(dte)
group by gs.dte
order by gs.dte;


dte
count


2020-01-01 00:00:00+00
1

2020-01-02 00:00:00+00
2

2020-01-03 00:00:00+00
1

2020-01-04 00:00:00+00
3

2020-01-05 00:00:00+00
3

2020-01-06 00:00:00+00
2

2020-01-07 00:00:00+00
2


select gs.dte, count(i.id)
from (select min(created_at) as first_date, max(deleted_at) as last_date
      from issues i
     ) m cross join lateral
     generate_series(m.first_date, m.last_date, interval '1 day') gs(dte) left join
     issues i
     on gs.dte >= i.created_at and gs.dte <= i.deleted_at
group by gs.dte
order by gs.dte


dte
count


2020-01-01 00:00:00+00
1

2020-01-02 00:00:00+00
2

2020-01-03 00:00:00+00
1

2020-01-04 00:00:00+00
3

2020-01-05 00:00:00+00
3

2020-01-06 00:00:00+00
2

2020-01-07 00:00:00+00
2

dte	count
2020-01-01 00:00:00+00	1
2020-01-02 00:00:00+00	2
2020-01-03 00:00:00+00	1
2020-01-04 00:00:00+00	3
2020-01-05 00:00:00+00	3
2020-01-06 00:00:00+00	2
2020-01-07 00:00:00+00	2