explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TdJM

Settings
# exclusive inclusive rows x rows loops node
1. 790.101 7,396.611 ↑ 1.4 1,501,593 1

Merge Right Join (cost=1,839,037.76..1,887,915.02 rows=2,146,205 width=138) (actual time=6,438.521..7,396.611 rows=1,501,593 loops=1)

  • Output: plan_items.plan_row_uuid, project_plans.project_id, plan_items.project_plan_id, plan_rows.epic_id, plan_rows.activity_id, plan_rows.user_id, (generate_series.generate_series)::date, summary_vacations_by_dates.daily_hours, summary_vacations_by_dates.user_id, plan_items.utilization, NULL::double precision, plan_items.id, 'PlanItem'::text, COALESCE(non_working_intervals_by_dates.id, nw_intervals_by_dates_roles.id), plan_items.updated_at
  • Merge Cond: ((nw_intervals_by_dates_roles.account_id = projects.account_id) AND (nw_intervals_by_dates_roles.date = generate_series.generate_series) AND (nw_intervals_by_dates_roles.office_id = clients.office_id))
  • Buffers: shared hit=3075
2. 30.329 42.298 ↑ 1.0 58,766 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=35.173..42.298 rows=58,766 loops=1)

  • Output: nw_intervals_by_dates_roles.id, nw_intervals_by_dates_roles.office_id, nw_intervals_by_dates_roles.date, nw_intervals_by_dates_roles.account_id
  • Sort Key: nw_intervals_by_dates_roles.account_id, nw_intervals_by_dates_roles.date, nw_intervals_by_dates_roles.office_id
  • Sort Method: quicksort Memory: 4293kB
  • Buffers: shared hit=673
3. 11.969 11.969 ↑ 1.0 58,799 1

Seq Scan on public.non_working_intervals_by_dates nw_intervals_by_dates_roles (cost=0.00..1,260.99 rows=58,799 width=16) (actual time=0.015..11.969 rows=58,799 loops=1)

  • Output: nw_intervals_by_dates_roles.id, nw_intervals_by_dates_roles.office_id, nw_intervals_by_dates_roles.date, nw_intervals_by_dates_roles.account_id
  • Buffers: shared hit=673
4. 1,435.890 6,564.212 ↓ 1.2 1,501,593 1

Sort (cost=1,833,118.86..1,836,260.80 rows=1,256,775 width=110) (actual time=6,403.337..6,564.212 rows=1,501,593 loops=1)

  • Output: plan_items.plan_row_uuid, plan_items.project_plan_id, plan_items.utilization, plan_items.id, plan_items.updated_at, plan_rows.epic_id, plan_rows.activity_id, plan_rows.user_id, generate_series.generate_series, project_plans.project_id, projects.account_id, clients.office_id, summary_vacations_by_dates.daily_hours, summary_vacations_by_dates.user_id, non_working_intervals_by_dates.id
  • Sort Key: projects.account_id, generate_series.generate_series, clients.office_id
  • Sort Method: quicksort Memory: 146956kB
  • Buffers: shared hit=2402
5. 296.259 5,128.322 ↑ 1.4 870,252 1

Merge Left Join (cost=1,677,030.13..1,705,799.42 rows=1,256,775 width=110) (actual time=4,316.090..5,128.322 rows=870,252 loops=1)

  • Output: plan_items.plan_row_uuid, plan_items.project_plan_id, plan_items.utilization, plan_items.id, plan_items.updated_at, plan_rows.epic_id, plan_rows.activity_id, plan_rows.user_id, generate_series.generate_series, project_plans.project_id, projects.account_id, clients.office_id, summary_vacations_by_dates.daily_hours, summary_vacations_by_dates.user_id, non_working_intervals_by_dates.id
  • Merge Cond: ((projects.account_id = non_working_intervals_by_dates.account_id) AND (generate_series.generate_series = non_working_intervals_by_dates.date) AND (plan_rows.user_id = non_working_intervals_by_dates.user_id))
  • Buffers: shared hit=2402
6. 382.973 4,768.742 ↑ 1.4 870,234 1

Merge Left Join (cost=1,671,111.23..1,689,418.84 rows=1,256,775 width=106) (actual time=4,260.314..4,768.742 rows=870,234 loops=1)

  • Output: plan_items.plan_row_uuid, plan_items.project_plan_id, plan_items.utilization, plan_items.id, plan_items.updated_at, plan_rows.epic_id, plan_rows.activity_id, plan_rows.user_id, generate_series.generate_series, project_plans.project_id, projects.account_id, clients.office_id, summary_vacations_by_dates.daily_hours, summary_vacations_by_dates.user_id
  • Merge Cond: ((projects.account_id = summary_vacations_by_dates.account_id) AND (generate_series.generate_series = summary_vacations_by_dates.date) AND (plan_rows.user_id = summary_vacations_by_dates.user_id))
  • Buffers: shared hit=1729
