gistfile1.txt

explain analyse
  SELECT
    SUM(vacations.daily_hours) AS daily_hours,
    generate_series :: DATE AS date,
    staff_memberships.user_id,
    staff_memberships.account_id
  FROM vacations
    LEFT JOIN staff_memberships ON staff_memberships.id = vacations.staff_membership_id

    CROSS JOIN generate_series(
      LEAST(staff_memberships.joined_at, vacations.start_date),
      LEAST(COALESCE(staff_memberships.archived_at, vacations.end_date), vacations.end_date),
      INTERVAL '1 day'
    )

    CROSS JOIN generate_series(vacations.start_date, vacations.end_date, INTERVAL '1 day')

    GROUP BY generate_series, staff_memberships.user_id, staff_memberships.account_id;

QUERY PLAN
GroupAggregate  (cost=2105498.41..2257807.41 rows=325600 width=21) (actual time=60647.090..89493.359 rows=8354107 loops=1)
"  Group Key: generate_series.generate_series, staff_memberships.user_id, staff_memberships.account_id"
  ->  Sort  (cost=2105498.41..2134983.41 rows=11794000 width=21) (actual time=60647.057..73250.975 rows=28514644 loops=1)
"        Sort Key: generate_series.generate_series, staff_memberships.user_id, staff_memberships.account_id"
        Sort Method: external merge  Disk: 858720kB
        ->  Nested Loop  (cost=113.61..236459.72 rows=11794000 width=21) (actual time=2.011..24818.980 rows=28514644 loops=1)
              ->  Hash Left Join  (cost=113.60..579.71 rows=11794 width=33) (actual time=1.990..25.547 rows=11795 loops=1)
                    Hash Cond: (vacations.staff_membership_id = staff_memberships.id)
                    ->  Seq Scan on vacations  (cost=0.00..303.94 rows=11794 width=17) (actual time=0.018..6.488 rows=11795 loops=1)
                    ->  Hash  (cost=79.38..79.38 rows=2738 width=24) (actual time=1.951..1.951 rows=2738 loops=1)
                          Buckets: 4096  Batches: 1  Memory Usage: 179kB
                          ->  Seq Scan on staff_memberships  (cost=0.00..79.38 rows=2738 width=24) (actual time=0.005..1.042 rows=2738 loops=1)
              ->  Function Scan on generate_series  (cost=0.01..10.01 rows=1000 width=8) (actual time=0.384..1.027 rows=2418 loops=11795)
Planning time: 0.608 ms
Execution time: 91362.175 ms