explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Tda5 : Optimization for: plan #tRfz

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 7,541.632 47,706.439 ↓ 2.4 850,040 1

Merge Left Join (cost=3,112,263.76..3,120,879.31 rows=355,801 width=140) (actual time=37,140.862..47,706.439 rows=850,040 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: 43,703,359
2. 697.813 37,203.926 ↓ 2.4 850,040 1

Sort (cost=3,112,018.90..3,112,908.40 rows=355,801 width=176) (actual time=37,139.184..37,203.926 rows=850,040 loops=1)

  • Sort Key: projects.account_id, staff_memberships_1.currency
  • Sort Method: quicksort Memory: 214,596kB
3. 6,662.025 36,506.113 ↓ 2.4 850,040 1

Merge Left Join (cost=3,075,934.26..3,079,212.78 rows=355,801 width=176) (actual time=27,207.322..36,506.113 rows=850,040 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: 38,541,529
4. 641.530 27,270.631 ↓ 2.4 850,040 1

Sort (cost=3,075,689.39..3,076,578.90 rows=355,801 width=168) (actual time=27,205.546..27,270.631 rows=850,040 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".currency
  • Sort Method: quicksort Memory: 189,324kB
5. 286.420 26,629.101 ↓ 2.4 850,040 1

Merge Left Join (cost=2,968,422.53..3,042,883.28 rows=355,801 width=168) (actual time=25,594.106..26,629.101 rows=850,040 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: 647,094
6. 507.231 26,294.853 ↓ 2.4 849,952 1

Merge Left Join (cost=2,968,399.76..3,025,639.72 rows=355,801 width=164) (actual time=25,593.876..26,294.853 rows=849,952 loops=1)

  • Merge Cond: ((projects.id = ""*SELECT* 1"".project_id) AND ((COALESCE(""*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: 229,908
7. 716.603 25,125.477 ↓ 2.4 849,952 1

Sort (cost=2,921,568.85..2,922,458.35 rows=355,801 width=136) (actual time=25,023.743..25,125.477 rows=849,952 loops=1)

  • Sort Key: projects.id, (COALESCE(""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id))
  • Sort Method: quicksort Memory: 144,101kB
8. 267.542 24,408.874 ↓ 2.4 849,952 1

Hash Join (cost=2,298,887.27..2,888,762.73 rows=355,801 width=136) (actual time=21,986.779..24,408.874 rows=849,952 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
9. 197.570 24,141.197 ↓ 1.5 1,614,425 1

Append (cost=2,298,868.60..2,875,234.54 rows=1,067,404 width=136) (actual time=21,986.611..24,141.197 rows=1,614,425 loops=1)

10. 696.300 23,646.497 ↓ 2.7 1,583,781 1

Hash Left Join (cost=2,298,868.60..2,342,459.08 rows=590,704 width=108) (actual time=21,986.610..23,646.497 rows=1,583,781 loops=1)

  • Hash Cond: (projects.account_id = accounts_1.id)
  • Filter: ((((""*SELECT* 1_1"".vacation_user_id)::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: 13,596
11. 718.272 22,949.961 ↑ 1.1 1,597,377 1

Merge Left Join (cost=2,298,849.64..2,315,603.07 rows=1,754,565 width=144) (actual time=21,986.345..22,949.961 rows=1,597,377 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: 258,899
12. 1,504.447 22,126.451 ↑ 1.1 1,597,377 1

Sort (cost=2,298,002.50..2,302,388.91 rows=1,754,565 width=120) (actual time=21,973.037..22,126.451 rows=1,597,377 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1_1"".user_id
  • Sort Method: quicksort Memory: 268,517kB
13. 429.657 20,622.004 ↑ 1.1 1,597,377 1

Hash Left Join (cost=1,702,859.05..2,116,030.58 rows=1,754,565 width=120) (actual time=4,504.496..20,622.004 rows=1,597,377 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
14. 214.367 20,190.470 ↑ 1.1 1,597,377 1

Append (cost=1,702,630.28..2,093,647.07 rows=1,754,565 width=116) (actual time=4,502.599..20,190.470 rows=1,597,377 loops=1)

15. 300.508 6,151.082 ↑ 1.2 893,416 1

Result (cost=1,702,630.28..1,894,670.02 rows=1,050,604 width=116) (actual time=4,502.598..6,151.082 rows=893,416 loops=1)

16. 107.492 5,850.574 ↑ 1.2 893,416 1

Append (cost=1,702,630.28..1,881,537.47 rows=1,050,604 width=140) (actual time=4,502.594..5,850.574 rows=893,416 loops=1)

17. 120.408 5,082.811 ↑ 1.5 661,732 1

Subquery Scan on *SELECT* 1_1 (cost=1,702,630.28..1,745,963.69 rows=968,516 width=114) (actual time=4,502.593..5,082.811 rows=661,732 loops=1)

18. 173.492 4,962.403 ↑ 1.5 661,732 1

Merge Left Join (cost=1,702,630.28..1,731,435.95 rows=968,516 width=134) (actual time=4,502.591..4,962.403 rows=661,732 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))
19. 204.145 4,651.818 ↑ 1.5 661,732 1

Merge Anti Join (cost=1,096,033.00..1,109,342.38 rows=968,516 width=66) (actual time=4,370.864..4,651.818 rows=661,732 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))
20. 2,165.501 4,401.625 ↑ 1.8 669,378 1

Sort (cost=1,090,015.01..1,093,082.01 rows=1,226,800 width=66) (actual time=4,329.456..4,401.625 rows=669,378 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, plan_rows.user_id
  • Sort Method: quicksort Memory: 118,708kB
21. 544.994 2,236.124 ↑ 1.8 669,378 1

Merge Left Join (cost=15,464.77..965,945.85 rows=1,226,800 width=66) (actual time=295.137..2,236.124 rows=669,378 loops=1)

  • Merge Cond: (plan_items.plan_row_uuid = pa.plan_row_uuid)
  • Join Filter: (((pa.epic_id = plan_rows.epic_id) OR (plan_rows.epic_id IS NULL)) AND (pa.project_plan_id = project_plans.id) AND (pa.user_id = plan_rows.user_id) AND ((generate_series.generate_series)::date >= pa.start_date) AND ((generate_series.generate_series)::date <= pa.end_date))
  • Rows Removed by Join Filter: 328,283
22. 262.829 1,352.872 ↑ 1.8 669,378 1

Nested Loop (cost=6,506.23..943,392.38 rows=1,226,800 width=70) (actual time=73.277..1,352.872 rows=669,378 loops=1)

23. 56.207 229.859 ↑ 1.0 47,788 1

Merge Join (cost=6,506.22..11,024.38 rows=49,072 width=70) (actual time=73.236..229.859 rows=47,788 loops=1)

  • Merge Cond: (plan_rows.uuid = plan_items.plan_row_uuid)
24. 86.032 86.032 ↑ 1.0 42,344 1

Index Scan using plan_rows_uuid_key on plan_rows (cost=0.29..3,663.60 rows=42,428 width=28) (actual time=0.009..86.032 rows=42,344 loops=1)

  • Filter: (user_id IS NOT NULL)
  • Rows Removed by Filter: 5,762
25. 44.006 87.620 ↓ 1.0 55,664 1

Sort (cost=6,504.63..6,643.72 rows=55,639 width=58) (actual time=73.219..87.620 rows=55,664 loops=1)

  • Sort Key: plan_items.plan_row_uuid
  • Sort Method: quicksort Memory: 9,364kB
26. 12.939 43.614 ↓ 1.0 55,664 1

Hash Left Join (cost=430.80..2,119.21 rows=55,639 width=58) (actual time=3.498..43.614 rows=55,664 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
27. 16.213 28.967 ↓ 1.0 55,664 1

Hash Left Join (cost=202.03..1,744.28 rows=55,639 width=54) (actual time=1.756..28.967 rows=55,664 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
28. 11.018 11.018 ↓ 1.0 55,664 1

Seq Scan on plan_items (cost=0.00..1,396.10 rows=55,639 width=46) (actual time=0.007..11.018 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
29. 0.479 1.736 ↑ 1.0 2,851 1

Hash (cost=166.39..166.39 rows=2,851 width=8) (actual time=1.736..1.736 rows=2,851 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 144kB
30. 1.257 1.257 ↑ 1.0 2,851 1

Seq Scan on project_plans (cost=0.00..166.39 rows=2,851 width=8) (actual time=0.005..1.257 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3,188
31. 0.917 1.708 ↑ 1.0 5,812 1

Hash (cost=156.12..156.12 rows=5,812 width=8) (actual time=1.708..1.708 rows=5,812 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 292kB
32. 0.791 0.791 ↑ 1.0 5,812 1

Seq Scan on projects projects_1 (cost=0.00..156.12 rows=5,812 width=8) (actual time=0.008..0.791 rows=5,812 loops=1)

33. 860.184 860.184 ↑ 1.8 14 47,788

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.008..0.018 rows=14 loops=47,788)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 5
34. 136.319 338.258 ↓ 141.0 700,994 1

Sort (cost=8,958.54..8,970.97 rows=4,971 width=36) (actual time=221.851..338.258 rows=700,994 loops=1)

  • Sort Key: pa.plan_row_uuid
  • Sort Method: quicksort Memory: 5,007kB
35. 6.704 201.939 ↓ 8.9 44,428 1

Subquery Scan on pa (cost=8,529.07..8,653.34 rows=4,971 width=36) (actual time=151.414..201.939 rows=44,428 loops=1)

36. 83.796 195.235 ↓ 8.9 44,428 1

HashAggregate (cost=8,529.07..8,603.63 rows=4,971 width=48) (actual time=151.413..195.235 rows=44,428 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)
37. 38.922 111.439 ↑ 1.0 44,581 1

WindowAgg (cost=6,077.00..7,080.12 rows=44,583 width=68) (actual time=65.111..111.439 rows=44,581 loops=1)

38. 30.161 72.517 ↑ 1.0 44,581 1

Sort (cost=6,077.00..6,188.46 rows=44,583 width=52) (actual time=65.099..72.517 rows=44,581 loops=1)

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 5,023kB
39. 18.034 42.356 ↑ 1.0 44,581 1

Hash Join (cost=1,631.38..2,634.26 rows=44,583 width=52) (actual time=19.586..42.356 rows=44,581 loops=1)

  • Hash Cond: (plan_roles.plan_row_uuid = plan_rows_1.uuid)
40. 5.045 5.045 ↑ 1.0 44,581 1

Seq Scan on plan_roles (cost=0.00..885.83 rows=44,583 width=24) (actual time=0.015..5.045 rows=44,581 loops=1)

41. 10.698 19.277 ↑ 1.0 48,106 1

Hash (cost=1,030.06..1,030.06 rows=48,106 width=28) (actual time=19.277..19.277 rows=48,106 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,180kB
42. 8.579 8.579 ↑ 1.0 48,106 1

Seq Scan on plan_rows plan_rows_1 (cost=0.00..1,030.06 rows=48,106 width=28) (actual time=0.012..8.579 rows=48,106 loops=1)

43. 36.021 46.048 ↑ 1.0 59,679 1

Sort (cost=6,017.99..6,167.27 rows=59,712 width=12) (actual time=41.401..46.048 rows=59,679 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: 4,336kB
44. 10.027 10.027 ↑ 1.0 59,712 1

Seq Scan on non_working_intervals_by_dates (cost=0.00..1,281.12 rows=59,712 width=12) (actual time=0.014..10.027 rows=59,712 loops=1)

45. 43.870 137.093 ↑ 15.5 37,180 1

Sort (cost=606,597.28..608,034.78 rows=575,000 width=44) (actual time=131.720..137.093 rows=37,180 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: 2,844kB
46. 4.961 93.223 ↑ 17.8 32,266 1

Subquery Scan on summary_vacations_by_dates (cost=537,214.32..551,589.32 rows=575,000 width=44) (actual time=69.312..93.223 rows=32,266 loops=1)

47. 36.820 88.262 ↑ 17.8 32,266 1

HashAggregate (cost=537,214.32..545,839.32 rows=575,000 width=52) (actual time=69.311..88.262 rows=32,266 loops=1)

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
48. 11.711 51.442 ↑ 134.2 32,422 1

Hash Join (cost=138.05..493,700.50 rows=4,351,382 width=21) (actual time=1.709..51.442 rows=32,422 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: 1,908
49. 13.854 38.054 ↑ 634.8 34,330 1

Nested Loop (cost=0.01..436,340.93 rows=21,792,000 width=17) (actual time=0.023..38.054 rows=34,330 loops=1)

50. 2.408 2.408 ↑ 1.0 21,792 1

Seq Scan on vacations (cost=0.00..500.92 rows=21,792 width=17) (actual time=0.013..2.408 rows=21,792 loops=1)

51. 21.792 21.792 ↑ 500.0 2 21,792

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=21,792)

52. 0.877 1.677 ↑ 1.0 4,002 1

Hash (cost=88.02..88.02 rows=4,002 width=20) (actual time=1.677..1.677 rows=4,002 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 229kB
53. 0.800 0.800 ↑ 1.0 4,002 1

Seq Scan on staff_memberships (cost=0.00..88.02 rows=4,002 width=20) (actual time=0.006..0.800 rows=4,002 loops=1)

54. 163.043 660.271 ↓ 2.8 231,684 1

Hash Left Join (cost=2,062.19..129,499.88 rows=82,088 width=114) (actual time=23.101..660.271 rows=231,684 loops=1)

  • Hash Cond: (project_plans_1.project_id = projects_1_1.id)
  • Filter: (NOT (SubPlan 1))
  • Rows Removed by Filter: 770
55. 50.694 261.799 ↓ 1.4 232,454 1

Nested Loop (cost=1,833.42..128,165.83 rows=164,175 width=58) (actual time=20.085..261.799 rows=232,454 loops=1)

56. 2.549 53.585 ↓ 1.2 7,876 1

Hash Left Join (cost=1,833.41..3,392.83 rows=6,567 width=58) (actual time=20.044..53.585 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.project_plan_id = project_plans_1.id)
57. 19.755 49.424 ↓ 1.2 7,876 1

Hash Left Join (cost=1,631.38..3,173.55 rows=6,567 width=54) (actual time=18.401..49.424 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.plan_row_uuid = plan_rows_2.uuid)
  • Filter: (plan_rows_2.user_id IS NULL)
  • Rows Removed by Filter: 47,788
58. 11.363 11.363 ↓ 1.0 55,664 1

Seq Scan on plan_items plan_items_1 (cost=0.00..1,396.10 rows=55,639 width=46) (actual time=0.016..11.363 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
59. 10.263 18.306 ↑ 1.0 48,106 1

Hash (cost=1,030.06..1,030.06 rows=48,106 width=28) (actual time=18.306..18.306 rows=48,106 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,117kB
60. 8.043 8.043 ↑ 1.0 48,106 1

Seq Scan on plan_rows plan_rows_2 (cost=0.00..1,030.06 rows=48,106 width=28) (actual time=0.009..8.043 rows=48,106 loops=1)

61. 0.522 1.612 ↑ 1.0 2,851 1

Hash (cost=166.39..166.39 rows=2,851 width=8) (actual time=1.612..1.612 rows=2,851 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 144kB
62. 1.090 1.090 ↑ 1.0 2,851 1

Seq Scan on project_plans project_plans_1 (cost=0.00..166.39 rows=2,851 width=8) (actual time=0.013..1.090 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3,188
63. 157.520 157.520 ↓ 1.2 30 7,876

Function Scan on generate_series generate_series_2 (cost=0.01..18.76 rows=25 width=8) (actual time=0.007..0.020 rows=30 loops=7,876)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 12
64. 1.396 2.975 ↑ 1.0 5,812 1

Hash (cost=156.12..156.12 rows=5,812 width=12) (actual time=2.975..2.975 rows=5,812 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 314kB
65. 1.579 1.579 ↑ 1.0 5,812 1

Seq Scan on projects projects_1_1 (cost=0.00..156.12 rows=5,812 width=12) (actual time=0.009..1.579 rows=5,812 loops=1)

66.          

SubPlan (for Hash Left Join)

67. 114.482 232.454 ↓ 0.0 0 232,454

Nested Loop (cost=0.69..16.74 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=232,454)

68. 0.000 0.000 ↓ 0.0 0 232,454

Index Scan using clients_pkey on clients clients_3 (cost=0.28..8.29 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=232,454)

  • Index Cond: (projects_1_1.client_id = id)
69. 117.972 117.972 ↓ 0.0 0 58,986

Index Only Scan using non_working_intervals_by_office_id_idx on non_working_intervals_by_dates non_working_intervals_by_dates_2 (cost=0.41..8.44 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=58,986)

  • Index Cond: ((account_id = projects_1_1.account_id) AND (office_id = clients_3.office_id) AND (date = generate_series_2.generate_series))
  • Heap Fetches: 770
70. 13,068.851 13,825.021 ↑ 1.0 703,961 1

Hash Right Join (cost=45,029.23..183,164.61 rows=703,961 width=116) (actual time=687.089..13,825.021 rows=703,961 loops=1)

  • Hash Cond: (plan_rows_3.user_id = time_logs.user_id)
  • Join Filter: ((time_logs.date >= (COALESCE(plan_roles_1.start_date, '1980-01-01'::date))) AND (time_logs.date <= (COALESCE(lag((plan_roles_1.start_date - 1), 1) OVER (?), '3000-01-01'::date))) AND (((plan_rows_3.epic_id = time_logs.epic_id) AND (plan_rows_3.project_plan_id = epics.project_plan_id)) OR ((plan_rows_3.epic_id IS NULL) AND (plan_rows_3.project_plan_id = general_epic_plans.id))))
  • Rows Removed by Join Filter: 63,215,381
71. 103.657 198.856 ↓ 8.9 44,428 1

HashAggregate (cost=8,529.07..8,603.63 rows=4,971 width=48) (actual time=125.300..198.856 rows=44,428 loops=1)

  • Group Key: plan_rows_3.project_plan_id, plan_rows_3.epic_id, plan_rows_3.user_id, plan_roles_1.activity_id, COALESCE(plan_roles_1.start_date, '1980-01-01'::date), COALESCE(lag((plan_roles_1.start_date - 1), 1) OVER (?), '3000-01-01'::date)
72. 30.015 95.199 ↑ 1.0 44,581 1

WindowAgg (cost=6,077.00..7,080.12 rows=44,583 width=68) (actual time=59.445..95.199 rows=44,581 loops=1)

73. 25.269 65.184 ↑ 1.0 44,581 1

Sort (cost=6,077.00..6,188.46 rows=44,583 width=52) (actual time=59.436..65.184 rows=44,581 loops=1)

  • Sort Key: plan_roles_1.plan_row_uuid, plan_roles_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 5,023kB
74. 16.327 39.915 ↑ 1.0 44,581 1

Hash Join (cost=1,631.38..2,634.26 rows=44,583 width=52) (actual time=19.487..39.915 rows=44,581 loops=1)

  • Hash Cond: (plan_roles_1.plan_row_uuid = plan_rows_3.uuid)
75. 4.414 4.414 ↑ 1.0 44,581 1

Seq Scan on plan_roles plan_roles_1 (cost=0.00..885.83 rows=44,583 width=24) (actual time=0.018..4.414 rows=44,581 loops=1)

76. 10.558 19.174 ↑ 1.0 48,106 1

Hash (cost=1,030.06..1,030.06 rows=48,106 width=28) (actual time=19.174..19.174 rows=48,106 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,180kB
77. 8.616 8.616 ↑ 1.0 48,106 1

Seq Scan on plan_rows plan_rows_3 (cost=0.00..1,030.06 rows=48,106 width=28) (actual time=0.007..8.616 rows=48,106 loops=1)

78. 196.229 557.314 ↑ 1.0 703,961 1

Hash (cost=27,700.65..27,700.65 rows=703,961 width=40) (actual time=557.314..557.314 rows=703,961 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 62,366kB
79. 249.307 361.085 ↑ 1.0 703,961 1

Hash Left Join (cost=1,826.58..27,700.65 rows=703,961 width=40) (actual time=20.152..361.085 rows=703,961 loops=1)

  • Hash Cond: (time_logs.epic_id = epics.id)
80. 92.753 92.753 ↑ 1.0 703,961 1

Seq Scan on time_logs (cost=0.00..16,194.61 rows=703,961 width=28) (actual time=1.061..92.753 rows=703,961 loops=1)

81. 5.869 19.025 ↑ 1.0 26,435 1

Hash (cost=1,496.14..1,496.14 rows=26,435 width=16) (actual time=19.025..19.025 rows=26,435 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,381kB
82. 8.378 13.156 ↑ 1.0 26,435 1

Hash Left Join (cost=202.03..1,496.14 rows=26,435 width=16) (actual time=1.909..13.156 rows=26,435 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: 11,144
83. 2.917 2.917 ↑ 1.0 26,435 1

Seq Scan on epics (cost=0.00..619.35 rows=26,435 width=12) (actual time=0.008..2.917 rows=26,435 loops=1)

84. 0.593 1.861 ↑ 1.0 2,851 1

Hash (cost=166.39..166.39 rows=2,851 width=8) (actual time=1.861..1.861 rows=2,851 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 144kB
85. 1.268 1.268 ↑ 1.0 2,851 1

Seq Scan on project_plans general_epic_plans (cost=0.00..166.39 rows=2,851 width=8) (actual time=0.008..1.268 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3,188
86. 0.975 1.877 ↑ 1.0 5,812 1

Hash (cost=156.12..156.12 rows=5,812 width=8) (actual time=1.877..1.877 rows=5,812 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 292kB
87. 0.902 0.902 ↑ 1.0 5,812 1

Seq Scan on projects (cost=0.00..156.12 rows=5,812 width=8) (actual time=0.014..0.902 rows=5,812 loops=1)

88. 89.691 105.238 ↓ 295.4 1,255,026 1

Materialize (cost=847.14..942.74 rows=4,249 width=40) (actual time=13.303..105.238 rows=1,255,026 loops=1)

89. 1.568 15.547 ↑ 1.0 4,218 1

Unique (cost=847.14..889.63 rows=4,249 width=65) (actual time=13.299..15.547 rows=4,218 loops=1)

90. 4.209 13.979 ↑ 1.0 4,246 1

Sort (cost=847.14..857.76 rows=4,249 width=65) (actual time=13.299..13.979 rows=4,246 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: 790kB
91. 3.908 9.770 ↑ 1.0 4,249 1

WindowAgg (cost=484.85..591.08 rows=4,249 width=65) (actual time=5.425..9.770 rows=4,249 loops=1)

92. 2.095 5.862 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=56) (actual time=5.416..5.862 rows=4,249 loops=1)

  • Sort Key: staff_membership_activity_links.staff_membership_id, staff_membership_activity_links.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 790kB
93. 1.451 3.767 ↑ 1.0 4,249 1

Hash Join (cost=138.05..228.79 rows=4,249 width=56) (actual time=1.965..3.767 rows=4,249 loops=1)

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
94. 0.407 0.407 ↑ 1.0 4,256 1

Seq Scan on staff_membership_activity_links (cost=0.00..79.56 rows=4,256 width=28) (actual time=0.022..0.407 rows=4,256 loops=1)

95. 1.024 1.909 ↑ 1.0 4,002 1

Hash (cost=88.02..88.02 rows=4,002 width=32) (actual time=1.909..1.909 rows=4,002 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 276kB
96. 0.885 0.885 ↑ 1.0 4,002 1

Seq Scan on staff_memberships staff_memberships_1 (cost=0.00..88.02 rows=4,002 width=32) (actual time=0.008..0.885 rows=4,002 loops=1)

97. 0.086 0.236 ↑ 1.0 354 1

Hash (cost=14.54..14.54 rows=354 width=9) (actual time=0.236..0.236 rows=354 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
98. 0.150 0.150 ↑ 1.0 354 1

Seq Scan on accounts accounts_1 (cost=0.00..14.54 rows=354 width=9) (actual time=0.011..0.150 rows=354 loops=1)

99. 11.145 297.130 ↑ 15.6 30,644 1

Subquery Scan on *SELECT* 2_1 (cost=3,354.66..521,531.40 rows=476,700 width=136) (actual time=39.797..297.130 rows=30,644 loops=1)

100. 158.944 285.985 ↑ 15.6 30,644 1

Hash Anti Join (cost=3,354.66..515,572.65 rows=476,700 width=143) (actual time=39.794..285.985 rows=30,644 loops=1)

  • Hash Cond: ((sa.account_id = non_working_intervals_by_dates_1.account_id) AND (sa.user_id = non_working_intervals_by_dates_1.user_id))
  • Join Filter: (generate_series_3.generate_series = non_working_intervals_by_dates_1.date)
  • Rows Removed by Join Filter: 1,153,172
101. 22.113 102.250 ↑ 17.5 31,132 1

Merge Left Join (cost=1,177.86..481,269.67 rows=544,800 width=64) (actual time=14.936..102.250 rows=31,132 loops=1)

  • Merge Cond: (vacations_1.staff_membership_id = sa.staff_membership_id)
  • Join Filter: (((generate_series_3.generate_series)::date >= sa.start_date) AND ((generate_series_3.generate_series)::date <= sa.end_date))
  • Rows Removed by Join Filter: 10,924
102. 8.178 61.710 ↑ 17.5 31,132 1

Nested Loop (cost=0.29..415,689.39 rows=544,800 width=44) (actual time=0.033..61.710 rows=31,132 loops=1)

103. 9.948 9.948 ↑ 1.0 21,792 1

Index Scan using index_vacations_on_staff_membership_id on vacations vacations_1 (cost=0.29..1,641.38 rows=21,792 width=44) (actual time=0.016..9.948 rows=21,792 loops=1)

104. 43.584 43.584 ↑ 25.0 1 21,792

Function Scan on generate_series generate_series_3 (cost=0.01..18.76 rows=25 width=8) (actual time=0.001..0.002 rows=1 loops=21,792)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 0
105. 5.287 18.427 ↓ 10.2 43,399 1

Sort (cost=1,177.56..1,188.19 rows=4,249 width=36) (actual time=14.892..18.427 rows=43,399 loops=1)

  • Sort Key: sa.staff_membership_id
  • Sort Method: quicksort Memory: 522kB
106. 0.835 13.140 ↑ 1.0 4,221 1

Subquery Scan on sa (cost=836.52..921.50 rows=4,249 width=36) (actual time=11.216..13.140 rows=4,221 loops=1)

107. 0.817 12.305 ↑ 1.0 4,221 1

Unique (cost=836.52..879.01 rows=4,249 width=65) (actual time=11.214..12.305 rows=4,221 loops=1)

108. 2.888 11.488 ↑ 1.0 4,249 1

Sort (cost=836.52..847.14 rows=4,249 width=65) (actual time=11.213..11.488 rows=4,249 loops=1)

  • Sort Key: staff_memberships_2.account_id, staff_memberships_2.user_id, (COALESCE(staff_membership_activity_links_1.start_date, staff_memberships_2.joined_at)), (COALESCE(lag((staff_membership_activity_links_1.start_date - 1), 1) OVER (?), staff_memberships_2.archived_at, '3000-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 592kB
109. 3.058 8.600 ↑ 1.0 4,249 1

WindowAgg (cost=484.85..580.45 rows=4,249 width=65) (actual time=5.219..8.600 rows=4,249 loops=1)

110. 2.024 5.542 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=44) (actual time=5.211..5.542 rows=4,249 loops=1)

  • Sort Key: staff_membership_activity_links_1.staff_membership_id, staff_membership_activity_links_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 561kB
111. 1.423 3.518 ↑ 1.0 4,249 1

Hash Join (cost=138.05..228.79 rows=4,249 width=44) (actual time=1.713..3.518 rows=4,249 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_2.id)
112. 0.405 0.405 ↑ 1.0 4,256 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_1 (cost=0.00..79.56 rows=4,256 width=20) (actual time=0.012..0.405 rows=4,256 loops=1)

113. 0.894 1.690 ↑ 1.0 4,002 1

Hash (cost=88.02..88.02 rows=4,002 width=24) (actual time=1.690..1.690 rows=4,002 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 245kB
114. 0.796 0.796 ↑ 1.0 4,002 1

Seq Scan on staff_memberships staff_memberships_2 (cost=0.00..88.02 rows=4,002 width=24) (actual time=0.006..0.796 rows=4,002 loops=1)

115. 12.883 24.791 ↑ 1.0 58,975 1

Hash (cost=1,281.12..1,281.12 rows=59,712 width=12) (actual time=24.791..24.791 rows=58,975 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,047kB
116. 11.908 11.908 ↑ 1.0 59,712 1

Seq Scan on non_working_intervals_by_dates non_working_intervals_by_dates_1 (cost=0.00..1,281.12 rows=59,712 width=12) (actual time=0.010..11.908 rows=59,712 loops=1)

117. 0.004 0.135 ↑ 9.8 12 1

Hash (cost=17.20..17.20 rows=118 width=4) (actual time=0.135..0.135 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
118. 0.131 0.131 ↑ 9.8 12 1

Seq Scan on accounts (cost=0.00..17.20 rows=118 width=4) (actual time=0.029..0.131 rows=12 loops=1)

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 342
119. 383.211 662.145 ↓ 4.1 1,115,238 1

Sort (cost=46,830.92..47,512.59 rows=272,669 width=44) (actual time=570.110..662.145 rows=1,115,238 loops=1)

  • Sort Key: ""*SELECT* 1"".project_id, ""*SELECT* 1"".activity_id
  • Sort Method: quicksort Memory: 32,838kB
120. 30.842 278.934 ↑ 1.0 263,033 1

Append (cost=300.88..22,213.28 rows=272,669 width=44) (actual time=2.026..278.934 rows=263,033 loops=1)

121. 0.094 15.881 ↓ 9.7 474 1

Subquery Scan on *SELECT* 1 (cost=300.88..470.62 rows=49 width=44) (actual time=2.025..15.881 rows=474 loops=1)

122. 0.142 15.787 ↓ 9.7 474 1

Merge Join (cost=300.88..470.13 rows=49 width=612) (actual time=2.024..15.787 rows=474 loops=1)

  • Merge Cond: (clients.brand_id = brands.id)
123. 0.144 15.585 ↓ 9.7 474 1

Nested Loop (cost=294.80..1,671.71 rows=49 width=52) (actual time=1.960..15.585 rows=474 loops=1)

124. 0.008 1.271 ↓ 5.0 5 1

Merge Join (cost=294.38..294.67 rows=1 width=32) (actual time=1.251..1.271 rows=5 loops=1)

  • Merge Cond: (rate_cards.rateable_id = clients.brand_id)
125. 0.025 0.577 ↑ 28.8 4 1

WindowAgg (cost=108.61..111.49 rows=115 width=45) (actual time=0.559..0.577 rows=4 loops=1)

126. 0.034 0.552 ↑ 23.0 5 1

Sort (cost=108.61..108.90 rows=115 width=29) (actual time=0.551..0.552 rows=5 loops=1)

  • Sort Key: rate_cards.rateable_id, rate_cards.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 33kB
127. 0.518 0.518 ↑ 1.0 115 1

Seq Scan on rate_cards (cost=0.00..104.68 rows=115 width=29) (actual time=0.127..0.518 rows=115 loops=1)

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4,739
128. 0.005 0.686 ↑ 1.0 5 1

Sort (cost=185.76..185.78 rows=5 width=8) (actual time=0.684..0.686 rows=5 loops=1)

  • Sort Key: clients.brand_id
  • Sort Method: quicksort Memory: 25kB
129. 0.006 0.681 ↑ 1.0 5 1

Nested Loop (cost=0.56..185.71 rows=5 width=8) (actual time=0.376..0.681 rows=5 loops=1)

130. 0.009 0.655 ↑ 1.0 5 1

Nested Loop (cost=0.28..184.13 rows=5 width=8) (actual time=0.361..0.655 rows=5 loops=1)

131. 0.631 0.631 ↑ 1.0 5 1

Seq Scan on pricing_models (cost=0.00..142.62 rows=5 width=4) (actual time=0.349..0.631 rows=5 loops=1)

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5,805
132. 0.015 0.015 ↑ 1.0 1 5

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

  • Index Cond: (id = pricing_models.project_id)
133. 0.020 0.020 ↑ 1.0 1 5

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

  • Index Cond: (id = projects_2.client_id)
134. 14.170 14.170 ↓ 1.9 95 5

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates (cost=0.42..1,376.55 rows=49 width=28) (actual time=0.667..2.834 rows=95 loops=5)

  • Index Cond: (rate_card_id = rate_cards.id)
135. 0.042 0.060 ↑ 38.3 3 1

Sort (cost=6.09..6.37 rows=115 width=4) (actual time=0.059..0.060 rows=3 loops=1)

  • Sort Key: brands.id
  • Sort Method: quicksort Memory: 30kB
136. 0.018 0.018 ↑ 1.0 115 1

Seq Scan on brands (cost=0.00..2.15 rows=115 width=4) (actual time=0.007..0.018 rows=115 loops=1)

137. 13.782 72.541 ↓ 2.6 91,600 1

Subquery Scan on *SELECT* 2 (cost=583.00..5,484.31 rows=35,240 width=44) (actual time=11.940..72.541 rows=91,600 loops=1)

138. 37.193 58.759 ↓ 2.6 91,600 1

Hash Join (cost=583.00..5,131.91 rows=35,240 width=612) (actual time=11.938..58.759 rows=91,600 loops=1)

  • Hash Cond: (rates_1.rate_card_id = rate_cards_1.id)
139. 16.372 16.372 ↑ 1.0 174,437 1

Seq Scan on rates rates_1 (cost=0.00..3,542.37 rows=174,437 width=28) (actual time=0.010..16.372 rows=174,437 loops=1)

140. 0.184 5.194 ↑ 1.1 657 1

Hash (cost=574.00..574.00 rows=720 width=24) (actual time=5.194..5.194 rows=657 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 47kB
141. 0.165 5.010 ↑ 1.1 657 1

Hash Join (cost=378.98..574.00 rows=720 width=24) (actual time=3.125..5.010 rows=657 loops=1)

  • Hash Cond: (clients_1.office_id = offices.id)
142. 0.172 3.325 ↑ 1.0 623 1

Hash Join (cost=214.02..399.80 rows=623 width=8) (actual time=1.601..3.325 rows=623 loops=1)

  • Hash Cond: (projects_3.client_id = clients_1.id)
143. 0.908 2.391 ↑ 1.0 623 1

Hash Join (cost=150.41..334.56 rows=623 width=8) (actual time=0.833..2.391 rows=623 loops=1)

  • Hash Cond: (projects_3.id = pricing_models_1.project_id)
144. 0.668 0.668 ↑ 1.0 5,812 1

Seq Scan on projects projects_3 (cost=0.00..156.12 rows=5,812 width=8) (actual time=0.009..0.668 rows=5,812 loops=1)

145. 0.100 0.815 ↑ 1.0 623 1

Hash (cost=142.62..142.62 rows=623 width=4) (actual time=0.815..0.815 rows=623 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
146. 0.715 0.715 ↑ 1.0 623 1

Seq Scan on pricing_models pricing_models_1 (cost=0.00..142.62 rows=623 width=4) (actual time=0.014..0.715 rows=623 loops=1)

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5,187
147. 0.361 0.762 ↑ 1.0 1,849 1

Hash (cost=40.49..40.49 rows=1,849 width=8) (actual time=0.762..0.762 rows=1,849 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 89kB
148. 0.401 0.401 ↑ 1.0 1,849 1

Seq Scan on clients clients_1 (cost=0.00..40.49 rows=1,849 width=8) (actual time=0.007..0.401 rows=1,849 loops=1)

149. 0.124 1.520 ↑ 1.1 450 1

Hash (cost=158.85..158.85 rows=489 width=28) (actual time=1.520..1.520 rows=450 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
150. 0.136 1.396 ↑ 1.1 450 1

Hash Join (cost=140.44..158.85 rows=489 width=28) (actual time=0.838..1.396 rows=450 loops=1)

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
151. 0.393 1.118 ↑ 1.0 489 1

WindowAgg (cost=126.52..138.74 rows=489 width=45) (actual time=0.692..1.118 rows=489 loops=1)

152. 0.164 0.725 ↑ 1.0 489 1

Sort (cost=126.52..127.74 rows=489 width=29) (actual time=0.686..0.725 rows=489 loops=1)

  • Sort Key: rate_cards_1.rateable_id, rate_cards_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 63kB
153. 0.561 0.561 ↑ 1.0 489 1

Seq Scan on rate_cards rate_cards_1 (cost=0.00..104.68 rows=489 width=29) (actual time=0.108..0.561 rows=489 loops=1)

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4,365
154. 0.075 0.142 ↑ 1.0 441 1

Hash (cost=8.41..8.41 rows=441 width=4) (actual time=0.142..0.142 rows=441 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
155. 0.067 0.067 ↑ 1.0 441 1

Seq Scan on offices (cost=0.00..8.41 rows=441 width=4) (actual time=0.015..0.067 rows=441 loops=1)

156. 15.084 87.579 ↑ 2.2 94,593 1

Subquery Scan on *SELECT* 3 (cost=840.78..9,363.71 rows=205,419 width=44) (actual time=12.284..87.579 rows=94,593 loops=1)

157. 42.082 72.495 ↑ 2.2 94,593 1

Hash Join (cost=840.78..7,309.52 rows=205,419 width=612) (actual time=12.283..72.495 rows=94,593 loops=1)

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
158. 18.176 18.176 ↑ 1.0 174,437 1

Seq Scan on rates rates_2 (cost=0.00..3,542.37 rows=174,437 width=28) (actual time=0.010..18.176 rows=174,437 loops=1)

159. 0.968 12.237 ↑ 1.0 4,004 1

Hash (cost=788.31..788.31 rows=4,197 width=24) (actual time=12.237..12.237 rows=4,004 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 299kB
160. 1.097 11.269 ↑ 1.0 4,004 1

Hash Join (cost=586.95..788.31 rows=4,197 width=24) (actual time=8.226..11.269 rows=4,004 loops=1)

  • Hash Cond: (projects_4.client_id = clients_2.id)
161. 1.090 4.682 ↑ 1.0 3,620 1

Hash Join (cost=228.77..380.91 rows=3,620 width=8) (actual time=2.680..4.682 rows=3,620 loops=1)

  • Hash Cond: (pricing_models_2.project_id = projects_4.id)
162. 0.981 0.981 ↑ 1.0 3,621 1

Seq Scan on pricing_models pricing_models_2 (cost=0.00..142.62 rows=3,621 width=4) (actual time=0.007..0.981 rows=3,621 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2,189
163. 1.163 2.611 ↑ 1.0 5,812 1

Hash (cost=156.12..156.12 rows=5,812 width=8) (actual time=2.611..2.611 rows=5,812 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 292kB
164. 1.448 1.448 ↑ 1.0 5,812 1

Seq Scan on projects projects_4 (cost=0.00..156.12 rows=5,812 width=8) (actual time=0.006..1.448 rows=5,812 loops=1)

165. 0.475 5.490 ↑ 1.0 1,820 1

Hash (cost=335.42..335.42 rows=1,821 width=28) (actual time=5.490..5.490 rows=1,820 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 123kB
166. 0.530 5.015 ↑ 1.0 1,820 1

Hash Join (cost=266.89..335.42 rows=1,821 width=28) (actual time=3.032..5.015 rows=1,820 loops=1)

  • Hash Cond: (rate_cards_2.rateable_id = clients_2.id)
167. 1.345 3.897 ↑ 1.0 1,821 1

WindowAgg (cost=203.29..248.81 rows=1,821 width=45) (actual time=2.421..3.897 rows=1,821 loops=1)

168. 1.751 2.552 ↑ 1.0 1,821 1

Sort (cost=203.29..207.84 rows=1,821 width=29) (actual time=2.410..2.552 rows=1,821 loops=1)

  • Sort Key: rate_cards_2.rateable_id, rate_cards_2.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 191kB
169. 0.801 0.801 ↑ 1.0 1,821 1

Seq Scan on rate_cards rate_cards_2 (cost=0.00..104.68 rows=1,821 width=29) (actual time=0.008..0.801 rows=1,821 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 3,033
170. 0.318 0.588 ↑ 1.0 1,849 1

Hash (cost=40.49..40.49 rows=1,849 width=4) (actual time=0.588..0.588 rows=1,849 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
171. 0.270 0.270 ↑ 1.0 1,849 1

Seq Scan on clients clients_2 (cost=0.00..40.49 rows=1,849 width=4) (actual time=0.009..0.270 rows=1,849 loops=1)

172. 11.394 72.091 ↓ 2.4 76,366 1

Subquery Scan on *SELECT* 4 (cost=695.56..5,531.29 rows=31,961 width=44) (actual time=6.626..72.091 rows=76,366 loops=1)

173. 36.815 60.697 ↓ 2.4 76,366 1

Hash Join (cost=695.56..5,211.68 rows=31,961 width=612) (actual time=6.624..60.697 rows=76,366 loops=1)

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
174. 17.295 17.295 ↑ 1.0 174,437 1

Seq Scan on rates rates_3 (cost=0.00..3,542.37 rows=174,437 width=28) (actual time=0.009..17.295 rows=174,437 loops=1)

175. 0.298 6.587 ↓ 2.4 1,591 1

Hash (cost=687.40..687.40 rows=653 width=24) (actual time=6.587..6.587 rows=1,591 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
176. 0.768 6.289 ↓ 2.4 1,591 1

Hash Join (cost=502.95..687.40 rows=653 width=24) (actual time=5.074..6.289 rows=1,591 loops=1)

  • Hash Cond: (projects_5.id = pricing_models_3.project_id)
177. 0.470 0.470 ↑ 1.0 5,812 1

Seq Scan on projects projects_5 (cost=0.00..156.12 rows=5,812 width=4) (actual time=0.005..0.470 rows=5,812 loops=1)

178. 0.344 5.051 ↓ 2.4 1,591 1

Hash (cost=494.79..494.79 rows=653 width=24) (actual time=5.051..5.051 rows=1,591 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
179. 0.500 4.707 ↓ 2.4 1,591 1

Hash Join (cost=403.40..494.79 rows=653 width=24) (actual time=2.592..4.707 rows=1,591 loops=1)

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
180. 1.505 3.216 ↑ 1.0 2,429 1

WindowAgg (cost=241.26..301.98 rows=2,429 width=45) (actual time=1.568..3.216 rows=2,429 loops=1)

181. 0.958 1.711 ↑ 1.0 2,429 1

Sort (cost=241.26..247.33 rows=2,429 width=29) (actual time=1.558..1.711 rows=2,429 loops=1)

  • Sort Key: rate_cards_3.rateable_id, rate_cards_3.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 286kB
182. 0.753 0.753 ↑ 1.0 2,429 1

Seq Scan on rate_cards rate_cards_3 (cost=0.00..104.68 rows=2,429 width=29) (actual time=0.009..0.753 rows=2,429 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2,425
183. 0.231 0.991 ↑ 1.0 1,561 1

Hash (cost=142.62..142.62 rows=1,561 width=8) (actual time=0.991..0.991 rows=1,561 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 77kB
184. 0.760 0.760 ↑ 1.0 1,561 1

Seq Scan on pricing_models pricing_models_3 (cost=0.00..142.62 rows=1,561 width=8) (actual time=0.010..0.760 rows=1,561 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4,249
185. 47.708 47.828 ↓ 2,151.2 658,252 1

Sort (cost=22.77..23.53 rows=306 width=26) (actual time=0.224..47.828 rows=658,252 loops=1)

  • Sort Key: discounts.project_id
  • Sort Method: quicksort Memory: 53kB
186. 0.120 0.120 ↓ 1.2 359 1

Seq Scan on discounts (cost=0.00..10.13 rows=306 width=26) (actual time=0.016..0.120 rows=359 loops=1)

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
187. 2,572.796 2,573.457 ↓ 12,841.5 39,140,740 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.705..2,573.457 rows=39,140,740 loops=1)

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 335kB
188. 0.661 0.661 ↑ 1.0 3,048 1

Seq Scan on exchange_rates_by_months (cost=0.00..68.48 rows=3,048 width=24) (actual time=0.020..0.661 rows=3,048 loops=1)

189. 2,960.298 2,960.881 ↓ 14,570.5 44,410,811 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.546..2,960.881 rows=44,410,811 loops=1)

  • Sort Key: cost_exchange_rates.account_id, cost_exchange_rates.currency
  • Sort Method: quicksort Memory: 335kB
190. 0.583 0.583 ↑ 1.0 3,048 1

Seq Scan on exchange_rates_by_months cost_exchange_rates (cost=0.00..68.48 rows=3,048 width=24) (actual time=0.015..0.583 rows=3,048 loops=1)

Planning time : 9.796 ms
Execution time : 47,963.539 ms