7. 3,140.178 4,223.173 ↑ 1.4 870,234 1

Sort (cost=1,086,921.64..1,090,063.58 rows=1,256,775 width=70) (actual time=4,104.225..4,223.173 rows=870,234 loops=1)

  • Output: plan_items.plan_row_uuid, plan_items.project_plan_id, plan_items.utilization, plan_items.id, plan_items.updated_at, plan_rows.epic_id, plan_rows.activity_id, plan_rows.user_id, generate_series.generate_series, project_plans.project_id, projects.account_id, clients.office_id
  • Sort Key: projects.account_id, generate_series.generate_series, plan_rows.user_id
  • Sort Method: quicksort Memory: 146953kB
  • Buffers: shared hit=1410
8. 199.722 1,082.995 ↑ 1.4 870,234 1

Nested Loop (cost=2,043.15..959,602.20 rows=1,256,775 width=70) (actual time=32.577..1,082.995 rows=870,234 loops=1)

  • Output: plan_items.plan_row_uuid, plan_items.project_plan_id, plan_items.utilization, plan_items.id, plan_items.updated_at, plan_rows.epic_id, plan_rows.activity_id, plan_rows.user_id, generate_series.generate_series, project_plans.project_id, projects.account_id, clients.office_id
  • Buffers: shared hit=1410
9. 20.747 149.407 ↓ 1.0 52,419 1

Hash Left Join (cost=2,043.14..4,453.19 rows=50,271 width=70) (actual time=32.543..149.407 rows=52,419 loops=1)

  • Output: plan_items.plan_row_uuid, plan_items.project_plan_id, plan_items.utilization, plan_items.id, plan_items.updated_at, plan_items.start_date, plan_items.end_date, plan_rows.epic_id, plan_rows.activity_id, plan_rows.user_id, project_plans.project_id, projects.account_id, clients.office_id
  • Inner Unique: true
  • Hash Cond: (projects.client_id = clients.id)
  • Buffers: shared hit=1410
10. 20.467 127.612 ↓ 1.0 52,419 1

Hash Left Join (cost=1,981.15..4,258.92 rows=50,271 width=70) (actual time=31.486..127.612 rows=52,419 loops=1)

  • Output: plan_items.plan_row_uuid, plan_items.project_plan_id, plan_items.utilization, plan_items.id, plan_items.updated_at, plan_items.start_date, plan_items.end_date, plan_rows.epic_id, plan_rows.activity_id, plan_rows.user_id, project_plans.project_id, projects.client_id, projects.account_id
  • Inner Unique: true
  • Hash Cond: (project_plans.project_id = projects.id)
  • Buffers: shared hit=1389
11. 20.570 103.424 ↓ 1.0 52,419 1

Hash Left Join (cost=1,756.21..3,901.92 rows=50,271 width=62) (actual time=27.743..103.424 rows=52,419 loops=1)

  • Output: plan_items.plan_row_uuid, plan_items.project_plan_id, plan_items.utilization, plan_items.id, plan_items.updated_at, plan_items.start_date, plan_items.end_date, plan_rows.epic_id, plan_rows.activity_id, plan_rows.user_id, project_plans.project_id
  • Inner Unique: true
  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
  • Buffers: shared hit=1292
12. 37.386 80.297 ↓ 1.0 52,419 1

Hash Join (cost=1,557.89..3,571.54 rows=50,271 width=58) (actual time=25.145..80.297 rows=52,419 loops=1)

  • Output: plan_items.plan_row_uuid, plan_items.project_plan_id, plan_items.utilization, plan_items.id, plan_items.updated_at, plan_items.start_date, plan_items.end_date, plan_rows.epic_id, plan_rows.activity_id, plan_rows.user_id
  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
  • Buffers: shared hit=1188
13. 17.889 17.889 ↓ 1.0 52,422 1

Seq Scan on public.plan_items (cost=0.00..1,314.46 rows=52,394 width=46) (actual time=0.022..17.889 rows=52,422 loops=1)

  • Output: plan_items.utilization, plan_items.created_at, plan_items.updated_at, plan_items.project_plan_id, plan_items.start_date, plan_items.end_date, plan_items.plan_row_uuid, plan_items.id, plan_items.uuid
  • Filter: (plan_items.utilization > '0'::numeric)
  • Rows Removed by Filter: 255
  • Buffers: shared hit=656
