explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2zMC

Settings
# exclusive inclusive rows x rows loops node
1. 116.224 48,891.394 ↓ 7.6 595,625 1

Subquery Scan on report_items_live (cost=2,422,745.03..2,430,791.07 rows=78,498 width=152) (actual time=48,103.952..48,891.394 rows=595,625 loops=1)

2. 603.279 48,775.170 ↓ 7.6 595,625 1

GroupAggregate (cost=2,422,745.03..2,430,006.09 rows=78,498 width=208) (actual time=48,103.950..48,775.170 rows=595,625 loops=1)

  • Group Key: projects.account_id, projects.id, clients.id, clients.office_id, rates_with_dates.rate_card_id, ""*SELECT* 1"".project_plan_id, ""*SELECT* 1"".epic_id, (COALESCE(pa.activity_id, ""*SELECT* 1"".activity_id, staff_membership_activity_links.activity_id)), ""*SELECT* 1"".user_id, ""*SELECT* 1"".date, (CASE WHEN (('PlanItem'::text) = 'TimeLog'::text) THEN (NULL::double precision) WHEN (((""*SELECT* 1"".vacation_user_id)::double precision) IS NOT NULL) THEN GREATEST(((((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision - COALESCE(((""*SELECT* 1"".vacation_hours)::double precision), (staff_membership_activity_links.capacity)::double precision, '0'::double precision)) * ((""*SELECT* 1"".utilization)::double precision)) / '100'::double precision), '0'::double precision) WHEN (""*SELECT* 1"".user_id IS NULL) THEN (((accounts_1.default_capacity)::double precision * ((""*SELECT* 1"".utilization)::double precision)) / '100'::double precision) ELSE (((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision * ((""*SELECT* 1"".utilization)::double precision)) / '100'::double precision) END), ('PlanItem'::text), ""*SELECT* 1"".reportable_id, ""*SELECT* 1"".plan_row_uuid, rates_with_dates.currency, (COALESCE(exchange_rates_by_months.rate, '1'::double precision)), rates_with_dates.value, staff_membership_activity_links.cost, (COALESCE(cost_exchange_rates.rate, '1'::double precision)), (COALESCE(original_exchange_rates.rate, '1'::double precision)), (GREATEST((GREATEST(""*SELECT* 1"".updated_at, pa.updated_at, (GREATEST(staff_memberships_1.updated_at, staff_membership_activity_links.updated_at)))), rates_with_dates.updated_at, rates_with_dates.rate_card_updated_at))
3. 2,213.542 48,171.891 ↓ 7.6 595,625 1

Sort (cost=2,422,745.03..2,422,941.27 rows=78,498 width=204) (actual time=48,103.936..48,171.891 rows=595,625 loops=1)

  • Sort Key: projects.account_id, projects.id, clients.id, clients.office_id, rates_with_dates.rate_card_id, ""*SELECT* 1"".project_plan_id, ""*SELECT* 1"".epic_id, (COALESCE(pa.activity_id, ""*SELECT* 1"".activity_id, staff_membership_activity_links.activity_id)), ""*SELECT* 1"".user_id, ""*SELECT* 1"".date, (CASE WHEN (('PlanItem'::text) = 'TimeLog'::text) THEN (NULL::double precision) WHEN (((""*SELECT* 1"".vacation_user_id)::double precision) IS NOT NULL) THEN GREATEST(((((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision - COALESCE(((""*SELECT* 1"".vacation_hours)::double precision), (staff_membership_activity_links.capacity)::double precision, '0'::double precision)) * ((""*SELECT* 1"".utilization)::double precision)) / '100'::double precision), '0'::double precision) WHEN (""*SELECT* 1"".user_id IS NULL) THEN (((accounts_1.default_capacity)::double precision * ((""*SELECT* 1"".utilization)::double precision)) / '100'::double precision) ELSE (((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision * ((""*SELECT* 1"".utilization)::double precision)) / '100'::double precision) END), ('PlanItem'::text), ""*SELECT* 1"".reportable_id, ""*SELECT* 1"".plan_row_uuid, rates_with_dates.currency, (COALESCE(exchange_rates_by_months.rate, '1'::double precision)), rates_with_dates.value, staff_membership_activity_links.cost, (COALESCE(cost_exchange_rates.rate, '1'::double precision)), (COALESCE(original_exchange_rates.rate, '1'::double precision)), (GREATEST((GREATEST(""*SELECT* 1"".updated_at, pa.updated_at, (GREATEST(staff_memberships_1.updated_at, staff_membership_activity_links.updated_at)))), rates_with_dates.updated_at, rates_with_dates.rate_card_updated_at))
  • Sort Method: quicksort Memory: 168717kB
4. 5,411.989 45,958.349 ↓ 7.6 595,625 1

Merge Left Join (cost=2,412,412.58..2,416,363.00 rows=78,498 width=204) (actual time=30,773.934..45,958.349 rows=595,625 loops=1)

  • Merge Cond: ((projects.account_id = original_exchange_rates.account_id) AND ((rates_with_dates.currency)::text = (original_exchange_rates.currency)::text))
  • Join Filter: ((original_exchange_rates.start_date <= "*SELECT* 1".date) AND (original_exchange_rates.end_date >= "*SELECT* 1".date))
  • Rows Removed by Join Filter: 31169112
5. 5,444.092 38,409.954 ↓ 7.6 595,625 1

Merge Left Join (cost=2,412,319.37..2,414,294.58 rows=78,498 width=188) (actual time=30,773.272..38,409.954 rows=595,625 loops=1)

  • Merge Cond: ((projects.account_id = exchange_rates_by_months.account_id) AND ((rates_with_dates.currency)::text = (exchange_rates_by_months.currency)::text))
  • Join Filter: ((exchange_rates_by_months.start_date <= "*SELECT* 1".date) AND (exchange_rates_by_months.end_date >= "*SELECT* 1".date))
  • Rows Removed by Join Filter: 31169112
6. 555.670 30,825.675 ↓ 7.6 595,625 1

Sort (cost=2,412,226.16..2,412,422.41 rows=78,498 width=180) (actual time=30,772.337..30,825.675 rows=595,625 loops=1)

  • Sort Key: projects.account_id, rates_with_dates.currency
  • Sort Method: quicksort Memory: 159966kB
7. 5,780.195 30,270.005 ↓ 7.6 595,625 1

Merge Left Join (cost=2,405,199.93..2,405,844.13 rows=78,498 width=180) (actual time=22,233.904..30,270.005 rows=595,625 loops=1)

  • Merge Cond: ((projects.account_id = cost_exchange_rates.account_id) AND ((staff_memberships_1.currency)::text = (cost_exchange_rates.currency)::text))
  • Join Filter: ((cost_exchange_rates.start_date <= "*SELECT* 1".date) AND (cost_exchange_rates.end_date >= "*SELECT* 1".date))
  • Rows Removed by Join Filter: 32276381
8. 514.368 22,281.113 ↓ 7.6 595,625 1

Sort (cost=2,405,106.72..2,405,302.97 rows=78,498 width=688) (actual time=22,233.184..22,281.113 rows=595,625 loops=1)

  • Sort Key: projects.account_id, staff_memberships_1.currency
  • Sort Method: quicksort Memory: 163035kB
9. 193.744 21,766.745 ↓ 7.6 595,625 1

Merge Left Join (cost=2,392,572.22..2,398,724.69 rows=78,498 width=688) (actual time=21,018.239..21,766.745 rows=595,625 loops=1)

  • Merge Cond: (projects.id = discounts.project_id)
  • Join Filter: (("*SELECT* 1".date >= discounts.start_date) AND ("*SELECT* 1".date <= discounts.end_date) AND (((discounts.discountable_id = "*SELECT* 1".user_id) AND ((discounts.discountable_type)::text = 'User'::text)) OR ((discounts.discountable_id = "*SELECT* 1".epic_id) AND ((discounts.discountable_type)::text = 'Epic'::text))))
  • Rows Removed by Join Filter: 411771
10. 396.955 21,546.110 ↓ 7.6 595,625 1

Merge Left Join (cost=2,392,550.76..2,395,139.43 rows=78,498 width=684) (actual time=21,018.016..21,546.110 rows=595,625 loops=1)

  • Merge Cond: ((projects.id = rates_with_dates.project_id) AND ((COALESCE(pa.activity_id, ""*SELECT* 1"".activity_id, staff_membership_activity_links.activity_id)) = rates_with_dates.activity_id))
  • Join Filter: (("*SELECT* 1".date >= rates_with_dates.start_date) AND (("*SELECT* 1".date <= rates_with_dates.end_date) OR (rates_with_dates.end_date IS NULL)))
  • Rows Removed by Join Filter: 228687
11. 446.492 20,686.634 ↓ 7.6 595,625 1

Sort (cost=2,363,981.73..2,364,177.98 rows=78,498 width=652) (actual time=20,627.182..20,686.634 rows=595,625 loops=1)

  • Sort Key: projects.id, (COALESCE(pa.activity_id, ""*SELECT* 1"".activity_id, staff_membership_activity_links.activity_id))
  • Sort Method: quicksort Memory: 108336kB
12. 211.109 20,240.142 ↓ 7.6 595,625 1

Hash Join (cost=2,035,340.23..2,357,599.70 rows=78,498 width=652) (actual time=11,037.191..20,240.142 rows=595,625 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
13. 139.162 20,028.775 ↓ 4.6 1,091,769 1

Append (cost=2,035,321.82..2,354,600.53 rows=235,493 width=652) (actual time=10,055.626..20,028.775 rows=1,091,769 loops=1)

14. 469.195 19,758.183 ↓ 4.6 1,075,067 1

Hash Left Join (cost=2,035,321.82..2,111,620.12 rows=235,492 width=111) (actual time=10,055.625..19,758.183 rows=1,075,067 loops=1)

  • Hash Cond: (projects.client_id = clients.id)
15. 6,606.514 19,287.913 ↓ 4.6 1,075,067 1

Merge Left Join (cost=2,035,259.82..2,102,696.30 rows=235,492 width=165) (actual time=10,054.534..19,287.913 rows=1,075,067 loops=1)

  • Merge Cond: ("*SELECT* 1".project_plan_id = pa.project_plan_id)
  • Join Filter: (("*SELECT* 1".date >= pa.start_date) AND ("*SELECT* 1".date <= pa.end_date) AND ((pa.plan_row_uuid = "*SELECT* 1".plan_row_uuid) OR ("*SELECT* 1".plan_row_uuid IS NULL)) AND ((pa.user_id = "*SELECT* 1".user_id) OR ("*SELECT* 1".user_id IS NULL)) AND ((pa.epic_id = "*SELECT* 1".epic_id) OR ((('PlanItem'::text) = 'PlanItem'::text) AND ("*SELECT* 1".epic_id IS NULL)) OR ((('PlanItem'::text) = 'TimeLog'::text) AND (pa.epic_id IS NULL))))
  • Rows Removed by Join Filter: 34327938
16. 1,250.528 10,226.889 ↓ 4.6 1,075,067 1

Sort (cost=2,031,118.65..2,031,707.38 rows=235,492 width=153) (actual time=10,023.882..10,226.889 rows=1,075,067 loops=1)

  • Sort Key: "*SELECT* 1".project_plan_id
  • Sort Method: external merge Disk: 124872kB
17. 319.931 8,976.361 ↓ 4.6 1,075,067 1

Hash Left Join (cost=2,001,513.58..2,010,106.50 rows=235,492 width=153) (actual time=7,857.520..8,976.361 rows=1,075,067 loops=1)

  • Hash Cond: (projects.account_id = accounts_1.id)
  • Filter: ((((""*SELECT* 1"".vacation_hours)::double precision) IS NULL) OR (((""*SELECT* 1"".vacation_hours)::double precision) < (COALESCE(staff_membership_activity_links.capacity, accounts_1.default_capacity))::double precision))
  • Rows Removed by Filter: 5359
18. 565.831 8,656.268 ↓ 1.5 1,080,426 1

Merge Left Join (cost=2,001,494.88..2,008,218.69 rows=699,480 width=148) (actual time=7,856.135..8,656.268 rows=1,080,426 loops=1)

  • Merge Cond: ((projects.account_id = staff_memberships_1.account_id) AND ("*SELECT* 1".user_id = staff_memberships_1.user_id))
  • Join Filter: ((""*SELECT* 1"".date >= (COALESCE(staff_membership_activity_links.start_date, staff_memberships_1.joined_at))) AND (""*SELECT* 1"".date <= (COALESCE(lag((staff_membership_activity_links.start_date - 1), 1) OVER (?), staff_memberships_1.archived_at, '3000-01-01'::date))))
  • Rows Removed by Join Filter: 198922
19. 1,188.037 7,989.057 ↓ 1.5 1,080,426 1

Sort (cost=2,000,674.00..2,002,422.70 rows=699,480 width=124) (actual time=7,842.823..7,989.057 rows=1,080,426 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".user_id
  • Sort Method: quicksort Memory: 181193kB
20. 315.575 6,801.020 ↓ 1.5 1,080,426 1

Hash Join (cost=1,839,262.70..1,932,768.75 rows=699,480 width=124) (actual time=4,688.438..6,801.020 rows=1,080,426 loops=1)

  • Hash Cond: ("*SELECT* 1".project_id = projects.id)
21. 182.784 6,480.891 ↓ 2.2 1,549,746 1

Append (cost=1,839,037.76..1,923,711.51 rows=699,480 width=120) (actual time=4,683.869..6,480.891 rows=1,549,746 loops=1)

22. 277.232 5,513.709 ↓ 80.2 860,997 1

Subquery Scan on *SELECT* 1 (cost=1,839,037.76..1,882,764.12 rows=10,731 width=120) (actual time=4,683.868..5,513.709 rows=860,997 loops=1)

23. 440.726 5,236.477 ↓ 80.2 860,997 1

Merge Right Join (cost=1,839,037.76..1,882,576.33 rows=10,731 width=138) (actual time=4,683.864..5,236.477 rows=860,997 loops=1)

  • Merge Cond: ((nw_intervals_by_dates_roles.account_id = projects_1.account_id) AND (nw_intervals_by_dates_roles.date = generate_series.generate_series) AND (nw_intervals_by_dates_roles.office_id = clients_1.office_id))
  • Filter: (COALESCE(non_working_intervals_by_dates.id, nw_intervals_by_dates_roles.id) IS NULL)
  • Rows Removed by Filter: 640596
24. 23.112 41.823 ↑ 1.0 58,766 1

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

  • 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
25. 18.711 18.711 ↑ 1.0 58,799 1

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

26. 1,000.418 4,753.928 ↓ 1.2 1,501,593 1

Sort (cost=1,833,118.86..1,836,260.80 rows=1,256,775 width=110) (actual time=4,646.530..4,753.928 rows=1,501,593 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, clients_1.office_id
  • Sort Method: quicksort Memory: 146956kB
27. 190.168 3,753.510 ↑ 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=3,212.454..3,753.510 rows=870,252 loops=1)

  • Merge Cond: ((projects_1.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))
28. 256.303 3,516.172 ↑ 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=3,170.262..3,516.172 rows=870,234 loops=1)

  • Merge Cond: ((projects_1.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))
29. 2,271.551 3,127.587 ↑ 1.4 870,234 1

Sort (cost=1,086,921.64..1,090,063.58 rows=1,256,775 width=70) (actual time=3,042.735..3,127.587 rows=870,234 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, plan_rows.user_id
  • Sort Method: quicksort Memory: 146953kB
30. 183.031 856.036 ↑ 1.4 870,234 1

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

31. 14.726 148.815 ↓ 1.0 52,419 1

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

  • Hash Cond: (projects_1.client_id = clients_1.id)
32. 15.604 133.052 ↓ 1.0 52,419 1

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

  • Hash Cond: (project_plans.project_id = projects_1.id)
33. 15.601 115.111 ↓ 1.0 52,419 1

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

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
34. 29.304 96.272 ↓ 1.0 52,419 1

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

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
35. 38.824 38.824 ↓ 1.0 52,422 1

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

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 255
36. 10.580 28.144 ↓ 1.0 46,641 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3035kB
37. 17.564 17.564 ↓ 1.0 46,641 1

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

  • Filter: ((user_id IS NOT NULL) OR (activity_id IS NOT NULL))
  • Rows Removed by Filter: 4
38. 0.506 3.238 ↓ 1.0 2,819 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 143kB
39. 2.732 2.732 ↓ 1.0 2,819 1

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

  • Filter: active
  • Rows Removed by Filter: 3091
40. 1.042 2.337 ↑ 1.0 5,686 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 309kB
41. 1.295 1.295 ↑ 1.0 5,686 1

Seq Scan on projects projects_1 (cost=0.00..153.86 rows=5,686 width=12) (actual time=0.007..1.295 rows=5,686 loops=1)

42. 0.311 1.037 ↑ 1.0 1,822 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
43. 0.726 0.726 ↑ 1.0 1,822 1

Seq Scan on clients clients_1 (cost=0.00..39.22 rows=1,822 width=8) (actual time=0.028..0.726 rows=1,822 loops=1)

44. 524.190 524.190 ↑ 1.5 17 52,419

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

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 6
45. 38.308 132.282 ↑ 16.5 34,338 1

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

  • Sort Key: summary_vacations_by_dates.account_id, summary_vacations_by_dates.date, summary_vacations_by_dates.user_id
  • Sort Method: quicksort Memory: 2705kB
46. 4.692 93.974 ↑ 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=70.965..93.974 rows=29,556 loops=1)

47. 33.757 89.282 ↑ 19.1 29,556 1

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

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
48. 11.430 55.525 ↑ 142.6 29,683 1

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

  • 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
49. 13.796 41.996 ↑ 661.8 31,591 1

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

50. 7.293 7.293 ↑ 1.0 20,907 1

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

51. 20.907 20.907 ↑ 500.0 2 20,907

Function Scan on 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)

52. 0.763 2.099 ↑ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
53. 1.336 1.336 ↑ 1.0 3,958 1

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

54. 37.041 47.170 ↓ 1.0 60,807 1

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

  • 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
55. 10.129 10.129 ↑ 1.0 58,799 1

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

56. 277.181 784.398 ↑ 1.0 688,749 1

Hash Join (cost=1,805.96..30,562.50 rows=688,749 width=120) (actual time=23.252..784.398 rows=688,749 loops=1)

  • Hash Cond: (time_logs.epic_id = epics.id)
57. 485.411 485.411 ↑ 1.0 688,749 1

Seq Scan on time_logs (cost=0.00..15,842.49 rows=688,749 width=28) (actual time=1.317..485.411 rows=688,749 loops=1)

58. 5.330 21.806 ↑ 1.0 26,131 1

Hash (cost=1,479.32..1,479.32 rows=26,131 width=16) (actual time=21.806..21.806 rows=26,131 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1368kB
59. 7.135 16.476 ↑ 1.0 26,131 1

Hash Left Join (cost=198.32..1,479.32 rows=26,131 width=16) (actual time=1.651..16.476 rows=26,131 loops=1)

  • Hash Cond: (epics.project_id = general_epic_plans.project_id)
  • Join Filter: (epics.project_plan_id IS NULL)
  • Rows Removed by Join Filter: 11264
60. 7.714 7.714 ↑ 1.0 26,131 1

Seq Scan on epics (cost=0.00..612.31 rows=26,131 width=12) (actual time=0.016..7.714 rows=26,131 loops=1)

61. 0.474 1.627 ↓ 1.0 2,819 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 143kB
62. 1.153 1.153 ↓ 1.0 2,819 1

Seq Scan on project_plans general_epic_plans (cost=0.00..163.10 rows=2,818 width=8) (actual time=0.008..1.153 rows=2,819 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3091
63. 1.467 4.554 ↑ 1.0 5,686 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 309kB
64. 3.087 3.087 ↑ 1.0 5,686 1

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

65. 85.910 101.380 ↓ 285.4 1,174,237 1

Materialize (cost=820.88..913.45 rows=4,114 width=40) (actual time=13.306..101.380 rows=1,174,237 loops=1)

66. 1.519 15.470 ↑ 1.0 4,053 1

Unique (cost=820.88..862.02 rows=4,114 width=65) (actual time=13.302..15.470 rows=4,053 loops=1)

67. 3.387 13.951 ↑ 1.0 4,075 1

Sort (cost=820.88..831.17 rows=4,114 width=65) (actual time=13.301..13.951 rows=4,075 loops=1)

  • Sort Key: staff_memberships_1.account_id, staff_memberships_1.user_id, (COALESCE(staff_membership_activity_links.start_date, staff_memberships_1.joined_at)), (COALESCE(lag((staff_membership_activity_links.start_date - 1), 1) OVER (?), staff_memberships_1.archived_at, '3000-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 771kB
68. 3.534 10.564 ↑ 1.0 4,114 1

WindowAgg (cost=471.06..573.91 rows=4,114 width=65) (actual time=6.682..10.564 rows=4,114 loops=1)

69. 2.483 7.030 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=56) (actual time=6.654..7.030 rows=4,114 loops=1)

  • Sort Key: staff_membership_activity_links.staff_membership_id, staff_membership_activity_links.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 771kB
70. 1.562 4.547 ↑ 1.0 4,114 1

Hash Join (cost=136.06..224.09 rows=4,114 width=56) (actual time=2.026..4.547 rows=4,114 loops=1)

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
71. 1.018 1.018 ↑ 1.0 4,121 1

Seq Scan on staff_membership_activity_links (cost=0.00..77.21 rows=4,121 width=28) (actual time=0.028..1.018 rows=4,121 loops=1)

72. 1.049 1.967 ↑ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 273kB
73. 0.918 0.918 ↑ 1.0 3,958 1

Seq Scan on staff_memberships staff_memberships_1 (cost=0.00..86.58 rows=3,958 width=32) (actual time=0.007..0.918 rows=3,958 loops=1)

74. 0.058 0.162 ↑ 1.0 342 1

Hash (cost=14.42..14.42 rows=342 width=9) (actual time=0.162..0.162 rows=342 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
75. 0.104 0.104 ↑ 1.0 342 1

Seq Scan on accounts accounts_1 (cost=0.00..14.42 rows=342 width=9) (actual time=0.018..0.104 rows=342 loops=1)

76. 2,440.658 2,454.510 ↓ 817.9 35,256,840 1

Sort (cost=4,141.18..4,248.94 rows=43,105 width=48) (actual time=30.616..2,454.510 rows=35,256,840 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: quicksort Memory: 5488kB
77. 13.852 13.852 ↑ 1.0 43,105 1

Seq Scan on planned_activities_with_dates pa (cost=0.00..823.05 rows=43,105 width=48) (actual time=0.066..13.852 rows=43,105 loops=1)

78. 0.467 1.075 ↑ 1.0 1,822 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
79. 0.608 0.608 ↑ 1.0 1,822 1

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

80. 5.490 131.430 ↓ 16,702.0 16,702 1

Subquery Scan on *SELECT* 2 (cost=232,836.32..239,448.02 rows=1 width=111) (actual time=81.515..131.430 rows=16,702 loops=1)

81. 18.197 125.940 ↓ 16,702.0 16,702 1

Merge Right Join (cost=232,836.32..239,448.01 rows=1 width=118) (actual time=81.508..125.940 rows=16,702 loops=1)

  • Merge Cond: ((non_working_intervals_by_dates_1.account_id = staff_memberships_2.account_id) AND (non_working_intervals_by_dates_1.user_id = staff_memberships_2.user_id) AND (non_working_intervals_by_dates_1.date = generate_series_2.generate_series))
  • Filter: (non_working_intervals_by_dates_1.id IS NULL)
  • Rows Removed by Filter: 326
82. 24.392 24.392 ↑ 1.0 58,756 1

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates non_working_intervals_by_dates_1 (cost=0.41..3,416.17 rows=58,799 width=16) (actual time=0.013..24.392 rows=58,756 loops=1)

83. 35.572 83.351 ↑ 16.0 17,028 1

Sort (cost=232,835.90..233,514.95 rows=271,620 width=64) (actual time=81.443..83.351 rows=17,028 loops=1)

  • Sort Key: staff_memberships_2.account_id, staff_memberships_2.user_id, generate_series_2.generate_series
  • Sort Method: quicksort Memory: 3163kB
84. 0.657 47.779 ↑ 16.0 17,028 1

Nested Loop (cost=1,217.40..208,320.53 rows=271,620 width=64) (actual time=17.341..47.779 rows=17,028 loops=1)

85. 5.275 24.666 ↓ 1.0 11,228 1

Hash Join (cost=1,217.39..1,885.52 rows=10,865 width=64) (actual time=17.321..24.666 rows=11,228 loops=1)

  • Hash Cond: (vacations_1.staff_membership_id = staff_memberships_2.id)
86. 2.116 2.116 ↑ 1.0 20,907 1

Seq Scan on vacations vacations_1 (cost=0.00..481.07 rows=20,907 width=44) (actual time=0.009..2.116 rows=20,907 loops=1)

87. 0.500 17.275 ↑ 1.3 1,635 1

Hash (cost=1,191.68..1,191.68 rows=2,057 width=32) (actual time=17.275..17.275 rows=1,635 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 137kB
88. 0.524 16.775 ↑ 1.3 1,635 1

Hash Join (cost=1,075.31..1,191.68 rows=2,057 width=32) (actual time=14.959..16.775 rows=1,635 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_2.id)
89. 0.884 14.494 ↑ 1.3 1,635 1

Hash Join (cost=939.26..1,050.20 rows=2,060 width=16) (actual time=13.182..14.494 rows=1,635 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
90. 0.450 0.450 ↑ 1.0 4,121 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_1 (cost=0.00..77.21 rows=4,121 width=20) (actual time=0.005..0.450 rows=4,121 loops=1)

91. 0.251 13.160 ↓ 9.0 1,635 1

Hash (cost=936.98..936.98 rows=182 width=4) (actual time=13.159..13.160 rows=1,635 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 74kB
92. 0.671 12.909 ↓ 9.0 1,635 1

HashAggregate (cost=935.16..936.98 rows=182 width=4) (actual time=12.669..12.909 rows=1,635 loops=1)

  • Group Key: staff_activities_with_dates.link_id
93. 1.348 12.238 ↓ 3.6 1,635 1

Subquery Scan on staff_activities_with_dates (cost=810.60..934.02 rows=457 width=4) (actual time=9.798..12.238 rows=1,635 loops=1)

  • Filter: ((CURRENT_DATE >= staff_activities_with_dates.start_date) AND (CURRENT_DATE <= staff_activities_with_dates.end_date))
  • Rows Removed by Filter: 2451
94. 0.810 10.890 ↑ 1.0 4,086 1

Unique (cost=810.60..851.74 rows=4,114 width=613) (actual time=9.792..10.890 rows=4,086 loops=1)

95. 2.726 10.080 ↑ 1.0 4,114 1

Sort (cost=810.60..820.88 rows=4,114 width=613) (actual time=9.791..10.080 rows=4,114 loops=1)

  • Sort Key: staff_memberships_3.account_id, staff_memberships_3.user_id, (COALESCE(staff_membership_activity_links_2.start_date, staff_memberships_3.joined_at)), (COALESCE(lag((staff_membership_activity_links_2.start_date - 1), 1) OVER (?), staff_memberships_3.archived_at, '3000-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 514kB
96. 2.632 7.354 ↑ 1.0 4,114 1

WindowAgg (cost=471.06..563.63 rows=4,114 width=613) (actual time=4.429..7.354 rows=4,114 loops=1)

97. 1.684 4.722 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=28) (actual time=4.419..4.722 rows=4,114 loops=1)

  • Sort Key: staff_membership_activity_links_2.staff_membership_id, staff_membership_activity_links_2.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 514kB
98. 1.093 3.038 ↑ 1.0 4,114 1

Hash Join (cost=136.06..224.09 rows=4,114 width=28) (actual time=1.618..3.038 rows=4,114 loops=1)

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_3.id)
99. 0.362 0.362 ↑ 1.0 4,121 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_2 (cost=0.00..77.21 rows=4,121 width=12) (actual time=0.008..0.362 rows=4,121 loops=1)

100. 0.829 1.583 ↑ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
101. 0.754 0.754 ↑ 1.0 3,958 1

Seq Scan on staff_memberships staff_memberships_3 (cost=0.00..86.58 rows=3,958 width=20) (actual time=0.013..0.754 rows=3,958 loops=1)

102. 0.864 1.757 ↑ 1.0 3,958 1

Hash (cost=86.58..86.58 rows=3,958 width=16) (actual time=1.756..1.757 rows=3,958 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 218kB
103. 0.893 0.893 ↑ 1.0 3,958 1

Seq Scan on staff_memberships staff_memberships_2 (cost=0.00..86.58 rows=3,958 width=16) (actual time=0.006..0.893 rows=3,958 loops=1)

104. 22.456 22.456 ↑ 12.5 2 11,228

Function Scan on generate_series generate_series_2 (cost=0.01..18.76 rows=25 width=8) (actual time=0.001..0.002 rows=2 loops=11,228)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 0
105. 0.006 0.258 ↑ 38.0 3 1

Hash (cost=16.98..16.98 rows=114 width=4) (actual time=0.258..0.258 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
106. 0.252 0.252 ↑ 38.0 3 1

Seq Scan on accounts (cost=0.00..16.98 rows=114 width=4) (actual time=0.036..0.252 rows=3 loops=1)

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 339
107. 370.437 462.521 ↓ 3.8 973,811 1

Sort (cost=28,569.03..29,203.51 rows=253,793 width=48) (actual time=384.239..462.521 rows=973,811 loops=1)

  • Sort Key: rates_with_dates.project_id, rates_with_dates.activity_id
  • Sort Method: quicksort Memory: 25972kB
108. 92.084 92.084 ↑ 1.0 253,793 1

Seq Scan on rates_with_dates (cost=0.00..5,786.93 rows=253,793 width=48) (actual time=0.032..92.084 rows=253,793 loops=1)

109. 26.765 26.891 ↓ 1,450.2 414,749 1

Sort (cost=21.46..22.17 rows=286 width=26) (actual time=0.207..26.891 rows=414,749 loops=1)

  • Sort Key: discounts.project_id
  • Sort Method: quicksort Memory: 51kB
110. 0.126 0.126 ↓ 1.2 336 1

Seq Scan on discounts (cost=0.00..9.79 rows=286 width=26) (actual time=0.028..0.126 rows=336 loops=1)

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
111. 2,208.460 2,208.697 ↓ 26,074.1 32,749,038 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=0.602..2,208.697 rows=32,749,038 loops=1)

  • Sort Key: cost_exchange_rates.account_id, cost_exchange_rates.currency
  • Sort Method: quicksort Memory: 147kB
112. 0.237 0.237 ↑ 1.0 1,256 1

Seq Scan on exchange_rates_by_months cost_exchange_rates (cost=0.00..28.56 rows=1,256 width=24) (actual time=0.015..0.237 rows=1,256 loops=1)

113. 2,139.824 2,140.187 ↓ 25,180.3 31,626,403 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=0.855..2,140.187 rows=31,626,403 loops=1)

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 147kB
114. 0.363 0.363 ↑ 1.0 1,256 1

Seq Scan on exchange_rates_by_months (cost=0.00..28.56 rows=1,256 width=24) (actual time=0.021..0.363 rows=1,256 loops=1)

115. 2,136.207 2,136.406 ↓ 25,180.3 31,626,403 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=0.584..2,136.406 rows=31,626,403 loops=1)

  • Sort Key: original_exchange_rates.account_id, original_exchange_rates.currency
  • Sort Method: quicksort Memory: 147kB
116. 0.199 0.199 ↑ 1.0 1,256 1

Seq Scan on exchange_rates_by_months original_exchange_rates (cost=0.00..28.56 rows=1,256 width=24) (actual time=0.004..0.199 rows=1,256 loops=1)

Planning time : 14.355 ms
Execution time : 49,100.029 ms