explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qD5n

Settings
# exclusive inclusive rows x rows loops node
1. 504.429 42,377.056 ↓ 7.6 595,625 1

GroupAggregate (cost=2,446,387.51..2,453,648.57 rows=78,498 width=208) (actual time=41,816.087..42,377.056 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))
2. 1,837.525 41,872.627 ↓ 7.6 595,625 1

Sort (cost=2,446,387.51..2,446,583.75 rows=78,498 width=204) (actual time=41,816.071..41,872.627 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
3. 4,752.596 40,035.102 ↓ 7.6 595,625 1

Merge Left Join (cost=2,438,717.08..2,440,005.48 rows=78,498 width=204) (actual time=26,722.209..40,035.102 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
4. 4,774.866 33,411.660 ↓ 7.6 595,625 1

Merge Left Join (cost=2,438,623.87..2,439,268.07 rows=78,498 width=188) (actual time=26,721.570..33,411.660 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
5. 471.230 26,767.429 ↓ 7.6 595,625 1

Sort (cost=2,438,530.66..2,438,726.91 rows=78,498 width=180) (actual time=26,720.943..26,767.429 rows=595,625 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".currency
  • Sort Method: quicksort Memory: 159966kB
6. 4,940.408 26,296.199 ↓ 7.6 595,625 1

Merge Left Join (cost=2,431,504.43..2,432,148.63 rows=78,498 width=180) (actual time=19,391.335..26,296.199 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
7. 454.541 19,431.411 ↓ 7.6 595,625 1

Sort (cost=2,431,411.22..2,431,607.47 rows=78,498 width=688) (actual time=19,390.653..19,431.411 rows=595,625 loops=1)

  • Sort Key: projects.account_id, staff_memberships_1.currency
  • Sort Method: quicksort Memory: 163035kB
8. 172.642 18,976.870 ↓ 7.6 595,625 1

Merge Left Join (cost=2,409,262.22..2,425,029.19 rows=78,498 width=688) (actual time=18,318.120..18,976.870 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
9. 347.431 18,779.221 ↓ 7.6 595,625 1

Merge Left Join (cost=2,409,240.76..2,421,443.93 rows=78,498 width=684) (actual time=18,317.949..18,779.221 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
10. 377.000 17,873.246 ↓ 7.6 595,625 1

Sort (cost=2,363,981.73..2,364,177.98 rows=78,498 width=652) (actual time=17,823.048..17,873.246 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
11. 184.502 17,496.246 ↓ 7.6 595,625 1

Hash Join (cost=2,035,340.23..2,357,599.70 rows=78,498 width=652) (actual time=9,508.545..17,496.246 rows=595,625 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
12. 117.934 17,311.646 ↓ 4.6 1,091,769 1

Append (cost=2,035,321.82..2,354,600.53 rows=235,493 width=652) (actual time=8,632.442..17,311.646 rows=1,091,769 loops=1)

13. 390.710 17,080.993 ↓ 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=8,632.442..17,080.993 rows=1,075,067 loops=1)

  • Hash Cond: (projects.client_id = clients.id)
14. 5,714.294 16,689.718 ↓ 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=8,631.866..16,689.718 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
15. 1,088.966 8,790.667 ↓ 4.6 1,075,067 1

Sort (cost=2,031,118.65..2,031,707.38 rows=235,492 width=153) (actual time=8,613.091..8,790.667 rows=1,075,067 loops=1)

  • Sort Key: "*SELECT* 1_1".project_plan_id
  • Sort Method: external merge Disk: 124872kB
16. 272.565 7,701.701 ↓ 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=6,728.953..7,701.701 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
17. 492.985 7,429.001 ↓ 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=6,728.811..7,429.001 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
18. 1,078.352 6,846.884 ↓ 1.5 1,080,426 1

Sort (cost=2,000,674.00..2,002,422.70 rows=699,480 width=124) (actual time=6,717.645..6,846.884 rows=1,080,426 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1_1"".user_id
  • Sort Method: quicksort Memory: 181193kB
19. 300.933 5,768.532 ↓ 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,006.543..5,768.532 rows=1,080,426 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
20. 169.971 5,465.785 ↓ 2.2 1,549,746 1

Append (cost=1,839,037.76..1,923,711.51 rows=699,480 width=120) (actual time=4,004.721..5,465.785 rows=1,549,746 loops=1)

21. 249.299 4,738.634 ↓ 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=4,004.721..4,738.634 rows=860,997 loops=1)

22. 386.324 4,489.335 ↓ 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,004.716..4,489.335 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
23. 19.102 27.498 ↑ 1.0 58,766 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=23.844..27.498 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
24. 8.396 8.396 ↑ 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.007..8.396 rows=58,799 loops=1)

25. 881.137 4,075.513 ↓ 1.2 1,501,593 1

Sort (cost=1,833,118.86..1,836,260.80 rows=1,256,775 width=110) (actual time=3,980.864..4,075.513 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
26. 166.840 3,194.376 ↑ 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=2,724.640..3,194.376 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))
27. 224.599 2,986.627 ↑ 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=2,687.896..2,986.627 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))
28. 1,936.618 2,655.772 ↑ 1.4 870,234 1

Sort (cost=1,086,921.64..1,090,063.58 rows=1,256,775 width=70) (actual time=2,585.921..2,655.772 rows=870,234 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, plan_rows.user_id
  • Sort Method: quicksort Memory: 146953kB
29. 149.280 719.154 ↑ 1.4 870,234 1

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

30. 12.046 98.103 ↓ 1.0 52,419 1

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

  • Hash Cond: (projects_1.client_id = clients_1.id)
31. 12.474 85.559 ↓ 1.0 52,419 1

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

  • Hash Cond: (project_plans.project_id = projects_1.id)
32. 15.595 71.276 ↓ 1.0 52,419 1

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

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
33. 23.772 54.337 ↓ 1.0 52,419 1

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

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

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

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 255
35. 10.603 19.152 ↓ 1.0 46,641 1

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

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

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

  • Filter: ((user_id IS NOT NULL) OR (activity_id IS NOT NULL))
  • Rows Removed by Filter: 4
37. 0.378 1.344 ↓ 1.0 2,819 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3091
39. 0.847 1.809 ↑ 1.0 5,686 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 309kB
40. 0.962 0.962 ↑ 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..0.962 rows=5,686 loops=1)

41. 0.231 0.498 ↑ 1.0 1,822 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
42. 0.267 0.267 ↑ 1.0 1,822 1

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

43. 471.771 471.771 ↑ 1.5 17 52,419

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.004..0.009 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
44. 32.444 106.256 ↑ 16.5 34,338 1

Sort (cost=584,189.58..585,604.08 rows=565,800 width=44) (actual time=101.969..106.256 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
45. 3.708 73.812 ↑ 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=55.543..73.812 rows=29,556 loops=1)

46. 27.403 70.104 ↑ 19.1 29,556 1

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

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
47. 9.529 42.701 ↑ 142.6 29,683 1

Hash Join (cost=136.06..473,655.25 rows=4,232,734 width=21) (actual time=1.501..42.701 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
48. 8.783 31.725 ↑ 661.8 31,591 1

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

49. 2.035 2.035 ↑ 1.0 20,907 1

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

50. 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)

51. 0.763 1.447 ↑ 1.0 3,958 1

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

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

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

53. 31.973 40.909 ↓ 1.0 60,807 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=36.739..40.909 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
54. 8.936 8.936 ↑ 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..8.936 rows=58,799 loops=1)

55. 276.568 557.180 ↑ 1.0 688,749 1

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

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

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

57. 4.820 15.576 ↑ 1.0 26,131 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1368kB
58. 6.816 10.756 ↑ 1.0 26,131 1

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

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

60. 0.446 1.467 ↓ 1.0 2,819 1

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

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

  • Filter: active
  • Rows Removed by Filter: 3091
62. 0.826 1.814 ↑ 1.0 5,686 1

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

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

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

64. 76.360 89.132 ↓ 285.4 1,174,237 1

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

65. 1.136 12.772 ↑ 1.0 4,053 1

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

66. 2.960 11.636 ↑ 1.0 4,075 1

Sort (cost=820.88..831.17 rows=4,114 width=65) (actual time=11.157..11.636 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
67. 2.878 8.676 ↑ 1.0 4,114 1

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

68. 2.186 5.798 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=56) (actual time=5.498..5.798 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
69. 1.396 3.612 ↑ 1.0 4,114 1

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

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

71. 0.967 1.805 ↑ 1.0 3,958 1

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

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

73. 0.050 0.135 ↑ 1.0 342 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
74. 0.085 0.085 ↑ 1.0 342 1

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

75. 2,178.094 2,184.757 ↓ 817.9 35,256,840 1

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

  • Sort Key: pa.project_plan_id
  • Sort Method: quicksort Memory: 5488kB
76. 6.663 6.663 ↑ 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.012..6.663 rows=43,105 loops=1)

77. 0.258 0.565 ↑ 1.0 1,822 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
78. 0.307 0.307 ↑ 1.0 1,822 1

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

79. 4.630 112.719 ↓ 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=73.187..112.719 rows=16,702 loops=1)

80. 15.372 108.089 ↓ 16,702.0 16,702 1

Merge Right Join (cost=232,836.32..239,448.01 rows=1 width=118) (actual time=73.183..108.089 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
81. 18.093 18.093 ↑ 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.014..18.093 rows=58,756 loops=1)

82. 31.206 74.624 ↑ 16.0 17,028 1

Sort (cost=232,835.90..233,514.95 rows=271,620 width=64) (actual time=73.149..74.624 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
83. 10.046 43.418 ↑ 16.0 17,028 1

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

84. 4.869 22.144 ↓ 1.0 11,228 1

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

  • Hash Cond: (vacations_1.staff_membership_id = staff_memberships_2.id)
85. 2.051 2.051 ↑ 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.051 rows=20,907 loops=1)

86. 0.418 15.224 ↑ 1.3 1,635 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 137kB
87. 0.414 14.806 ↑ 1.3 1,635 1

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

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_2.id)
88. 0.714 12.988 ↑ 1.3 1,635 1

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

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

90. 0.243 11.925 ↓ 9.0 1,635 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 74kB
91. 0.503 11.682 ↓ 9.0 1,635 1

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

  • Group Key: staff_activities_with_dates.link_id
92. 1.226 11.179 ↓ 3.6 1,635 1

Subquery Scan on staff_activities_with_dates (cost=810.60..934.02 rows=457 width=4) (actual time=8.984..11.179 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
93. 0.718 9.953 ↑ 1.0 4,086 1

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

94. 2.363 9.235 ↑ 1.0 4,114 1

Sort (cost=810.60..820.88 rows=4,114 width=613) (actual time=8.978..9.235 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
95. 2.515 6.872 ↑ 1.0 4,114 1

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

96. 1.533 4.357 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=28) (actual time=4.071..4.357 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
97. 1.059 2.824 ↑ 1.0 4,114 1

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

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

99. 0.738 1.415 ↑ 1.0 3,958 1

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

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

101. 0.693 1.404 ↑ 1.0 3,958 1

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

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

103. 11.228 11.228 ↑ 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.001 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
104. 0.002 0.098 ↑ 38.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
105. 0.096 0.096 ↑ 38.0 3 1

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 339
106. 325.387 558.544 ↓ 3.7 973,811 1

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

  • Sort Key: ""*SELECT* 1"".project_id, ""*SELECT* 1"".activity_id
  • Sort Method: quicksort Memory: 25972kB
107. 25.726 233.157 ↑ 1.0 253,793 1

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

108. 0.062 10.380 ↓ 8.1 388 1

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

109. 0.087 10.318 ↓ 8.1 388 1

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

  • Merge Cond: (clients_2.brand_id = brands.id)
110. 0.096 10.197 ↓ 8.1 388 1

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

111. 0.005 1.065 ↓ 4.0 4 1

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

  • Merge Cond: (rate_cards.rateable_id = clients_2.brand_id)
112. 0.019 0.477 ↑ 37.7 3 1

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

113. 0.037 0.458 ↑ 28.2 4 1

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

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

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

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

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

  • Sort Key: clients_2.brand_id
  • Sort Method: quicksort Memory: 25kB
116. 0.003 0.574 ↑ 1.0 4 1

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

117. 0.005 0.559 ↑ 1.0 4 1

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

118. 0.538 0.538 ↑ 1.0 4 1

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

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5680
119. 0.016 0.016 ↑ 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.004..0.004 rows=1 loops=4)

  • Index Cond: (id = pricing_models.project_id)
120. 0.012 0.012 ↑ 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.003..0.003 rows=1 loops=4)

  • Index Cond: (id = projects_2.client_id)
121. 9.036 9.036 ↓ 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.567..2.259 rows=97 loops=4)

  • Index Cond: (rate_card_id = rate_cards.id)