14. 13.579 25.022 ↓ 1.0 46,641 1

Hash (cost=998.45..998.45 rows=44,755 width=28) (actual time=25.021..25.022 rows=46,641 loops=1)

  • Output: plan_rows.epic_id, plan_rows.activity_id, plan_rows.user_id, plan_rows.uuid
  • Buckets: 65536 Batches: 1 Memory Usage: 3035kB
  • Buffers: shared hit=532
15. 11.443 11.443 ↓ 1.0 46,641 1

Seq Scan on public.plan_rows (cost=0.00..998.45 rows=44,755 width=28) (actual time=0.009..11.443 rows=46,641 loops=1)

  • Output: plan_rows.epic_id, plan_rows.activity_id, plan_rows.user_id, plan_rows.uuid
  • Filter: ((plan_rows.user_id IS NOT NULL) OR (plan_rows.activity_id IS NOT NULL))
  • Rows Removed by Filter: 4
  • Buffers: shared hit=532
16. 0.796 2.557 ↓ 1.0 2,819 1

Hash (cost=163.10..163.10 rows=2,818 width=8) (actual time=2.557..2.557 rows=2,819 loops=1)

  • Output: project_plans.project_id, project_plans.id
  • Buckets: 4096 Batches: 1 Memory Usage: 143kB
  • Buffers: shared hit=104
17. 1.761 1.761 ↓ 1.0 2,819 1

Seq Scan on public.project_plans (cost=0.00..163.10 rows=2,818 width=8) (actual time=0.015..1.761 rows=2,819 loops=1)

  • Output: project_plans.project_id, project_plans.id
  • Filter: project_plans.active
  • Rows Removed by Filter: 3091
  • Buffers: shared hit=104
18. 1.779 3.721 ↑ 1.0 5,686 1

Hash (cost=153.86..153.86 rows=5,686 width=12) (actual time=3.721..3.721 rows=5,686 loops=1)

  • Output: projects.id, projects.client_id, projects.account_id
  • Buckets: 8192 Batches: 1 Memory Usage: 309kB
  • Buffers: shared hit=97
19. 1.942 1.942 ↑ 1.0 5,686 1

Seq Scan on public.projects (cost=0.00..153.86 rows=5,686 width=12) (actual time=0.009..1.942 rows=5,686 loops=1)

  • Output: projects.id, projects.client_id, projects.account_id
  • Buffers: shared hit=97
20. 0.508 1.048 ↑ 1.0 1,822 1

Hash (cost=39.22..39.22 rows=1,822 width=8) (actual time=1.047..1.048 rows=1,822 loops=1)

  • Output: clients.id, clients.office_id
  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
  • Buffers: shared hit=21
21. 0.540 0.540 ↑ 1.0 1,822 1

Seq Scan on public.clients (cost=0.00..39.22 rows=1,822 width=8) (actual time=0.008..0.540 rows=1,822 loops=1)

  • Output: clients.id, clients.office_id
  • Buffers: shared hit=21
22. 733.866 733.866 ↑ 1.5 17 52,419

