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

select version();


version


PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


--
--  Original table design from the OP
--

CREATE TABLE event_entries
(
  id INT NOT NULL,
  start_date timestamp with time zone NOT NULL,
  end_date  timestamp with time zone,
  state text not null
);
--
--  OP's data
--

INSERT INTO event_entries VALUES
(1, '2020-04-09 21:34:00.000', '2020-04-13 15:46:52.000',  'green'),
(1, '2020-04-13 15:46:52.000', '2020-04-16 15:46:52.000', 'red'),
(1, '2020-04-16 15:46:52.000', null,  'yellow'),

(2, '2020-04-09 20:33:00.000', '2020-04-16 15:46:52.000',  'green'),              
(2, '2020-04-16 15:46:52.000', '2020-04-21 15:46:52.000', 'red'),
(2, '2020-04-21 15:46:52.000', null, 'yellow'),

(3, '2020-04-09 21:34:00.000', '2020-04-09 22:34:00.000', 'green'),
(3, '2020-04-09 22:34:00.000', '2020-04-09 23:36:00.000', 'red'),
(3, '2020-04-09 23:36:00.000', '2020-04-09 23:38:00.000', 'yellow'),
(3, '2020-04-09 23:38:00.000', null, 'red');
10 rows affected

select serie.day as time,
 a.state,
 count(a.start_date) as total,
 a.id
 from (
 SELECT date_series::date AS day
    FROM generate_series(
        '2020-04-09 00:00:00.000'::date,
        '2020-04-24 15:22:22.000',
        '1 day'
    ) AS date_series
 ) as serie
 left join event_entries a on a.start_date::date = serie.day::date 
 group by serie.day, a.state, a.id order by time


time
state
total
id


2020-04-09
green
1
1

2020-04-09
green
1
2

2020-04-09
green
1
3

2020-04-09
red
2
3

2020-04-09
yellow
1
3

2020-04-10
null
0
null

2020-04-11
null
0
null

2020-04-12
null
0
null

2020-04-13
red
1
1

2020-04-14
null
0
null

2020-04-15
null
0
null

2020-04-16
red
1
2

2020-04-16
yellow
1
1

2020-04-17
null
0
null

2020-04-18
null
0
null

2020-04-19
null
0
null

2020-04-20
null
0
null

2020-04-21
yellow
1
2

2020-04-22
null
0
null

2020-04-23
null
0
null

2020-04-24
null
0
null

time	state	total	id
2020-04-09	green	1	1
2020-04-09	green	1	2
2020-04-09	green	1	3
2020-04-09	red	2	3
2020-04-09	yellow	1	3
2020-04-10	null	0	null
2020-04-11	null	0	null
2020-04-12	null	0	null
2020-04-13	red	1	1
2020-04-14	null	0	null
2020-04-15	null	0	null
2020-04-16	red	1	2
2020-04-16	yellow	1	1
2020-04-17	null	0	null
2020-04-18	null	0	null
2020-04-19	null	0	null
2020-04-20	null	0	null
2020-04-21	yellow	1	2
2020-04-22	null	0	null
2020-04-23	null	0	null
2020-04-24	null	0	null