122. 0.019 0.034 ↑ 58.0 2 1

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

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

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

124. 11.452 62.354 ↓ 2.6 87,611 1

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

125. 32.306 50.902 ↓ 2.6 87,611 1

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

  • Hash Cond: (rates_1.rate_card_id = rate_cards_1.id)
126. 14.409 14.409 ↑ 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..14.409 rows=170,280 loops=1)

127. 0.141 4.187 ↑ 1.1 631 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
128. 0.131 4.046 ↑ 1.1 631 1

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

  • Hash Cond: (clients_3.office_id = offices.id)
129. 0.136 2.690 ↑ 1.0 597 1

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

  • Hash Cond: (projects_3.client_id = clients_3.id)
130. 0.693 1.968 ↑ 1.0 597 1

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

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

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

132. 0.082 0.739 ↑ 1.0 597 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
133. 0.657 0.657 ↑ 1.0 597 1

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

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5087
134. 0.275 0.586 ↑ 1.0 1,822 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
135. 0.311 0.311 ↑ 1.0 1,822 1

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

136. 0.097 1.225 ↑ 1.1 438 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
137. 0.100 1.128 ↑ 1.1 438 1

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

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
138. 0.283 0.929 ↑ 1.0 477 1

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

139. 0.153 0.646 ↑ 1.0 477 1

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

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

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

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4278
141. 0.049 0.099 ↑ 1.0 428 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
142. 0.050 0.050 ↑ 1.0 428 1

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

