explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4nNA

Settings
# exclusive inclusive rows x rows loops node
1. 167.016 71,723.030 ↓ 7.6 595,625 1

Subquery Scan on report_items_live (cost=2,571,364.59..2,579,410.64 rows=78,498 width=152) (actual time=70,580.887..71,723.030 rows=595,625 loops=1)

2. 886.413 71,556.014 ↓ 7.6 595,625 1

GroupAggregate (cost=2,571,364.59..2,578,625.66 rows=78,498 width=208) (actual time=70,580.885..71,556.014 rows=595,625 loops=1)

  • Group Key: projects.account_id, projects.id, clients.id, clients.office_id, ""*SELECT* 1"".rate_card_id, ""*SELECT* 1_1"".project_plan_id, ""*SELECT* 1_1"".epic_id, (COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id)), ""*SELECT* 1_1"".user_id, ""*SELECT* 1_1"".date, (CASE WHEN (('PlanItem'::text) = 'TimeLog'::text) THEN (NULL::double precision) WHEN (((""*SELECT* 1_1"".vacation_user_id)::double precision) IS NOT NULL) THEN GREATEST(((((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision - COALESCE(((""*SELECT* 1_1"".vacation_hours)::double precision), (staff_membership_activity_links.capacity)::double precision, '0'::double precision)) * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision), '0'::double precision) WHEN (""*SELECT* 1_1"".user_id IS NULL) THEN (((accounts_1.default_capacity)::double precision * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision) ELSE (((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision) END), ('PlanItem'::text), ""*SELECT* 1_1"".reportable_id, ""*SELECT* 1_1"".plan_row_uuid, ""*SELECT* 1"".currency, (COALESCE(exchange_rates_by_months.rate, '1'::double precision)), ""*SELECT* 1"".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_1"".updated_at, pa.updated_at, (GREATEST(staff_memberships_1.updated_at, staff_membership_activity_links.updated_at)))), ""*SELECT* 1"".updated_at, ""*SELECT* 1"".rate_card_updated_at))
3. 3,147.138 70,669.601 ↓ 7.6 595,625 1

Sort (cost=2,571,364.59..2,571,560.84 rows=78,498 width=204) (actual time=70,580.862..70,669.601 rows=595,625 loops=1)

  • Sort Key: projects.account_id, projects.id, clients.id, clients.office_id, ""*SELECT* 1"".rate_card_id, ""*SELECT* 1_1"".project_plan_id, ""*SELECT* 1_1"".epic_id, (COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id)), ""*SELECT* 1_1"".user_id, ""*SELECT* 1_1"".date, (CASE WHEN (('PlanItem'::text) = 'TimeLog'::text) THEN (NULL::double precision) WHEN (((""*SELECT* 1_1"".vacation_user_id)::double precision) IS NOT NULL) THEN GREATEST(((((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision - COALESCE(((""*SELECT* 1_1"".vacation_hours)::double precision), (staff_membership_activity_links.capacity)::double precision, '0'::double precision)) * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision), '0'::double precision) WHEN (""*SELECT* 1_1"".user_id IS NULL) THEN (((accounts_1.default_capacity)::double precision * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision) ELSE (((COALESCE(staff_membership_activity_links.capacity, '0'::numeric))::double precision * ((""*SELECT* 1_1"".utilization)::double precision)) / '100'::double precision) END), ('PlanItem'::text), ""*SELECT* 1_1"".reportable_id, ""*SELECT* 1_1"".plan_row_uuid, ""*SELECT* 1"".currency, (COALESCE(exchange_rates_by_months.rate, '1'::double precision)), ""*SELECT* 1"".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_1"".updated_at, pa.updated_at, (GREATEST(staff_memberships_1.updated_at, staff_membership_activity_links.updated_at)))), ""*SELECT* 1"".updated_at, ""*SELECT* 1"".rate_card_updated_at))
  • Sort Method: quicksort Memory: 168717kB
4. 8,355.065 67,522.463 ↓ 7.6 595,625 1

