Recognize if not enough resources during timeframe, PostgreSQL

Recognize if not enough resources during timeframe

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

select     g.id group_id, i start_time, i + interval '5 minutes' end_time, g.required_people - count(a.id)
from       groups g
cross join generate_series(g.start_time, g.end_time, interval '5 minutes') i
left join  people_assignments a on a.group_id = g.id
where      tsrange(a.start_time, a.end_time) && tsrange(i, i + interval '5 minutes')
group by   g.id, i
having     g.required_people - count(a.id) > 0
order by   g.id, i


absolute service time: 0,47 sec

	group_id	start_time	end_time	?column?
1	1	30.03.2017 08:00:00	30.03.2017 08:05:00	1
2	1	30.03.2017 08:05:00	30.03.2017 08:10:00	1
3	1	30.03.2017 08:10:00	30.03.2017 08:15:00	1
4	1	30.03.2017 08:15:00	30.03.2017 08:20:00	1
5	1	30.03.2017 08:20:00	30.03.2017 08:25:00	1
6	1	30.03.2017 08:25:00	30.03.2017 08:30:00	1
7	1	30.03.2017 08:30:00	30.03.2017 08:35:00	1
8	1	30.03.2017 08:35:00	30.03.2017 08:40:00	1
9	1	30.03.2017 08:40:00	30.03.2017 08:45:00	1
10	1	30.03.2017 08:45:00	30.03.2017 08:50:00	1
11	1	30.03.2017 08:50:00	30.03.2017 08:55:00	1
12	1	30.03.2017 08:55:00	30.03.2017 09:00:00	1
13	1	30.03.2017 10:00:00	30.03.2017 10:05:00	1
14	1	30.03.2017 10:05:00	30.03.2017 10:10:00	1
15	1	30.03.2017 10:10:00	30.03.2017 10:15:00	1
16	1	30.03.2017 10:15:00	30.03.2017 10:20:00	1
17	1	30.03.2017 10:20:00	30.03.2017 10:25:00	1
18	1	30.03.2017 10:25:00	30.03.2017 10:30:00	1
19	1	30.03.2017 10:30:00	30.03.2017 10:35:00	1
20	1	30.03.2017 10:35:00	30.03.2017 10:40:00	1
21	1	30.03.2017 10:40:00	30.03.2017 10:45:00	1
22	1	30.03.2017 10:45:00	30.03.2017 10:50:00	1
23	1	30.03.2017 10:50:00	30.03.2017 10:55:00	1
24	1	30.03.2017 10:55:00	30.03.2017 11:00:00	1
25	1	30.03.2017 11:00:00	30.03.2017 11:05:00	2
26	1	30.03.2017 11:05:00	30.03.2017 11:10:00	2
27	1	30.03.2017 11:10:00	30.03.2017 11:15:00	2
28	1	30.03.2017 11:15:00	30.03.2017 11:20:00	2
29	1	30.03.2017 11:20:00	30.03.2017 11:25:00	2
30	1	30.03.2017 11:25:00	30.03.2017 11:30:00	2
31	1	30.03.2017 11:30:00	30.03.2017 11:35:00	2
32	1	30.03.2017 11:35:00	30.03.2017 11:40:00	2
33	1	30.03.2017 11:40:00	30.03.2017 11:45:00	2
34	1	30.03.2017 11:45:00	30.03.2017 11:50:00	2
35	1	30.03.2017 11:50:00	30.03.2017 11:55:00	2
36	1	30.03.2017 11:55:00	30.03.2017 12:00:00	2