explain.depesz.com

PostgreSQL's explain analyze made readable

Result: N6Za

Settings
# exclusive inclusive rows x rows loops node
1. 642.494 64,716.018 ↓ 7.6 595,625 1

GroupAggregate (cost=3,662,910.03..3,669,583.55 rows=78,512 width=196) (actual time=63,912.093..64,716.018 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,881.116 64,073.524 ↓ 7.6 595,625 1

Sort (cost=3,662,910.03..3,663,106.31 rows=78,512 width=192) (actual time=63,912.056..64,073.524 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: external merge Disk: 84104kB
3. 287.908 62,192.408 ↓ 7.6 595,625 1

Hash Left Join (cost=3,642,503.33..3,649,278.26 rows=78,512 width=192) (actual time=42,978.802..62,192.408 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. 6,590.050 61,904.337 ↓ 7.6 595,625 1

Merge Left Join (cost=3,642,489.96..3,643,710.18 rows=78,512 width=180) (actual time=42,978.620..61,904.337 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
5. 6,700.038 52,688.402 ↓ 7.6 595,625 1

Merge Left Join (cost=3,642,269.78..3,642,978.03 rows=78,512 width=172) (actual time=42,977.287..52,688.402 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
6. 1,124.058 43,356.183 ↓ 7.6 595,625 1

Sort (cost=3,642,049.60..3,642,245.88 rows=78,512 width=164) (actual time=42,975.795..43,356.183 rows=595,625 loops=1)

  • Sort Key: projects.account_id, "*SELECT* 1".currency
  • Sort Method: external merge Disk: 72360kB
7. 431.249 42,232.125 ↓ 7.6 595,625 1

Merge Left Join (cost=3,616,361.85..3,629,222.83 rows=78,512 width=164) (actual time=41,514.556..42,232.125 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. 637.714 40,913.453 ↓ 7.6 595,625 1

Sort (cost=3,562,760.59..3,562,956.87 rows=78,512 width=136) (actual time=40,784.780..40,913.453 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: 56544kB
9. 6,795.003 40,275.739 ↓ 7.6 595,625 1

Merge Left Join (cost=3,550,300.08..3,551,008.32 rows=78,512 width=136) (actual time=30,439.081..40,275.739 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
10. 918.274 30,749.451 ↓ 7.6 595,625 1

Sort (cost=3,550,079.90..3,550,276.18 rows=78,512 width=644) (actual time=30,434.864..30,749.451 rows=595,625 loops=1)

  • Sort Key: projects.account_id, staff_memberships_1.currency
  • Sort Method: external merge Disk: 54920kB
11. 266.708 29,831.177 ↓ 7.6 595,625 1

Hash Join (cost=3,068,251.50..3,521,153.12 rows=78,512 width=644) (actual time=15,655.797..29,831.177 rows=595,625 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
12. 178.131 29,563.197 ↓ 4.6 1,091,769 1

Append (cost=3,068,224.09..3,518,144.40 rows=235,537 width=644) (actual time=14,204.938..29,563.197 rows=1,091,769 loops=1)

13. 9,007.476 29,182.485 ↓ 4.6 1,075,067 1

Merge Left Join (cost=3,068,224.09..3,263,774.92 rows=235,536 width=103) (actual time=14,204.937..29,182.485 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,290.105 14,145.889 ↓ 4.6 1,075,067 1

Sort (cost=3,058,980.50..3,059,569.34 rows=235,536 width=149) (actual time=13,906.690..14,145.889 rows=1,075,067 loops=1)

  • Sort Key: "*SELECT* 1_1".project_plan_id
  • Sort Method: external merge Disk: 121192kB
15. 400.506 12,855.784 ↓ 4.6 1,075,067 1

Hash Left Join (cost=3,011,660.42..3,020,250.61 rows=235,536 width=149) (actual time=11,222.339..12,855.784 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. 697.612 12,455.081 ↓ 1.5 1,080,426 1

Merge Left Join (cost=3,011,632.72..3,018,353.45 rows=699,611 width=144) (actual time=11,222.129..12,455.081 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,802.124 11,623.519 ↓ 1.5 1,080,426 1

Sort (cost=3,010,814.17..3,012,563.20 rows=699,611 width=120) (actual time=11,199.712..11,623.519 rows=1,080,426 loops=1)

  • Sort Key: projects.account_id, "*SELECT* 1_1".user_id
  • Sort Method: external merge Disk: 86656kB
18. 408.128 9,821.395 ↓ 1.5 1,080,426 1

Hash Join (cost=2,759,579.78..2,856,809.26 rows=699,611 width=120) (actual time=6,776.230..9,821.395 rows=1,080,426 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
19. 238.673 9,405.543 ↓ 2.2 1,549,746 1

Append (cost=2,759,260.89..2,847,656.41 rows=699,611 width=120) (actual time=6,768.442..9,405.543 rows=1,549,746 loops=1)

20. 371.955 8,343.249 ↓ 79.2 860,997 1

Subquery Scan on *SELECT* 1_1 (cost=2,759,260.89..2,806,708.98 rows=10,871 width=120) (actual time=6,768.440..8,343.249 rows=860,997 loops=1)

21. 618.165 7,971.294 ↓ 79.2 860,997 1

Merge Right Join (cost=2,759,260.89..2,806,518.74 rows=10,871 width=138) (actual time=6,768.434..7,971.294 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. 42.566 73.485 ↑ 1.0 58,766 1

Sort (cost=6,166.37..6,316.54 rows=60,068 width=16) (actual time=63.017..73.485 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. 30.919 30.919 ↑ 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.017..30.919 rows=58,799 loops=1)

24. 226.018 7,279.644 ↓ 1.2 1,501,593 1

Materialize (cost=2,753,094.52..2,759,387.02 rows=1,258,500 width=110) (actual time=6,705.403..7,279.644 rows=1,501,593 loops=1)

25. 1,567.096 7,053.626 ↑ 1.4 870,252 1

Sort (cost=2,753,094.52..2,756,240.77 rows=1,258,500 width=110) (actual time=6,705.398..7,053.626 rows=870,252 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, clients.office_id
  • Sort Method: external merge Disk: 69272kB
26. 246.944 5,486.530 ↑ 1.4 870,252 1

Merge Left Join (cost=2,449,138.25..2,479,329.87 rows=1,258,500 width=110) (actual time=4,242.686..5,486.530 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. 318.229 5,161.963 ↑ 1.4 870,234 1

Merge Left Join (cost=2,442,971.88..2,462,688.59 rows=1,258,500 width=106) (actual time=4,174.539..5,161.963 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,289.666 4,669.637 ↑ 1.4 870,234 1

Sort (cost=1,191,759.08..1,194,905.33 rows=1,258,500 width=70) (actual time=4,010.161..4,669.637 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. 205.885 1,379.971 ↑ 1.4 870,234 1

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

30. 18.971 440.220 ↓ 1.0 52,419 1

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

  • Hash Cond: (projects_1.client_id = clients.id)
31. 19.324 415.621 ↓ 1.0 52,419 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3091
39. 1.525 3.338 ↓ 1.0 5,686 1

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

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

41. 0.537 5.628 ↓ 1.0 1,822 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
42. 5.091 5.091 ↓ 1.0 1,822 1

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

43. 733.866 733.866 ↑ 1.5 17 52,419

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.006..0.014 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. 6.484 174.097 ↑ 16.4 34,338 1

Materialize (cost=1,251,212.80..1,254,032.80 rows=564,000 width=44) (actual time=164.367..174.097 rows=34,338 loops=1)

45. 34.964 167.613 ↑ 19.1 29,556 1

Sort (cost=1,251,212.80..1,252,622.80 rows=564,000 width=44) (actual time=164.362..167.613 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. 4.033 132.649 ↑ 19.1 29,556 1

Subquery Scan on summary_vacations_by_dates (cost=1,112,981.17..1,179,982.75 rows=564,000 width=44) (actual time=109.574..132.649 rows=29,556 loops=1)

47. 19.427 128.616 ↑ 19.1 29,556 1

GroupAggregate (cost=1,112,981.17..1,174,342.75 rows=564,000 width=52) (actual time=109.571..128.616 rows=29,556 loops=1)

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

Sort (cost=1,112,981.17..1,123,561.48 rows=4,232,127 width=21) (actual time=106.447..109.189 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. 14.862 87.372 ↑ 142.6 29,683 1

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

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

51. 22.863 22.863 ↓ 1.0 20,907 1

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

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

53. 0.974 6.496 ↓ 1.0 3,958 1

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

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

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

55. 64.295 77.623 ↓ 1.0 60,807 1

Sort (cost=6,166.37..6,316.54 rows=60,068 width=16) (actual time=68.140..77.623 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. 13.328 13.328 ↑ 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.018..13.328 rows=58,799 loops=1)

57. 354.976 823.621 ↓ 1.0 688,749 1

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

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

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

59. 8.229 49.781 ↓ 1.0 26,131 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1368kB
60. 10.986 41.552 ↓ 1.0 26,131 1

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

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

62. 0.736 2.422 ↓ 1.0 2,819 1

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

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

  • Filter: active
  • Rows Removed by Filter: 3091
64. 0.966 7.724 ↓ 1.0 5,686 1

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

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

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

66. 108.916 133.950 ↓ 286.3 1,174,237 1

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

67. 1.933 25.034 ↑ 1.0 4,053 1

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

68. 4.324 23.101 ↑ 1.0 4,075 1

Sort (cost=818.55..828.80 rows=4,102 width=65) (actual time=22.405..23.101 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.636 18.777 ↓ 1.0 4,114 1

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

70. 2.373 14.141 ↓ 1.0 4,114 1

Sort (cost=469.84..480.09 rows=4,102 width=56) (actual time=13.647..14.141 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. 1.789 11.768 ↓ 1.0 4,114 1

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

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

73. 1.253 2.466 ↓ 1.0 3,958 1

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

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

75. 0.069 0.197 ↑ 1.0 342 1

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

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

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

77. 5,767.835 6,029.120 ↓ 7,250.0 35,256,840 1

Sort (cost=9,243.59..9,255.75 rows=4,863 width=48) (actual time=298.225..6,029.120 rows=35,256,840 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: external sort Disk: 2544kB
78. 8.350 261.285 ↓ 8.9 43,105 1

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

79. 92.971 252.935 ↓ 8.9 43,105 1

HashAggregate (cost=8,824.21..8,897.16 rows=4,863 width=48) (actual time=199.208..252.935 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)
80. 39.602 159.964 ↓ 1.0 43,258 1

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

81. 45.679 120.362 ↓ 1.0 43,258 1

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

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: external merge Disk: 2688kB
82. 24.523 74.683 ↓ 1.0 43,258 1

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

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

84. 13.260 43.952 ↓ 1.0 43,260 1

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

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

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

86. 7.107 202.581 ↓ 16,702.0 16,702 1

Subquery Scan on *SELECT* 2_1 (cost=243,026.24..250,836.43 rows=1 width=103) (actual time=115.561..202.581 rows=16,702 loops=1)

87. 24.181 195.474 ↓ 16,702.0 16,702 1

Merge Right Join (cost=243,026.24..250,836.42 rows=1 width=110) (actual time=115.555..195.474 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
88. 49.262 49.262 ↑ 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.018..49.262 rows=58,756 loops=1)

89. 4.019 122.031 ↑ 16.0 17,028 1

Materialize (cost=243,025.82..244,383.88 rows=271,612 width=64) (actual time=115.510..122.031 rows=17,028 loops=1)

90. 50.606 118.012 ↑ 16.0 17,028 1

Sort (cost=243,025.82..243,704.85 rows=271,612 width=64) (actual time=115.506..118.012 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
91. 9.979 67.406 ↑ 16.0 17,028 1

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

92. 7.277 34.971 ↓ 1.0 11,228 1

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

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

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

94. 0.596 24.761 ↑ 1.3 1,635 1

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

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

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

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

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

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

98. 0.425 19.923 ↓ 9.0 1,635 1

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

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

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

  • Group Key: staff_activities_with_dates.link_id
100. 1.872 18.590 ↓ 3.6 1,635 1

Subquery Scan on staff_activities_with_dates (cost=808.29..931.35 rows=456 width=4) (actual time=15.223..18.590 rows=1,635 loops=1)

  • Filter: ((CURRENT_DATE >= staff_activities_with_dates.start_date) AND (CURRENT_DATE <= staff_activities_with_dates.end_date))
  • Rows Removed by Filter: 2451
101. 1.097 16.718 ↑ 1.0 4,086 1

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

102. 3.817 15.621 ↓ 1.0 4,114 1

Sort (cost=808.29..818.55 rows=4,102 width=613) (actual time=15.212..15.621 rows=4,114 loops=1)

  • Sort Key: staff_memberships_3.account_id, staff_memberships_3.user_id, (COALESCE(staff_membership_activity_links_2.start_date, staff_memberships_3.joined_at)), (COALESCE(lag((staff_membership_activity_links_2.start_date - 1), 1) OVER (?), staff_memberships_3.archived_at, '3000-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 514kB
103. 4.434 11.804 ↓ 1.0 4,114 1

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

104. 2.797 7.370 ↓ 1.0 4,114 1

Sort (cost=469.84..480.09 rows=4,102 width=28) (actual time=6.903..7.370 rows=4,114 loops=1)

  • Sort Key: staff_membership_activity_links_2.staff_membership_id, staff_membership_activity_links_2.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 514kB
105. 1.868 4.573 ↓ 1.0 4,114 1

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

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

107. 1.064 2.083 ↓ 1.0 3,958 1

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

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

109. 0.941 2.013 ↓ 1.0 3,958 1

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

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

111. 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.002..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
112. 0.009 1.272 ↑ 38.0 3 1

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

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

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 339
114. 2,728.148 2,731.285 ↓ 11,794.5 31,892,417 1

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

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

116. 103.397 887.423 ↓ 3.7 973,811 1

Materialize (cost=53,601.25..54,916.23 rows=262,995 width=44) (actual time=711.969..887.423 rows=973,811 loops=1)

117. 397.236 784.026 ↑ 1.0 253,593 1

Sort (cost=53,601.25..54,258.74 rows=262,995 width=44) (actual time=711.964..784.026 rows=253,593 loops=1)

  • Sort Key: "*SELECT* 1".project_id, "*SELECT* 1".activity_id
  • Sort Method: external merge Disk: 14384kB
118. 32.996 386.790 ↑ 1.0 253,793 1

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

119. 0.113 34.144 ↓ 8.1 388 1

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

120. 0.150 34.031 ↓ 8.1 388 1

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

  • Merge Cond: (clients_1.brand_id = brands.id)
121. 0.151 33.812 ↓ 8.1 388 1

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

122. 0.011 7.477 ↓ 4.0 4 1

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

  • Merge Cond: (rate_cards.rateable_id = clients_1.brand_id)
123. 0.034 4.852 ↑ 37.7 3 1

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

124. 0.056 4.818 ↑ 28.2 4 1

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

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

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

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4642
126. 0.011 2.614 ↑ 1.0 4 1

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

  • Sort Key: clients_1.brand_id
  • Sort Method: quicksort Memory: 25kB
127. 0.009 2.603 ↑ 1.0 4 1

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

128. 0.013 2.550 ↑ 1.0 4 1

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

129. 2.501 2.501 ↑ 1.0 4 1

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

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5680
130. 0.036 0.036 ↑ 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.009..0.009 rows=1 loops=4)

  • Index Cond: (id = pricing_models.project_id)
131. 0.044 0.044 ↑ 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.011..0.011 rows=1 loops=4)

  • Index Cond: (id = projects_2.client_id)
132. 26.184 26.184 ↓ 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.366..6.546 rows=97 loops=4)

  • Index Cond: (rate_card_id = rate_cards.id)
133. 0.031 0.069 ↑ 56.5 2 1

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

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

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

135. 14.729 141.760 ↓ 2.6 87,611 1

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

136. 46.524 127.031 ↓ 2.6 87,611 1

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

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

138. 0.202 6.880 ↑ 1.1 631 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
139. 0.219 6.678 ↑ 1.1 631 1

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

  • Hash Cond: (clients_2.office_id = offices.id)
140. 0.219 4.025 ↑ 1.0 597 1

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

  • Hash Cond: (projects_3.client_id = clients_2.id)
141. 1.177 2.924 ↑ 1.0 597 1

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

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

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

143. 0.119 0.989 ↑ 1.0 597 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
144. 0.870 0.870 ↑ 1.0 597 1

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

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5087
145. 0.399 0.882 ↓ 1.0 1,822 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 88kB
146. 0.483 0.483 ↓ 1.0 1,822 1

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

147. 0.153 2.434 ↑ 1.1 438 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
148. 0.169 2.281 ↑ 1.1 438 1

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

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
149. 0.476 1.750 ↓ 1.0 477 1

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

150. 0.252 1.274 ↓ 1.0 477 1

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

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

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

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4278
152. 0.101 0.362 ↑ 1.0 428 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
153. 0.261 0.261 ↑ 1.0 428 1

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

154. 14.483 90.959 ↑ 2.2 91,407 1

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

155. 42.768 76.476 ↑ 2.2 91,407 1

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

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

157. 1.349 14.159 ↑ 1.1 3,909 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 294kB
158. 1.553 12.810 ↑ 1.1 3,909 1

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

  • Hash Cond: (projects_4.client_id = clients_3.id)
159. 1.601 5.611 ↑ 1.0 3,545 1

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

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

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2138
161. 1.056 2.480 ↓ 1.0 5,686 1

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

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

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

163. 0.697 5.646 ↑ 1.0 1,792 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 121kB
164. 0.712 4.949 ↑ 1.0 1,792 1

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

  • Hash Cond: (rate_cards_2.rateable_id = clients_3.id)
165. 1.938 3.660 ↓ 1.0 1,793 1

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

166. 0.908 1.722 ↓ 1.0 1,793 1

Sort (cost=199.21..203.69 rows=1,792 width=29) (actual time=1.516..1.722 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
167. 0.814 0.814 ↓ 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.010..0.814 rows=1,793 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 2962
168. 0.310 0.577 ↓ 1.0 1,822 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
169. 0.267 0.267 ↓ 1.0 1,822 1

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

170. 13.432 86.931 ↓ 2.4 74,387 1

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

171. 43.095 73.499 ↓ 2.4 74,387 1

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

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

173. 0.436 10.412 ↓ 2.4 1,567 1

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

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

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

175. 0.626 6.325 ↓ 2.4 1,567 1

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

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
176. 1.836 4.299 ↓ 1.0 2,372 1

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

177. 1.417 2.463 ↓ 1.0 2,372 1

Sort (cost=235.16..241.08 rows=2,369 width=29) (actual time=2.261..2.463 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
178. 1.046 1.046 ↓ 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.011..1.046 rows=2,372 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2383
179. 0.362 1.400 ↓ 1.0 1,537 1

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

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

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4147
181. 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
182. 2,631.602 2,632.181 ↓ 11,403.2 30,834,286 1

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

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 308kB
183. 0.579 0.579 ↑ 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.579 rows=2,704 loops=1)

184. 2,625.445 2,625.885 ↓ 11,403.2 30,834,286 1

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

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

186. 0.062 0.163 ↓ 1.2 336 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
187. 0.101 0.101 ↓ 1.2 336 1

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

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
Planning time : 130.506 ms
Execution time : 64,908.247 ms