143. 12.223 70.713 ↑ 2.2 91,407 1

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

144. 34.637 58.490 ↑ 2.2 91,407 1

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

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
145. 14.821 14.821 ↑ 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.011..14.821 rows=170,280 loops=1)

146. 0.803 9.032 ↑ 1.1 3,909 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 294kB
147. 0.894 8.229 ↑ 1.1 3,909 1

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

  • Hash Cond: (projects_4.client_id = clients_4.id)
148. 0.898 3.750 ↑ 1.0 3,545 1

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

  • Hash Cond: (pricing_models_2.project_id = projects_4.id)
149. 0.883 0.883 ↑ 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.007..0.883 rows=3,546 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2138
150. 0.851 1.969 ↑ 1.0 5,686 1

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

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

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

152. 0.386 3.585 ↑ 1.0 1,792 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 121kB
153. 0.400 3.199 ↑ 1.0 1,792 1

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

  • Hash Cond: (rate_cards_2.rateable_id = clients_4.id)
154. 1.077 2.334 ↑ 1.0 1,793 1

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

155. 0.640 1.257 ↑ 1.0 1,793 1

Sort (cost=199.39..203.88 rows=1,794 width=29) (actual time=1.143..1.257 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
156. 0.617 0.617 ↑ 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.006..0.617 rows=1,793 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 2962
157. 0.249 0.465 ↑ 1.0 1,822 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
158. 0.216 0.216 ↑ 1.0 1,822 1

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

159. 10.370 63.984 ↓ 2.4 74,387 1

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

160. 32.152 53.614 ↓ 2.4 74,387 1

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

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
161. 14.960 14.960 ↑ 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.012..14.960 rows=170,280 loops=1)

