Recognize if not enough resources during timeframe, PostgreSQL

Language: Layout:

--PostgreSQL 9.5
--'\\' is a delimiter

create table groups (
  id              serial    primary key,
  title           text      not null,
  start_time      timestamp not null,
  end_time        timestamp not null,
  required_people int       not null
);

create table people_assignments (
  id              serial    primary key,
  user_id         int       not null, -- references ...
  group_id        int       not null references groups(id),
  start_time      timestamp not null,
  end_time        timestamp not null
);

insert into groups (title, start_time, end_time, required_people)
values ('given timeframe', '2017-03-30 08:00', '2017-03-30 12:00', 4);

insert into people_assignments (user_id, group_id, start_time, end_time)
values (1, currval('groups_id_seq'), '2017-03-30 09:00', '2017-03-30 11:00'),
       (2, currval('groups_id_seq'), '2017-03-30 09:00', '2017-03-30 12:00'),
       (3, currval('groups_id_seq'), '2017-03-30 08:00', '2017-03-30 12:00'),
       (4, currval('groups_id_seq'), '2017-03-30 08:00', '2017-03-30 10:00'),
       (1, currval('groups_id_seq'), '2017-03-30 08:00', '2017-03-30 09:00');

-- 1          +---------+
-- 2          +--------------+
-- 3     +-------------------+
-- 4     +---------+
-- 5     +----+
-- time -+----+----+----+----+-
--       8    9   10   11   12

with recursive r as (
    select 0 iteration,
           id group_id,
           array[]::int[] used_assignment_ids,
           jsonb_build_object(tsrange(start_time, end_time), required_people) required_people_per_time_range
    from   groups
    where  required_people > 0
    and    id = 1 -- query parameter
  union all
    select r.iteration + 1,
           r.group_id,
           r.used_assignment_ids || a.assignment_id,
           d.required_people_per_time_range
    from   r
    cross join lateral (
      select     a.id assignment_id, tsrange(start_time, end_time) time_range
      from       people_assignments a
      cross join (select key::tsrange time_range from jsonb_each(r.required_people_per_time_range)) j
      where      a.group_id = r.group_id
      and        a.id <> ALL (r.used_assignment_ids)
      and        tsrange(start_time, end_time) && j.time_range
      limit      1
    ) a
    cross join lateral (
      select jsonb_object_agg(u.time_range, u.required_people) required_people_per_time_range
      from   (select key::tsrange time_range, value::int required_people
              from   jsonb_each_text(r.required_people_per_time_range)) j
      cross join lateral (
        select u time_range, j.required_people - case when u && a.time_range then 1 else 0 end required_people
        from   unnest(case
                 when j.time_range @> a.time_range
                 then array[tsrange(lower(j.time_range), lower(a.time_range)), a.time_range, tsrange(upper(a.time_range), upper(j.time_range))]
                 when j.time_range && a.time_range
                 then array[j.time_range * a.time_range, j.time_range - a.time_range]
                 else array[j.time_range]
               end) u
        where not isempty(u)
      ) u
    ) d
),
l as (
  select   group_id, required_people_per_time_range
  from     r
  order by iteration desc
  limit    1
)
select     l.group_id, lower(time_range) start_time, upper(time_range) end_time, missing_required_people
from       l
cross join lateral (
  select key::tsrange time_range, value::int missing_required_people
  from   jsonb_each_text(l.required_people_per_time_range)
) j
where      j.missing_required_people > 0;


absolute service time: 0,48 sec