explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IF3E

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

GroupAggregate (cost=3,563,069.78..3,570,330.85 rows=78,498 width=208) (actual time=70,742.649..71,809.697 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. 3,043.556 70,954.486 ↓ 7.6 595,625 1

Sort (cost=3,563,069.78..3,563,266.03 rows=78,498 width=204) (actual time=70,742.627..70,954.486 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: external merge Disk: 89832kB
3. 309.295 67,910.930 ↓ 7.6 595,625 1

Hash Left Join (cost=3,542,257.87..3,548,903.75 rows=78,498 width=204) (actual time=46,240.619..67,910.930 rows=595,625 loops=1)

  • Hash 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
4. 7,465.768 67,601.377 ↓ 7.6 595,625 1

Merge Left Join (cost=3,542,244.51..3,543,336.66 rows=78,498 width=192) (actual time=46,240.350..67,601.377 rows=595,625 loops=1)

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

Merge Left Join (cost=3,542,151.30..3,542,795.50 rows=78,498 width=184) (actual time=46,239.300..57,175.548 rows=595,625 loops=1)

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

Sort (cost=3,542,058.09..3,542,254.33 rows=78,498 width=176) (actual time=46,238.220..46,673.620 rows=595,625 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".currency
  • Sort Method: external merge Disk: 76952kB
7. 598.129 45,210.611 ↓ 7.6 595,625 1

Merge Left Join (cost=3,516,105.80..3,528,966.56 rows=78,498 width=176) (actual time=44,205.152..45,210.611 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
8. 601.000 43,852.400 ↓ 7.6 595,625 1

Sort (cost=3,462,754.77..3,462,951.02 rows=78,498 width=144) (actual time=43,660.348..43,852.400 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: external merge Disk: 61128kB
9. 5,248.547 43,251.400 ↓ 7.6 595,625 1

Merge Left Join (cost=3,450,093.54..3,450,737.74 rows=78,498 width=144) (actual time=35,654.348..43,251.400 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
10. 861.021 35,881.419 ↓ 7.6 595,625 1

Sort (cost=3,450,000.33..3,450,196.58 rows=78,498 width=652) (actual time=35,653.713..35,881.419 rows=595,625 loops=1)

  • Sort Key: projects.account_id, staff_memberships_1.currency
  • Sort Method: external merge Disk: 59512kB
11. 322.180 35,020.398 ↓ 7.6 595,625 1

Hash Join (cost=3,013,635.39..3,420,812.30 rows=78,498 width=652) (actual time=12,028.778..35,020.398 rows=595,625 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
12. 197.077 34,698.097 ↓ 4.6 1,091,769 1

Append (cost=3,013,616.98..3,417,813.13 rows=235,493 width=652) (actual time=12,028.601..34,698.097 rows=1,091,769 loops=1)

13. 496.389 34,384.035 ↓ 4.6 1,075,067 1

Nested Loop Left Join (cost=3,013,616.98..3,163,940.67 rows=235,492 width=111) (actual time=12,028.600..34,384.035 rows=1,075,067 loops=1)

14. 490.442 14,536.440 ↓ 4.6 1,075,067 1

Hash Left Join (cost=3,013,616.57..3,022,829.09 rows=235,492 width=157) (actual time=12,028.543..14,536.440 rows=1,075,067 loops=1)

  • Hash Cond: (projects.client_id = clients.id)
15. 489.486 14,045.176 ↓ 4.6 1,075,067 1

Hash Left Join (cost=3,013,554.57..3,022,147.49 rows=235,492 width=153) (actual time=12,027.715..14,045.176 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. 827.853 13,555.497 ↓ 1.5 1,080,426 1

Merge Left Join (cost=3,013,535.88..3,020,259.68 rows=699,480 width=148) (actual time=12,027.509..13,555.497 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. 2,028.305 12,549.543 ↓ 1.5 1,080,426 1

Sort (cost=3,012,714.99..3,014,463.69 rows=699,480 width=124) (actual time=12,011.751..12,549.543 rows=1,080,426 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1_1"".user_id
  • Sort Method: external merge Disk: 90888kB
18. 453.219 10,521.238 ↓ 1.5 1,080,426 1

Hash Join (cost=2,757,308.75..2,853,956.74 rows=699,480 width=124) (actual time=7,321.332..10,521.238 rows=1,080,426 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
19. 270.664 10,065.880 ↓ 2.2 1,549,746 1

Append (cost=2,757,083.82..2,844,899.50 rows=699,480 width=120) (actual time=7,319.143..10,065.880 rows=1,549,746 loops=1)

20. 434.741 9,145.895 ↓ 80.2 860,997 1

Subquery Scan on *SELECT* 1_1 (cost=2,757,083.82..2,803,952.12 rows=10,731 width=120) (actual time=7,319.142..9,145.895 rows=860,997 loops=1)

21. 720.972 8,711.154 ↓ 80.2 860,997 1

Merge Right Join (cost=2,757,083.82..2,803,764.32 rows=10,731 width=138) (actual time=7,319.137..8,711.154 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
22. 33.423 42.558 ↑ 1.0 58,766 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=30.813..42.558 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: external merge Disk: 1504kB
23. 9.135 9.135 ↑ 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.005..9.135 rows=58,799 loops=1)

24. 258.869 7,947.624 ↓ 1.2 1,501,593 1

Materialize (cost=2,751,164.92..2,757,448.79 rows=1,256,775 width=110) (actual time=7,288.309..7,947.624 rows=1,501,593 loops=1)

25. 1,838.541 7,688.755 ↑ 1.4 870,252 1

Sort (cost=2,751,164.92..2,754,306.86 rows=1,256,775 width=110) (actual time=7,288.305..7,688.755 rows=870,252 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, clients_1.office_id
  • Sort Method: external merge Disk: 69272kB
26. 295.638 5,850.214 ↑ 1.4 870,252 1

Merge Left Join (cost=2,447,606.68..2,477,790.47 rows=1,256,775 width=110) (actual time=4,358.646..5,850.214 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. 379.589 5,459.317 ↑ 1.4 870,234 1

Merge Left Join (cost=2,441,687.78..2,461,409.89 rows=1,256,775 width=106) (actual time=4,274.437..5,459.317 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. 3,733.285 4,885.098 ↑ 1.4 870,234 1

Sort (cost=1,190,017.64..1,193,159.58 rows=1,256,775 width=70) (actual time=4,091.328..4,885.098 rows=870,234 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, plan_rows.user_id
  • Sort Method: external merge Disk: 68496kB
29. 213.628 1,151.813 ↑ 1.4 870,234 1

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

30. 20.700 151.900 ↓ 1.0 52,419 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

41. 0.517 1.058 ↑ 1.0 1,822 1

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

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

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

43. 786.285 786.285 ↑ 1.5 17 52,419

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.006..0.015 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. 7.653 194.630 ↑ 16.5 34,338 1

Materialize (cost=1,251,670.14..1,254,499.14 rows=565,800 width=44) (actual time=183.098..194.630 rows=34,338 loops=1)

45. 54.664 186.977 ↑ 19.1 29,556 1

Sort (cost=1,251,670.14..1,253,084.64 rows=565,800 width=44) (actual time=183.093..186.977 rows=29,556 loops=1)

  • Sort Key: summary_vacations_by_dates.account_id, summary_vacations_by_dates.date, summary_vacations_by_dates.user_id
  • Sort Method: quicksort Memory: 2705kB
46. 6.522 132.313 ↑ 19.1 29,556 1

Subquery Scan on summary_vacations_by_dates (cost=1,113,148.47..1,180,202.64 rows=565,800 width=44) (actual time=95.706..132.313 rows=29,556 loops=1)

47. 26.426 125.791 ↑ 19.1 29,556 1

GroupAggregate (cost=1,113,148.47..1,174,544.64 rows=565,800 width=52) (actual time=95.705..125.791 rows=29,556 loops=1)

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

Sort (cost=1,113,148.47..1,123,730.30 rows=4,232,734 width=21) (actual time=95.691..99.365 rows=29,683 loops=1)

  • Sort Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Sort Method: quicksort Memory: 2714kB
49. 17.357 75.629 ↑ 142.6 29,683 1

Hash Join (cost=136.06..473,655.25 rows=4,232,734 width=21) (actual time=2.731..75.629 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
50. 10.088 55.587 ↑ 661.8 31,591 1

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

51. 3.685 3.685 ↑ 1.0 20,907 1

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

52. 41.814 41.814 ↑ 500.0 2 20,907

Function Scan on generate_series generate_series_1 (cost=0.01..10.01 rows=1,000 width=8) (actual time=0.001..0.002 rows=2 loops=20,907)

53. 1.434 2.685 ↑ 1.0 3,958 1

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

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

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

55. 79.559 95.259 ↓ 1.0 60,807 1

Sort (cost=5,918.90..6,065.90 rows=58,799 width=16) (actual time=84.201..95.259 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: external sort Disk: 1728kB
56. 15.700 15.700 ↑ 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.019..15.700 rows=58,799 loops=1)

57. 363.738 649.321 ↑ 1.0 688,749 1

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

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

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

59. 8.381 26.803 ↑ 1.0 26,131 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1368kB
60. 11.655 18.422 ↑ 1.0 26,131 1

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

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

62. 0.762 2.570 ↓ 1.0 2,819 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3091
64. 0.952 2.139 ↑ 1.0 5,686 1

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

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

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

66. 159.113 178.101 ↓ 285.4 1,174,237 1

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

67. 2.382 18.988 ↑ 1.0 4,053 1

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

68. 4.484 16.606 ↑ 1.0 4,075 1

Sort (cost=820.88..831.17 rows=4,114 width=65) (actual time=15.746..16.606 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
69. 4.291 12.122 ↑ 1.0 4,114 1

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

70. 2.878 7.831 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=56) (actual time=7.358..7.831 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
71. 2.000 4.953 ↑ 1.0 4,114 1

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

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

73. 1.308 2.396 ↑ 1.0 3,958 1

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

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

75. 0.072 0.193 ↑ 1.0 342 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
76. 0.121 0.121 ↑ 1.0 342 1

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

77. 0.389 0.822 ↑ 1.0 1,822 1

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

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

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

79. 19,351.206 19,351.206 ↑ 1.0 1 1,075,067

Index Scan using pawd on planned_activities_with_dates pa (cost=0.41..0.55 rows=1 width=48) (actual time=0.011..0.018 rows=1 loops=1,075,067)

  • Index Cond: ((project_plan_id = "*SELECT* 1_1".project_plan_id) AND ("*SELECT* 1_1".date >= start_date) AND ("*SELECT* 1_1".date <= end_date))
  • Filter: (((plan_row_uuid = "*SELECT* 1_1".plan_row_uuid) OR ("*SELECT* 1_1".plan_row_uuid IS NULL)) AND ((user_id = "*SELECT* 1_1".user_id) OR ("*SELECT* 1_1".user_id IS NULL)) AND ((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 (epic_id IS NULL))))
  • Rows Removed by Filter: 31
80. 4.718 116.985 ↓ 16,702.0 16,702 1

Subquery Scan on *SELECT* 2_1 (cost=243,049.32..250,340.07 rows=1 width=111) (actual time=73.698..116.985 rows=16,702 loops=1)

81. 16.009 112.267 ↓ 16,702.0 16,702 1

Merge Right Join (cost=243,049.32..250,340.06 rows=1 width=118) (actual time=73.694..112.267 rows=16,702 loops=1)

  • Merge Cond: ((non_working_intervals_by_dates_1.account_id = staff_memberships_2.account_id) AND (non_working_intervals_by_dates_1.user_id = staff_memberships_2.user_id) AND (non_working_intervals_by_dates_1.date = generate_series_2.generate_series))
  • Filter: (non_working_intervals_by_dates_1.id IS NULL)
  • Rows Removed by Filter: 326
82. 18.372 18.372 ↑ 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.008..18.372 rows=58,756 loops=1)

83. 2.622 77.886 ↑ 16.0 17,028 1

Materialize (cost=243,048.90..244,407.00 rows=271,620 width=64) (actual time=73.669..77.886 rows=17,028 loops=1)

84. 31.316 75.264 ↑ 16.0 17,028 1

Sort (cost=243,048.90..243,727.95 rows=271,620 width=64) (actual time=73.667..75.264 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: 2980kB
85. 10.397 43.948 ↑ 16.0 17,028 1

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

86. 4.837 22.323 ↓ 1.0 11,228 1

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

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

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

88. 0.445 15.474 ↑ 1.3 1,635 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 137kB
89. 0.458 15.029 ↑ 1.3 1,635 1

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

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_2.id)
90. 0.750 13.142 ↑ 1.3 1,635 1

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

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

92. 0.249 11.996 ↓ 9.0 1,635 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 74kB
93. 0.539 11.747 ↓ 9.0 1,635 1

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

  • Group Key: staff_activities_with_dates.link_id
94. 1.217 11.208 ↓ 3.6 1,635 1

Subquery Scan on staff_activities_with_dates (cost=810.60..934.02 rows=457 width=4) (actual time=8.982..11.208 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
95. 0.755 9.991 ↑ 1.0 4,086 1

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

96. 2.276 9.236 ↑ 1.0 4,114 1

Sort (cost=810.60..820.88 rows=4,114 width=613) (actual time=8.977..9.236 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
97. 2.517 6.960 ↑ 1.0 4,114 1

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

98. 1.604 4.443 ↑ 1.0 4,114 1

Sort (cost=471.06..481.35 rows=4,114 width=28) (actual time=4.181..4.443 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
99. 1.053 2.839 ↑ 1.0 4,114 1

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

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

101. 0.743 1.437 ↑ 1.0 3,958 1

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

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

103. 0.715 1.429 ↑ 1.0 3,958 1

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

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

105. 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
106. 0.003 0.121 ↑ 38.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
107. 0.118 0.118 ↑ 38.0 3 1

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 339
108. 2,121.221 2,121.434 ↓ 26,074.1 32,749,038 1

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

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

110. 137.336 760.082 ↓ 3.7 973,811 1

Materialize (cost=53,351.02..54,666.21 rows=263,037 width=48) (actual time=526.748..760.082 rows=973,811 loops=1)

111. 376.418 622.746 ↑ 1.0 253,593 1

Sort (cost=53,351.02..54,008.62 rows=263,037 width=48) (actual time=526.745..622.746 rows=253,593 loops=1)

  • Sort Key: ""*SELECT* 1"".project_id, ""*SELECT* 1"".activity_id
  • Sort Method: external merge Disk: 14432kB
112. 26.891 246.328 ↑ 1.0 253,793 1

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

113. 0.064 10.773 ↓ 8.1 388 1

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

114. 0.099 10.709 ↓ 8.1 388 1

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

  • Merge Cond: (clients_2.brand_id = brands.id)
115. 0.116 10.566 ↓ 8.1 388 1

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

116. 0.006 1.102 ↓ 4.0 4 1

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

  • Merge Cond: (rate_cards.rateable_id = clients_2.brand_id)
117. 0.018 0.497 ↑ 37.7 3 1

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

118. 0.029 0.479 ↑ 28.2 4 1

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

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

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

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4642
120. 0.005 0.599 ↑ 1.0 4 1

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

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

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

122. 0.007 0.575 ↑ 1.0 4 1

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

123. 0.548 0.548 ↑ 1.0 4 1

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

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

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

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

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

  • Index Cond: (id = projects_2.client_id)
126. 9.348 9.348 ↓ 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.558..2.337 rows=97 loops=4)

  • Index Cond: (rate_card_id = rate_cards.id)
127. 0.028 0.044 ↑ 58.0 2 1

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

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

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

129. 11.464 63.014 ↓ 2.6 87,611 1

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

130. 32.355 51.550 ↓ 2.6 87,611 1

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

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

132. 0.155 4.594 ↑ 1.1 631 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
133. 0.157 4.439 ↑ 1.1 631 1

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

  • Hash Cond: (clients_3.office_id = offices.id)
134. 0.151 3.062 ↑ 1.0 597 1

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

  • Hash Cond: (projects_3.client_id = clients_3.id)
135. 0.836 2.218 ↑ 1.0 597 1

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

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

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

137. 0.092 0.764 ↑ 1.0 597 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
138. 0.672 0.672 ↑ 1.0 597 1

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

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5087
139. 0.318 0.693 ↑ 1.0 1,822 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
140. 0.375 0.375 ↑ 1.0 1,822 1

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

141. 0.114 1.220 ↑ 1.1 438 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
142. 0.106 1.106 ↑ 1.1 438 1

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

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
143. 0.286 0.902 ↑ 1.0 477 1

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

144. 0.138 0.616 ↑ 1.0 477 1

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

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

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

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4278
146. 0.050 0.098 ↑ 1.0 428 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
147. 0.048 0.048 ↑ 1.0 428 1

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

148. 12.162 70.947 ↑ 2.2 91,407 1

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

149. 34.683 58.785 ↑ 2.2 91,407 1

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

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

151. 0.830 9.329 ↑ 1.1 3,909 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 294kB
152. 0.935 8.499 ↑ 1.1 3,909 1

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

  • Hash Cond: (projects_4.client_id = clients_4.id)
153. 0.965 3.869 ↑ 1.0 3,545 1

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

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

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2138
155. 0.882 2.026 ↑ 1.0 5,686 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 287kB
156. 1.144 1.144 ↑ 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.144 rows=5,686 loops=1)

157. 0.420 3.695 ↑ 1.0 1,792 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 121kB
158. 0.430 3.275 ↑ 1.0 1,792 1

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

  • Hash Cond: (rate_cards_2.rateable_id = clients_4.id)
159. 1.121 2.374 ↑ 1.0 1,793 1

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

160. 0.643 1.253 ↑ 1.0 1,793 1

Sort (cost=199.39..203.88 rows=1,794 width=29) (actual time=1.116..1.253 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
161. 0.610 0.610 ↑ 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.007..0.610 rows=1,793 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 2962
162. 0.255 0.471 ↑ 1.0 1,822 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
163. 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)

164. 12.319 74.703 ↓ 2.4 74,387 1

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

165. 38.346 62.384 ↓ 2.4 74,387 1

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

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

167. 0.359 6.565 ↓ 2.4 1,567 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 108kB
168. 0.902 6.206 ↓ 2.4 1,567 1

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

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

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

170. 0.326 4.726 ↓ 2.4 1,567 1

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

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

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

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
172. 1.457 2.968 ↓ 1.0 2,372 1

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

173. 0.852 1.511 ↓ 1.0 2,372 1

Sort (cost=235.35..241.27 rows=2,371 width=29) (actual time=1.364..1.511 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
174. 0.659 0.659 ↓ 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.659 rows=2,372 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2383
175. 0.238 0.988 ↓ 1.0 1,537 1

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

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

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4147
177. 2,943.117 2,943.483 ↓ 25,180.3 31,626,403 1

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

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

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

179. 2,959.717 2,960.061 ↓ 25,180.3 31,626,403 1

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

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

181. 0.128 0.258 ↓ 1.2 336 1

Hash (cost=9.79..9.79 rows=286 width=26) (actual time=0.258..0.258 rows=336 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
182. 0.130 0.130 ↓ 1.2 336 1

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

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
Planning time : 11.282 ms
Execution time : 71,966.702 ms