explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Er82

Settings
# exclusive inclusive rows x rows loops node
1. 551.353 45,422.769 ↓ 7.6 595,625 1

GroupAggregate (cost=2,574,694.01..2,581,367.53 rows=78,512 width=196) (actual time=44,808.485..45,422.769 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,564.656 44,871.416 ↓ 7.6 595,625 1

Sort (cost=2,574,694.01..2,574,890.29 rows=78,512 width=192) (actual time=44,808.463..44,871.416 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: 159817kB
3. 5,405.312 43,306.760 ↓ 7.6 595,625 1

Merge Left Join (cost=2,566,894.24..2,568,310.74 rows=78,512 width=192) (actual time=28,066.420..43,306.760 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,495.919 35,749.363 ↓ 7.6 595,625 1

Merge Left Join (cost=2,566,674.06..2,567,382.31 rows=78,512 width=176) (actual time=28,064.848..35,749.363 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. 491.083 28,117.753 ↓ 7.6 595,625 1

Sort (cost=2,566,453.89..2,566,650.17 rows=78,512 width=168) (actual time=28,063.279..28,117.753 rows=595,625 loops=1)

  • Sort Key: projects.account_id, "*SELECT* 1".currency
  • Sort Method: quicksort Memory: 148639kB
6. 5,415.823 27,626.670 ↓ 7.6 595,625 1

Merge Left Join (cost=2,559,362.37..2,560,070.61 rows=78,512 width=168) (actual time=20,058.974..27,626.670 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. 448.347 20,104.327 ↓ 7.6 595,625 1

Sort (cost=2,559,142.19..2,559,338.47 rows=78,512 width=676) (actual time=20,056.736..20,104.327 rows=595,625 loops=1)

  • Sort Key: projects.account_id, staff_memberships_1.currency
  • Sort Method: quicksort Memory: 128819kB
8. 180.458 19,655.980 ↓ 7.6 595,625 1

Merge Left Join (cost=2,536,990.98..2,552,758.92 rows=78,512 width=676) (actual time=18,958.973..19,655.980 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. 366.051 19,450.072 ↓ 7.6 595,625 1

Merge Left Join (cost=2,536,969.52..2,549,173.02 rows=78,512 width=672) (actual time=18,958.721..19,450.072 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. 382.156 18,477.370 ↓ 7.6 595,625 1

Sort (cost=2,491,460.27..2,491,656.55 rows=78,512 width=644) (actual time=18,420.864..18,477.370 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. 172.263 18,095.214 ↓ 7.6 595,625 1

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

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

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

13. 5,766.117 17,681.991 ↓ 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,618.747..17,681.991 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. 1,104.359 9,604.244 ↓ 4.6 1,075,067 1

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

  • Sort Key: "*SELECT* 1_1".project_plan_id
  • Sort Method: external merge Disk: 121160kB
15. 286.344 8,499.885 ↓ 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,486.012..8,499.885 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. 509.123 8,213.376 ↓ 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,485.773..8,213.376 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,131.772 7,610.639 ↓ 1.5 1,080,426 1

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

  • Sort Key: projects.account_id, "*SELECT* 1_1".user_id
  • Sort Method: quicksort Memory: 181193kB
18. 302.225 6,478.867 ↓ 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,641.205..6,478.867 rows=1,080,426 loops=1)

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

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

20. 268.290 5,447.574 ↓ 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,633.979..5,447.574 rows=860,997 loops=1)

21. 434.456 5,179.284 ↓ 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,633.973..5,179.284 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. 29.238 60.917 ↑ 1.0 58,766 1

Sort (cost=6,166.37..6,316.54 rows=60,068 width=16) (actual time=56.417..60.917 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. 31.679 31.679 ↑ 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.027..31.679 rows=58,799 loops=1)

24. 1,042.448 4,683.911 ↓ 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,577.542..4,683.911 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. 199.374 3,641.463 ↑ 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,079.707..3,641.463 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. 266.045 3,396.782 ↑ 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,039.474..3,396.782 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,193.074 2,981.024 ↑ 1.4 870,234 1

Sort (cost=1,088,516.08..1,091,662.33 rows=1,258,500 width=70) (actual time=2,894.613..2,981.024 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. 158.466 787.950 ↑ 1.4 870,234 1

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

29. 12.983 157.713 ↓ 1.0 52,419 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

40. 0.270 0.853 ↓ 1.0 1,822 1

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

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

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

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

Sort (cost=583,885.45..585,295.45 rows=564,000 width=44) (actual time=144.852..149.713 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.471 109.307 ↑ 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=87.004..109.307 rows=29,556 loops=1)

45. 36.232 104.836 ↑ 19.1 29,556 1

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

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

Hash Join (cost=135.79..473,587.13 rows=4,232,127 width=21) (actual time=2.932..68.604 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. 18.353 52.550 ↑ 661.7 31,591 1

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

48. 13.290 13.290 ↓ 1.0 20,907 1

Seq Scan on vacations (cost=0.00..481.04 rows=20,904 width=17) (actual time=0.064..13.290 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. 1.075 2.788 ↓ 1.0 3,958 1

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

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

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

52. 35.292 45.307 ↓ 1.0 60,807 1

Sort (cost=6,166.37..6,316.54 rows=60,068 width=16) (actual time=40.227..45.307 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.015 10.015 ↑ 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.013..10.015 rows=58,799 loops=1)

54. 268.447 546.368 ↓ 1.0 688,749 1

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

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

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

56. 5.392 27.638 ↓ 1.0 26,131 1

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

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

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

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

59. 0.410 1.418 ↓ 1.0 2,819 1

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

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

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

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

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

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

63. 77.863 93.614 ↓ 286.3 1,174,237 1

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

64. 1.286 15.751 ↑ 1.0 4,053 1

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

65. 3.474 14.465 ↑ 1.0 4,075 1

Sort (cost=818.55..828.80 rows=4,102 width=65) (actual time=13.931..14.465 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.244 10.991 ↓ 1.0 4,114 1

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

67. 2.385 7.747 ↓ 1.0 4,114 1

Sort (cost=469.84..480.09 rows=4,102 width=56) (actual time=7.365..7.747 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.483 5.362 ↓ 1.0 4,114 1

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

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
69. 1.987 1.987 ↓ 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.021..1.987 rows=4,121 loops=1)

70. 0.999 1.892 ↓ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 273kB
71. 0.893 0.893 ↓ 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.013..0.893 rows=3,958 loops=1)

72. 0.057 0.165 ↑ 1.0 342 1

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

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

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

74. 2,140.967 2,311.630 ↓ 7,250.0 35,256,840 1

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

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

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

76. 64.927 164.815 ↓ 8.9 43,105 1

HashAggregate (cost=8,824.21..8,897.16 rows=4,863 width=48) (actual time=127.254..164.815 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. 27.884 99.888 ↓ 1.0 43,258 1

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

78. 23.303 72.004 ↓ 1.0 43,258 1

Sort (cost=6,336.99..6,445.13 rows=43,256 width=60) (actual time=65.806..72.004 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. 15.674 48.701 ↓ 1.0 43,258 1

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

  • Hash Cond: (plan_rows_1.uuid = plan_roles.plan_row_uuid)
80. 4.481 4.481 ↓ 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.012..4.481 rows=46,645 loops=1)

81. 10.209 28.546 ↓ 1.0 43,260 1

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

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

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

83. 4.552 121.772 ↓ 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=73.671..121.772 rows=16,702 loops=1)

84. 14.910 117.220 ↓ 16,702.0 16,702 1

Merge Right Join (cost=232,813.24..239,944.39 rows=1 width=110) (actual time=73.666..117.220 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. 27.136 27.136 ↑ 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.031..27.136 rows=58,756 loops=1)

86. 31.045 75.174 ↑ 16.0 17,028 1

Sort (cost=232,812.82..233,491.85 rows=271,612 width=64) (actual time=73.618..75.174 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. 8.910 44.129 ↑ 16.0 17,028 1

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

88. 4.516 23.991 ↓ 1.0 11,228 1

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

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

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

90. 0.445 17.555 ↑ 1.3 1,635 1

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

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

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

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

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

  • Hash Cond: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
93. 0.373 0.373 ↓ 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.005..0.373 rows=4,121 loops=1)

94. 0.296 12.815 ↓ 9.0 1,635 1

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

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

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

  • Group Key: staff_activities_with_dates.link_id
96. 1.222 11.785 ↓ 3.6 1,635 1

Subquery Scan on staff_activities_with_dates (cost=808.29..931.35 rows=456 width=4) (actual time=9.571..11.785 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.741 10.563 ↑ 1.0 4,086 1

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

98. 2.486 9.822 ↓ 1.0 4,114 1

Sort (cost=808.29..818.55 rows=4,102 width=613) (actual time=9.564..9.822 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.655 7.336 ↓ 1.0 4,114 1

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

100. 1.694 4.681 ↓ 1.0 4,114 1

Sort (cost=469.84..480.09 rows=4,102 width=28) (actual time=4.399..4.681 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.123 2.987 ↓ 1.0 4,114 1

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

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_3.id)
102. 0.381 0.381 ↓ 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.381 rows=4,121 loops=1)

103. 0.785 1.483 ↓ 1.0 3,958 1

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

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

105. 0.768 1.537 ↓ 1.0 3,958 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 218kB
106. 0.769 0.769 ↓ 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.011..0.769 rows=3,958 loops=1)

107. 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
108. 0.016 1.236 ↑ 38.0 3 1

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

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

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

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

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

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

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

112. 0.073 26.720 ↓ 8.1 388 1

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

113. 0.109 26.647 ↓ 8.1 388 1

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

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

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

115. 0.007 5.810 ↓ 4.0 4 1

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

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

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

117. 0.077 2.029 ↑ 28.2 4 1

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

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

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

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

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

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

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

121. 0.013 3.671 ↑ 1.0 4 1

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

122. 3.610 3.610 ↑ 1.0 4 1

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

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

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

  • Index Cond: (id = projects_2.client_id)
125. 20.516 20.516 ↓ 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=1.466..5.129 rows=97 loops=4)

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

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

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

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

128. 11.515 100.737 ↓ 2.6 87,611 1

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

129. 32.516 89.222 ↓ 2.6 87,611 1

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

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

131. 0.143 4.641 ↑ 1.1 631 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
132. 0.165 4.498 ↑ 1.1 631 1

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

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

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

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

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

  • Hash Cond: (projects_3.id = pricing_models_1.project_id)
135. 0.493 0.493 ↓ 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.493 rows=5,686 loops=1)

136. 0.088 0.776 ↑ 1.0 597 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
137. 0.688 0.688 ↑ 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.688 rows=597 loops=1)

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

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

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

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

140. 0.104 1.437 ↑ 1.1 438 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
141. 0.132 1.333 ↑ 1.1 438 1

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

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

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

143. 0.243 0.753 ↓ 1.0 477 1

Sort (cost=123.54..124.73 rows=476 width=29) (actual time=0.723..0.753 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.510 0.510 ↓ 1.0 477 1

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

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

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

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

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

147. 12.533 71.639 ↑ 2.2 91,407 1

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

148. 34.193 59.106 ↑ 2.2 91,407 1

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

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

150. 0.781 9.338 ↑ 1.1 3,909 1

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

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

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

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

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

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

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

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

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

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

156. 0.421 3.808 ↑ 1.0 1,792 1

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

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

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

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

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

159. 0.684 1.308 ↓ 1.0 1,793 1

Sort (cost=199.21..203.69 rows=1,792 width=29) (actual time=1.195..1.308 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.624 0.624 ↓ 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.018..0.624 rows=1,793 loops=1)

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

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

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
162. 0.219 0.219 ↓ 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.219 rows=1,822 loops=1)

163. 10.060 66.109 ↓ 2.4 74,387 1

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

164. 32.187 56.049 ↓ 2.4 74,387 1

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

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

166. 0.408 8.319 ↓ 2.4 1,567 1

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

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

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

168. 0.475 5.003 ↓ 2.4 1,567 1

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

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

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

170. 1.000 1.676 ↓ 1.0 2,372 1

Sort (cost=235.16..241.08 rows=2,369 width=29) (actual time=1.480..1.676 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.676 0.676 ↓ 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.016..0.676 rows=2,372 loops=1)

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

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

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

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4147
174. 3.134 3.134 ↑ 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.002..0.002 rows=1 loops=1,567)

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

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

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

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

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

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

  • Sort Key: cost_exchange_rates.account_id, cost_exchange_rates.currency
  • Sort Method: quicksort Memory: 308kB
178. 0.989 0.989 ↑ 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.039..0.989 rows=2,704 loops=1)

179. 2,135.197 2,135.691 ↓ 11,403.2 30,834,286 1

Sort (cost=220.18..226.94 rows=2,704 width=24) (actual time=1.489..2,135.691 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.494 0.494 ↑ 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.014..0.494 rows=2,704 loops=1)

181. 2,151.587 2,152.085 ↓ 11,403.2 30,834,286 1

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

  • Sort Key: original_exchange_rates.account_id, original_exchange_rates.currency
  • Sort Method: quicksort Memory: 308kB
182. 0.498 0.498 ↑ 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.498 rows=2,704 loops=1)

Planning time : 28.116 ms
Execution time : 45,660.379 ms