In my app, there is a table called
schedules which have mixed types
'sick' and have a date range
CREATE TABLE public.schedules ( id int8 NOT NULL DEFAULT next_id(), user_id int8 NOT NULL, project_id int8 NULL, "date" daterange NOT NULL, "type" enum_schedules_type NULL DEFAULT 'project'::enum_schedules_type, "time" int4 NOT NULL, CONSTRAINT schedules_pkey PRIMARY KEY (id), CONSTRAINT schedules_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.project(id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT schedules_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE ON UPDATE CASCADE );
Each user also has an office, which can have holidays:
CREATE TABLE public.office_holiday ( id int8 NOT NULL DEFAULT next_id(), office_id int8 NULL, "date" daterange NOT NULL, CONSTRAINT calendar_events_pkey PRIMARY KEY (id), CONSTRAINT calendar_office_id_fkey FOREIGN KEY (office_id) REFERENCES public.offices(id) ON UPDATE CASCADE );
CREATE TABLE public.users ( id int8 NOT NULL DEFAULT next_id(), office_id int8 NULL, CONSTRAINT users_pkey PRIMARY KEY (id), CONSTRAINT users_office_id_fkey FOREIGN KEY (office_id) REFERENCES public.offices(id) ON DELETE SET NULL ON UPDATE CASCADE, );
What I’m attempting to do is get a list of days per user per project based on
schedules.type = 'project' but de-duplicated these days based on overlapping date ranges of schedule of
schedule.type = 'pto' or schedule.type = 'sick' and/or if the current row’s user has a date overlap from office_holiday date range.
Things I have tried:
Explode all schedules as
generate_series(schedule.lower, schedule.upper, '1 day') as datethen tried to:
select * from all_exploded_schedules a left join all_exploded_schedules b on a.user_id = b.user_id and a.date = b.date and ( b.type = 'sick' or b.type = 'pto' ) where a.type = 'project'
This is horribly slow, which I assume is due to not having an index to join on (
calendartable with every day from 2012 to 2038, with the
dateas the PK, so I have an index to join schedules on. I then tried to use the range-type overlap operator to hopefully help with the speed a bit:
select * FROM calendar c LEFT JOIN schedules s ON s.date @> c.day_id AND s.type = 'project' LEFT JOIN LATERAL ( SELECT * FROM schedules s2 WHERE s2.user_id = s.user_id AND (s2.type = 'pto' OR s2.type = 'sick') AND s2.date @> c.day_id AND s2.approved = true LIMIT 1 ) a ON true LEFT JOIN LATERAL ( select * FROM users u LEFT JOIN office_holidays_exploded ohe ON u.office_id = ohe.office_id AND ohe.date = c.day_id WHERE u.id = s.user_id ) b ON true
which seems to accomplish all the objectives, but is still pretty slow, especially with no filtering on the table.
Is there a better way to approach this?