162. 0.338 6.502 ↓ 2.4 1,567 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 108kB
163. 0.841 6.164 ↓ 2.4 1,567 1

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

  • Hash Cond: (projects_5.id = pricing_models_3.project_id)
164. 0.533 0.533 ↑ 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..0.533 rows=5,686 loops=1)

165. 0.319 4.790 ↓ 2.4 1,567 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 108kB
166. 0.489 4.471 ↓ 2.4 1,567 1

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

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
167. 1.482 2.995 ↓ 1.0 2,372 1

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

168. 0.852 1.513 ↓ 1.0 2,372 1

Sort (cost=235.35..241.27 rows=2,371 width=29) (actual time=1.364..1.513 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
169. 0.661 0.661 ↓ 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.007..0.661 rows=2,372 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2383
170. 0.235 0.987 ↓ 1.0 1,537 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
171. 0.752 0.752 ↓ 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.008..0.752 rows=1,537 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4147
172. 24.927 25.007 ↓ 1,450.2 414,749 1

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

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

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

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
174. 1,924.162 1,924.380 ↓ 26,074.1 32,749,038 1

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

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

176. 1,869.150 1,869.365 ↓ 25,180.3 31,626,403 1

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

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

178. 1,870.640 1,870.846 ↓ 25,180.3 31,626,403 1

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

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

Planning time : 6.914 ms
Execution time : 42,590.168 ms