explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R1Cx

Settings
# exclusive inclusive rows x rows loops node
1. 2,168.344 149,723.409 ↓ 10.0 694,531 1

GroupAggregate (cost=2,618,637.73..2,624,388.39 rows=69,705 width=184) (actual time=146,657.539..149,723.409 rows=694,531 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. 5,443.003 147,555.065 ↓ 10.0 694,581 1

Sort (cost=2,618,637.73..2,618,811.99 rows=69,705 width=184) (actual time=146,657.500..147,555.065 rows=694,581 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: 97544kB
3. 24,569.433 142,112.062 ↓ 10.0 694,581 1

Merge Left Join (cost=2,605,296.23..2,606,835.32 rows=69,705 width=184) (actual time=67,607.370..142,112.062 rows=694,581 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: 30771485
4. 22,753.592 105,277.127 ↓ 10.0 694,581 1

Merge Left Join (cost=2,605,295.95..2,605,990.64 rows=69,705 width=176) (actual time=67,606.885..105,277.127 rows=694,581 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: 30771485
5. 3,089.250 69,083.282 ↓ 10.0 694,581 1

Sort (cost=2,604,931.92..2,605,106.18 rows=69,705 width=168) (actual time=67,602.474..69,083.282 rows=694,581 loops=1)

  • Sort Key: projects.account_id, "*SELECT* 1".currency
  • Sort Method: external merge Disk: 83440kB
6. 526.837 65,994.032 ↓ 10.0 694,581 1

Merge Left Join (cost=2,581,125.51..2,593,605.51 rows=69,705 width=168) (actual time=63,764.469..65,994.032 rows=694,581 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: 488166
7. 800.151 65,353.613 ↓ 10.0 694,531 1

Merge Left Join (cost=2,581,106.49..2,591,153.79 rows=69,705 width=164) (actual time=63,764.205..65,353.613 rows=694,531 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: 220854
8. 1,497.555 63,311.746 ↓ 10.0 694,531 1

Sort (cost=2,535,474.60..2,535,648.87 rows=69,705 width=136) (actual time=62,913.160..63,311.746 rows=694,531 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: 66128kB
9. 11,838.003 61,814.191 ↓ 10.0 694,531 1

Merge Left Join (cost=2,524,405.51..2,525,100.19 rows=69,705 width=136) (actual time=41,819.890..61,814.191 rows=694,531 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: 31959433
10. 2,495.213 42,630.443 ↓ 10.0 694,531 1

Sort (cost=2,524,041.48..2,524,215.74 rows=69,705 width=644) (actual time=41,815.210..42,630.443 rows=694,531 loops=1)

  • Sort Key: projects.account_id, staff_memberships_1.currency
  • Sort Method: external merge Disk: 62392kB
11. 485.373 40,135.230 ↓ 10.0 694,531 1

Hash Join (cost=2,135,431.59..2,498,421.07 rows=69,705 width=644) (actual time=15,828.622..40,135.230 rows=694,531 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
12. 401.125 39,649.678 ↓ 4.6 957,530 1

Append (cost=2,135,412.92..2,494,839.08 rows=208,456 width=161) (actual time=15,828.412..39,649.678 rows=957,530 loops=1)

13. 13,453.209 38,650.313 ↓ 4.5 943,680 1

Merge Left Join (cost=2,135,412.92..2,297,614.38 rows=208,455 width=161) (actual time=15,828.411..38,650.313 rows=943,680 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: 27436223
14. 1,801.878 16,068.726 ↓ 4.5 943,680 1

Sort (cost=2,126,859.72..2,127,380.86 rows=208,455 width=149) (actual time=15,512.108..16,068.726 rows=943,680 loops=1)

  • Sort Key: "*SELECT* 1_1".project_plan_id
  • Sort Method: external merge Disk: 105920kB
15. 998.369 14,266.848 ↓ 4.5 943,680 1

Merge Left Join (cost=2,086,485.47..2,092,766.87 rows=208,455 width=149) (actual time=12,301.046..14,266.848 rows=943,680 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: 117656
  • 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: 5531
16. 2,890.993 13,003.859 ↓ 1.5 949,211 1

Sort (cost=2,085,705.77..2,087,253.70 rows=619,172 width=125) (actual time=12,260.932..13,003.859 rows=949,211 loops=1)

  • Sort Key: projects.account_id, "*SELECT* 1_1".user_id
  • Sort Method: external merge Disk: 80776kB
17. 690.749 10,112.866 ↓ 1.5 949,211 1

Hash Join (cost=1,878,808.21..1,985,929.99 rows=619,172 width=125) (actual time=4,804.940..10,112.866 rows=949,211 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
18. 544.942 9,415.833 ↓ 2.2 1,354,103 1

Append (cost=1,878,391.64..1,970,808.08 rows=619,172 width=41) (actual time=4,550.569..9,415.833 rows=1,354,103 loops=1)

19. 523.980 7,644.318 ↓ 735,534.0 735,534 1

Subquery Scan on *SELECT* 1_1 (cost=1,878,391.64..1,939,824.12 rows=1 width=101) (actual time=4,550.567..7,644.318 rows=735,534 loops=1)

20. 2,583.933 7,120.338 ↓ 735,534.0 735,534 1

Hash Right Join (cost=1,878,391.64..1,939,824.11 rows=1 width=101) (actual time=4,550.556..7,120.338 rows=735,534 loops=1)

  • Hash Cond: ((staff_memberships.account_id = projects_1.account_id) AND (staff_memberships.user_id = plan_rows.user_id))
  • Join Filter: (generate_series.generate_series = ((generate_series_1.generate_series)::date))
  • Rows Removed by Join Filter: 8590484
21. 18.580 104.712 ↑ 24.2 21,323 1

GroupAggregate (cost=969,958.32..1,022,371.26 rows=515,400 width=21) (actual time=79.663..104.712 rows=21,323 loops=1)

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
22. 19.795 86.132 ↑ 166.7 21,440 1

Sort (cost=969,958.32..978,894.71 rows=3,574,555 width=21) (actual time=79.647..86.132 rows=21,440 loops=1)

  • Sort Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Sort Method: quicksort Memory: 2268kB
23. 12.753 66.337 ↑ 166.7 21,440 1

Nested Loop (cost=131.17..434,264.94 rows=3,574,555 width=21) (actual time=2.608..66.337 rows=21,440 loops=1)

  • 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
24. 12.078 18.880 ↓ 1.0 17,352 1

Hash Join (cost=131.16..814.94 rows=17,338 width=29) (actual time=2.579..18.880 rows=17,352 loops=1)

  • Hash Cond: (vacations.staff_membership_id = staff_memberships.id)
25. 4.267 4.267 ↓ 1.0 17,352 1

Seq Scan on vacations (cost=0.00..445.38 rows=17,338 width=17) (actual time=0.009..4.267 rows=17,352 loops=1)

26. 1.202 2.535 ↓ 1.0 3,701 1

Hash (cost=84.96..84.96 rows=3,696 width=20) (actual time=2.535..2.535 rows=3,701 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 214kB
27. 1.333 1.333 ↓ 1.0 3,701 1

Seq Scan on staff_memberships (cost=0.00..84.96 rows=3,696 width=20) (actual time=0.006..1.333 rows=3,701 loops=1)

28. 34.704 34.704 ↑ 1,000.0 1 17,352

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

29. 370.283 4,431.693 ↓ 735,534.0 735,534 1

Hash (cost=908,433.31..908,433.31 rows=1 width=69) (actual time=4,431.693..4,431.693 rows=735,534 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 2048 (originally 1) Memory Usage: 14565kB
30. 447.697 4,061.410 ↓ 735,534.0 735,534 1

Merge Left Join (cost=897,817.18..908,433.31 rows=1 width=69) (actual time=2,814.589..4,061.410 rows=735,534 loops=1)

  • Merge Cond: ((plan_rows.user_id = non_working_intervals_by_dates.user_id) AND (generate_series.generate_series = non_working_intervals_by_dates.date) AND (projects_1.account_id = non_working_intervals_by_dates.account_id))
  • Filter: (non_working_intervals_by_dates.id IS NULL)
  • Rows Removed by Filter: 5375
31. 2,364.972 3,562.148 ↑ 1.3 740,909 1

Sort (cost=893,590.22..896,059.97 rows=987,900 width=65) (actual time=2,814.583..3,562.148 rows=740,909 loops=1)

  • Sort Key: plan_rows.user_id, generate_series.generate_series, projects_1.account_id
  • Sort Method: external merge Disk: 56664kB
32. 369.058 1,197.176 ↑ 1.3 740,909 1

Nested Loop (cost=1,947.33..754,705.49 rows=987,900 width=65) (actual time=36.333..1,197.176 rows=740,909 loops=1)

33. 29.121 126.154 ↓ 1.0 41,292 1

Hash Left Join (cost=1,947.32..3,901.49 rows=39,516 width=65) (actual time=36.299..126.154 rows=41,292 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
34. 40.805 90.060 ↓ 1.0 41,292 1

Hash Join (cost=1,383.33..2,996.19 rows=39,516 width=57) (actual time=29.312..90.060 rows=41,292 loops=1)

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
35. 20.030 20.030 ↓ 1.0 41,295 1

Seq Scan on plan_items (cost=0.00..1,063.15 rows=41,214 width=45) (actual time=0.004..20.030 rows=41,295 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 194
36. 14.118 29.225 ↓ 1.1 41,074 1

Hash (cost=895.79..895.79 rows=39,003 width=28) (actual time=29.225..29.225 rows=41,074 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2726kB
37. 15.107 15.107 ↓ 1.1 41,074 1

Seq Scan on plan_rows (cost=0.00..895.79 rows=39,003 width=28) (actual time=0.004..15.107 rows=41,074 loops=1)

  • Filter: ((user_id IS NOT NULL) OR (activity_id IS NOT NULL))
  • Rows Removed by Filter: 4
38. 0.850 6.973 ↓ 1.0 2,683 1

Hash (cost=531.00..531.00 rows=2,639 width=12) (actual time=6.973..6.973 rows=2,683 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 148kB
39. 1.514 6.123 ↓ 1.0 2,683 1

Hash Left Join (cost=327.72..531.00 rows=2,639 width=12) (actual time=3.198..6.123 rows=2,683 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
40. 1.431 1.431 ↓ 1.0 2,683 1

Seq Scan on project_plans (cost=0.00..167.00 rows=2,639 width=8) (actual time=0.004..1.431 rows=2,683 loops=1)

  • Filter: active
  • Rows Removed by Filter: 2768
41. 1.494 3.178 ↓ 1.0 5,242 1

Hash (cost=263.43..263.43 rows=5,143 width=8) (actual time=3.178..3.178 rows=5,242 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 269kB
42. 1.684 1.684 ↓ 1.0 5,242 1

Seq Scan on projects projects_1 (cost=0.00..263.43 rows=5,143 width=8) (actual time=0.004..1.684 rows=5,242 loops=1)

43. 701.964 701.964 ↑ 1.4 18 41,292

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.005..0.017 rows=18 loops=41,292)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 7
44. 37.859 51.565 ↓ 1.0 43,798 1

Sort (cost=4,226.92..4,334.28 rows=42,943 width=16) (actual time=37.163..51.565 rows=43,798 loops=1)

  • Sort Key: non_working_intervals_by_dates.user_id, non_working_intervals_by_dates.date, non_working_intervals_by_dates.account_id
  • Sort Method: quicksort Memory: 3379kB
45. 13.706 13.706 ↑ 1.0 42,943 1

Seq Scan on non_working_intervals_by_dates (cost=0.00..922.43 rows=42,943 width=16) (actual time=0.011..13.706 rows=42,943 loops=1)

46. 487.561 1,226.573 ↑ 1.0 618,569 1

Subquery Scan on *SELECT* 2_2 (cost=1,839.94..30,983.97 rows=619,171 width=41) (actual time=31.429..1,226.573 rows=618,569 loops=1)

47. 535.467 739.012 ↑ 1.0 618,569 1

Hash Join (cost=1,839.94..24,792.26 rows=619,171 width=41) (actual time=31.423..739.012 rows=618,569 loops=1)

  • Hash Cond: (time_logs.epic_id = epics.id)
48. 172.195 172.195 ↑ 1.0 618,569 1

Seq Scan on time_logs (cost=0.00..14,438.71 rows=619,171 width=29) (actual time=0.015..172.195 rows=618,569 loops=1)

49. 8.171 31.350 ↑ 1.0 24,890 1

Hash (cost=1,524.01..1,524.01 rows=25,275 width=16) (actual time=31.350..31.350 rows=24,890 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1316kB
50. 14.708 23.179 ↑ 1.0 24,890 1

Hash Left Join (cost=199.99..1,524.01 rows=25,275 width=16) (actual time=2.182..23.179 rows=24,890 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: 11217
51. 6.308 6.308 ↑ 1.0 24,890 1

Seq Scan on epics (cost=0.00..672.75 rows=25,275 width=12) (actual time=0.003..6.308 rows=24,890 loops=1)

52. 0.789 2.163 ↓ 1.0 2,683 1

Hash (cost=167.00..167.00 rows=2,639 width=8) (actual time=2.163..2.163 rows=2,683 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 137kB
53. 1.374 1.374 ↓ 1.0 2,683 1

Seq Scan on project_plans general_epic_plans (cost=0.00..167.00 rows=2,639 width=8) (actual time=0.005..1.374 rows=2,683 loops=1)

  • Filter: active
  • Rows Removed by Filter: 2768
54. 1.602 6.284 ↓ 1.0 5,242 1

Hash (cost=352.28..352.28 rows=5,143 width=13) (actual time=6.284..6.284 rows=5,242 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 295kB
55. 2.719 4.682 ↓ 1.0 5,242 1

Hash Left Join (cost=18.13..352.28 rows=5,143 width=13) (actual time=0.210..4.682 rows=5,242 loops=1)

  • Hash Cond: (projects.account_id = accounts_1.id)
56. 1.764 1.764 ↓ 1.0 5,242 1

Seq Scan on projects (cost=0.00..263.43 rows=5,143 width=8) (actual time=0.004..1.764 rows=5,242 loops=1)

57. 0.091 0.199 ↑ 1.1 299 1

Hash (cost=14.17..14.17 rows=317 width=9) (actual time=0.199..0.199 rows=299 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
58. 0.108 0.108 ↑ 1.1 299 1

Seq Scan on accounts accounts_1 (cost=0.00..14.17 rows=317 width=9) (actual time=0.002..0.108 rows=299 loops=1)

59. 220.019 264.620 ↓ 259.3 966,502 1

Materialize (cost=779.70..863.56 rows=3,727 width=40) (actual time=40.104..264.620 rows=966,502 loops=1)

60. 2.753 44.601 ↓ 1.0 3,761 1

Unique (cost=779.70..816.97 rows=3,727 width=56) (actual time=40.099..44.601 rows=3,761 loops=1)

61. 8.513 41.848 ↓ 1.0 3,772 1

Sort (cost=779.70..789.02 rows=3,727 width=56) (actual time=40.098..41.848 rows=3,772 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: 627kB
62. 9.399 33.335 ↓ 1.0 3,772 1

WindowAgg (cost=474.76..558.62 rows=3,727 width=56) (actual time=22.092..33.335 rows=3,772 loops=1)

63. 7.157 23.936 ↓ 1.0 3,772 1

Sort (cost=474.76..484.08 rows=3,727 width=56) (actual time=22.079..23.936 rows=3,772 loops=1)

  • Sort Key: staff_membership_activity_links.staff_membership_id, staff_membership_activity_links.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 627kB
64. 7.420 16.779 ↓ 1.0 3,772 1

Hash Join (cost=131.16..253.68 rows=3,727 width=56) (actual time=7.411..16.779 rows=3,772 loops=1)

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
65. 1.983 1.983 ↓ 1.0 3,779 1

Seq Scan on staff_membership_activity_links (cost=0.00..71.27 rows=3,727 width=28) (actual time=0.011..1.983 rows=3,779 loops=1)

66. 3.035 7.376 ↓ 1.0 3,701 1

Hash (cost=84.96..84.96 rows=3,696 width=32) (actual time=7.376..7.376 rows=3,701 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 258kB
67. 4.341 4.341 ↓ 1.0 3,701 1

Seq Scan on staff_memberships staff_memberships_1 (cost=0.00..84.96 rows=3,696 width=32) (actual time=0.008..4.341 rows=3,701 loops=1)

68. 8,856.865 9,128.378 ↓ 6,177.4 28,249,033 1

Sort (cost=8,553.20..8,564.63 rows=4,573 width=48) (actual time=316.271..9,128.378 rows=28,249,033 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: external sort Disk: 2232kB
69. 16.921 271.513 ↓ 8.3 38,184 1

Subquery Scan on pa (cost=8,160.86..8,275.19 rows=4,573 width=48) (actual time=210.026..271.513 rows=38,184 loops=1)

70. 83.350 254.592 ↓ 8.3 38,184 1

HashAggregate (cost=8,160.86..8,229.46 rows=4,573 width=48) (actual time=210.024..254.592 rows=38,184 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)
71. 38.480 171.242 ↓ 1.0 38,331 1

WindowAgg (cost=5,970.80..6,827.78 rows=38,088 width=60) (actual time=117.607..171.242 rows=38,331 loops=1)

72. 62.460 132.762 ↓ 1.0 38,331 1

Sort (cost=5,970.80..6,066.02 rows=38,088 width=60) (actual time=117.597..132.762 rows=38,331 loops=1)

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: external merge Disk: 2352kB
73. 30.722 70.302 ↓ 1.0 38,331 1

Hash Join (cost=1,404.28..3,072.87 rows=38,088 width=60) (actual time=29.337..70.302 rows=38,331 loops=1)

  • Hash Cond: (plan_roles.plan_row_uuid = plan_rows_1.uuid)
74. 10.353 10.353 ↓ 1.0 38,333 1

Seq Scan on plan_roles (cost=0.00..1,144.88 rows=38,088 width=32) (actual time=0.011..10.353 rows=38,333 loops=1)

75. 14.713 29.227 ↓ 1.0 41,078 1

Hash (cost=895.79..895.79 rows=40,679 width=28) (actual time=29.227..29.227 rows=41,078 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2784kB
76. 14.514 14.514 ↓ 1.0 41,078 1

Seq Scan on plan_rows plan_rows_1 (cost=0.00..895.79 rows=40,679 width=28) (actual time=0.004..14.514 rows=41,078 loops=1)

77. 14.706 598.240 ↓ 13,850.0 13,850 1

Subquery Scan on *SELECT* 2_1 (cost=1,021.05..195,140.16 rows=1 width=64) (actual time=25.048..598.240 rows=13,850 loops=1)

78. 21.967 583.534 ↓ 13,850.0 13,850 1

Nested Loop (cost=1,021.05..195,140.15 rows=1 width=64) (actual time=25.039..583.534 rows=13,850 loops=1)

79. 11.390 41.187 ↓ 1.1 10,620 1

Hash Join (cost=1,020.47..1,668.56 rows=9,432 width=60) (actual time=24.961..41.187 rows=10,620 loops=1)

  • Hash Cond: (vacations_1.staff_membership_id = staff_membership_activity_links_1.staff_membership_id)
80. 4.865 4.865 ↓ 1.0 17,352 1

Seq Scan on vacations vacations_1 (cost=0.00..445.38 rows=17,338 width=44) (actual time=0.008..4.865 rows=17,352 loops=1)

81. 0.562 24.932 ↑ 1.2 1,582 1

Hash (cost=997.17..997.17 rows=1,864 width=16) (actual time=24.932..24.932 rows=1,582 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 93kB
82. 1.533 24.370 ↑ 1.2 1,582 1

Hash Join (cost=911.70..997.17 rows=1,864 width=16) (actual time=22.043..24.370 rows=1,582 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
83. 0.810 0.810 ↓ 1.0 3,779 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_1 (cost=0.00..71.27 rows=3,727 width=20) (actual time=0.005..0.810 rows=3,779 loops=1)

84. 0.449 22.027 ↓ 68.8 1,582 1

Hash (cost=911.41..911.41 rows=23 width=4) (actual time=22.027..22.027 rows=1,582 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 72kB
85. 1.097 21.578 ↓ 68.8 1,582 1

HashAggregate (cost=911.18..911.41 rows=23 width=4) (actual time=21.136..21.578 rows=1,582 loops=1)

  • Group Key: staff_activities_with_dates.link_id
86. 3.344 20.481 ↓ 3.8 1,582 1

Subquery Scan on staff_activities_with_dates (cost=779.70..910.14 rows=414 width=4) (actual time=14.458..20.481 rows=1,582 loops=1)

  • Filter: ((('now'::cstring)::date >= staff_activities_with_dates.start_date) AND (('now'::cstring)::date <= staff_activities_with_dates.end_date))
  • Rows Removed by Filter: 2179
87. 1.754 17.137 ↓ 1.0 3,761 1

Unique (cost=779.70..816.97 rows=3,727 width=28) (actual time=14.442..17.137 rows=3,761 loops=1)

88. 3.428 15.383 ↓ 1.0 3,772 1

Sort (cost=779.70..789.02 rows=3,727 width=28) (actual time=14.440..15.383 rows=3,772 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: 391kB
89. 3.612 11.955 ↓ 1.0 3,772 1

WindowAgg (cost=474.76..558.62 rows=3,727 width=28) (actual time=7.439..11.955 rows=3,772 loops=1)

90. 2.655 8.343 ↓ 1.0 3,772 1

Sort (cost=474.76..484.08 rows=3,727 width=28) (actual time=7.431..8.343 rows=3,772 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: 391kB
91. 2.324 5.688 ↓ 1.0 3,772 1

Hash Join (cost=131.16..253.68 rows=3,727 width=28) (actual time=2.455..5.688 rows=3,772 loops=1)

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_3.id)
92. 0.927 0.927 ↓ 1.0 3,779 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_2 (cost=0.00..71.27 rows=3,727 width=12) (actual time=0.003..0.927 rows=3,779 loops=1)

93. 1.133 2.437 ↓ 1.0 3,701 1

Hash (cost=84.96..84.96 rows=3,696 width=20) (actual time=2.437..2.437 rows=3,701 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 214kB
94. 1.304 1.304 ↓ 1.0 3,701 1

Seq Scan on staff_memberships staff_memberships_3 (cost=0.00..84.96 rows=3,696 width=20) (actual time=0.005..1.304 rows=3,701 loops=1)

95. 181.351 520.380 ↑ 1.0 1 10,620

Nested Loop Left Join (cost=0.58..20.50 rows=1 width=24) (actual time=0.041..0.049 rows=1 loops=10,620)

  • Join Filter: (generate_series_2.generate_series = non_working_intervals_by_dates_1.date)
  • Rows Removed by Join Filter: 44
  • Filter: (non_working_intervals_by_dates_1.id IS NULL)
  • Rows Removed by Filter: 0
96. 21.240 74.340 ↑ 1.0 1 10,620

Nested Loop (cost=0.29..19.43 rows=1 width=24) (actual time=0.005..0.007 rows=1 loops=10,620)

  • Join Filter: (vacations_1.staff_membership_id = staff_memberships_2.id)
97. 21.240 21.240 ↑ 1.0 1 10,620

Index Scan using memberships_pkey on staff_memberships staff_memberships_2 (cost=0.28..0.36 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=10,620)

  • Index Cond: (id = staff_membership_activity_links_1.staff_membership_id)
98. 31.860 31.860 ↑ 25.0 1 10,620

Function Scan on generate_series generate_series_2 (cost=0.01..18.76 rows=25 width=8) (actual time=0.002..0.003 rows=1 loops=10,620)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 0
99. 264.689 264.689 ↓ 16.5 33 13,931

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates non_working_intervals_by_dates_1 (cost=0.29..1.04 rows=2 width=16) (actual time=0.002..0.019 rows=33 loops=13,931)

  • Index Cond: ((staff_memberships_2.account_id = account_id) AND (user_id = staff_memberships_2.user_id))
100. 0.005 0.179 ↑ 5.6 19 1

Hash (cost=17.34..17.34 rows=106 width=4) (actual time=0.179..0.179 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
101. 0.174 0.174 ↑ 5.6 19 1

Seq Scan on accounts (cost=0.00..17.34 rows=106 width=4) (actual time=0.042..0.174 rows=19 loops=1)

  • Filter: ((suspended_at)::date >= ('now'::cstring)::date)
  • Rows Removed by Filter: 280
102. 7,344.231 7,345.745 ↓ 7,417.2 32,517,216 1

Sort (cost=364.03..374.99 rows=4,384 width=24) (actual time=4.332..7,345.745 rows=32,517,216 loops=1)

  • Sort Key: cost_exchange_rates.account_id, cost_exchange_rates.currency
  • Sort Method: quicksort Memory: 535kB
103. 1.514 1.514 ↑ 1.0 4,384 1

Seq Scan on exchange_rates_by_months cost_exchange_rates (cost=0.00..98.84 rows=4,384 width=24) (actual time=0.007..1.514 rows=4,384 loops=1)

104. 260.891 1,241.716 ↓ 4.4 999,827 1

Materialize (cost=45,631.88..46,773.66 rows=228,356 width=44) (actual time=851.037..1,241.716 rows=999,827 loops=1)

105. 443.233 980.825 ↑ 1.0 223,013 1

Sort (cost=45,631.88..46,202.77 rows=228,356 width=44) (actual time=851.034..980.825 rows=223,013 loops=1)

  • Sort Key: "*SELECT* 1".project_id, "*SELECT* 1".activity_id
  • Sort Method: external merge Disk: 12584kB
106. 85.620 537.592 ↑ 1.0 223,021 1

Append (cost=267.17..18,279.14 rows=228,356 width=44) (actual time=2.346..537.592 rows=223,021 loops=1)

107. 0.052 5.726 ↓ 47.5 95 1

Subquery Scan on *SELECT* 1 (cost=267.17..346.85 rows=2 width=44) (actual time=2.344..5.726 rows=95 loops=1)

108. 0.076 5.674 ↓ 47.5 95 1

Merge Join (cost=267.17..346.83 rows=2 width=44) (actual time=2.344..5.674 rows=95 loops=1)

  • Merge Cond: (clients.brand_id = brands.id)
109. 0.072 5.516 ↓ 2.1 95 1

Nested Loop (cost=264.26..1,455.01 rows=45 width=52) (actual time=2.274..5.516 rows=95 loops=1)

110. 0.005 1.542 ↑ 1.0 1 1

Merge Join (cost=263.84..264.07 rows=1 width=32) (actual time=1.536..1.542 rows=1 loops=1)

  • Merge Cond: (rate_cards.rateable_id = clients.brand_id)
111. 0.015 0.662 ↑ 32.3 3 1

WindowAgg (cost=107.21..109.40 rows=97 width=29) (actual time=0.653..0.662 rows=3 loops=1)

112. 0.052 0.647 ↑ 32.3 3 1

Sort (cost=107.21..107.46 rows=97 width=29) (actual time=0.646..0.647 rows=3 loops=1)

  • Sort Key: rate_cards.rateable_id, rate_cards.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 32kB
113. 0.595 0.595 ↓ 1.0 99 1

Seq Scan on rate_cards (cost=0.00..104.01 rows=97 width=29) (actual time=0.145..0.595 rows=99 loops=1)

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4241
114. 0.004 0.875 ↑ 1.0 1 1

Sort (cost=156.63..156.63 rows=1 width=8) (actual time=0.875..0.875 rows=1 loops=1)

  • Sort Key: clients.brand_id
  • Sort Method: quicksort Memory: 25kB
115. 0.004 0.871 ↑ 1.0 1 1

Nested Loop (cost=0.56..156.62 rows=1 width=8) (actual time=0.480..0.871 rows=1 loops=1)

116. 0.007 0.852 ↑ 1.0 1 1

Nested Loop (cost=0.28..156.29 rows=1 width=8) (actual time=0.462..0.852 rows=1 loops=1)

117. 0.833 0.833 ↑ 1.0 1 1

Seq Scan on pricing_models (cost=0.00..147.98 rows=1 width=4) (actual time=0.444..0.833 rows=1 loops=1)

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5239
118. 0.012 0.012 ↑ 1.0 1 1

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

  • Index Cond: (id = pricing_models.project_id)
119. 0.015 0.015 ↑ 1.0 1 1

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

  • Index Cond: (id = projects_2.client_id)
120. 3.902 3.902 ↓ 2.1 95 1

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates (cost=0.42..1,190.49 rows=45 width=28) (actual time=0.734..3.902 rows=95 loops=1)

  • Index Cond: (rate_card_id = rate_cards.id)
121. 0.056 0.082 ↓ 1.9 97 1

Sort (cost=2.91..3.04 rows=50 width=4) (actual time=0.062..0.082 rows=97 loops=1)

  • Sort Key: brands.id
  • Sort Method: quicksort Memory: 29kB
122. 0.026 0.026 ↓ 2.0 99 1

Seq Scan on brands (cost=0.00..1.50 rows=50 width=4) (actual time=0.005..0.026 rows=99 loops=1)

123. 31.211 142.862 ↓ 2.7 76,116 1

Subquery Scan on *SELECT* 2 (cost=685.79..4,839.52 rows=28,141 width=44) (actual time=23.028..142.862 rows=76,116 loops=1)

124. 68.769 111.651 ↓ 2.7 76,116 1

Hash Join (cost=685.79..4,558.11 rows=28,141 width=44) (actual time=23.028..111.651 rows=76,116 loops=1)

  • Hash Cond: (rates_1.rate_card_id = rate_cards_with_dates.id)
125. 34.574 34.574 ↑ 1.0 148,394 1

Seq Scan on rates rates_1 (cost=0.00..3,034.02 rows=148,502 width=28) (actual time=0.004..34.574 rows=148,394 loops=1)

126. 0.199 8.308 ↑ 1.1 548 1

Hash (cost=677.94..677.94 rows=628 width=24) (actual time=8.308..8.308 rows=548 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
127. 0.303 8.109 ↑ 1.1 548 1

Hash Join (cost=368.16..677.94 rows=628 width=24) (actual time=3.921..8.109 rows=548 loops=1)

  • Hash Cond: (clients_1.office_id = rate_cards_with_dates.rateable_id)
128. 0.270 6.209 ↓ 1.0 518 1

Hash Join (cost=226.95..528.56 rows=504 width=12) (actual time=2.314..6.209 rows=518 loops=1)

  • Hash Cond: (clients_1.office_id = offices.id)
129. 0.286 5.721 ↓ 1.0 518 1

Hash Join (cost=213.91..508.60 rows=504 width=8) (actual time=2.088..5.721 rows=518 loops=1)

  • Hash Cond: (projects_3.client_id = clients_1.id)
130. 1.428 4.374 ↓ 1.0 518 1

Hash Join (cost=154.34..442.09 rows=504 width=8) (actual time=1.019..4.374 rows=518 loops=1)

  • Hash Cond: (projects_3.id = pricing_models_1.project_id)
131. 1.939 1.939 ↓ 1.0 5,242 1

Seq Scan on projects projects_3 (cost=0.00..263.43 rows=5,143 width=8) (actual time=0.004..1.939 rows=5,242 loops=1)

132. 0.157 1.007 ↓ 1.0 518 1

Hash (cost=147.98..147.98 rows=509 width=4) (actual time=1.007..1.007 rows=518 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
133. 0.850 0.850 ↓ 1.0 518 1

Seq Scan on pricing_models pricing_models_1 (cost=0.00..147.98 rows=509 width=4) (actual time=0.014..0.850 rows=518 loops=1)

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 4722
134. 0.477 1.061 ↓ 1.0 1,690 1

Hash (cost=38.70..38.70 rows=1,670 width=8) (actual time=1.061..1.061 rows=1,690 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 83kB
135. 0.584 0.584 ↓ 1.0 1,690 1

Seq Scan on clients clients_1 (cost=0.00..38.70 rows=1,670 width=8) (actual time=0.005..0.584 rows=1,690 loops=1)

136. 0.099 0.218 ↓ 1.1 375 1

Hash (cost=8.57..8.57 rows=357 width=4) (actual time=0.218..0.218 rows=375 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
137. 0.119 0.119 ↓ 1.1 375 1

Seq Scan on offices (cost=0.00..8.57 rows=357 width=4) (actual time=0.006..0.119 rows=375 loops=1)

138. 0.139 1.597 ↑ 1.0 420 1

Hash (cost=135.96..135.96 rows=420 width=24) (actual time=1.597..1.597 rows=420 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
139. 0.198 1.458 ↑ 1.0 420 1

Subquery Scan on rate_cards_with_dates (cost=122.31..135.96 rows=420 width=24) (actual time=0.798..1.458 rows=420 loops=1)

140. 0.367 1.260 ↑ 1.0 420 1

WindowAgg (cost=122.31..131.76 rows=420 width=29) (actual time=0.796..1.260 rows=420 loops=1)

141. 0.269 0.893 ↑ 1.0 420 1

Sort (cost=122.31..123.36 rows=420 width=29) (actual time=0.793..0.893 rows=420 loops=1)

  • Sort Key: rate_cards_1.rateable_id, rate_cards_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 57kB
142. 0.624 0.624 ↑ 1.0 420 1

Seq Scan on rate_cards rate_cards_1 (cost=0.00..104.01 rows=420 width=29) (actual time=0.111..0.624 rows=420 loops=1)

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 3920
143. 33.744 157.071 ↑ 2.2 79,513 1

Subquery Scan on *SELECT* 3 (cost=944.03..8,195.16 rows=173,730 width=44) (actual time=15.966..157.071 rows=79,513 loops=1)

144. 73.331 123.327 ↑ 2.2 79,513 1

Hash Join (cost=944.03..6,457.86 rows=173,730 width=44) (actual time=15.966..123.327 rows=79,513 loops=1)

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
145. 34.080 34.080 ↑ 1.0 148,394 1

Seq Scan on rates rates_2 (cost=0.00..3,034.02 rows=148,502 width=28) (actual time=0.005..34.080 rows=148,394 loops=1)

146. 1.208 15.916 ↑ 1.1 3,556 1

Hash (cost=895.56..895.56 rows=3,877 width=24) (actual time=15.916..15.916 rows=3,556 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 241kB
147. 1.899 14.708 ↑ 1.1 3,556 1

Hash Join (cost=536.93..895.56 rows=3,877 width=24) (actual time=8.550..14.708 rows=3,556 loops=1)

  • Hash Cond: (projects_4.client_id = clients_2.id)
148. 2.292 6.842 ↓ 1.0 3,273 1

Hash Join (cost=188.59..503.45 rows=3,215 width=8) (actual time=2.574..6.842 rows=3,273 loops=1)

  • Hash Cond: (projects_4.id = pricing_models_2.project_id)
149. 1.992 1.992 ↓ 1.0 5,242 1

Seq Scan on projects projects_4 (cost=0.00..263.43 rows=5,143 width=8) (actual time=0.005..1.992 rows=5,242 loops=1)

150. 0.908 2.558 ↓ 1.0 3,273 1

Hash (cost=147.98..147.98 rows=3,249 width=4) (actual time=2.558..2.558 rows=3,273 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 148kB
151. 1.650 1.650 ↓ 1.0 3,274 1

Seq Scan on pricing_models pricing_models_2 (cost=0.00..147.98 rows=3,249 width=4) (actual time=0.011..1.650 rows=3,274 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 1966
152. 0.533 5.967 ↓ 1.0 1,652 1

Hash (cost=327.76..327.76 rows=1,647 width=28) (actual time=5.967..5.967 rows=1,652 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 113kB
153. 0.883 5.434 ↓ 1.0 1,652 1

Hash Join (cost=251.58..327.76 rows=1,647 width=28) (actual time=2.686..5.434 rows=1,652 loops=1)

  • Hash Cond: (rate_cards_2.rateable_id = clients_2.id)
154. 1.515 3.604 ↓ 1.0 1,653 1

WindowAgg (cost=192.01..229.07 rows=1,647 width=29) (actual time=1.730..3.604 rows=1,653 loops=1)

155. 1.131 2.089 ↓ 1.0 1,653 1

Sort (cost=192.01..196.13 rows=1,647 width=29) (actual time=1.723..2.089 rows=1,653 loops=1)

  • Sort Key: rate_cards_2.rateable_id, rate_cards_2.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 178kB
156. 0.958 0.958 ↓ 1.0 1,653 1

Seq Scan on rate_cards rate_cards_2 (cost=0.00..104.01 rows=1,647 width=29) (actual time=0.006..0.958 rows=1,653 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 2687
157. 0.472 0.947 ↓ 1.0 1,690 1

Hash (cost=38.70..38.70 rows=1,670 width=4) (actual time=0.947..0.947 rows=1,690 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 76kB
158. 0.475 0.475 ↓ 1.0 1,690 1

Seq Scan on clients clients_2 (cost=0.00..38.70 rows=1,670 width=4) (actual time=0.006..0.475 rows=1,690 loops=1)

159. 28.286 146.313 ↓ 2.5 67,297 1

Subquery Scan on *SELECT* 4 (cost=777.05..4,897.62 rows=26,483 width=44) (actual time=11.502..146.313 rows=67,297 loops=1)

160. 72.123 118.027 ↓ 2.5 67,297 1

Hash Join (cost=777.05..4,632.79 rows=26,483 width=44) (actual time=11.502..118.027 rows=67,297 loops=1)

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
161. 34.429 34.429 ↑ 1.0 148,394 1

Seq Scan on rates rates_3 (cost=0.00..3,034.02 rows=148,502 width=28) (actual time=0.005..34.429 rows=148,394 loops=1)

162. 0.525 11.475 ↓ 2.5 1,471 1

Hash (cost=769.67..769.67 rows=591 width=24) (actual time=11.475..11.475 rows=1,471 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 103kB
163. 1.768 10.950 ↓ 2.5 1,471 1

Hash Join (cost=481.04..769.67 rows=591 width=24) (actual time=7.619..10.950 rows=1,471 loops=1)

  • Hash Cond: (projects_5.id = pricing_models_3.project_id)
164. 1.581 1.581 ↓ 1.0 5,242 1

Seq Scan on projects projects_5 (cost=0.00..263.43 rows=5,143 width=4) (actual time=0.004..1.581 rows=5,242 loops=1)

165. 0.486 7.601 ↓ 2.5 1,471 1

Hash (cost=473.58..473.58 rows=597 width=24) (actual time=7.601..7.601 rows=1,471 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 103kB
166. 0.995 7.115 ↓ 2.5 1,471 1

Hash Join (cost=389.42..473.58 rows=597 width=24) (actual time=3.690..7.115 rows=1,471 loops=1)

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
167. 1.935 4.514 ↓ 1.0 2,168 1

WindowAgg (cost=223.45..271.99 rows=2,157 width=29) (actual time=2.069..4.514 rows=2,168 loops=1)

168. 1.525 2.579 ↓ 1.0 2,168 1

Sort (cost=223.45..228.85 rows=2,157 width=29) (actual time=2.063..2.579 rows=2,168 loops=1)

  • Sort Key: rate_cards_3.rateable_id, rate_cards_3.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 266kB
169. 1.054 1.054 ↓ 1.0 2,168 1

Seq Scan on rate_cards rate_cards_3 (cost=0.00..104.01 rows=2,157 width=29) (actual time=0.006..1.054 rows=2,168 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2172
170. 0.395 1.606 ↓ 1.0 1,447 1

Hash (cost=147.98..147.98 rows=1,439 width=8) (actual time=1.606..1.606 rows=1,447 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 73kB
171. 1.211 1.211 ↓ 1.0 1,447 1

Seq Scan on pricing_models pricing_models_3 (cost=0.00..147.98 rows=1,439 width=8) (actual time=0.011..1.211 rows=1,447 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 3793
172. 113.448 113.582 ↓ 2,292.0 495,078 1

Sort (cost=19.03..19.57 rows=216 width=27) (actual time=0.261..113.582 rows=495,078 loops=1)

  • Sort Key: discounts.project_id
  • Sort Method: quicksort Memory: 45kB
173. 0.134 0.134 ↓ 1.2 263 1

Seq Scan on discounts (cost=0.00..10.65 rows=216 width=27) (actual time=0.022..0.134 rows=263 loops=1)

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
174. 13,438.744 13,440.253 ↓ 7,138.7 31,296,141 1

Sort (cost=364.03..374.99 rows=4,384 width=24) (actual time=4.198..13,440.253 rows=31,296,141 loops=1)

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 535kB
175. 1.509 1.509 ↑ 1.0 4,384 1

Seq Scan on exchange_rates_by_months (cost=0.00..98.84 rows=4,384 width=24) (actual time=0.011..1.509 rows=4,384 loops=1)

176. 12,261.526 12,265.502 ↓ 7,138.7 31,296,141 1

Materialize (cost=0.28..335.22 rows=4,384 width=24) (actual time=0.015..12,265.502 rows=31,296,141 loops=1)

177. 3.976 3.976 ↑ 1.0 4,384 1

Index Scan using exchange_rates_by_months_idx on exchange_rates_by_months original_exchange_rates (cost=0.28..324.26 rows=4,384 width=24) (actual time=0.013..3.976 rows=4,384 loops=1)

Planning time : 9.161 ms
Execution time : 150,160.809 ms