explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lcrz

Settings
# exclusive inclusive rows x rows loops node
1. 7,464.535 66,225.029 ↓ 2.4 850,040 1

Merge Left Join (cost=3,480,078.13..3,488,693.67 rows=355,801 width=140) (actual time=55,776.466..66,225.029 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: 43703359
2. 704.573 55,838.206 ↓ 2.4 850,040 1

Sort (cost=3,479,833.27..3,480,722.77 rows=355,801 width=176) (actual time=55,774.686..55,838.206 rows=850,040 loops=1)

  • Sort Key: projects.account_id, staff_memberships_1.currency
  • Sort Method: quicksort Memory: 215846kB
3. 6,972.579 55,133.633 ↓ 2.4 850,040 1

Merge Left Join (cost=3,443,748.62..3,447,027.15 rows=355,801 width=176) (actual time=45,325.812..55,133.633 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: 40293124
4. 626.562 45,389.660 ↓ 2.4 850,040 1

Sort (cost=3,443,503.76..3,444,393.26 rows=355,801 width=168) (actual time=45,324.374..45,389.660 rows=850,040 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".currency
  • Sort Method: quicksort Memory: 171286kB
5. 288.548 44,763.098 ↓ 2.4 850,040 1

Merge Left Join (cost=3,336,236.90..3,410,697.64 rows=355,801 width=168) (actual time=43,734.974..44,763.098 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: 647094
6. 516.082 44,427.276 ↓ 2.4 849,952 1

Merge Left Join (cost=3,336,214.13..3,393,454.09 rows=355,801 width=164) (actual time=43,734.802..44,427.276 rows=849,952 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: 243485
7. 624.080 43,269.289 ↓ 2.4 849,952 1

Sort (cost=3,289,383.21..3,290,272.72 rows=355,801 width=136) (actual time=43,185.237..43,269.289 rows=849,952 loops=1)

  • Sort Key: projects.id, (COALESCE(pa.activity_id, ""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id))
  • Sort Method: quicksort Memory: 144101kB
8. 278.450 42,645.209 ↓ 2.4 849,952 1

Hash Join (cost=2,208,023.89..3,256,577.10 rows=355,801 width=136) (actual time=29,780.818..42,645.209 rows=849,952 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
9. 202.238 42,366.631 ↓ 1.5 1,614,425 1

Append (cost=2,208,005.22..3,243,048.91 rows=1,067,404 width=136) (actual time=29,778.848..42,366.631 rows=1,614,425 loops=1)

10. 8,627.114 41,878.486 ↓ 2.7 1,583,781 1

Merge Left Join (cost=2,208,005.22..2,710,273.45 rows=590,704 width=108) (actual time=29,778.847..41,878.486 rows=1,583,781 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: 43669087
11. 1,743.539 29,850.047 ↓ 2.7 1,583,781 1

Sort (cost=2,198,439.24..2,199,916.00 rows=590,704 width=149) (actual time=29,574.464..29,850.047 rows=1,583,781 loops=1)

  • Sort Key: "*SELECT* 1_1".project_plan_id
  • Sort Method: external merge Disk: 165136kB
12. 437.231 28,106.508 ↓ 2.7 1,583,781 1

Hash Left Join (cost=2,120,375.06..2,141,814.13 rows=590,704 width=149) (actual time=26,636.411..28,106.508 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: 13596
13. 733.882 27,669.113 ↑ 1.1 1,597,377 1

Merge Left Join (cost=2,120,356.09..2,137,109.52 rows=1,754,565 width=144) (actual time=26,636.236..27,669.113 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: 258899
14. 1,754.842 26,826.228 ↑ 1.1 1,597,377 1

Sort (cost=2,119,508.95..2,123,895.36 rows=1,754,565 width=120) (actual time=26,622.575..26,826.228 rows=1,597,377 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1_1"".user_id
  • Sort Method: quicksort Memory: 268517kB
15. 426.965 25,071.386 ↑ 1.1 1,597,377 1

Hash Left Join (cost=1,673,625.69..1,937,537.03 rows=1,754,565 width=120) (actual time=1,789.861..25,071.386 rows=1,597,377 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
16. 203.155 24,642.547 ↑ 1.1 1,597,377 1

Append (cost=1,673,396.92..1,915,153.52 rows=1,754,565 width=116) (actual time=1,787.971..24,642.547 rows=1,597,377 loops=1)

17. 317.653 24,024.957 ↑ 1.2 893,416 1

Result (cost=1,673,396.92..1,868,120.63 rows=1,050,604 width=116) (actual time=1,787.970..24,024.957 rows=893,416 loops=1)

18. 110.909 23,707.304 ↑ 1.2 893,416 1

Append (cost=1,673,396.92..1,854,988.08 rows=1,050,604 width=140) (actual time=1,787.966..23,707.304 rows=893,416 loops=1)

19. 125.591 2,576.793 ↑ 1.5 661,732 1

Subquery Scan on *SELECT* 1_1 (cost=1,673,396.92..1,716,730.33 rows=968,516 width=114) (actual time=1,787.965..2,576.793 rows=661,732 loops=1)

20. 186.989 2,451.202 ↑ 1.5 661,732 1

Merge Left Join (cost=1,673,396.92..1,702,202.59 rows=968,516 width=134) (actual time=1,787.963..2,451.202 rows=661,732 loops=1)

  • Merge Cond: ((generate_series.generate_series = summary_vacations_by_dates.date) AND (projects_1.account_id = summary_vacations_by_dates.account_id) AND (plan_rows.user_id = summary_vacations_by_dates.user_id))
21. 218.432 2,144.528 ↑ 1.5 661,732 1

Merge Anti Join (cost=1,066,799.64..1,080,109.02 rows=968,516 width=66) (actual time=1,787.961..2,144.528 rows=661,732 loops=1)

  • Merge Cond: ((generate_series.generate_series = non_working_intervals_by_dates.date) AND (projects_1.account_id = non_working_intervals_by_dates.account_id) AND (plan_rows.user_id = non_working_intervals_by_dates.user_id))
22. 1,255.921 1,883.066 ↑ 1.8 669,378 1

Sort (cost=1,060,781.65..1,063,848.65 rows=1,226,800 width=66) (actual time=1,787.957..1,883.066 rows=669,378 loops=1)

  • Sort Key: generate_series.generate_series, projects_1.account_id, plan_rows.user_id
  • Sort Method: quicksort Memory: 118708kB
23. 102.372 627.145 ↑ 1.8 669,378 1

Nested Loop (cost=1,991.21..936,712.49 rows=1,226,800 width=66) (actual time=27.367..627.145 rows=669,378 loops=1)

24. 13.229 94.681 ↑ 1.0 47,788 1

Hash Left Join (cost=1,991.21..4,344.48 rows=49,072 width=66) (actual time=27.341..94.681 rows=47,788 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
25. 13.658 79.289 ↑ 1.0 47,788 1

Hash Left Join (cost=1,762.44..3,986.81 rows=49,072 width=62) (actual time=25.162..79.289 rows=47,788 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
26. 27.367 63.602 ↑ 1.0 47,788 1

Hash Join (cost=1,560.41..3,655.88 rows=49,072 width=58) (actual time=23.089..63.602 rows=47,788 loops=1)

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
27. 13.459 13.459 ↓ 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..13.459 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
28. 11.635 22.776 ↑ 1.0 42,344 1

Hash (cost=1,030.06..1,030.06 rows=42,428 width=28) (actual time=22.775..22.776 rows=42,344 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2816kB
29. 11.141 11.141 ↑ 1.0 42,344 1

Seq Scan on plan_rows (cost=0.00..1,030.06 rows=42,428 width=28) (actual time=0.010..11.141 rows=42,344 loops=1)

  • Filter: (user_id IS NOT NULL)
  • Rows Removed by Filter: 5762
30. 0.614 2.029 ↑ 1.0 2,851 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
31. 1.415 1.415 ↑ 1.0 2,851 1

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

  • Filter: active
  • Rows Removed by Filter: 3188
32. 1.177 2.163 ↑ 1.0 5,812 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 292kB
33. 0.986 0.986 ↑ 1.0 5,812 1

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

34. 430.092 430.092 ↑ 1.8 14 47,788

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.004..0.009 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
35. 32.454 43.030 ↑ 1.0 59,712 1

Sort (cost=6,017.99..6,167.27 rows=59,712 width=12) (actual time=38.143..43.030 rows=59,712 loops=1)

  • Sort Key: non_working_intervals_by_dates.date, non_working_intervals_by_dates.account_id, non_working_intervals_by_dates.user_id
  • Sort Method: quicksort Memory: 4336kB
36. 10.576 10.576 ↑ 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.007..10.576 rows=59,712 loops=1)

37. 30.447 119.685 ↑ 15.5 37,179 1

Sort (cost=606,597.28..608,034.78 rows=575,000 width=44) (actual time=113.113..119.685 rows=37,179 loops=1)

  • Sort Key: summary_vacations_by_dates.date, summary_vacations_by_dates.account_id, summary_vacations_by_dates.user_id
  • Sort Method: quicksort Memory: 2844kB
38. 4.472 89.238 ↑ 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=68.182..89.238 rows=32,266 loops=1)

39. 33.057 84.766 ↑ 17.8 32,266 1

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

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
40. 12.070 51.709 ↑ 134.2 32,422 1

Hash Join (cost=138.05..493,700.50 rows=4,351,382 width=21) (actual time=1.973..51.709 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: 1908
41. 13.420 37.705 ↑ 634.8 34,330 1

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

42. 2.493 2.493 ↑ 1.0 21,792 1

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

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

44. 1.002 1.934 ↑ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 229kB
45. 0.932 0.932 ↑ 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.932 rows=4,002 loops=1)

46. 259.233 21,019.602 ↓ 2.8 231,684 1

Hash Left Join (cost=2,062.19..132,183.85 rows=82,088 width=114) (actual time=23.155..21,019.602 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
47. 56.051 301.606 ↓ 1.4 232,454 1

Nested Loop (cost=1,833.42..128,784.81 rows=164,175 width=58) (actual time=20.317..301.606 rows=232,454 loops=1)

48. 3.801 64.407 ↓ 1.2 7,876 1

Hash Left Join (cost=1,833.41..4,011.80 rows=6,567 width=58) (actual time=20.289..64.407 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.project_plan_id = project_plans_1.id)
49. 26.893 58.885 ↓ 1.2 7,876 1

Hash Left Join (cost=1,631.38..3,792.52 rows=6,567 width=54) (actual time=18.539..58.885 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.plan_row_uuid = plan_rows_1.uuid)
  • Filter: (plan_rows_1.user_id IS NULL)
  • Rows Removed by Filter: 47788
50. 13.556 13.556 ↓ 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.015..13.556 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
51. 9.975 18.436 ↑ 1.0 48,106 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3117kB
52. 8.461 8.461 ↑ 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.008..8.461 rows=48,106 loops=1)

53. 0.529 1.721 ↑ 1.0 2,851 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
54. 1.192 1.192 ↑ 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.010..1.192 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
55. 181.148 181.148 ↓ 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.008..0.023 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
56. 1.250 2.811 ↑ 1.0 5,812 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 314kB
57. 1.561 1.561 ↑ 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.561 rows=5,812 loops=1)

58.          

SubPlan (for Hash Left Join)

59. 0.000 20,455.952 ↓ 0.0 0 232,454

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

  • Join Filter: (non_working_intervals_by_dates_2.office_id = clients_3.office_id)
  • Rows Removed by Join Filter: 1
60. 232.454 232.454 ↓ 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.001 rows=0 loops=232,454)

  • Index Cond: (projects_1_1.client_id = id)
61. 20,350.170 20,350.170 ↑ 3.7 3 58,986

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates non_working_intervals_by_dates_2 (cost=0.41..82.06 rows=11 width=4) (actual time=0.336..0.345 rows=3 loops=58,986)

  • Index Cond: ((projects_1_1.account_id = account_id) AND (generate_series_2.generate_series = date))
62. 299.276 414.435 ↑ 1.0 703,961 1

Hash Join (cost=1,826.58..31,220.45 rows=703,961 width=116) (actual time=15.475..414.435 rows=703,961 loops=1)

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

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

64. 4.751 15.429 ↑ 1.0 26,435 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1381kB
65. 6.619 10.678 ↑ 1.0 26,435 1

Hash Left Join (cost=202.03..1,496.14 rows=26,435 width=16) (actual time=1.606..10.678 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: 11144
66. 2.490 2.490 ↑ 1.0 26,435 1

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

67. 0.446 1.569 ↑ 1.0 2,851 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 144kB
68. 1.123 1.123 ↑ 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.006..1.123 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3188
69. 0.989 1.874 ↑ 1.0 5,812 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 292kB
70. 0.885 0.885 ↑ 1.0 5,812 1

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

71. 93.055 109.003 ↓ 295.4 1,255,026 1

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

72. 1.644 15.948 ↑ 1.0 4,218 1

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

73. 3.571 14.304 ↑ 1.0 4,246 1

Sort (cost=847.14..857.76 rows=4,249 width=65) (actual time=13.651..14.304 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
74. 3.393 10.733 ↑ 1.0 4,249 1

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

75. 3.229 7.340 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=56) (actual time=6.979..7.340 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
76. 1.635 4.111 ↑ 1.0 4,249 1

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

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
77. 0.489 0.489 ↑ 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.016..0.489 rows=4,256 loops=1)

78. 1.036 1.987 ↑ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 276kB
79. 0.951 0.951 ↑ 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.007..0.951 rows=4,002 loops=1)

80. 0.058 0.164 ↑ 1.0 354 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
81. 0.106 0.106 ↑ 1.0 354 1

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

82. 3,213.452 3,401.325 ↓ 9,048.8 44,981,371 1

Sort (cost=9,565.98..9,578.40 rows=4,971 width=48) (actual time=204.368..3,401.325 rows=44,981,371 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: quicksort Memory: 5645kB
83. 6.693 187.873 ↓ 8.9 44,428 1

Subquery Scan on pa (cost=9,136.50..9,260.77 rows=4,971 width=48) (actual time=137.025..187.873 rows=44,428 loops=1)

84. 77.521 181.180 ↓ 8.9 44,428 1

HashAggregate (cost=9,136.50..9,211.06 rows=4,971 width=48) (actual time=137.024..181.180 rows=44,428 loops=1)

  • Group Key: plan_rows_2.project_plan_id, plan_rows_2.epic_id, plan_rows_2.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)
85. 34.703 103.659 ↑ 1.0 44,581 1

WindowAgg (cost=6,572.98..7,576.09 rows=44,583 width=68) (actual time=62.421..103.659 rows=44,581 loops=1)

86. 24.536 68.956 ↑ 1.0 44,581 1

Sort (cost=6,572.98..6,684.43 rows=44,583 width=60) (actual time=62.409..68.956 rows=44,581 loops=1)

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 7154kB
87. 17.490 44.420 ↑ 1.0 44,581 1

Hash Join (cost=1,631.38..3,130.23 rows=44,583 width=60) (actual time=22.765..44.420 rows=44,581 loops=1)

  • Hash Cond: (plan_roles.plan_row_uuid = plan_rows_2.uuid)
88. 4.417 4.417 ↑ 1.0 44,583 1

Seq Scan on plan_roles (cost=0.00..885.83 rows=44,583 width=32) (actual time=0.011..4.417 rows=44,583 loops=1)

89. 12.563 22.513 ↑ 1.0 48,106 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3180kB
90. 9.950 9.950 ↑ 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..9.950 rows=48,106 loops=1)

91. 11.287 285.907 ↑ 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.662..285.907 rows=30,644 loops=1)

92. 146.301 274.620 ↑ 15.6 30,644 1

Hash Anti Join (cost=3,354.66..515,572.65 rows=476,700 width=143) (actual time=39.660..274.620 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: 1153172
93. 22.521 105.147 ↑ 17.5 31,132 1

Merge Left Join (cost=1,177.86..481,269.67 rows=544,800 width=64) (actual time=16.217..105.147 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: 10924
94. 8.112 62.864 ↑ 17.5 31,132 1

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

95. 11.168 11.168 ↑ 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.015..11.168 rows=21,792 loops=1)

96. 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
97. 5.312 19.762 ↓ 10.2 43,399 1

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

  • Sort Key: sa.staff_membership_id
  • Sort Method: quicksort Memory: 522kB
98. 0.904 14.450 ↑ 1.0 4,221 1

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

99. 0.903 13.546 ↑ 1.0 4,221 1

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

100. 3.189 12.643 ↑ 1.0 4,249 1

Sort (cost=836.52..847.14 rows=4,249 width=65) (actual time=12.342..12.643 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
101. 3.128 9.454 ↑ 1.0 4,249 1

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

102. 2.269 6.326 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=44) (actual time=5.985..6.326 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
103. 1.515 4.057 ↑ 1.0 4,249 1

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

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_2.id)
104. 0.447 0.447 ↑ 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.015..0.447 rows=4,256 loops=1)

105. 1.098 2.095 ↑ 1.0 4,002 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 245kB
106. 0.997 0.997 ↑ 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.997 rows=4,002 loops=1)

107. 11.978 23.172 ↑ 1.0 58,975 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3047kB
108. 11.194 11.194 ↑ 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.014..11.194 rows=59,712 loops=1)

109. 0.004 0.128 ↑ 9.8 12 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
110. 0.124 0.124 ↑ 9.8 12 1

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 342
111. 368.403 641.905 ↓ 4.3 1,160,408 1

Sort (cost=46,830.92..47,512.59 rows=272,669 width=44) (actual time=549.543..641.905 rows=1,160,408 loops=1)

  • Sort Key: ""*SELECT* 1"".project_id, ""*SELECT* 1"".activity_id
  • Sort Method: quicksort Memory: 32838kB
112. 29.676 273.502 ↑ 1.0 263,033 1

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

113. 0.096 16.486 ↓ 9.7 474 1

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

114. 0.169 16.390 ↓ 9.7 474 1

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

  • Merge Cond: (clients.brand_id = brands.id)
115. 0.188 16.176 ↓ 9.7 474 1

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

116. 0.010 1.308 ↓ 5.0 5 1

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

  • Merge Cond: (rate_cards.rateable_id = clients.brand_id)
117. 0.044 0.610 ↑ 28.8 4 1

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

118. 0.033 0.566 ↑ 23.0 5 1

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

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

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

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4739
120. 0.008 0.688 ↑ 1.0 5 1

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

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

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

122. 0.007 0.664 ↑ 1.0 5 1

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

123. 0.642 0.642 ↑ 1.0 5 1

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

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5805
124. 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)
125. 0.010 0.010 ↑ 1.0 1 5

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

  • Index Cond: (id = projects_2.client_id)
126. 14.680 14.680 ↓ 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.696..2.936 rows=95 loops=5)

  • Index Cond: (rate_card_id = rate_cards.id)
127. 0.024 0.045 ↑ 38.3 3 1

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

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

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

129. 12.993 73.119 ↓ 2.6 91,600 1

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

130. 36.726 60.126 ↓ 2.6 91,600 1

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

  • Hash Cond: (rates_1.rate_card_id = rate_cards_1.id)
131. 17.507 17.507 ↑ 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.014..17.507 rows=174,437 loops=1)

132. 0.198 5.893 ↑ 1.1 657 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
133. 0.165 5.695 ↑ 1.1 657 1

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

  • Hash Cond: (clients_1.office_id = offices.id)
134. 0.184 3.595 ↑ 1.0 623 1

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

  • Hash Cond: (projects_3.client_id = clients_1.id)
135. 0.860 2.518 ↑ 1.0 623 1

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

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

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

137. 0.109 0.961 ↑ 1.0 623 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
138. 0.852 0.852 ↑ 1.0 623 1

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

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5187
139. 0.437 0.893 ↑ 1.0 1,849 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 89kB
140. 0.456 0.456 ↑ 1.0 1,849 1

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

141. 0.169 1.935 ↑ 1.1 450 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
142. 0.219 1.766 ↑ 1.1 450 1

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

  • Hash Cond: (rate_cards_1.rateable_id = offices.id)
143. 0.550 1.398 ↑ 1.0 489 1

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

144. 0.193 0.848 ↑ 1.0 489 1

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

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

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

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4365
146. 0.088 0.149 ↑ 1.0 441 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
147. 0.061 0.061 ↑ 1.0 441 1

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

148. 13.496 79.370 ↑ 2.2 94,593 1

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

149. 38.625 65.874 ↑ 2.2 94,593 1

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

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
150. 17.658 17.658 ↑ 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.013..17.658 rows=174,437 loops=1)

151. 0.964 9.591 ↑ 1.0 4,004 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 299kB
152. 1.002 8.627 ↑ 1.0 4,004 1

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

  • Hash Cond: (projects_4.client_id = clients_2.id)
153. 0.984 3.982 ↑ 1.0 3,620 1

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

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

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2189
155. 0.864 2.074 ↑ 1.0 5,812 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 292kB
156. 1.210 1.210 ↑ 1.0 5,812 1

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

157. 0.428 3.643 ↑ 1.0 1,820 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 123kB
158. 0.381 3.215 ↑ 1.0 1,820 1

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

  • Hash Cond: (rate_cards_2.rateable_id = clients_2.id)
159. 1.072 2.348 ↑ 1.0 1,821 1

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

160. 0.620 1.276 ↑ 1.0 1,821 1

Sort (cost=203.29..207.84 rows=1,821 width=29) (actual time=1.162..1.276 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
161. 0.656 0.656 ↑ 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.007..0.656 rows=1,821 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 3033
162. 0.257 0.486 ↑ 1.0 1,849 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
163. 0.229 0.229 ↑ 1.0 1,849 1

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

164. 12.166 74.851 ↓ 2.4 76,366 1

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

165. 37.714 62.685 ↓ 2.4 76,366 1

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

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
166. 18.057 18.057 ↑ 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..18.057 rows=174,437 loops=1)

167. 0.367 6.914 ↓ 2.4 1,591 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
168. 0.901 6.547 ↓ 2.4 1,591 1

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

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

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

170. 0.363 4.990 ↓ 2.4 1,591 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
171. 0.452 4.627 ↓ 2.4 1,591 1

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

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
172. 1.546 3.077 ↑ 1.0 2,429 1

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

173. 0.850 1.531 ↑ 1.0 2,429 1

Sort (cost=241.26..247.33 rows=2,429 width=29) (actual time=1.368..1.531 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
174. 0.681 0.681 ↑ 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.007..0.681 rows=2,429 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2425
175. 0.281 1.098 ↑ 1.0 1,561 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
176. 0.817 0.817 ↑ 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.008..0.817 rows=1,561 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4249
177. 47.184 47.274 ↓ 2,151.2 658,252 1

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

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

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

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
179. 2,770.872 2,771.394 ↓ 13,424.2 40,916,815 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.363..2,771.394 rows=40,916,815 loops=1)

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 335kB
180. 0.522 0.522 ↑ 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.015..0.522 rows=3,048 loops=1)

181. 2,921.744 2,922.288 ↓ 14,570.5 44,410,811 1

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

  • Sort Key: cost_exchange_rates.account_id, cost_exchange_rates.currency
  • Sort Method: quicksort Memory: 335kB
182. 0.544 0.544 ↑ 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.016..0.544 rows=3,048 loops=1)

Planning time : 8.541 ms
Execution time : 66,446.695 ms