explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XcOR

Settings
# exclusive inclusive rows x rows loops node
1. 587.723 47,284.557 ↓ 7.6 595,625 1

GroupAggregate (cost=2,574,694.01..2,581,367.53 rows=78,512 width=196) (actual time=46,631.629..47,284.557 rows=595,625 loops=1)

  • Group Key: projects.account_id, projects.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,568.553 46,696.834 ↓ 7.6 595,625 1

Sort (cost=2,574,694.01..2,574,890.29 rows=78,512 width=192) (actual time=46,631.616..46,696.834 rows=595,625 loops=1)

  • Sort Key: projects.account_id, projects.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: 166614kB
3. 5,468.843 45,128.281 ↓ 7.6 595,625 1

Merge Left Join (cost=2,566,894.24..2,568,310.74 rows=78,512 width=192) (actual time=29,746.474..45,128.281 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: 30375983
4. 5,556.605 37,509.137 ↓ 7.6 595,625 1

Merge Left Join (cost=2,566,674.06..2,567,382.31 rows=78,512 width=176) (actual time=29,744.939..37,509.137 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: 30375983
5. 510.492 29,798.851 ↓ 7.6 595,625 1

Sort (cost=2,566,453.89..2,566,650.17 rows=78,512 width=168) (actual time=29,743.680..29,798.851 rows=595,625 loops=1)

  • Sort Key: projects.account_id, "*SELECT* 1".currency
  • Sort Method: quicksort Memory: 153616kB
6. 5,625.087 29,288.359 ↓ 7.6 595,625 1

Merge Left Join (cost=2,559,362.37..2,560,070.61 rows=78,512 width=168) (actual time=21,423.138..29,288.359 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: 31418826
7. 484.134 21,471.079 ↓ 7.6 595,625 1

Sort (cost=2,559,142.19..2,559,338.47 rows=78,512 width=676) (actual time=21,421.679..21,471.079 rows=595,625 loops=1)

  • Sort Key: projects.account_id, staff_memberships_1.currency
  • Sort Method: quicksort Memory: 128831kB
8. 199.540 20,986.945 ↓ 7.6 595,625 1

Merge Left Join (cost=2,536,990.98..2,552,758.92 rows=78,512 width=676) (actual time=20,227.103..20,986.945 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. 397.593 20,758.294 ↓ 7.6 595,625 1

Merge Left Join (cost=2,536,969.52..2,549,173.02 rows=78,512 width=672) (actual time=20,226.923..20,758.294 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. 467.560 19,723.071 ↓ 7.6 595,625 1

Sort (cost=2,491,460.27..2,491,656.55 rows=78,512 width=644) (actual time=19,664.101..19,723.071 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. 200.339 19,255.511 ↓ 7.6 595,625 1

Hash Join (cost=2,043,067.40..2,485,077.00 rows=78,512 width=644) (actual time=10,478.609..19,255.511 rows=595,625 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
12. 137.144 19,055.042 ↓ 4.6 1,091,769 1

Append (cost=2,043,039.99..2,482,068.27 rows=235,537 width=644) (actual time=9,513.234..19,055.042 rows=1,091,769 loops=1)

13. 6,690.197 18,785.300 ↓ 4.6 1,075,067 1

Merge Left Join (cost=2,043,039.99..2,238,590.82 rows=235,536 width=103) (actual time=9,513.233..18,785.300 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
14. 747.164 9,473.645 ↓ 4.6 1,075,067 1

Sort (cost=2,033,796.40..2,034,385.24 rows=235,536 width=149) (actual time=9,325.304..9,473.645 rows=1,075,067 loops=1)

  • Sort Key: "*SELECT* 1_1".project_plan_id
  • Sort Method: quicksort Memory: 231956kB
15. 319.518 8,726.481 ↓ 4.6 1,075,067 1

Hash Left Join (cost=2,004,189.82..2,012,780.01 rows=235,536 width=149) (actual time=7,607.378..8,726.481 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
16. 564.288 8,406.802 ↓ 1.5 1,080,426 1

Merge Left Join (cost=2,004,162.12..2,010,882.85 rows=699,611 width=144) (actual time=7,607.204..8,406.802 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
17. 1,220.333 7,741.439 ↓ 1.5 1,080,426 1

Sort (cost=2,003,343.57..2,005,092.60 rows=699,611 width=120) (actual time=7,594.094..7,741.439 rows=1,080,426 loops=1)

  • Sort Key: projects.account_id, "*SELECT* 1_1".user_id
  • Sort Method: quicksort Memory: 201087kB
18. 330.491 6,521.106 ↓ 1.5 1,080,426 1

Hash Join (cost=1,841,341.43..1,935,424.66 rows=699,611 width=120) (actual time=4,703.845..6,521.106 rows=1,080,426 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
19. 197.038 6,188.648 ↓ 2.2 1,549,746 1

Append (cost=1,841,022.54..1,926,271.81 rows=699,611 width=120) (actual time=4,701.862..6,188.648 rows=1,549,746 loops=1)

20. 292.869 5,572.195 ↓ 79.2 860,997 1

Subquery Scan on *SELECT* 1_1 (cost=1,841,022.54..1,885,324.38 rows=10,871 width=120) (actual time=4,701.861..5,572.195 rows=860,997 loops=1)

21. 461.297 5,279.326 ↓ 79.2 860,997 1

Merge Right Join (cost=1,841,022.54..1,885,134.14 rows=10,871 width=138) (actual time=4,701.856..5,279.326 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.office_id))
  • Filter: (COALESCE(non_working_intervals_by_dates.id, nw_intervals_by_dates_roles.id) IS NULL)
  • Rows Removed by Filter: 640596
22. 22.506 32.648 ↑ 1.0 58,766 1

Sort (cost=6,166.37..6,316.54 rows=60,068 width=16) (actual time=27.990..32.648 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
23. 10.142 10.142 ↑ 1.0 58,799 1

Seq Scan on non_working_intervals_by_dates nw_intervals_by_dates_roles (cost=0.00..1,398.68 rows=60,068 width=16) (actual time=0.007..10.142 rows=58,799 loops=1)

24. 1,026.497 4,785.381 ↓ 1.2 1,501,593 1

Sort (cost=1,834,856.17..1,838,002.42 rows=1,258,500 width=110) (actual time=4,673.857..4,785.381 rows=1,501,593 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, clients.office_id
  • Sort Method: quicksort Memory: 146956kB
25. 187.446 3,758.884 ↑ 1.4 870,252 1

Merge Left Join (cost=1,678,567.90..1,707,349.52 rows=1,258,500 width=110) (actual time=3,226.883..3,758.884 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))
26. 250.864 3,524.825 ↑ 1.4 870,234 1

Merge Left Join (cost=1,672,401.53..1,690,708.24 rows=1,258,500 width=106) (actual time=3,185.337..3,524.825 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))
27. 2,333.656 3,149.323 ↑ 1.4 870,234 1

Sort (cost=1,088,516.08..1,091,662.33 rows=1,258,500 width=70) (actual time=3,065.823..3,149.323 rows=870,234 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, plan_rows.user_id
  • Sort Method: quicksort Memory: 146953kB
28. 181.795 815.667 ↑ 1.4 870,234 1

Nested Loop (cost=2,136.98..961,009.43 rows=1,258,500 width=70) (actual time=25.241..815.667 rows=870,234 loops=1)

29. 14.096 109.682 ↓ 1.0 52,419 1

Hash Left Join (cost=2,136.97..4,549.42 rows=50,340 width=70) (actual time=25.212..109.682 rows=52,419 loops=1)

  • Hash Cond: (projects_1.client_id = clients.id)
30. 14.957 94.950 ↓ 1.0 52,419 1

Hash Left Join (cost=2,075.00..4,355.00 rows=50,340 width=70) (actual time=24.553..94.950 rows=52,419 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
31. 14.676 77.727 ↓ 1.0 52,419 1

Hash Left Join (cost=1,756.11..3,903.87 rows=50,340 width=62) (actual time=22.255..77.727 rows=52,419 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
32. 27.456 61.252 ↓ 1.0 52,419 1

Hash Join (cost=1,557.82..3,573.33 rows=50,340 width=58) (actual time=20.428..61.252 rows=52,419 loops=1)

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
33. 13.649 13.649 ↑ 1.0 52,422 1

Seq Scan on plan_items (cost=0.00..1,315.36 rows=52,466 width=46) (actual time=0.015..13.649 rows=52,422 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 255
34. 10.421 20.147 ↓ 1.0 46,641 1

Hash (cost=998.42..998.42 rows=44,752 width=28) (actual time=20.147..20.147 rows=46,641 loops=1)

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

Seq Scan on plan_rows (cost=0.00..998.42 rows=44,752 width=28) (actual time=0.007..9.726 rows=46,641 loops=1)

  • Filter: ((user_id IS NOT NULL) OR (activity_id IS NOT NULL))
  • Rows Removed by Filter: 4
36. 0.510 1.799 ↓ 1.0 2,819 1

Hash (cost=163.08..163.08 rows=2,817 width=8) (actual time=1.799..1.799 rows=2,819 loops=1)

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

Seq Scan on project_plans (cost=0.00..163.08 rows=2,817 width=8) (actual time=0.011..1.289 rows=2,819 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3091
38. 0.993 2.266 ↓ 1.0 5,686 1

Hash (cost=247.84..247.84 rows=5,684 width=12) (actual time=2.266..2.266 rows=5,686 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 309kB
39. 1.273 1.273 ↓ 1.0 5,686 1

Seq Scan on projects projects_1 (cost=0.00..247.84 rows=5,684 width=12) (actual time=0.006..1.273 rows=5,686 loops=1)

40. 0.301 0.636 ↓ 1.0 1,822 1

Hash (cost=39.21..39.21 rows=1,821 width=8) (actual time=0.636..0.636 rows=1,822 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
41. 0.335 0.335 ↓ 1.0 1,822 1

Seq Scan on clients (cost=0.00..39.21 rows=1,821 width=8) (actual time=0.007..0.335 rows=1,822 loops=1)

42. 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
43. 38.886 124.638 ↑ 16.4 34,338 1

Sort (cost=583,885.45..585,295.45 rows=564,000 width=44) (actual time=119.506..124.638 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
44. 4.307 85.752 ↑ 19.1 29,556 1

Subquery Scan on summary_vacations_by_dates (cost=515,908.40..530,008.40 rows=564,000 width=44) (actual time=64.313..85.752 rows=29,556 loops=1)

45. 31.807 81.445 ↑ 19.1 29,556 1

HashAggregate (cost=515,908.40..524,368.40 rows=564,000 width=52) (actual time=64.312..81.445 rows=29,556 loops=1)

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
46. 11.203 49.638 ↑ 142.6 29,683 1

Hash Join (cost=135.79..473,587.13 rows=4,232,127 width=21) (actual time=1.719..49.638 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
47. 13.408 36.750 ↑ 661.7 31,591 1

Nested Loop (cost=0.01..418,561.05 rows=20,904,000 width=17) (actual time=0.024..36.750 rows=31,591 loops=1)

48. 2.435 2.435 ↓ 1.0 20,907 1

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

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

50. 0.877 1.685 ↓ 1.0 3,958 1

Hash (cost=86.46..86.46 rows=3,946 width=20) (actual time=1.685..1.685 rows=3,958 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
51. 0.808 0.808 ↓ 1.0 3,958 1

Seq Scan on staff_memberships (cost=0.00..86.46 rows=3,946 width=20) (actual time=0.007..0.808 rows=3,958 loops=1)

52. 36.144 46.613 ↓ 1.0 60,807 1

Sort (cost=6,166.37..6,316.54 rows=60,068 width=16) (actual time=41.540..46.613 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
53. 10.469 10.469 ↑ 1.0 58,799 1

Seq Scan on non_working_intervals_by_dates (cost=0.00..1,398.68 rows=60,068 width=16) (actual time=0.015..10.469 rows=58,799 loops=1)

54. 300.637 419.415 ↓ 1.0 688,749 1

Hash Join (cost=1,805.70..30,561.98 rows=688,740 width=120) (actual time=16.149..419.415 rows=688,749 loops=1)

  • Hash Cond: (time_logs.epic_id = epics.id)
55. 102.808 102.808 ↓ 1.0 688,749 1

Seq Scan on time_logs (cost=0.00..15,842.40 rows=688,740 width=28) (actual time=0.050..102.808 rows=688,749 loops=1)

56. 5.048 15.970 ↓ 1.0 26,131 1

Hash (cost=1,479.11..1,479.11 rows=26,127 width=16) (actual time=15.970..15.970 rows=26,131 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1368kB
57. 6.862 10.922 ↓ 1.0 26,131 1

Hash Left Join (cost=198.29..1,479.11 rows=26,127 width=16) (actual time=1.553..10.922 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
58. 2.522 2.522 ↓ 1.0 26,131 1

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

59. 0.510 1.538 ↓ 1.0 2,819 1

Hash (cost=163.08..163.08 rows=2,817 width=8) (actual time=1.538..1.538 rows=2,819 loops=1)

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

Seq Scan on project_plans general_epic_plans (cost=0.00..163.08 rows=2,817 width=8) (actual time=0.007..1.028 rows=2,819 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3091
61. 0.950 1.967 ↓ 1.0 5,686 1

Hash (cost=247.84..247.84 rows=5,684 width=8) (actual time=1.967..1.967 rows=5,686 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 287kB
62. 1.017 1.017 ↓ 1.0 5,686 1

Seq Scan on projects (cost=0.00..247.84 rows=5,684 width=8) (actual time=0.018..1.017 rows=5,686 loops=1)

63. 85.884 101.075 ↓ 286.3 1,174,237 1

Materialize (cost=818.55..910.84 rows=4,102 width=40) (actual time=13.103..101.075 rows=1,174,237 loops=1)

64. 1.516 15.191 ↑ 1.0 4,053 1

Unique (cost=818.55..859.57 rows=4,102 width=65) (actual time=13.099..15.191 rows=4,053 loops=1)

65. 3.462 13.675 ↑ 1.0 4,075 1

Sort (cost=818.55..828.80 rows=4,102 width=65) (actual time=13.098..13.675 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
66. 3.364 10.213 ↓ 1.0 4,114 1

WindowAgg (cost=469.84..572.39 rows=4,102 width=65) (actual time=6.486..10.213 rows=4,114 loops=1)

67. 2.562 6.849 ↓ 1.0 4,114 1

Sort (cost=469.84..480.09 rows=4,102 width=56) (actual time=6.475..6.849 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
68. 1.802 4.287 ↓ 1.0 4,114 1

Hash Join (cost=135.79..223.67 rows=4,102 width=56) (actual time=1.985..4.287 rows=4,114 loops=1)

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
69. 0.557 0.557 ↓ 1.0 4,121 1

Seq Scan on staff_membership_activity_links (cost=0.00..77.09 rows=4,109 width=28) (actual time=0.016..0.557 rows=4,121 loops=1)

70. 1.021 1.928 ↓ 1.0 3,958 1

Hash (cost=86.46..86.46 rows=3,946 width=32) (actual time=1.928..1.928 rows=3,958 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 273kB
71. 0.907 0.907 ↓ 1.0 3,958 1

Seq Scan on staff_memberships staff_memberships_1 (cost=0.00..86.46 rows=3,946 width=32) (actual time=0.008..0.907 rows=3,958 loops=1)

72. 0.055 0.161 ↑ 1.0 342 1

Hash (cost=23.42..23.42 rows=342 width=9) (actual time=0.161..0.161 rows=342 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
73. 0.106 0.106 ↑ 1.0 342 1

Seq Scan on accounts accounts_1 (cost=0.00..23.42 rows=342 width=9) (actual time=0.015..0.106 rows=342 loops=1)

74. 2,451.014 2,621.458 ↓ 7,250.0 35,256,840 1

Sort (cost=9,243.59..9,255.75 rows=4,863 width=48) (actual time=187.916..2,621.458 rows=35,256,840 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: quicksort Memory: 5488kB
75. 6.748 170.444 ↓ 8.9 43,105 1

Subquery Scan on pa (cost=8,824.21..8,945.79 rows=4,863 width=48) (actual time=120.632..170.444 rows=43,105 loops=1)

76. 71.867 163.696 ↓ 8.9 43,105 1

HashAggregate (cost=8,824.21..8,897.16 rows=4,863 width=48) (actual time=120.631..163.696 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)
77. 29.292 91.829 ↓ 1.0 43,258 1

WindowAgg (cost=6,336.99..7,310.25 rows=43,256 width=68) (actual time=55.979..91.829 rows=43,258 loops=1)

78. 25.347 62.537 ↓ 1.0 43,258 1

Sort (cost=6,336.99..6,445.13 rows=43,256 width=60) (actual time=55.970..62.537 rows=43,258 loops=1)

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 6969kB
79. 16.900 37.190 ↓ 1.0 43,258 1

Hash Join (cost=1,400.26..3,006.15 rows=43,256 width=60) (actual time=15.777..37.190 rows=43,258 loops=1)

  • Hash Cond: (plan_rows_1.uuid = plan_roles.plan_row_uuid)
80. 4.759 4.759 ↓ 1.0 46,645 1

Seq Scan on plan_rows plan_rows_1 (cost=0.00..998.42 rows=46,642 width=28) (actual time=0.013..4.759 rows=46,645 loops=1)

81. 8.883 15.531 ↓ 1.0 43,260 1

Hash (cost=859.56..859.56 rows=43,256 width=32) (actual time=15.530..15.531 rows=43,260 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3096kB
82. 6.648 6.648 ↓ 1.0 43,260 1

Seq Scan on plan_roles (cost=0.00..859.56 rows=43,256 width=32) (actual time=0.008..6.648 rows=43,260 loops=1)

83. 5.070 132.598 ↓ 16,702.0 16,702 1

Subquery Scan on *SELECT* 2_1 (cost=232,813.24..239,944.40 rows=1 width=103) (actual time=88.408..132.598 rows=16,702 loops=1)

84. 16.821 127.528 ↓ 16,702.0 16,702 1

Merge Right Join (cost=232,813.24..239,944.39 rows=1 width=110) (actual time=88.404..127.528 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
85. 20.705 20.705 ↑ 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,926.69 rows=60,068 width=16) (actual time=0.033..20.705 rows=58,756 loops=1)

86. 39.343 90.002 ↑ 16.0 17,028 1

Sort (cost=232,812.82..233,491.85 rows=271,612 width=64) (actual time=88.355..90.002 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
87. 2.819 50.659 ↑ 16.0 17,028 1

Nested Loop (cost=1,214.15..208,298.23 rows=271,612 width=64) (actual time=17.435..50.659 rows=17,028 loops=1)

88. 5.692 25.384 ↓ 1.0 11,228 1

Hash Join (cost=1,214.14..1,882.22 rows=10,864 width=64) (actual time=17.405..25.384 rows=11,228 loops=1)

  • Hash Cond: (vacations_1.staff_membership_id = staff_memberships_2.id)
89. 2.319 2.319 ↓ 1.0 20,907 1

Seq Scan on vacations vacations_1 (cost=0.00..481.04 rows=20,904 width=44) (actual time=0.010..2.319 rows=20,907 loops=1)

90. 0.523 17.373 ↑ 1.3 1,635 1

Hash (cost=1,188.50..1,188.50 rows=2,051 width=32) (actual time=17.373..17.373 rows=1,635 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 137kB
91. 0.556 16.850 ↑ 1.3 1,635 1

Hash Join (cost=1,072.37..1,188.50 rows=2,051 width=32) (actual time=14.997..16.850 rows=1,635 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_2.id)
92. 0.906 14.724 ↑ 1.3 1,635 1

Hash Join (cost=936.59..1,047.32 rows=2,054 width=16) (actual time=13.389..14.724 rows=1,635 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
93. 0.465 0.465 ↓ 1.0 4,121 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_1 (cost=0.00..77.09 rows=4,109 width=20) (actual time=0.006..0.465 rows=4,121 loops=1)

94. 0.395 13.353 ↓ 9.0 1,635 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 74kB
95. 0.684 12.958 ↓ 9.0 1,635 1

HashAggregate (cost=932.49..934.31 rows=182 width=4) (actual time=12.686..12.958 rows=1,635 loops=1)

  • Group Key: staff_activities_with_dates.link_id
96. 1.458 12.274 ↓ 3.6 1,635 1

Subquery Scan on staff_activities_with_dates (cost=808.29..931.35 rows=456 width=4) (actual time=9.736..12.274 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
97. 0.803 10.816 ↑ 1.0 4,086 1

Unique (cost=808.29..849.31 rows=4,102 width=613) (actual time=9.730..10.816 rows=4,086 loops=1)

98. 2.638 10.013 ↓ 1.0 4,114 1

Sort (cost=808.29..818.55 rows=4,102 width=613) (actual time=9.729..10.013 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
99. 2.494 7.375 ↓ 1.0 4,114 1

WindowAgg (cost=469.84..562.13 rows=4,102 width=613) (actual time=4.581..7.375 rows=4,114 loops=1)

100. 1.830 4.881 ↓ 1.0 4,114 1

Sort (cost=469.84..480.09 rows=4,102 width=28) (actual time=4.566..4.881 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
101. 1.135 3.051 ↓ 1.0 4,114 1

Hash Join (cost=135.79..223.67 rows=4,102 width=28) (actual time=1.538..3.051 rows=4,114 loops=1)

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_3.id)
102. 0.402 0.402 ↓ 1.0 4,121 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_2 (cost=0.00..77.09 rows=4,109 width=12) (actual time=0.003..0.402 rows=4,121 loops=1)

103. 0.796 1.514 ↓ 1.0 3,958 1

Hash (cost=86.46..86.46 rows=3,946 width=20) (actual time=1.514..1.514 rows=3,958 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 227kB
104. 0.718 0.718 ↓ 1.0 3,958 1

Seq Scan on staff_memberships staff_memberships_3 (cost=0.00..86.46 rows=3,946 width=20) (actual time=0.006..0.718 rows=3,958 loops=1)

105. 0.763 1.570 ↓ 1.0 3,958 1

Hash (cost=86.46..86.46 rows=3,946 width=16) (actual time=1.570..1.570 rows=3,958 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 218kB
106. 0.807 0.807 ↓ 1.0 3,958 1

Seq Scan on staff_memberships staff_memberships_2 (cost=0.00..86.46 rows=3,946 width=16) (actual time=0.007..0.807 rows=3,958 loops=1)

107. 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
108. 0.007 0.130 ↑ 38.0 3 1

Hash (cost=25.98..25.98 rows=114 width=4) (actual time=0.130..0.130 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
109. 0.123 0.123 ↑ 38.0 3 1

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 339
110. 367.556 637.630 ↓ 3.7 973,811 1

Sort (cost=45,509.25..46,166.74 rows=262,995 width=44) (actual time=557.046..637.630 rows=973,811 loops=1)

  • Sort Key: "*SELECT* 1".project_id, "*SELECT* 1".activity_id
  • Sort Method: quicksort Memory: 25972kB
111. 29.805 270.074 ↑ 1.0 253,793 1

Append (cost=286.16..21,833.55 rows=262,995 width=44) (actual time=2.920..270.074 rows=253,793 loops=1)

112. 0.079 13.514 ↓ 8.1 388 1

Subquery Scan on *SELECT* 1 (cost=286.16..427.39 rows=48 width=44) (actual time=2.918..13.514 rows=388 loops=1)

113. 0.108 13.435 ↓ 8.1 388 1

Merge Join (cost=286.16..426.91 rows=48 width=612) (actual time=2.917..13.435 rows=388 loops=1)

  • Merge Cond: (clients_1.brand_id = brands.id)
114. 0.113 13.254 ↓ 8.1 388 1

Nested Loop (cost=280.18..1,625.40 rows=48 width=52) (actual time=2.839..13.254 rows=388 loops=1)

115. 0.006 1.901 ↓ 4.0 4 1

Merge Join (cost=279.76..280.05 rows=1 width=32) (actual time=1.896..1.901 rows=4 loops=1)

  • Merge Cond: (rate_cards.rateable_id = clients_1.brand_id)
116. 0.019 0.864 ↑ 37.7 3 1

WindowAgg (cost=106.23..109.05 rows=113 width=45) (actual time=0.856..0.864 rows=3 loops=1)

117. 0.064 0.845 ↑ 28.2 4 1

Sort (cost=106.23..106.51 rows=113 width=29) (actual time=0.844..0.845 rows=4 loops=1)

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

Seq Scan on rate_cards (cost=0.00..102.38 rows=113 width=29) (actual time=0.191..0.781 rows=113 loops=1)

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4642
119. 0.010 1.031 ↑ 1.0 4 1

Sort (cost=173.53..173.54 rows=4 width=8) (actual time=1.031..1.031 rows=4 loops=1)

  • Sort Key: clients_1.brand_id
  • Sort Method: quicksort Memory: 25kB
120. 0.006 1.021 ↑ 1.0 4 1

Nested Loop (cost=0.56..173.49 rows=4 width=8) (actual time=0.583..1.021 rows=4 loops=1)

121. 0.009 0.987 ↑ 1.0 4 1

Nested Loop (cost=0.28..172.23 rows=4 width=8) (actual time=0.560..0.987 rows=4 loops=1)

122. 0.954 0.954 ↑ 1.0 4 1

Seq Scan on pricing_models (cost=0.00..139.03 rows=4 width=4) (actual time=0.539..0.954 rows=4 loops=1)

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5680
123. 0.024 0.024 ↑ 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.006..0.006 rows=1 loops=4)

  • Index Cond: (id = pricing_models.project_id)
124. 0.028 0.028 ↑ 1.0 1 4

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

  • Index Cond: (id = projects_2.client_id)
125. 11.240 11.240 ↓ 2.0 97 4

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates (cost=0.42..1,344.88 rows=48 width=28) (actual time=0.736..2.810 rows=97 loops=4)

  • Index Cond: (rate_card_id = rate_cards.id)
126. 0.046 0.073 ↑ 56.5 2 1

Sort (cost=5.98..6.27 rows=113 width=4) (actual time=0.073..0.073 rows=2 loops=1)

  • Sort Key: brands.id
  • Sort Method: quicksort Memory: 30kB
127. 0.027 0.027 ↓ 1.0 116 1

Seq Scan on brands (cost=0.00..2.13 rows=113 width=4) (actual time=0.008..0.027 rows=116 loops=1)

128. 13.552 72.348 ↓ 2.6 87,611 1

Subquery Scan on *SELECT* 2 (cost=663.78..5,432.39 rows=33,568 width=44) (actual time=11.380..72.348 rows=87,611 loops=1)

129. 37.144 58.796 ↓ 2.6 87,611 1

Hash Join (cost=663.78..5,096.71 rows=33,568 width=612) (actual time=11.379..58.796 rows=87,611 loops=1)

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

Seq Scan on rates rates_1 (cost=0.00..3,458.46 rows=170,346 width=28) (actual time=0.006..16.831 rows=170,280 loops=1)

131. 0.145 4.821 ↑ 1.1 631 1

Hash (cost=655.08..655.08 rows=696 width=24) (actual time=4.821..4.821 rows=631 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
132. 0.144 4.676 ↑ 1.1 631 1

Hash Join (cost=369.51..655.08 rows=696 width=24) (actual time=3.110..4.676 rows=631 loops=1)

  • Hash Cond: (clients_2.office_id = offices.id)
133. 0.141 2.863 ↑ 1.0 597 1

Hash Join (cost=208.46..485.15 rows=597 width=8) (actual time=1.429..2.863 rows=597 loops=1)

  • Hash Cond: (projects_3.client_id = clients_2.id)
134. 0.806 2.130 ↑ 1.0 597 1

Hash Join (cost=146.49..421.61 rows=597 width=8) (actual time=0.831..2.130 rows=597 loops=1)

  • Hash Cond: (projects_3.id = pricing_models_1.project_id)
135. 0.502 0.502 ↓ 1.0 5,686 1

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

136. 0.092 0.822 ↑ 1.0 597 1

Hash (cost=139.03..139.03 rows=597 width=4) (actual time=0.822..0.822 rows=597 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
137. 0.730 0.730 ↑ 1.0 597 1

Seq Scan on pricing_models pricing_models_1 (cost=0.00..139.03 rows=597 width=4) (actual time=0.012..0.730 rows=597 loops=1)

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5087
138. 0.273 0.592 ↓ 1.0 1,822 1

Hash (cost=39.21..39.21 rows=1,821 width=8) (actual time=0.592..0.592 rows=1,822 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
139. 0.319 0.319 ↓ 1.0 1,822 1

Seq Scan on clients clients_2 (cost=0.00..39.21 rows=1,821 width=8) (actual time=0.008..0.319 rows=1,822 loops=1)

140. 0.103 1.669 ↑ 1.1 438 1

Hash (cost=155.10..155.10 rows=476 width=28) (actual time=1.669..1.669 rows=438 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
141. 0.109 1.566 ↑ 1.1 438 1

Hash Join (cost=137.17..155.10 rows=476 width=28) (actual time=1.148..1.566 rows=438 loops=1)

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
142. 0.297 1.353 ↓ 1.0 477 1

WindowAgg (cost=123.54..135.44 rows=476 width=45) (actual time=1.032..1.353 rows=477 loops=1)

143. 0.236 1.056 ↓ 1.0 477 1

Sort (cost=123.54..124.73 rows=476 width=29) (actual time=1.025..1.056 rows=477 loops=1)

  • Sort Key: rate_cards_1.rateable_id, rate_cards_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 62kB
144. 0.820 0.820 ↓ 1.0 477 1

Seq Scan on rate_cards rate_cards_1 (cost=0.00..102.38 rows=476 width=29) (actual time=0.098..0.820 rows=477 loops=1)

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4278
145. 0.051 0.104 ↑ 1.0 428 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
146. 0.053 0.053 ↑ 1.0 428 1

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

147. 14.751 81.300 ↑ 2.2 91,407 1

Subquery Scan on *SELECT* 3 (cost=917.88..9,198.31 rows=198,512 width=44) (actual time=11.075..81.300 rows=91,407 loops=1)

148. 38.862 66.549 ↑ 2.2 91,407 1

Hash Join (cost=917.88..7,213.19 rows=198,512 width=612) (actual time=11.074..66.549 rows=91,407 loops=1)

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

Seq Scan on rates rates_2 (cost=0.00..3,458.46 rows=170,346 width=28) (actual time=0.009..16.650 rows=170,280 loops=1)

150. 0.901 11.037 ↑ 1.1 3,909 1

Hash (cost=866.43..866.43 rows=4,116 width=24) (actual time=11.037..11.037 rows=3,909 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 294kB
151. 1.013 10.136 ↑ 1.1 3,909 1

Hash Join (cost=669.91..866.43 rows=4,116 width=24) (actual time=7.142..10.136 rows=3,909 loops=1)

  • Hash Cond: (projects_4.client_id = clients_3.id)
152. 1.009 4.199 ↑ 1.0 3,545 1

Hash Join (cost=318.89..467.23 rows=3,545 width=8) (actual time=2.211..4.199 rows=3,545 loops=1)

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

Seq Scan on pricing_models pricing_models_2 (cost=0.00..139.03 rows=3,546 width=4) (actual time=0.007..1.027 rows=3,546 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2138
154. 0.908 2.163 ↓ 1.0 5,686 1

Hash (cost=247.84..247.84 rows=5,684 width=8) (actual time=2.163..2.163 rows=5,686 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 287kB
155. 1.255 1.255 ↓ 1.0 5,686 1

Seq Scan on projects projects_4 (cost=0.00..247.84 rows=5,684 width=8) (actual time=0.006..1.255 rows=5,686 loops=1)

156. 0.640 4.924 ↑ 1.0 1,792 1

Hash (cost=328.62..328.62 rows=1,792 width=28) (actual time=4.924..4.924 rows=1,792 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 121kB
157. 0.643 4.284 ↑ 1.0 1,792 1

Hash Join (cost=261.18..328.62 rows=1,792 width=28) (actual time=1.631..4.284 rows=1,792 loops=1)

  • Hash Cond: (rate_cards_2.rateable_id = clients_3.id)
158. 1.821 3.141 ↓ 1.0 1,793 1

WindowAgg (cost=199.21..244.01 rows=1,792 width=45) (actual time=1.127..3.141 rows=1,793 loops=1)

159. 0.691 1.320 ↓ 1.0 1,793 1

Sort (cost=199.21..203.69 rows=1,792 width=29) (actual time=1.119..1.320 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
160. 0.629 0.629 ↓ 1.0 1,793 1

Seq Scan on rate_cards rate_cards_2 (cost=0.00..102.38 rows=1,792 width=29) (actual time=0.009..0.629 rows=1,793 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 2962
161. 0.278 0.500 ↓ 1.0 1,822 1

Hash (cost=39.21..39.21 rows=1,821 width=4) (actual time=0.500..0.500 rows=1,822 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
162. 0.222 0.222 ↓ 1.0 1,822 1

Seq Scan on clients clients_3 (cost=0.00..39.21 rows=1,821 width=4) (actual time=0.006..0.222 rows=1,822 loops=1)

163. 11.506 73.107 ↓ 2.4 74,387 1

Subquery Scan on *SELECT* 4 (cost=745.88..5,460.48 rows=30,867 width=44) (actual time=7.040..73.107 rows=74,387 loops=1)

164. 37.123 61.601 ↓ 2.4 74,387 1

Hash Join (cost=745.88..5,151.81 rows=30,867 width=612) (actual time=7.039..61.601 rows=74,387 loops=1)

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

Seq Scan on rates rates_3 (cost=0.00..3,458.46 rows=170,346 width=28) (actual time=0.010..17.459 rows=170,280 loops=1)

166. 0.378 7.019 ↓ 2.4 1,567 1

Hash (cost=737.88..737.88 rows=640 width=24) (actual time=7.019..7.019 rows=1,567 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 108kB
167. 0.296 6.641 ↓ 2.4 1,567 1

Nested Loop (cost=393.65..737.88 rows=640 width=24) (actual time=2.474..6.641 rows=1,567 loops=1)

168. 0.479 4.778 ↓ 2.4 1,567 1

Hash Join (cost=393.37..482.50 rows=640 width=24) (actual time=2.456..4.778 rows=1,567 loops=1)

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
169. 1.702 3.342 ↓ 1.0 2,372 1

WindowAgg (cost=235.16..294.38 rows=2,369 width=45) (actual time=1.494..3.342 rows=2,372 loops=1)

170. 0.915 1.640 ↓ 1.0 2,372 1

Sort (cost=235.16..241.08 rows=2,369 width=29) (actual time=1.485..1.640 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
171. 0.725 0.725 ↓ 1.0 2,372 1

Seq Scan on rate_cards rate_cards_3 (cost=0.00..102.38 rows=2,369 width=29) (actual time=0.007..0.725 rows=2,372 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2383
172. 0.228 0.957 ↓ 1.0 1,537 1

Hash (cost=139.03..139.03 rows=1,535 width=8) (actual time=0.957..0.957 rows=1,537 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
173. 0.729 0.729 ↓ 1.0 1,537 1

Seq Scan on pricing_models pricing_models_3 (cost=0.00..139.03 rows=1,535 width=8) (actual time=0.008..0.729 rows=1,537 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4147
174. 1.567 1.567 ↑ 1.0 1 1,567

Index Only Scan using projects_pkey on projects projects_5 (cost=0.28..0.40 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1,567)

  • Index Cond: (id = pricing_models_3.project_id)
  • Heap Fetches: 124
175. 29.023 29.111 ↓ 1,450.2 414,749 1

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

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

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

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
177. 2,191.731 2,192.193 ↓ 11,794.5 31,892,417 1

Sort (cost=220.18..226.94 rows=2,704 width=24) (actual time=1.334..2,192.193 rows=31,892,417 loops=1)

  • Sort Key: cost_exchange_rates.account_id, cost_exchange_rates.currency
  • Sort Method: quicksort Memory: 308kB
178. 0.462 0.462 ↑ 1.0 2,704 1

Seq Scan on exchange_rates_by_months cost_exchange_rates (cost=0.00..66.04 rows=2,704 width=24) (actual time=0.015..0.462 rows=2,704 loops=1)

179. 2,153.249 2,153.681 ↓ 11,403.2 30,834,286 1

Sort (cost=220.18..226.94 rows=2,704 width=24) (actual time=1.177..2,153.681 rows=30,834,286 loops=1)

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 308kB
180. 0.432 0.432 ↑ 1.0 2,704 1

Seq Scan on exchange_rates_by_months (cost=0.00..66.04 rows=2,704 width=24) (actual time=0.013..0.432 rows=2,704 loops=1)

181. 2,149.776 2,150.301 ↓ 11,403.2 30,834,286 1

Sort (cost=220.18..226.94 rows=2,704 width=24) (actual time=1.454..2,150.301 rows=30,834,286 loops=1)

  • Sort Key: original_exchange_rates.account_id, original_exchange_rates.currency
  • Sort Method: quicksort Memory: 308kB
182. 0.525 0.525 ↑ 1.0 2,704 1

Seq Scan on exchange_rates_by_months original_exchange_rates (cost=0.00..66.04 rows=2,704 width=24) (actual time=0.005..0.525 rows=2,704 loops=1)

Planning time : 10.047 ms
Execution time : 47,525.020 ms