Function Scan on pg_catalog.generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.005..0.014 rows=17 loops=52,419)

  • Output: generate_series.generate_series
  • Function Call: generate_series((plan_items.start_date)::timestamp without time zone, (plan_items.end_date - '1 day'::interval), '1 day'::interval)
  • Filter: (date_part('dow'::text, generate_series.generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 6
23. 57.658 162.596 ↑ 16.5 34,338 1

Sort (cost=584,189.58..585,604.08 rows=565,800 width=44) (actual time=156.080..162.596 rows=34,338 loops=1)

  • Output: summary_vacations_by_dates.daily_hours, summary_vacations_by_dates.user_id, summary_vacations_by_dates.date, summary_vacations_by_dates.account_id
  • Sort Key: summary_vacations_by_dates.account_id, summary_vacations_by_dates.date, summary_vacations_by_dates.user_id
  • Sort Method: quicksort Memory: 2705kB
  • Buffers: shared hit=319
24. 7.016 104.938 ↑ 19.1 29,556 1

Subquery Scan on summary_vacations_by_dates (cost=515,982.59..530,127.59 rows=565,800 width=44) (actual time=72.731..104.938 rows=29,556 loops=1)

  • Output: summary_vacations_by_dates.daily_hours, summary_vacations_by_dates.user_id, summary_vacations_by_dates.date, summary_vacations_by_dates.account_id
  • Buffers: shared hit=319
25. 41.735 97.922 ↑ 19.1 29,556 1

HashAggregate (cost=515,982.59..524,469.59 rows=565,800 width=52) (actual time=72.730..97.922 rows=29,556 loops=1)

  • Output: sum(vacations.daily_hours), (generate_series_1.generate_series)::date, staff_memberships.user_id, staff_memberships.account_id, generate_series_1.generate_series
  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Buffers: shared hit=319
26. 13.008 56.187 ↑ 142.6 29,683 1

Hash Join (cost=136.06..473,655.25 rows=4,232,734 width=21) (actual time=1.813..56.187 rows=29,683 loops=1)

  • Output: staff_memberships.user_id, staff_memberships.account_id, generate_series_1.generate_series, vacations.daily_hours
  • Inner Unique: true
  • Hash Cond: (vacations.staff_membership_id = staff_memberships.id)
  • Join Filter: ((generate_series_1.generate_series >= staff_memberships.joined_at) AND ((staff_memberships.archived_at IS NULL) OR (generate_series_1.generate_series <= staff_memberships.archived_at)))
  • Rows Removed by Join Filter: 1908
  • Buffers: shared hit=319
27. 17.828 41.428 ↑ 661.8 31,591 1

Nested Loop (cost=0.01..418,621.08 rows=20,907,000 width=17) (actual time=0.026..41.428 rows=31,591 loops=1)

  • Output: vacations.daily_hours, vacations.staff_membership_id, generate_series_1.generate_series
  • Buffers: shared hit=272
28. 2.693 2.693 ↑ 1.0 20,907 1

Seq Scan on public.vacations (cost=0.00..481.07 rows=20,907 width=17) (actual time=0.014..2.693 rows=20,907 loops=1)

  • Output: vacations.id, vacations.staff_membership_id, vacations.start_date, vacations.end_date, vacations.creator_id, vacations.type, vacations.created_at, vacations.updated_at, vacations.daily_hours, vacations.sync_uid
  • Buffers: shared hit=272
29. 20.907 20.907 ↑ 500.0 2 20,907

Function Scan on pg_catalog.generate_series generate_series_1 (cost=0.01..10.01 rows=1,000 width=8) (actual time=0.001..0.001 rows=2 loops=20,907)

  • Output: generate_series_1.generate_series
  • Function Call: generate_series((vacations.start_date)::timestamp with time zone, (vacations.end_date)::timestamp with time zone, '1 day'::interval)
30. 0.927 1.751 ↑ 1.0 3,958 1

Hash (cost=86.58..86.58 rows=3,958 width=20) (actual time=1.751..1.751 rows=3,958 loops=1)

  • Output: staff_memberships.user_id, staff_memberships.account_id, staff_memberships.id, staff_memberships.joined_at, staff_memberships.archived_at
  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
  • Buffers: shared hit=47
31. 0.824 0.824 ↑ 1.0 3,958 1

Seq Scan on public.staff_memberships (cost=0.00..86.58 rows=3,958 width=20) (actual time=0.007..0.824 rows=3,958 loops=1)

  • Output: staff_memberships.user_id, staff_memberships.account_id, staff_memberships.id, staff_memberships.joined_at, staff_memberships.archived_at
  • Buffers: shared hit=47
32. 47.403 63.321 ↓ 1.0 60,807 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=55.770..63.321 rows=60,807 loops=1)

  • Output: non_working_intervals_by_dates.id, non_working_intervals_by_dates.user_id, non_working_intervals_by_dates.date, non_working_intervals_by_dates.account_id
  • Sort Key: non_working_intervals_by_dates.account_id, non_working_intervals_by_dates.date, non_working_intervals_by_dates.user_id
  • Sort Method: quicksort Memory: 4293kB
  • Buffers: shared hit=673
33. 15.918 15.918 ↑ 1.0 58,799 1

Seq Scan on public.non_working_intervals_by_dates (cost=0.00..1,260.99 rows=58,799 width=16) (actual time=0.017..15.918 rows=58,799 loops=1)

  • Output: non_working_intervals_by_dates.id, non_working_intervals_by_dates.user_id, non_working_intervals_by_dates.date, non_working_intervals_by_dates.account_id
  • Buffers: shared hit=673
Planning time : 2.376 ms
Execution time : 7,615.845 ms