explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a3u2

Settings
# exclusive inclusive rows x rows loops node
1. 661.517 59,078.576 ↓ 9.5 674,721 1

GroupAggregate (cost=2,666,643.27..2,672,498.87 rows=70,977 width=184) (actual time=58,128.073..59,078.576 rows=674,721 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. 2,258.376 58,417.059 ↓ 9.5 674,734 1

Sort (cost=2,666,643.27..2,666,820.71 rows=70,977 width=184) (actual time=58,128.052..58,417.059 rows=674,734 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: 94688kB
3. 5,663.746 56,158.683 ↓ 9.5 674,734 1

Merge Left Join (cost=2,652,974.48..2,654,613.77 rows=70,977 width=184) (actual time=40,830.470..56,158.683 rows=674,734 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: 30413421
4. 5,242.261 48,980.761 ↓ 9.5 674,734 1

Merge Left Join (cost=2,652,974.19..2,653,691.76 rows=70,977 width=176) (actual time=40,828.793..48,980.761 rows=674,734 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: 30413421
5. 2,641.750 41,572.186 ↓ 9.5 674,734 1

Sort (cost=2,652,587.22..2,652,764.66 rows=70,977 width=168) (actual time=40,821.393..41,572.186 rows=674,734 loops=1)

  • Sort Key: projects.account_id, "*SELECT* 1".currency
  • Sort Method: external merge Disk: 81000kB
6. 181.741 38,930.436 ↓ 9.5 674,734 1

Merge Left Join (cost=2,628,596.56..2,641,044.23 rows=70,977 width=168) (actual time=37,924.015..38,930.436 rows=674,734 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: 461555
7. 376.349 38,717.536 ↓ 9.5 674,721 1

Merge Left Join (cost=2,628,580.93..2,638,615.37 rows=70,977 width=164) (actual time=37,923.855..38,717.536 rows=674,721 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: 220620
8. 1,390.556 37,448.473 ↓ 9.5 674,721 1

Sort (cost=2,584,001.82..2,584,179.27 rows=70,977 width=136) (actual time=37,172.865..37,448.473 rows=674,721 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: 64272kB
9. 682.170 36,057.917 ↓ 9.5 674,721 1

Nested Loop Left Join (cost=2,185,902.10..2,573,428.33 rows=70,977 width=136) (actual time=11,807.020..36,057.917 rows=674,721 loops=1)

10. 211.725 23,905.490 ↓ 9.5 674,721 1

Hash Join (cost=2,185,901.82..2,550,788.78 rows=70,977 width=644) (actual time=11,806.114..23,905.490 rows=674,721 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
11. 90.031 23,692.660 ↓ 4.5 946,316 1

Append (cost=2,185,885.59..2,547,144.76 rows=212,220 width=160) (actual time=11,804.985..23,692.660 rows=946,316 loops=1)

12. 7,158.575 23,160.195 ↓ 4.4 932,423 1

Merge Left Join (cost=2,185,885.59..2,350,503.77 rows=211,120 width=161) (actual time=11,804.984..23,160.195 rows=932,423 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: 27229021
13. 1,599.198 12,008.894 ↓ 4.4 932,423 1

Sort (cost=2,177,725.74..2,178,253.54 rows=211,120 width=149) (actual time=11,551.317..12,008.894 rows=932,423 loops=1)

  • Sort Key: "*SELECT* 1_1".project_plan_id
  • Sort Method: external merge Disk: 104584kB
14. 443.607 10,409.696 ↓ 4.4 932,423 1

Merge Left Join (cost=2,136,807.00..2,143,178.60 rows=211,120 width=149) (actual time=9,340.329..10,409.696 rows=932,423 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: 117125
  • 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: 5422
15. 2,306.324 9,898.567 ↓ 1.5 937,845 1

Sort (cost=2,136,024.63..2,137,592.35 rows=627,090 width=125) (actual time=9,328.888..9,898.567 rows=937,845 loops=1)

  • Sort Key: projects.account_id, "*SELECT* 1_1".user_id
  • Sort Method: external merge Disk: 79704kB
16. 268.361 7,592.243 ↓ 1.5 937,845 1

Hash Join (cost=1,871,126.00..2,034,915.15 rows=627,090 width=125) (actual time=3,828.269..7,592.243 rows=937,845 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
17. 105.216 7,316.302 ↓ 2.1 1,344,045 1

Append (cost=1,870,832.61..2,019,728.37 rows=627,090 width=41) (actual time=3,684.769..7,316.302 rows=1,344,045 loops=1)

18. 228.238 6,185.833 ↓ 147.6 726,784 1

Subquery Scan on *SELECT* 1_1 (cost=1,870,832.61..1,988,993.77 rows=4,923 width=101) (actual time=3,684.767..6,185.833 rows=726,784 loops=1)

19. 2,334.931 5,957.595 ↓ 147.6 726,784 1

Hash Right Join (cost=1,870,832.61..1,988,944.54 rows=4,923 width=101) (actual time=3,684.761..5,957.595 rows=726,784 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: 8448763
20. 10.849 106.380 ↑ 24.6 20,979 1

GroupAggregate (cost=965,850.96..1,018,094.03 rows=515,800 width=21) (actual time=93.219..106.380 rows=20,979 loops=1)

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
21. 12.298 95.531 ↑ 168.8 21,095 1

Sort (cost=965,850.96..974,752.17 rows=3,560,486 width=21) (actual time=93.197..95.531 rows=21,095 loops=1)

  • Sort Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
  • Sort Method: quicksort Memory: 2251kB
22. 12.443 83.233 ↑ 168.8 21,095 1

Nested Loop (cost=127.21..432,364.23 rows=3,560,486 width=21) (actual time=4.137..83.233 rows=21,095 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
23. 6.726 53.526 ↑ 1.0 17,264 1

Hash Join (cost=127.21..764.23 rows=17,264 width=29) (actual time=4.119..53.526 rows=17,264 loops=1)

  • Hash Cond: (vacations.staff_membership_id = staff_memberships.id)
24. 43.349 43.349 ↑ 1.0 17,264 1

Seq Scan on vacations (cost=0.00..399.64 rows=17,264 width=17) (actual time=0.630..43.349 rows=17,264 loops=1)

25. 0.702 3.451 ↑ 1.0 3,698 1

Hash (cost=80.98..80.98 rows=3,698 width=20) (actual time=3.451..3.451 rows=3,698 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 214kB
26. 2.749 2.749 ↑ 1.0 3,698 1

Seq Scan on staff_memberships (cost=0.00..80.98 rows=3,698 width=20) (actual time=0.025..2.749 rows=3,698 loops=1)

27. 17.264 17.264 ↑ 1,000.0 1 17,264

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=1 loops=17,264)

28. 648.687 3,516.284 ↓ 147.6 726,784 1

Hash (cost=904,907.81..904,907.81 rows=4,923 width=69) (actual time=3,516.284..3,516.284 rows=726,784 loops=1)

  • Buckets: 65536 (originally 8192) Batches: 2048 (originally 1) Memory Usage: 14593kB
29. 189.820 2,867.597 ↓ 147.6 726,784 1

Merge Left Join (cost=894,631.61..904,907.81 rows=4,923 width=69) (actual time=1,829.703..2,867.597 rows=726,784 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))
  • Filter: (non_working_intervals_by_dates.id IS NULL)
  • Rows Removed by Filter: 5209
30. 1,828.169 2,582.685 ↑ 1.3 731,993 1

Sort (cost=890,467.43..892,929.12 rows=984,675 width=65) (actual time=1,829.699..2,582.685 rows=731,993 loops=1)

  • Sort Key: generate_series.generate_series, projects_1.account_id, plan_rows.user_id
  • Sort Method: external merge Disk: 55896kB
31. 87.527 754.516 ↑ 1.3 731,993 1

Nested Loop (cost=1,786.53..752,056.54 rows=984,675 width=65) (actual time=57.983..754.516 rows=731,993 loops=1)

32. 12.769 173.969 ↓ 1.0 41,085 1

Hash Left Join (cost=1,786.52..3,703.53 rows=39,387 width=65) (actual time=57.320..173.969 rows=41,085 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
33. 22.951 150.385 ↓ 1.0 41,085 1

Hash Join (cost=1,361.61..2,937.72 rows=39,387 width=57) (actual time=46.379..150.385 rows=41,085 loops=1)

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows.uuid)
34. 82.179 82.179 ↑ 1.0 41,088 1

Seq Scan on plan_items (cost=0.00..1,028.03 rows=41,125 width=45) (actual time=0.732..82.179 rows=41,088 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 194
35. 8.972 45.255 ↓ 1.0 40,886 1

Hash (cost=871.98..871.98 rows=39,170 width=28) (actual time=45.255..45.255 rows=40,886 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2715kB
36. 36.283 36.283 ↓ 1.0 40,886 1

Seq Scan on plan_rows (cost=0.00..871.98 rows=39,170 width=28) (actual time=1.036..36.283 rows=40,886 loops=1)

  • Filter: ((user_id IS NOT NULL) OR (activity_id IS NOT NULL))
  • Rows Removed by Filter: 4
37. 0.522 10.815 ↓ 1.0 2,666 1

Hash (cost=391.63..391.63 rows=2,663 width=12) (actual time=10.815..10.815 rows=2,666 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 147kB
38. 1.025 10.293 ↓ 1.0 2,666 1

Hash Left Join (cost=205.72..391.63 rows=2,663 width=12) (actual time=2.154..10.293 rows=2,666 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
39. 7.303 7.303 ↓ 1.0 2,666 1

Seq Scan on project_plans (cost=0.00..149.29 rows=2,663 width=8) (actual time=0.057..7.303 rows=2,666 loops=1)

  • Filter: active
  • Rows Removed by Filter: 2766
40. 1.037 1.965 ↑ 1.0 5,232 1

Hash (cost=140.32..140.32 rows=5,232 width=8) (actual time=1.965..1.965 rows=5,232 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 269kB
41. 0.928 0.928 ↑ 1.0 5,232 1

Seq Scan on projects projects_1 (cost=0.00..140.32 rows=5,232 width=8) (actual time=0.016..0.928 rows=5,232 loops=1)

42. 493.020 493.020 ↑ 1.4 18 41,085

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.004..0.012 rows=18 loops=41,085)

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

Sort (cost=4,164.17..4,270.02 rows=42,340 width=16) (actual time=91.467..95.092 rows=43,815 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: 3351kB
44. 70.342 70.342 ↑ 1.0 42,340 1

Seq Scan on non_working_intervals_by_dates (cost=0.00..910.40 rows=42,340 width=16) (actual time=1.370..70.342 rows=42,340 loops=1)

45. 213.290 1,025.253 ↑ 1.0 617,261 1

Subquery Scan on *SELECT* 2_2 (cost=1,712.47..30,734.60 rows=622,167 width=41) (actual time=65.349..1,025.253 rows=617,261 loops=1)

46. 274.531 811.963 ↑ 1.0 617,261 1

Hash Join (cost=1,712.47..24,512.93 rows=622,167 width=41) (actual time=65.341..811.963 rows=617,261 loops=1)

  • Hash Cond: (time_logs.epic_id = epics.id)
47. 472.840 472.840 ↑ 1.0 617,261 1

Seq Scan on time_logs (cost=0.00..14,245.67 rows=622,167 width=29) (actual time=0.572..472.840 rows=617,261 loops=1)

48. 5.594 64.592 ↓ 1.0 24,788 1

Hash (cost=1,402.63..1,402.63 rows=24,787 width=16) (actual time=64.592..64.592 rows=24,788 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1312kB
49. 7.456 58.998 ↓ 1.0 24,788 1

Hash Left Join (cost=182.58..1,402.63 rows=24,787 width=16) (actual time=1.822..58.998 rows=24,788 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: 11154
50. 50.488 50.488 ↓ 1.0 24,788 1

Seq Scan on epics (cost=0.00..580.87 rows=24,787 width=12) (actual time=0.748..50.488 rows=24,788 loops=1)

51. 0.294 1.054 ↓ 1.0 2,666 1

Hash (cost=149.29..149.29 rows=2,663 width=8) (actual time=1.054..1.054 rows=2,666 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 137kB
52. 0.760 0.760 ↓ 1.0 2,666 1

Seq Scan on project_plans general_epic_plans (cost=0.00..149.29 rows=2,663 width=8) (actual time=0.007..0.760 rows=2,666 loops=1)

  • Filter: active
  • Rows Removed by Filter: 2766
53. 0.984 7.580 ↑ 1.0 5,232 1

Hash (cost=227.99..227.99 rows=5,232 width=13) (actual time=7.580..7.580 rows=5,232 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 294kB
54. 1.020 6.596 ↑ 1.0 5,232 1

Hash Left Join (cost=15.73..227.99 rows=5,232 width=13) (actual time=0.586..6.596 rows=5,232 loops=1)

  • Hash Cond: (projects.account_id = accounts_1.id)
55. 5.499 5.499 ↑ 1.0 5,232 1

Seq Scan on projects (cost=0.00..140.32 rows=5,232 width=8) (actual time=0.501..5.499 rows=5,232 loops=1)

56. 0.031 0.077 ↑ 1.0 299 1

Hash (cost=11.99..11.99 rows=299 width=9) (actual time=0.077..0.077 rows=299 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
57. 0.046 0.046 ↑ 1.0 299 1

Seq Scan on accounts accounts_1 (cost=0.00..11.99 rows=299 width=9) (actual time=0.003..0.046 rows=299 loops=1)

58. 54.232 67.522 ↓ 254.2 958,123 1

Materialize (cost=782.37..867.18 rows=3,769 width=40) (actual time=11.434..67.522 rows=958,123 loops=1)

59. 1.251 13.290 ↑ 1.0 3,758 1

Unique (cost=782.37..820.06 rows=3,769 width=56) (actual time=11.430..13.290 rows=3,758 loops=1)

60. 2.734 12.039 ↑ 1.0 3,769 1

Sort (cost=782.37..791.80 rows=3,769 width=56) (actual time=11.430..12.039 rows=3,769 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: 626kB
61. 2.617 9.305 ↑ 1.0 3,769 1

WindowAgg (cost=473.69..558.50 rows=3,769 width=56) (actual time=6.360..9.305 rows=3,769 loops=1)

62. 1.960 6.688 ↑ 1.0 3,769 1

Sort (cost=473.69..483.12 rows=3,769 width=56) (actual time=6.343..6.688 rows=3,769 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
63. 1.452 4.728 ↑ 1.0 3,769 1

Hash Join (cost=127.21..249.81 rows=3,769 width=56) (actual time=2.225..4.728 rows=3,769 loops=1)

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
64. 1.121 1.121 ↑ 1.0 3,776 1

Seq Scan on staff_membership_activity_links (cost=0.00..70.76 rows=3,776 width=28) (actual time=0.023..1.121 rows=3,776 loops=1)

65. 1.144 2.155 ↑ 1.0 3,698 1

Hash (cost=80.98..80.98 rows=3,698 width=32) (actual time=2.155..2.155 rows=3,698 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 257kB
66. 1.011 1.011 ↑ 1.0 3,698 1

Seq Scan on staff_memberships staff_memberships_1 (cost=0.00..80.98 rows=3,698 width=32) (actual time=0.004..1.011 rows=3,698 loops=1)

67. 3,775.612 3,992.726 ↓ 6,117.0 28,034,117 1

Sort (cost=8,159.84..8,171.30 rows=4,583 width=48) (actual time=253.651..3,992.726 rows=28,034,117 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: external sort Disk: 2224kB
68. 4.282 217.114 ↓ 8.3 38,019 1

Subquery Scan on pa (cost=7,766.57..7,881.15 rows=4,583 width=48) (actual time=177.125..217.114 rows=38,019 loops=1)

69. 66.857 212.832 ↓ 8.3 38,019 1

HashAggregate (cost=7,766.57..7,835.32 rows=4,583 width=48) (actual time=177.123..212.832 rows=38,019 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)
70. 22.027 145.975 ↑ 1.0 38,166 1

WindowAgg (cost=5,570.65..6,429.92 rows=38,190 width=60) (actual time=114.112..145.975 rows=38,166 loops=1)

71. 44.805 123.948 ↑ 1.0 38,166 1

Sort (cost=5,570.65..5,666.12 rows=38,190 width=60) (actual time=114.104..123.948 rows=38,166 loops=1)

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: external merge Disk: 2352kB
72. 17.905 79.143 ↑ 1.0 38,166 1

Hash Join (cost=1,383.20..2,664.22 rows=38,190 width=60) (actual time=15.825..79.143 rows=38,166 loops=1)

  • Hash Cond: (plan_roles.plan_row_uuid = plan_rows_1.uuid)
73. 46.994 46.994 ↑ 1.0 38,168 1

Seq Scan on plan_roles (cost=0.00..755.90 rows=38,190 width=32) (actual time=1.249..46.994 rows=38,168 loops=1)

74. 7.695 14.244 ↑ 1.0 40,890 1

Hash (cost=871.98..871.98 rows=40,898 width=28) (actual time=14.244..14.244 rows=40,890 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2773kB
75. 6.549 6.549 ↑ 1.0 40,890 1

Seq Scan on plan_rows plan_rows_1 (cost=0.00..871.98 rows=40,898 width=28) (actual time=0.007..6.549 rows=40,890 loops=1)

76. 8.893 442.434 ↓ 12.6 13,893 1

Subquery Scan on *SELECT* 2_1 (cost=1,025.18..194,529.79 rows=1,100 width=64) (actual time=12.937..442.434 rows=13,893 loops=1)

77. 10.522 433.541 ↓ 12.6 13,893 1

Nested Loop (cost=1,025.18..194,518.79 rows=1,100 width=64) (actual time=12.932..433.541 rows=13,893 loops=1)

78. 6.718 21.791 ↓ 1.2 10,844 1

Hash Join (cost=1,024.60..1,647.96 rows=9,424 width=60) (actual time=12.850..21.791 rows=10,844 loops=1)

  • Hash Cond: (vacations_1.staff_membership_id = staff_membership_activity_links_1.staff_membership_id)
79. 2.243 2.243 ↑ 1.0 17,264 1

Seq Scan on vacations vacations_1 (cost=0.00..399.64 rows=17,264 width=44) (actual time=0.003..2.243 rows=17,264 loops=1)

80. 0.310 12.830 ↑ 1.2 1,588 1

Hash (cost=1,001.00..1,001.00 rows=1,888 width=16) (actual time=12.830..12.830 rows=1,588 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 93kB
81. 0.620 12.520 ↑ 1.2 1,588 1

Hash Join (cost=915.85..1,001.00 rows=1,888 width=16) (actual time=11.589..12.520 rows=1,588 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
82. 0.325 0.325 ↑ 1.0 3,776 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_1 (cost=0.00..70.76 rows=3,776 width=20) (actual time=0.003..0.325 rows=3,776 loops=1)

83. 0.207 11.575 ↓ 69.0 1,588 1

Hash (cost=915.57..915.57 rows=23 width=4) (actual time=11.575..11.575 rows=1,588 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 72kB
84. 0.907 11.368 ↓ 69.0 1,588 1

HashAggregate (cost=915.34..915.57 rows=23 width=4) (actual time=11.018..11.368 rows=1,588 loops=1)

  • Group Key: staff_activities_with_dates.link_id
85. 2.118 10.461 ↓ 3.8 1,588 1

Subquery Scan on staff_activities_with_dates (cost=782.37..914.29 rows=419 width=4) (actual time=7.445..10.461 rows=1,588 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: 2170
86. 0.598 8.343 ↑ 1.0 3,758 1

Unique (cost=782.37..820.06 rows=3,769 width=28) (actual time=7.436..8.343 rows=3,758 loops=1)

87. 1.897 7.745 ↑ 1.0 3,769 1

Sort (cost=782.37..791.80 rows=3,769 width=28) (actual time=7.435..7.745 rows=3,769 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
88. 2.019 5.848 ↑ 1.0 3,769 1

WindowAgg (cost=473.69..558.50 rows=3,769 width=28) (actual time=3.533..5.848 rows=3,769 loops=1)

89. 1.385 3.829 ↑ 1.0 3,769 1

Sort (cost=473.69..483.12 rows=3,769 width=28) (actual time=3.525..3.829 rows=3,769 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
90. 1.006 2.444 ↑ 1.0 3,769 1

Hash Join (cost=127.21..249.81 rows=3,769 width=28) (actual time=1.140..2.444 rows=3,769 loops=1)

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_3.id)
91. 0.312 0.312 ↑ 1.0 3,776 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_2 (cost=0.00..70.76 rows=3,776 width=12) (actual time=0.002..0.312 rows=3,776 loops=1)

92. 0.504 1.126 ↑ 1.0 3,698 1

Hash (cost=80.98..80.98 rows=3,698 width=20) (actual time=1.126..1.126 rows=3,698 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 214kB
93. 0.622 0.622 ↑ 1.0 3,698 1

Seq Scan on staff_memberships staff_memberships_3 (cost=0.00..80.98 rows=3,698 width=20) (actual time=0.003..0.622 rows=3,698 loops=1)

94. 81.692 401.228 ↑ 1.0 1 10,844

Nested Loop Left Join (cost=0.58..20.46 rows=1 width=24) (actual time=0.032..0.037 rows=1 loops=10,844)

  • 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
95. 0.000 54.220 ↑ 1.0 1 10,844

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

  • Join Filter: (vacations_1.staff_membership_id = staff_memberships_2.id)
96. 21.688 21.688 ↑ 1.0 1 10,844

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,844)

  • Index Cond: (id = staff_membership_activity_links_1.staff_membership_id)
97. 32.532 32.532 ↑ 25.0 1 10,844

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,844)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 0
98. 265.316 265.316 ↓ 34.0 34 13,964

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.02 rows=1 width=16) (actual time=0.007..0.019 rows=34 loops=13,964)

  • Index Cond: ((staff_memberships_2.account_id = account_id) AND (user_id = staff_memberships_2.user_id))
99. 0.006 1.105 ↑ 5.9 17 1

Hash (cost=14.98..14.98 rows=100 width=4) (actual time=1.105..1.105 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
100. 1.099 1.099 ↑ 5.9 17 1

Seq Scan on accounts (cost=0.00..14.98 rows=100 width=4) (actual time=0.561..1.099 rows=17 loops=1)

  • Filter: ((suspended_at)::date >= ('now'::cstring)::date)
  • Rows Removed by Filter: 282
101. 11,470.257 11,470.257 ↑ 1.0 1 674,721

Index Scan using exchange_rates_by_months_uniq on exchange_rates_by_months cost_exchange_rates (cost=0.28..0.31 rows=1 width=536) (actual time=0.011..0.017 rows=1 loops=674,721)

  • Index Cond: ((account_id = projects.account_id) AND ((currency)::text = (staff_memberships_1.currency)::text))
  • Filter: ((start_date <= "*SELECT* 1_1".date) AND (end_date >= "*SELECT* 1_1".date))
  • Rows Removed by Filter: 47
102. 64.030 892.714 ↓ 4.4 983,370 1

Materialize (cost=44,579.10..45,698.99 rows=223,977 width=44) (actual time=750.985..892.714 rows=983,370 loops=1)

103. 328.963 828.684 ↑ 1.0 221,825 1

Sort (cost=44,579.10..45,139.05 rows=223,977 width=44) (actual time=750.981..828.684 rows=221,825 loops=1)

  • Sort Key: "*SELECT* 1".project_id, "*SELECT* 1".activity_id
  • Sort Method: external merge Disk: 12512kB
104. 15.434 499.721 ↑ 1.0 221,849 1

Append (cost=239.89..17,783.90 rows=223,977 width=44) (actual time=37.547..499.721 rows=221,849 loops=1)

105. 0.041 134.452 ↓ 19.0 95 1

Subquery Scan on *SELECT* 1 (cost=239.89..373.27 rows=5 width=44) (actual time=37.545..134.452 rows=95 loops=1)

106. 0.238 134.411 ↓ 19.0 95 1

Merge Join (cost=239.89..373.22 rows=5 width=44) (actual time=37.544..134.411 rows=95 loops=1)

  • Merge Cond: (clients.brand_id = brands.id)
107. 0.131 134.094 ↓ 2.2 95 1

Nested Loop (cost=234.72..1,403.22 rows=44 width=52) (actual time=37.506..134.094 rows=95 loops=1)

108. 0.008 27.898 ↑ 1.0 1 1

Merge Join (cost=234.30..234.52 rows=1 width=32) (actual time=27.886..27.898 rows=1 loops=1)

  • Merge Cond: (rate_cards.rateable_id = clients.brand_id)
109. 0.023 9.543 ↑ 32.3 3 1

WindowAgg (cost=97.28..99.46 rows=97 width=29) (actual time=9.526..9.543 rows=3 loops=1)

110. 0.056 9.520 ↑ 32.3 3 1

Sort (cost=97.28..97.52 rows=97 width=29) (actual time=9.518..9.520 rows=3 loops=1)

  • Sort Key: rate_cards.rateable_id, rate_cards.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 32kB
111. 9.464 9.464 ↑ 1.0 97 1

Seq Scan on rate_cards (cost=0.00..94.08 rows=97 width=29) (actual time=3.705..9.464 rows=97 loops=1)

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4229
112. 0.014 18.347 ↑ 1.0 1 1

Sort (cost=137.02..137.03 rows=1 width=8) (actual time=18.345..18.347 rows=1 loops=1)

  • Sort Key: clients.brand_id
  • Sort Method: quicksort Memory: 25kB
113. 0.006 18.333 ↑ 1.0 1 1

Nested Loop (cost=0.56..137.01 rows=1 width=8) (actual time=12.107..18.333 rows=1 loops=1)

114. 0.018 18.297 ↑ 1.0 1 1

Nested Loop (cost=0.28..136.69 rows=1 width=8) (actual time=12.073..18.297 rows=1 loops=1)

115. 18.242 18.242 ↑ 1.0 1 1

Seq Scan on pricing_models (cost=0.00..128.38 rows=1 width=4) (actual time=12.020..18.242 rows=1 loops=1)

  • Filter: ((rates_type)::text = 'brand'::text)
  • Rows Removed by Filter: 5229
116. 0.037 0.037 ↑ 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.037..0.037 rows=1 loops=1)

  • Index Cond: (id = pricing_models.project_id)
117. 0.030 0.030 ↑ 1.0 1 1

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

  • Index Cond: (id = projects_2.client_id)
118. 106.065 106.065 ↓ 2.2 95 1

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates (cost=0.42..1,168.25 rows=44 width=28) (actual time=9.612..106.065 rows=95 loops=1)

  • Index Cond: (rate_card_id = rate_cards.id)
119. 0.066 0.079 ↑ 1.0 97 1

Sort (cost=5.17..5.41 rows=97 width=4) (actual time=0.034..0.079 rows=97 loops=1)

  • Sort Key: brands.id
  • Sort Method: quicksort Memory: 29kB
120. 0.013 0.013 ↑ 1.0 97 1

Seq Scan on brands (cost=0.00..1.97 rows=97 width=4) (actual time=0.004..0.013 rows=97 loops=1)

121. 8.188 238.414 ↓ 2.6 75,640 1

Subquery Scan on *SELECT* 2 (cost=532.25..4,827.36 rows=28,640 width=44) (actual time=35.890..238.414 rows=75,640 loops=1)

122. 31.262 230.226 ↓ 2.6 75,640 1

Hash Join (cost=532.25..4,540.96 rows=28,640 width=44) (actual time=35.889..230.226 rows=75,640 loops=1)

  • Hash Cond: (rates_1.rate_card_id = rate_cards_with_dates.id)
123. 187.682 187.682 ↑ 1.0 147,154 1

Seq Scan on rates rates_1 (cost=0.00..2,986.54 rows=147,154 width=28) (actual time=0.018..187.682 rows=147,154 loops=1)

124. 0.336 11.282 ↑ 1.2 547 1

Hash (cost=524.19..524.19 rows=645 width=24) (actual time=11.282..11.282 rows=547 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
125. 0.315 10.946 ↑ 1.2 547 1

Hash Join (cost=336.52..524.19 rows=645 width=24) (actual time=8.146..10.946 rows=547 loops=1)

  • Hash Cond: (clients_1.office_id = rate_cards_with_dates.rateable_id)
126. 0.422 9.484 ↑ 1.0 516 1

Hash Join (cost=205.24..384.53 rows=516 width=12) (actual time=6.987..9.484 rows=516 loops=1)

  • Hash Cond: (clients_1.office_id = offices.id)
127. 0.225 8.902 ↑ 1.0 516 1

Hash Join (cost=192.80..365.00 rows=516 width=8) (actual time=6.816..8.902 rows=516 loops=1)

  • Hash Cond: (projects_3.client_id = clients_1.id)
128. 1.138 6.333 ↑ 1.0 516 1

Hash Join (cost=134.82..299.92 rows=516 width=8) (actual time=4.460..6.333 rows=516 loops=1)

  • Hash Cond: (projects_3.id = pricing_models_1.project_id)
129. 0.842 0.842 ↑ 1.0 5,232 1

Seq Scan on projects projects_3 (cost=0.00..140.32 rows=5,232 width=8) (actual time=0.007..0.842 rows=5,232 loops=1)

130. 0.507 4.353 ↑ 1.0 516 1

Hash (cost=128.38..128.38 rows=516 width=4) (actual time=4.353..4.353 rows=516 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
131. 3.846 3.846 ↑ 1.0 516 1

Seq Scan on pricing_models pricing_models_1 (cost=0.00..128.38 rows=516 width=4) (actual time=0.044..3.846 rows=516 loops=1)

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 4714
132. 0.289 2.344 ↑ 1.0 1,688 1

Hash (cost=36.88..36.88 rows=1,688 width=8) (actual time=2.344..2.344 rows=1,688 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
133. 2.055 2.055 ↑ 1.0 1,688 1

Seq Scan on clients clients_1 (cost=0.00..36.88 rows=1,688 width=8) (actual time=0.011..2.055 rows=1,688 loops=1)

134. 0.051 0.160 ↑ 1.0 375 1

Hash (cost=7.75..7.75 rows=375 width=4) (actual time=0.160..0.160 rows=375 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
135. 0.109 0.109 ↑ 1.0 375 1

Seq Scan on offices (cost=0.00..7.75 rows=375 width=4) (actual time=0.011..0.109 rows=375 loops=1)

136. 0.087 1.147 ↑ 1.0 420 1

Hash (cost=126.02..126.02 rows=420 width=24) (actual time=1.147..1.147 rows=420 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
137. 0.051 1.060 ↑ 1.0 420 1

Subquery Scan on rate_cards_with_dates (cost=112.37..126.02 rows=420 width=24) (actual time=0.741..1.060 rows=420 loops=1)

138. 0.249 1.009 ↑ 1.0 420 1

WindowAgg (cost=112.37..121.82 rows=420 width=29) (actual time=0.740..1.009 rows=420 loops=1)

139. 0.166 0.760 ↑ 1.0 420 1

Sort (cost=112.37..113.42 rows=420 width=29) (actual time=0.735..0.760 rows=420 loops=1)

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

Seq Scan on rate_cards rate_cards_1 (cost=0.00..94.08 rows=420 width=29) (actual time=0.124..0.594 rows=420 loops=1)

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 3906
141. 7.929 57.448 ↑ 2.1 79,167 1

Subquery Scan on *SELECT* 3 (cost=791.11..7,892.54 rows=168,956 width=44) (actual time=7.323..57.448 rows=79,167 loops=1)

142. 30.671 49.519 ↑ 2.1 79,167 1

Hash Join (cost=791.11..6,202.98 rows=168,956 width=44) (actual time=7.321..49.519 rows=79,167 loops=1)

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
143. 11.560 11.560 ↑ 1.0 147,154 1

Seq Scan on rates rates_2 (cost=0.00..2,986.54 rows=147,154 width=28) (actual time=0.003..11.560 rows=147,154 loops=1)

144. 0.623 7.288 ↑ 1.1 3,556 1

Hash (cost=743.54..743.54 rows=3,805 width=24) (actual time=7.288..7.288 rows=3,556 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 241kB
145. 0.787 6.665 ↑ 1.1 3,556 1

Hash Join (cost=506.59..743.54 rows=3,805 width=24) (actual time=4.235..6.665 rows=3,556 loops=1)

  • Hash Cond: (projects_4.client_id = clients_2.id)
146. 1.191 2.996 ↑ 1.0 3,273 1

Hash Join (cost=169.30..361.97 rows=3,273 width=8) (actual time=1.345..2.996 rows=3,273 loops=1)

  • Hash Cond: (projects_4.id = pricing_models_2.project_id)
147. 0.492 0.492 ↑ 1.0 5,232 1

Seq Scan on projects projects_4 (cost=0.00..140.32 rows=5,232 width=8) (actual time=0.003..0.492 rows=5,232 loops=1)

148. 0.420 1.313 ↑ 1.0 3,273 1

Hash (cost=128.38..128.38 rows=3,274 width=4) (actual time=1.313..1.313 rows=3,273 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 148kB
149. 0.893 0.893 ↑ 1.0 3,274 1

Seq Scan on pricing_models pricing_models_2 (cost=0.00..128.38 rows=3,274 width=4) (actual time=0.006..0.893 rows=3,274 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 1956
150. 0.299 2.882 ↑ 1.0 1,650 1

Hash (cost=316.65..316.65 rows=1,651 width=28) (actual time=2.882..2.882 rows=1,650 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 113kB
151. 0.357 2.583 ↑ 1.0 1,650 1

Hash Join (cost=240.29..316.65 rows=1,651 width=28) (actual time=1.338..2.583 rows=1,650 loops=1)

  • Hash Cond: (rate_cards_2.rateable_id = clients_2.id)
152. 0.785 1.845 ↑ 1.0 1,651 1

WindowAgg (cost=182.31..219.46 rows=1,651 width=29) (actual time=0.952..1.845 rows=1,651 loops=1)

153. 0.494 1.060 ↑ 1.0 1,651 1

Sort (cost=182.31..186.44 rows=1,651 width=29) (actual time=0.948..1.060 rows=1,651 loops=1)

  • Sort Key: rate_cards_2.rateable_id, rate_cards_2.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 177kB
154. 0.566 0.566 ↑ 1.0 1,651 1

Seq Scan on rate_cards rate_cards_2 (cost=0.00..94.08 rows=1,651 width=29) (actual time=0.004..0.566 rows=1,651 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 2675
155. 0.201 0.381 ↑ 1.0 1,688 1

Hash (cost=36.88..36.88 rows=1,688 width=4) (actual time=0.381..0.381 rows=1,688 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 76kB
156. 0.180 0.180 ↑ 1.0 1,688 1

Seq Scan on clients clients_2 (cost=0.00..36.88 rows=1,688 width=4) (actual time=0.004..0.180 rows=1,688 loops=1)

157. 6.894 53.973 ↓ 2.5 66,947 1

Subquery Scan on *SELECT* 4 (cost=624.84..4,690.73 rows=26,376 width=44) (actual time=5.239..53.973 rows=66,947 loops=1)

158. 30.244 47.079 ↓ 2.5 66,947 1

Hash Join (cost=624.84..4,426.97 rows=26,376 width=44) (actual time=5.239..47.079 rows=66,947 loops=1)

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
159. 11.617 11.617 ↑ 1.0 147,154 1

Seq Scan on rates rates_3 (cost=0.00..2,986.54 rows=147,154 width=28) (actual time=0.003..11.617 rows=147,154 loops=1)

160. 0.247 5.218 ↓ 2.5 1,463 1

Hash (cost=617.41..617.41 rows=594 width=24) (actual time=5.218..5.218 rows=1,463 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 102kB
161. 0.604 4.971 ↓ 2.5 1,463 1

Hash Join (cost=451.53..617.41 rows=594 width=24) (actual time=4.003..4.971 rows=1,463 loops=1)

  • Hash Cond: (projects_5.id = pricing_models_3.project_id)
162. 0.374 0.374 ↑ 1.0 5,232 1

Seq Scan on projects projects_5 (cost=0.00..140.32 rows=5,232 width=4) (actual time=0.002..0.374 rows=5,232 loops=1)

163. 0.225 3.993 ↓ 2.5 1,463 1

Hash (cost=444.11..444.11 rows=594 width=24) (actual time=3.993..3.993 rows=1,463 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 102kB
164. 0.361 3.768 ↓ 2.5 1,463 1

Hash Join (cost=359.94..444.11 rows=594 width=24) (actual time=2.293..3.768 rows=1,463 loops=1)

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
165. 0.976 2.480 ↑ 1.0 2,158 1

WindowAgg (cost=213.58..262.13 rows=2,158 width=29) (actual time=1.358..2.480 rows=2,158 loops=1)

166. 0.839 1.504 ↑ 1.0 2,158 1

Sort (cost=213.58..218.97 rows=2,158 width=29) (actual time=1.353..1.504 rows=2,158 loops=1)

  • Sort Key: rate_cards_3.rateable_id, rate_cards_3.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 265kB
167. 0.665 0.665 ↑ 1.0 2,158 1

Seq Scan on rate_cards rate_cards_3 (cost=0.00..94.08 rows=2,158 width=29) (actual time=0.004..0.665 rows=2,158 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2168
168. 0.191 0.927 ↑ 1.0 1,439 1

Hash (cost=128.38..128.38 rows=1,439 width=8) (actual time=0.927..0.927 rows=1,439 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 73kB
169. 0.736 0.736 ↑ 1.0 1,439 1

Seq Scan on pricing_models pricing_models_3 (cost=0.00..128.38 rows=1,439 width=8) (actual time=0.007..0.736 rows=1,439 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 3791
170. 31.054 31.159 ↓ 2,225.9 467,447 1

Sort (cost=15.63..16.15 rows=210 width=27) (actual time=0.157..31.159 rows=467,447 loops=1)

  • Sort Key: discounts.project_id
  • Sort Method: quicksort Memory: 44kB
171. 0.105 0.105 ↓ 1.2 252 1

Seq Scan on discounts (cost=0.00..7.53 rows=210 width=27) (actual time=0.033..0.105 rows=252 loops=1)

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
172. 2,165.626 2,166.314 ↓ 6,664.6 30,923,648 1

Sort (cost=386.97..398.57 rows=4,640 width=536) (actual time=7.124..2,166.314 rows=30,923,648 loops=1)

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 524kB
173. 0.688 0.688 ↑ 1.1 4,248 1

Seq Scan on exchange_rates_by_months (cost=0.00..104.40 rows=4,640 width=536) (actual time=0.015..0.688 rows=4,248 loops=1)

174. 1,507.494 1,514.176 ↓ 6,664.6 30,923,648 1

Materialize (cost=0.28..393.48 rows=4,640 width=536) (actual time=1.237..1,514.176 rows=30,923,648 loops=1)

175. 6.682 6.682 ↑ 1.1 4,248 1

Index Scan using exchange_rates_by_months_uniq on exchange_rates_by_months original_exchange_rates (cost=0.28..381.88 rows=4,640 width=536) (actual time=1.234..6.682 rows=4,248 loops=1)