De-duping postgres date ranges with other date ranges for analysis

In my app, there is a table called schedules which have mixed types type: 'project', 'pto', 'sick' and have a date range date.

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:

  1. Explode all schedules as all_exploded_schedules using generate_series(schedule.lower, schedule.upper, '1 day') as date then tried to:

        select * from all_exploded_schedules a     left join all_exploded_schedules b on a.user_id = b.user_id and = 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 (date)

  2. Create a calendar table with every day from 2012 to 2038, with the date as 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 @> 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 @> 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 = c.day_id         WHERE = 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?