Merge Left Join (cost=2,563,694.17..2,564,982.56 rows=78,498 width=204) (actual time=44,367.894..67,522.463 rows=595,625 loops=1)

  • Merge Cond: ((projects.account_id = original_exchange_rates.account_id) AND (("*SELECT* 1".currency)::text = (original_exchange_rates.currency)::text))
  • Join Filter: ((original_exchange_rates.start_date <= "*SELECT* 1_1".date) AND (original_exchange_rates.end_date >= "*SELECT* 1_1".date))
  • Rows Removed by Join Filter: 31169112
5. 8,261.980 55,940.254 ↓ 7.6 595,625 1

Merge Left Join (cost=2,563,600.96..2,564,245.15 rows=78,498 width=188) (actual time=44,367.028..55,940.254 rows=595,625 loops=1)

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

Sort (cost=2,563,507.75..2,563,703.99 rows=78,498 width=180) (actual time=44,366.119..44,444.443 rows=595,625 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".currency
  • Sort Method: quicksort Memory: 159966kB
7. 8,063.696 43,651.772 ↓ 7.6 595,625 1

Merge Left Join (cost=2,556,481.52..2,557,125.71 rows=78,498 width=180) (actual time=32,398.357..43,651.772 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_1".date) AND (cost_exchange_rates.end_date >= "*SELECT* 1_1".date))
  • Rows Removed by Join Filter: 32276381
8. 705.575 32,463.756 ↓ 7.6 595,625 1

Sort (cost=2,556,388.31..2,556,584.55 rows=78,498 width=688) (actual time=32,397.107..32,463.756 rows=595,625 loops=1)

  • Sort Key: projects.account_id, staff_memberships_1.currency
  • Sort Method: quicksort Memory: 163035kB
9. 264.986 31,758.181 ↓ 7.6 595,625 1

Merge Left Join (cost=2,534,239.30..2,550,006.27 rows=78,498 width=688) (actual time=30,737.213..31,758.181 rows=595,625 loops=1)

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

Merge Left Join (cost=2,534,217.84..2,546,421.01 rows=78,498 width=684) (actual time=30,736.892..31,456.168 rows=595,625 loops=1)

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

Sort (cost=2,488,958.82..2,489,155.06 rows=78,498 width=652) (actual time=29,876.164..29,952.666 rows=595,625 loops=1)

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

Hash Join (cost=2,040,443.47..2,482,576.79 rows=78,498 width=652) (actual time=15,646.934..29,300.732 rows=595,625 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
13. 204.187 28,993.693 ↓ 4.6 1,091,769 1

Append (cost=2,040,425.06..2,479,577.61 rows=235,493 width=652) (actual time=14,198.183..28,993.693 rows=1,091,769 loops=1)

14. 673.911 28,587.583 ↓ 4.6 1,075,067 1

Hash Left Join (cost=2,040,425.06..2,236,597.20 rows=235,492 width=111) (actual time=14,198.183..28,587.583 rows=1,075,067 loops=1)

  • Hash Cond: (projects.client_id = clients.id)
15. 9,740.074 27,912.589 ↓ 4.6 1,075,067 1

Merge Left Join (cost=2,040,363.06..2,227,673.39 rows=235,492 width=165) (actual time=14,197.078..27,912.589 rows=1,075,067 loops=1)

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

Sort (cost=2,031,118.65..2,031,707.38 rows=235,492 width=153) (actual time=13,941.793..14,225.290 rows=1,075,067 loops=1)

  • Sort Key: "*SELECT* 1_1".project_plan_id
  • Sort Method: external merge Disk: 124872kB
17. 470.738 12,439.150 ↓ 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=10,820.611..12,439.150 rows=1,075,067 loops=1)

  • Hash Cond: (projects.account_id = accounts_1.id)
  • Filter: ((((""*SELECT* 1_1"".vacation_hours)::double precision) IS NULL) OR (((""*SELECT* 1_1"".vacation_hours)::double precision) < (COALESCE(staff_membership_activity_links.capacity, accounts_1.default_capacity))::double precision))
  • Rows Removed by Filter: 5359
18. 824.858 11,968.178 ↓ 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=10,820.361..11,968.178 rows=1,080,426 loops=1)

  • Merge Cond: ((projects.account_id = staff_memberships_1.account_id) AND ("*SELECT* 1_1".user_id = staff_memberships_1.user_id))
  • Join Filter: ((""*SELECT* 1_1"".date >= (COALESCE(staff_membership_activity_links.start_date, staff_memberships_1.joined_at))) AND (""*SELECT* 1_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,583.900 10,996.210 ↓ 1.5 1,080,426 1

Sort (cost=2,000,674.00..2,002,422.70 rows=699,480 width=124) (actual time=10,803.079..10,996.210 rows=1,080,426 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1_1"".user_id
  • Sort Method: quicksort Memory: 181193kB
20. 454.508 9,412.310 ↓ 1.5 1,080,426 1

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

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
21. 269.623 8,955.064 ↓ 2.2 1,549,746 1

Append (cost=1,839,037.76..1,923,711.51 rows=699,480 width=120) (actual time=6,967.234..8,955.064 rows=1,549,746 loops=1)

22. 402.771 8,142.976 ↓ 80.2 860,997 1

Subquery Scan on *SELECT* 1_1 (cost=1,839,037.76..1,882,764.12 rows=10,731 width=120) (actual time=6,967.233..8,142.976 rows=860,997 loops=1)

23. 616.236 7,740.205 ↓ 80.2 860,997 1

Merge Right Join (cost=1,839,037.76..1,882,576.33 rows=10,731 width=138) (actual time=6,967.228..7,740.205 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. 30.846 45.506 ↑ 1.0 58,766 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=40.037..45.506 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. 14.660 14.660 ↑ 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.010..14.660 rows=58,799 loops=1)

26. 1,509.435 7,078.463 ↓ 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,927.180..7,078.463 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. 290.090 5,569.028 ↑ 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,756.803..5,569.028 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. 384.665 5,205.740 ↑ 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,690.560..5,205.740 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. 3,371.935 4,643.165 ↑ 1.4 870,234 1

Sort (cost=1,086,921.64..1,090,063.58 rows=1,256,775 width=70) (actual time=4,519.286..4,643.165 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. 271.198 1,271.230 ↑ 1.4 870,234 1

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

31. 22.673 161.328 ↓ 1.0 52,419 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

42. 0.488 1.009 ↑ 1.0 1,822 1

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

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

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

44. 838.704 838.704 ↑ 1.5 17 52,419

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.007..0.016 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. 54.883 177.910 ↑ 16.5 34,338 1

Sort (cost=584,189.58..585,604.08 rows=565,800 width=44) (actual time=171.262..177.910 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. 5.794 123.027 ↑ 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=95.066..123.027 rows=29,556 loops=1)

47. 44.041 117.233 ↑ 19.1 29,556 1

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

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

Hash Join (cost=136.06..473,655.25 rows=4,232,734 width=21) (actual time=2.603..73.192 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. 9.214 54.368 ↑ 661.8 31,591 1

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

50. 3.340 3.340 ↑ 1.0 20,907 1

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

51. 41.814 41.814 ↑ 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.002 rows=2 loops=20,907)

52. 1.314 2.517 ↑ 1.0 3,958 1

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

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

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

54. 57.363 73.198 ↓ 1.0 60,807 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=66.234..73.198 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. 15.835 15.835 ↑ 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.020..15.835 rows=58,799 loops=1)

56. 404.622 542.465 ↑ 1.0 688,749 1

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

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

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

58. 8.099 25.819 ↑ 1.0 26,131 1

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

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

Hash Left Join (cost=198.32..1,479.32 rows=26,131 width=16) (actual time=2.553..17.720 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. 3.863 3.863 ↑ 1.0 26,131 1

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

61. 0.765 2.530 ↓ 1.0 2,819 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 143kB
62. 1.765 1.765 ↓ 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.009..1.765 rows=2,819 loops=1)

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

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

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

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

65. 126.971 147.110 ↓ 285.4 1,174,237 1

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

66. 2.098 20.139 ↑ 1.0 4,053 1

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

67. 4.595 18.041 ↑ 1.0 4,075 1

Sort (cost=820.88..831.17 rows=4,114 width=65) (actual time=17.266..18.041 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. 4.349 13.446 ↑ 1.0 4,114 1

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

69. 3.352 9.097 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=56) (actual time=8.610..9.097 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. 2.271 5.745 ↑ 1.0 4,114 1

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

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
71. 0.635 0.635 ↑ 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.020..0.635 rows=4,121 loops=1)

72. 1.530 2.839 ↑ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 273kB
73. 1.309 1.309 ↑ 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.009..1.309 rows=3,958 loops=1)

74. 0.087 0.234 ↑ 1.0 342 1

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

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

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

76. 3,714.239 3,947.225 ↓ 7,248.5 35,256,840 1

Sort (cost=9,244.42..9,256.58 rows=4,864 width=48) (actual time=255.268..3,947.225 rows=35,256,840 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: quicksort Memory: 5488kB
77. 8.303 232.986 ↓ 8.9 43,105 1

Subquery Scan on pa (cost=8,824.95..8,946.55 rows=4,864 width=48) (actual time=172.263..232.986 rows=43,105 loops=1)

78. 98.176 224.683 ↓ 8.9 43,105 1

HashAggregate (cost=8,824.95..8,897.91 rows=4,864 width=48) (actual time=172.261..224.683 rows=43,105 loops=1)

  • Group Key: plan_rows_1.project_plan_id, plan_rows_1.epic_id, plan_rows_1.user_id, plan_roles.activity_id, COALESCE(plan_roles.start_date, '1980-01-01'::date), COALESCE(lag((plan_roles.start_date - 1), 1) OVER (?), '3000-01-01'::date)
79. 46.675 126.507 ↑ 1.0 43,258 1

WindowAgg (cost=6,337.50..7,310.85 rows=43,260 width=68) (actual time=70.747..126.507 rows=43,258 loops=1)

80. 32.399 79.832 ↑ 1.0 43,258 1

Sort (cost=6,337.50..6,445.65 rows=43,260 width=60) (actual time=70.731..79.832 rows=43,258 loops=1)

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 6969kB
81. 22.596 47.433 ↑ 1.0 43,258 1

Hash Join (cost=1,400.35..3,006.32 rows=43,260 width=60) (actual time=19.545..47.433 rows=43,258 loops=1)

  • Hash Cond: (plan_rows_1.uuid = plan_roles.plan_row_uuid)
82. 5.417 5.417 ↑ 1.0 46,645 1

Seq Scan on plan_rows plan_rows_1 (cost=0.00..998.45 rows=46,645 width=28) (actual time=0.015..5.417 rows=46,645 loops=1)

83. 11.242 19.420 ↑ 1.0 43,260 1

Hash (cost=859.60..859.60 rows=43,260 width=32) (actual time=19.420..19.420 rows=43,260 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3096kB
84. 8.178 8.178 ↑ 1.0 43,260 1

Seq Scan on plan_roles (cost=0.00..859.60 rows=43,260 width=32) (actual time=0.010..8.178 rows=43,260 loops=1)

85. 0.528 1.083 ↑ 1.0 1,822 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
86. 0.555 0.555 ↑ 1.0 1,822 1

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

87. 8.579 201.923 ↓ 16,702.0 16,702 1

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

88. 27.478 193.344 ↓ 16,702.0 16,702 1

Merge Right Join (cost=232,836.32..239,448.01 rows=1 width=118) (actual time=130.433..193.344 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
89. 32.906 32.906 ↑ 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.019..32.906 rows=58,756 loops=1)

90. 55.621 132.960 ↑ 16.0 17,028 1

Sort (cost=232,835.90..233,514.95 rows=271,620 width=64) (actual time=130.386..132.960 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
91. 4.827 77.339 ↑ 16.0 17,028 1

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

92. 8.614 38.828 ↓ 1.0 11,228 1

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

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

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

94. 0.803 26.831 ↑ 1.3 1,635 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 137kB
95. 0.721 26.028 ↑ 1.3 1,635 1

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

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_2.id)
96. 1.188 22.775 ↑ 1.3 1,635 1

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

  • Hash Cond: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
97. 0.625 0.625 ↑ 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.010..0.625 rows=4,121 loops=1)

98. 0.431 20.962 ↓ 9.0 1,635 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 74kB
99. 0.913 20.531 ↓ 9.0 1,635 1

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

  • Group Key: staff_activities_with_dates.link_id
100. 1.986 19.618 ↓ 3.6 1,635 1

Subquery Scan on staff_activities_with_dates (cost=810.60..934.02 rows=457 width=4) (actual time=16.052..19.618 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
101. 1.173 17.632 ↑ 1.0 4,086 1

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

102. 4.099 16.459 ↑ 1.0 4,114 1

Sort (cost=810.60..820.88 rows=4,114 width=613) (actual time=16.043..16.459 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
103. 4.271 12.360 ↑ 1.0 4,114 1

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

104. 2.802 8.089 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=28) (actual time=7.608..8.089 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
105. 1.969 5.287 ↑ 1.0 4,114 1

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

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_3.id)
106. 0.620 0.620 ↑ 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.005..0.620 rows=4,121 loops=1)

107. 1.348 2.698 ↑ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
108. 1.350 1.350 ↑ 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.007..1.350 rows=3,958 loops=1)

109. 1.255 2.532 ↑ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 218kB
110. 1.277 1.277 ↑ 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.009..1.277 rows=3,958 loops=1)

111. 33.684 33.684 ↑ 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.002..0.003 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
112. 0.002 0.117 ↑ 38.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
113. 0.115 0.115 ↑ 38.0 3 1

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 339
114. 552.488 957.059 ↓ 3.7 973,811 1

Sort (cost=45,259.02..45,916.62 rows=263,037 width=48) (actual time=850.810..957.059 rows=973,811 loops=1)

  • Sort Key: ""*SELECT* 1"".project_id, ""*SELECT* 1"".activity_id
  • Sort Method: quicksort Memory: 25972kB
115. 44.671 404.571 ↑ 1.0 253,793 1

Append (cost=286.40..21,579.24 rows=263,037 width=48) (actual time=2.618..404.571 rows=253,793 loops=1)

116. 0.112 17.695 ↓ 8.1 388 1

Subquery Scan on *SELECT* 1 (cost=286.40..427.60 rows=48 width=48) (actual time=2.616..17.695 rows=388 loops=1)

117. 0.148 17.583 ↓ 8.1 388 1

Merge Join (cost=286.40..427.12 rows=48 width=612) (actual time=2.615..17.583 rows=388 loops=1)

  • Merge Cond: (clients_2.brand_id = brands.id)
118. 0.170 17.362 ↓ 8.1 388 1

Nested Loop (cost=280.26..1,625.03 rows=48 width=56) (actual time=2.538..17.362 rows=388 loops=1)

119. 0.008 1.560 ↓ 4.0 4 1

Merge Join (cost=279.84..280.14 rows=1 width=32) (actual time=1.542..1.560 rows=4 loops=1)

  • Merge Cond: (rate_cards.rateable_id = clients_2.brand_id)
120. 0.027 0.691 ↑ 37.7 3 1

WindowAgg (cost=106.29..109.12 rows=113 width=45) (actual time=0.673..0.691 rows=3 loops=1)

121. 0.046 0.664 ↑ 28.2 4 1

Sort (cost=106.29..106.57 rows=113 width=29) (actual time=0.663..0.664 rows=4 loops=1)

  • Sort Key: rate_cards.rateable_id, rate_cards.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 33kB
122. 0.618 0.618 ↑ 1.0 113 1

Seq Scan on rate_cards (cost=0.00..102.44 rows=113 width=29) (actual time=0.153..0.618 rows=113 loops=1)

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4642
123. 0.009 0.861 ↑ 1.0 4 1

Sort (cost=173.55..173.56 rows=4 width=8) (actual time=0.859..0.861 rows=4 loops=1)

  • Sort Key: clients_2.brand_id
  • Sort Method: quicksort Memory: 25kB
124. 0.005 0.852 ↑ 1.0 4 1

Nested Loop (cost=0.56..173.51 rows=4 width=8) (actual time=0.454..0.852 rows=4 loops=1)

125. 0.007 0.831 ↑ 1.0 4 1

Nested Loop (cost=0.28..172.25 rows=4 width=8) (actual time=0.442..0.831 rows=4 loops=1)

126. 0.804 0.804 ↑ 1.0 4 1

Seq Scan on pricing_models (cost=0.00..139.05 rows=4 width=4) (actual time=0.424..0.804 rows=4 loops=1)

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5680
127. 0.020 0.020 ↑ 1.0 1 4

Index Scan using projects_pkey on projects projects_2 (cost=0.28..8.30 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=4)

  • Index Cond: (id = pricing_models.project_id)
128. 0.016 0.016 ↑ 1.0 1 4

Index Scan using clients_pkey on clients clients_2 (cost=0.28..0.31 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=4)

  • Index Cond: (id = projects_2.client_id)
129. 15.632 15.632 ↓ 2.0 97 4

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates (cost=0.42..1,344.42 rows=48 width=28) (actual time=0.975..3.908 rows=97 loops=4)

  • Index Cond: (rate_card_id = rate_cards.id)
130. 0.047 0.073 ↑ 58.0 2 1

Sort (cost=6.14..6.43 rows=116 width=4) (actual time=0.072..0.073 rows=2 loops=1)

  • Sort Key: brands.id
  • Sort Method: quicksort Memory: 30kB
131. 0.026 0.026 ↑ 1.0 116 1

Seq Scan on brands (cost=0.00..2.16 rows=116 width=4) (actual time=0.008..0.026 rows=116 loops=1)

132. 20.746 110.526 ↓ 2.6 87,611 1

Subquery Scan on *SELECT* 2 (cost=570.01..5,336.48 rows=33,506 width=48) (actual time=17.633..110.526 rows=87,611 loops=1)

133. 57.615 89.780 ↓ 2.6 87,611 1

Hash Join (cost=570.01..5,001.42 rows=33,506 width=612) (actual time=17.632..89.780 rows=87,611 loops=1)

  • Hash Cond: (rates_1.rate_card_id = rate_cards_1.id)
134. 24.698 24.698 ↑ 1.0 170,280 1

Seq Scan on rates rates_1 (cost=0.00..3,457.80 rows=170,280 width=28) (actual time=0.014..24.698 rows=170,280 loops=1)

135. 0.242 7.467 ↑ 1.1 631 1

Hash (cost=561.33..561.33 rows=695 width=24) (actual time=7.467..7.467 rows=631 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
136. 0.250 7.225 ↑ 1.1 631 1

Hash Join (cost=369.72..561.33 rows=695 width=24) (actual time=4.581..7.225 rows=631 loops=1)

  • Hash Cond: (clients_3.office_id = offices.id)
137. 0.253 4.786 ↑ 1.0 597 1

Hash Join (cost=208.51..391.23 rows=597 width=8) (actual time=2.386..4.786 rows=597 loops=1)

  • Hash Cond: (projects_3.client_id = clients_3.id)
138. 1.272 3.476 ↑ 1.0 597 1

Hash Join (cost=146.51..327.67 rows=597 width=8) (actual time=1.322..3.476 rows=597 loops=1)

  • Hash Cond: (projects_3.id = pricing_models_1.project_id)
139. 0.920 0.920 ↑ 1.0 5,686 1

Seq Scan on projects projects_3 (cost=0.00..153.86 rows=5,686 width=8) (actual time=0.009..0.920 rows=5,686 loops=1)

140. 0.153 1.284 ↑ 1.0 597 1

Hash (cost=139.05..139.05 rows=597 width=4) (actual time=1.283..1.284 rows=597 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
141. 1.131 1.131 ↑ 1.0 597 1

Seq Scan on pricing_models pricing_models_1 (cost=0.00..139.05 rows=597 width=4) (actual time=0.021..1.131 rows=597 loops=1)

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5087
142. 0.497 1.057 ↑ 1.0 1,822 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
143. 0.560 0.560 ↑ 1.0 1,822 1

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

144. 0.189 2.189 ↑ 1.1 438 1

Hash (cost=155.25..155.25 rows=477 width=28) (actual time=2.188..2.189 rows=438 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
145. 0.175 2.000 ↑ 1.1 438 1

Hash Join (cost=137.29..155.25 rows=477 width=28) (actual time=1.263..2.000 rows=438 loops=1)

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
146. 0.523 1.644 ↑ 1.0 477 1

WindowAgg (cost=123.66..135.58 rows=477 width=45) (actual time=1.076..1.644 rows=477 loops=1)

147. 0.254 1.121 ↑ 1.0 477 1

Sort (cost=123.66..124.85 rows=477 width=29) (actual time=1.068..1.121 rows=477 loops=1)

  • Sort Key: rate_cards_1.rateable_id, rate_cards_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 62kB
148. 0.867 0.867 ↑ 1.0 477 1

Seq Scan on rate_cards rate_cards_1 (cost=0.00..102.44 rows=477 width=29) (actual time=0.168..0.867 rows=477 loops=1)

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4278
149. 0.093 0.181 ↑ 1.0 428 1

Hash (cost=8.28..8.28 rows=428 width=4) (actual time=0.181..0.181 rows=428 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
150. 0.088 0.088 ↑ 1.0 428 1

Seq Scan on offices (cost=0.00..8.28 rows=428 width=4) (actual time=0.007..0.088 rows=428 loops=1)

151. 20.931 121.380 ↑ 2.2 91,407 1

Subquery Scan on *SELECT* 3 (cost=824.34..9,105.14 rows=198,580 width=48) (actual time=15.852..121.380 rows=91,407 loops=1)

152. 59.917 100.449 ↑ 2.2 91,407 1

Hash Join (cost=824.34..7,119.34 rows=198,580 width=612) (actual time=15.851..100.449 rows=91,407 loops=1)

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
153. 24.788 24.788 ↑ 1.0 170,280 1

Seq Scan on rates rates_2 (cost=0.00..3,457.80 rows=170,280 width=28) (actual time=0.014..24.788 rows=170,280 loops=1)

154. 1.441 15.744 ↑ 1.1 3,909 1

Hash (cost=772.85..772.85 rows=4,119 width=24) (actual time=15.744..15.744 rows=3,909 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 294kB
155. 1.629 14.303 ↑ 1.1 3,909 1

Hash Join (cost=576.26..772.85 rows=4,119 width=24) (actual time=9.491..14.303 rows=3,909 loops=1)

  • Hash Cond: (projects_4.client_id = clients_4.id)
156. 1.727 6.691 ↑ 1.0 3,545 1

Hash Join (cost=224.94..373.31 rows=3,546 width=8) (actual time=3.497..6.691 rows=3,545 loops=1)

  • Hash Cond: (pricing_models_2.project_id = projects_4.id)
157. 1.538 1.538 ↑ 1.0 3,546 1

Seq Scan on pricing_models pricing_models_2 (cost=0.00..139.05 rows=3,547 width=4) (actual time=0.010..1.538 rows=3,546 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2138
158. 1.473 3.426 ↑ 1.0 5,686 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 287kB
159. 1.953 1.953 ↑ 1.0 5,686 1

Seq Scan on projects projects_4 (cost=0.00..153.86 rows=5,686 width=8) (actual time=0.008..1.953 rows=5,686 loops=1)

160. 0.672 5.983 ↑ 1.0 1,792 1

Hash (cost=328.90..328.90 rows=1,794 width=28) (actual time=5.983..5.983 rows=1,792 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 121kB
161. 0.698 5.311 ↑ 1.0 1,792 1

Hash Join (cost=261.39..328.90 rows=1,794 width=28) (actual time=2.671..5.311 rows=1,792 loops=1)

  • Hash Cond: (rate_cards_2.rateable_id = clients_4.id)
162. 1.774 3.664 ↑ 1.0 1,793 1

WindowAgg (cost=199.39..244.24 rows=1,794 width=45) (actual time=1.713..3.664 rows=1,793 loops=1)

163. 0.970 1.890 ↑ 1.0 1,793 1

Sort (cost=199.39..203.88 rows=1,794 width=29) (actual time=1.702..1.890 rows=1,793 loops=1)

  • Sort Key: rate_cards_2.rateable_id, rate_cards_2.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 189kB
164. 0.920 0.920 ↑ 1.0 1,793 1

Seq Scan on rate_cards rate_cards_2 (cost=0.00..102.44 rows=1,794 width=29) (actual time=0.012..0.920 rows=1,793 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 2962
165. 0.549 0.949 ↑ 1.0 1,822 1

Hash (cost=39.22..39.22 rows=1,822 width=4) (actual time=0.949..0.949 rows=1,822 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
166. 0.400 0.400 ↑ 1.0 1,822 1

Seq Scan on clients clients_4 (cost=0.00..39.22 rows=1,822 width=4) (actual time=0.012..0.400 rows=1,822 loops=1)

167. 18.158 110.299 ↓ 2.4 74,387 1

Subquery Scan on *SELECT* 4 (cost=680.42..5,394.83 rows=30,903 width=48) (actual time=12.157..110.299 rows=74,387 loops=1)

168. 55.401 92.141 ↓ 2.4 74,387 1

Hash Join (cost=680.42..5,085.80 rows=30,903 width=612) (actual time=12.156..92.141 rows=74,387 loops=1)

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
169. 24.622 24.622 ↑ 1.0 170,280 1

Seq Scan on rates rates_3 (cost=0.00..3,457.80 rows=170,280 width=28) (actual time=0.014..24.622 rows=170,280 loops=1)

170. 0.662 12.118 ↓ 2.4 1,567 1

Hash (cost=672.41..672.41 rows=641 width=24) (actual time=12.118..12.118 rows=1,567 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 108kB
171. 1.840 11.456 ↓ 2.4 1,567 1

Hash Join (cost=490.82..672.41 rows=641 width=24) (actual time=8.384..11.456 rows=1,567 loops=1)

  • Hash Cond: (projects_5.id = pricing_models_3.project_id)
172. 1.261 1.261 ↑ 1.0 5,686 1

Seq Scan on projects projects_5 (cost=0.00..153.86 rows=5,686 width=4) (actual time=0.007..1.261 rows=5,686 loops=1)

173. 0.597 8.355 ↓ 2.4 1,567 1

Hash (cost=482.81..482.81 rows=641 width=24) (actual time=8.355..8.355 rows=1,567 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 108kB
174. 0.780 7.758 ↓ 2.4 1,567 1

Hash Join (cost=393.60..482.81 rows=641 width=24) (actual time=4.176..7.758 rows=1,567 loops=1)

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
175. 2.558 5.260 ↓ 1.0 2,372 1

WindowAgg (cost=235.35..294.62 rows=2,371 width=45) (actual time=2.445..5.260 rows=2,372 loops=1)

176. 1.548 2.702 ↓ 1.0 2,372 1

Sort (cost=235.35..241.27 rows=2,371 width=29) (actual time=2.433..2.702 rows=2,372 loops=1)

  • Sort Key: rate_cards_3.rateable_id, rate_cards_3.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 282kB
177. 1.154 1.154 ↓ 1.0 2,372 1

Seq Scan on rate_cards rate_cards_3 (cost=0.00..102.44 rows=2,371 width=29) (actual time=0.011..1.154 rows=2,372 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2383
178. 0.414 1.718 ↓ 1.0 1,537 1

Hash (cost=139.05..139.05 rows=1,536 width=8) (actual time=1.718..1.718 rows=1,537 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
179. 1.304 1.304 ↓ 1.0 1,537 1

Seq Scan on pricing_models pricing_models_3 (cost=0.00..139.05 rows=1,536 width=8) (actual time=0.012..1.304 rows=1,537 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4147
180. 36.883 37.027 ↓ 1,450.2 414,749 1

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

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

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

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
182. 3,123.943 3,124.320 ↓ 26,074.1 32,749,038 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=1.052..3,124.320 rows=32,749,038 loops=1)

  • Sort Key: cost_exchange_rates.account_id, cost_exchange_rates.currency
  • Sort Method: quicksort Memory: 147kB
183. 0.377 0.377 ↑ 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.022..0.377 rows=1,256 loops=1)

184. 3,233.534 3,233.831 ↓ 25,180.3 31,626,403 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=0.798..3,233.831 rows=31,626,403 loops=1)

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 147kB
185. 0.297 0.297 ↑ 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.018..0.297 rows=1,256 loops=1)

186. 3,226.864 3,227.144 ↓ 25,180.3 31,626,403 1

Sort (cost=93.21..96.35 rows=1,256 width=24) (actual time=0.756..3,227.144 rows=31,626,403 loops=1)

  • Sort Key: original_exchange_rates.account_id, original_exchange_rates.currency
  • Sort Method: quicksort Memory: 147kB
187. 0.280 0.280 ↑ 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.007..0.280 rows=1,256 loops=1)

Planning time : 10.500 ms
Execution time : 72,034.789 ms