Postgres 14

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 event_entries (
  id         int NOT NULL,
  start_date timestamptz NOT NULL,
  end_date   timestamptz,
  state      text NOT NULL
);

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

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

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

CREATE TABLE



INSERT 0 10


-- basic solution
SELECT (d.the_day AT TIME ZONE 'UTC')::date AS "day"  -- corrsponding UTC days
     , a.state
     , count(*) AS ct
     , array_agg(i.id) AS ids
FROM   generate_series(timestamptz '2020-04-09 00:00:00+0'  -- UTC timestamps
                     , timestamptz '2020-04-24 15:22:22+0'
                     , interval    '1 day') d(the_day)
CROSS  JOIN (SELECT DISTINCT id FROM event_entries) AS i  -- there are faster ways
LEFT   JOIN LATERAL (
   SELECT state
   FROM   event_entries a
   WHERE  a.id = i.id
   AND    a.start_date <  d.the_day + interval '1 day'
   AND   (a.end_date   >= d.the_day OR a.end_date IS NULL)
   ORDER  BY a.end_date DESC  -- NULLS FIRST is the default we need
   LIMIT  1
   ) a ON true
GROUP  BY d.the_day, a.state
ORDER  BY d.the_day, a.state;


day
state
ct
ids


2020-04-09
green
2
{2,1}

2020-04-09
red
1
{3}

2020-04-10
green
2
{2,1}

2020-04-10
red
1
{3}

2020-04-11
green
2
{2,1}

2020-04-11
red
1
{3}

2020-04-12
green
2
{2,1}

2020-04-12
red
1
{3}

2020-04-13
green
1
{2}

2020-04-13
red
2
{3,1}

2020-04-14
green
1
{2}

2020-04-14
red
2
{3,1}

2020-04-15
green
1
{2}

2020-04-15
red
2
{3,1}

2020-04-16
red
2
{3,2}

2020-04-16
yellow
1
{1}

2020-04-17
red
2
{3,2}

2020-04-17
yellow
1
{1}

2020-04-18
red
2
{3,2}

2020-04-18
yellow
1
{1}

2020-04-19
red
2
{3,2}

2020-04-19
yellow
1
{1}

2020-04-20
red
2
{3,2}

2020-04-20
yellow
1
{1}

2020-04-21
red
1
{3}

2020-04-21
yellow
2
{2,1}

2020-04-22
red
1
{3}

2020-04-22
yellow
2
{2,1}

2020-04-23
red
1
{3}

2020-04-23
yellow
2
{2,1}

2020-04-24
red
1
{3}

2020-04-24
yellow
2
{2,1}



SELECT 32


-- with one row for every day and state
SELECT (the_day AT TIME ZONE 'UTC')::date AS "day"  -- corrsponding UTC days
     , state
     , COALESCE(e.ct, 0) AS total
     , COALESCE(e.ids, '{}') AS ids
FROM  (
   SELECT d.the_day, a.state, count(*) AS ct, array_agg(i.id) AS ids
   FROM   generate_series(timestamptz '2020-04-09 00:00:00+0'  -- UTC timestamps
                        , timestamptz '2020-04-24 15:22:22+0'
                        , interval    '1 day') d(the_day)
   CROSS  JOIN (SELECT DISTINCT id FROM event_entries) AS i  -- there are faster ways
   LEFT   JOIN LATERAL (
      SELECT state
      FROM   event_entries a
      WHERE  a.id = i.id
      AND    a.start_date <  d.the_day + interval '1 day'
      AND   (a.end_date   >= d.the_day OR a.end_date IS NULL)
      ORDER  BY a.end_date DESC  -- NULLS FIRST is the default we need
      LIMIT  1
      ) a ON true
   GROUP  BY d.the_day, a.state
   ) e
RIGHT JOIN (
   generate_series(timestamptz '2020-04-09 00:00:00+0'  -- UTC timestamps
                 , timestamptz '2020-04-24 15:22:22+0'
                 , interval    '1 day') d(the_day)
   CROSS JOIN (SELECT DISTINCT state FROM event_entries) s  -- there are faster ways
   ) e0  USING (the_day, state)
ORDER  BY the_day, state;


day
state
total
ids


2020-04-09
green
2
{2,1}

2020-04-09
red
1
{3}

2020-04-09
yellow
0
{}

2020-04-10
green
2
{2,1}

2020-04-10
red
1
{3}

2020-04-10
yellow
0
{}

2020-04-11
green
2
{2,1}

2020-04-11
red
1
{3}

2020-04-11
yellow
0
{}

2020-04-12
green
2
{2,1}

2020-04-12
red
1
{3}

2020-04-12
yellow
0
{}

2020-04-13
green
1
{2}

2020-04-13
red
2
{3,1}

2020-04-13
yellow
0
{}

2020-04-14
green
1
{2}

2020-04-14
red
2
{3,1}

2020-04-14
yellow
0
{}

2020-04-15
green
1
{2}

2020-04-15
red
2
{3,1}

2020-04-15
yellow
0
{}

2020-04-16
green
0
{}

2020-04-16
red
2
{3,2}

2020-04-16
yellow
1
{1}

2020-04-17
green
0
{}

2020-04-17
red
2
{3,2}

2020-04-17
yellow
1
{1}

2020-04-18
green
0
{}

2020-04-18
red
2
{3,2}

2020-04-18
yellow
1
{1}

2020-04-19
green
0
{}

2020-04-19
red
2
{3,2}

2020-04-19
yellow
1
{1}

2020-04-20
green
0
{}

2020-04-20
red
2
{3,2}

2020-04-20
yellow
1
{1}

2020-04-21
green
0
{}

2020-04-21
red
1
{3}

2020-04-21
yellow
2
{2,1}

2020-04-22
green
0
{}

2020-04-22
red
1
{3}

2020-04-22
yellow
2
{2,1}

2020-04-23
green
0
{}

2020-04-23
red
1
{3}

2020-04-23
yellow
2
{2,1}

2020-04-24
green
0
{}

2020-04-24
red
1
{3}

2020-04-24
yellow
2
{2,1}



SELECT 48

day	state	ct	ids
2020-04-09	green	2	{2,1}
2020-04-09	red	1	{3}
2020-04-10	green	2	{2,1}
2020-04-10	red	1	{3}
2020-04-11	green	2	{2,1}
2020-04-11	red	1	{3}
2020-04-12	green	2	{2,1}
2020-04-12	red	1	{3}
2020-04-13	green	1	{2}
2020-04-13	red	2	{3,1}
2020-04-14	green	1	{2}
2020-04-14	red	2	{3,1}
2020-04-15	green	1	{2}
2020-04-15	red	2	{3,1}
2020-04-16	red	2	{3,2}
2020-04-16	yellow	1	{1}
2020-04-17	red	2	{3,2}
2020-04-17	yellow	1	{1}
2020-04-18	red	2	{3,2}
2020-04-18	yellow	1	{1}
2020-04-19	red	2	{3,2}
2020-04-19	yellow	1	{1}
2020-04-20	red	2	{3,2}
2020-04-20	yellow	1	{1}
2020-04-21	red	1	{3}
2020-04-21	yellow	2	{2,1}
2020-04-22	red	1	{3}
2020-04-22	yellow	2	{2,1}
2020-04-23	red	1	{3}
2020-04-23	yellow	2	{2,1}
2020-04-24	red	1	{3}
2020-04-24	yellow	2	{2,1}