explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SQ9e

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,123.565 94,614.144 ↓ 9.5 673,271 1

GroupAggregate (cost=2,686,838.17..2,692,693.77 rows=70,977 width=184) (actual time=93,037.368..94,614.144 rows=673,271 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. 3,639.922 93,490.579 ↓ 9.5 673,284 1

Sort (cost=2,686,838.17..2,687,015.61 rows=70,977 width=184) (actual time=93,037.313..93,490.579 rows=673,284 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: 94480kB
3. 9,162.382 89,850.657 ↓ 9.5 673,284 1

Merge Left Join (cost=2,673,169.38..2,674,808.68 rows=70,977 width=184) (actual time=64,208.761..89,850.657 rows=673,284 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: 30314085
4. 8,456.354 78,267.125 ↓ 9.5 673,284 1

Merge Left Join (cost=2,673,169.10..2,673,886.67 rows=70,977 width=176) (actual time=64,208.067..78,267.125 rows=673,284 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: 30314085
5. 5,082.203 66,336.840 ↓ 9.5 673,284 1

Sort (cost=2,672,782.12..2,672,959.57 rows=70,977 width=168) (actual time=64,196.687..66,336.840 rows=673,284 loops=1)

  • Sort Key: projects.account_id, "*SELECT* 1".currency
  • Sort Method: external merge Disk: 80824kB
6. 285.859 61,254.637 ↓ 9.5 673,284 1

Merge Left Join (cost=2,648,791.46..2,661,239.13 rows=70,977 width=168) (actual time=59,646.864..61,254.637 rows=673,284 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: 459945
7. 591.176 60,919.777 ↓ 9.5 673,271 1

Merge Left Join (cost=2,648,775.83..2,658,810.28 rows=70,977 width=164) (actual time=59,646.591..60,919.777 rows=673,271 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: 219923
8. 2,248.932 59,351.323 ↓ 9.5 673,271 1

Sort (cost=2,604,196.73..2,604,374.17 rows=70,977 width=136) (actual time=58,877.412..59,351.323 rows=673,271 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: 64136kB
9. 587.596 57,102.391 ↓ 9.5 673,271 1

Nested Loop Left Join (cost=2,206,097.00..2,593,623.23 rows=70,977 width=136) (actual time=16,473.563..57,102.391 rows=673,271 loops=1)

10. 364.912 36,989.936 ↓ 9.5 673,271 1

Hash Join (cost=2,206,096.72..2,570,983.69 rows=70,977 width=644) (actual time=16,473.427..36,989.936 rows=673,271 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
11. 135.441 36,623.871 ↓ 4.5 944,829 1

Append (cost=2,206,080.49..2,567,339.66 rows=212,220 width=160) (actual time=16,472.241..36,623.871 rows=944,829 loops=1)

12. 12,024.873 35,928.569 ↓ 4.4 930,936 1

Merge Left Join (cost=2,206,080.49..2,370,698.68 rows=211,120 width=161) (actual time=16,472.240..35,928.569 rows=930,936 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: 27205817
13. 2,563.245 17,310.145 ↓ 4.4 930,936 1

Sort (cost=2,197,920.65..2,198,448.45 rows=211,120 width=149) (actual time=16,157.576..17,310.145 rows=930,936 loops=1)

  • Sort Key: "*SELECT* 1_1".project_plan_id
  • Sort Method: external merge Disk: 104424kB
14. 643.097 14,746.900 ↓ 4.4 930,936 1

Merge Left Join (cost=2,157,001.90..2,163,373.51 rows=211,120 width=149) (actual time=13,195.427..14,746.900 rows=930,936 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: 116855
  • 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: 5398
15. 3,643.422 14,013.994 ↓ 1.5 936,334 1

Sort (cost=2,156,219.53..2,157,787.25 rows=627,090 width=125) (actual time=13,186.927..14,013.994 rows=936,334 loops=1)

  • Sort Key: projects.account_id, "*SELECT* 1_1".user_id
  • Sort Method: external merge Disk: 79552kB
16. 389.649 10,370.572 ↓ 1.5 936,334 1

Hash Join (cost=1,891,320.90..2,055,110.05 rows=627,090 width=125) (actual time=5,632.051..10,370.572 rows=936,334 loops=1)

  • Hash Cond: ("*SELECT* 1_1".project_id = projects.id)
17. 151.215 9,963.203 ↓ 2.1 1,342,534 1

Append (cost=1,891,027.51..2,039,923.28 rows=627,090 width=41) (actual time=5,441.135..9,963.203 rows=1,342,534 loops=1)

18. 332.790 8,873.610 ↓ 147.3 725,273 1

Subquery Scan on *SELECT* 1_1 (cost=1,891,027.51..2,009,188.68 rows=4,923 width=101) (actual time=5,441.134..8,873.610 rows=725,273 loops=1)

19. 3,170.113 8,540.820 ↓ 147.3 725,273 1

Hash Right Join (cost=1,891,027.51..2,009,139.45 rows=4,923 width=101) (actual time=5,441.123..8,540.820 rows=725,273 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: 8403763
20. 15.283 117.114 ↑ 24.6 20,979 1

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

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

Sort (cost=965,850.96..974,752.17 rows=3,560,486 width=21) (actual time=98.143..101.831 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. 22.565 80.931 ↑ 168.8 21,095 1

Nested Loop (cost=127.21..432,364.23 rows=3,560,486 width=21) (actual time=4.261..80.931 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. 10.596 23.838 ↑ 1.0 17,264 1

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

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

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

25. 1.033 4.134 ↑ 1.0 3,698 1

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

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

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

27. 34.528 34.528 ↑ 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.002..0.002 rows=1 loops=17,264)

28. 931.132 5,253.593 ↓ 147.3 725,273 1

Hash (cost=925,102.71..925,102.71 rows=4,923 width=69) (actual time=5,253.593..5,253.593 rows=725,273 loops=1)

  • Buckets: 65536 (originally 8192) Batches: 2048 (originally 1) Memory Usage: 14593kB
29. 834.698 4,322.461 ↓ 147.3 725,273 1

Merge Left Join (cost=894,726.20..925,102.71 rows=4,923 width=69) (actual time=2,352.324..4,322.461 rows=725,273 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))
  • Join Filter: ((non_working_intervals_by_dates.user_id = plan_rows.user_id) OR (non_working_intervals_by_dates.office_id = clients.office_id))
  • Rows Removed by Join Filter: 1470512
  • Filter: (non_working_intervals_by_dates.id IS NULL)
  • Rows Removed by Filter: 520843
30. 1,939.435 3,033.167 ↑ 1.3 731,993 1

Sort (cost=890,562.03..893,023.72 rows=984,675 width=69) (actual time=2,352.320..3,033.167 rows=731,993 loops=1)

  • Sort Key: generate_series.generate_series, projects_1.account_id
  • Sort Method: external merge Disk: 57160kB
31. 152.463 1,093.732 ↑ 1.3 731,993 1

Nested Loop (cost=1,881.12..752,151.14 rows=984,675 width=69) (actual time=59.094..1,093.732 rows=731,993 loops=1)

32. 20.201 160.654 ↓ 1.0 41,085 1

Hash Left Join (cost=1,881.12..3,798.13 rows=39,387 width=69) (actual time=59.043..160.654 rows=41,085 loops=1)

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

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

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

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

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

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

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

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

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

Hash (cost=486.22..486.22 rows=2,663 width=16) (actual time=14.148..14.148 rows=2,666 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 157kB
38. 0.753 13.600 ↓ 1.0 2,666 1

Hash Left Join (cost=263.70..486.22 rows=2,663 width=16) (actual time=8.095..13.600 rows=2,666 loops=1)

  • Hash Cond: (projects_1.client_id = clients.id)
39. 0.977 6.648 ↓ 1.0 2,666 1

Hash Left Join (cost=205.72..391.63 rows=2,663 width=16) (actual time=1.878..6.648 rows=2,666 loops=1)

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

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

  • Filter: active
  • Rows Removed by Filter: 2766
41. 0.825 1.793 ↑ 1.0 5,232 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 289kB
42. 0.968 0.968 ↑ 1.0 5,232 1

Seq Scan on projects projects_1 (cost=0.00..140.32 rows=5,232 width=12) (actual time=0.004..0.968 rows=5,232 loops=1)

43. 0.305 6.199 ↑ 1.0 1,688 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
44. 5.894 5.894 ↑ 1.0 1,688 1

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

45. 780.615 780.615 ↑ 1.4 18 41,085

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.007..0.019 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
46. 432.125 454.596 ↓ 47.5 2,009,210 1

Sort (cost=4,164.17..4,270.02 rows=42,340 width=20) (actual time=63.243..454.596 rows=2,009,210 loops=1)

  • Sort Key: non_working_intervals_by_dates.date, non_working_intervals_by_dates.account_id
  • Sort Method: external sort Disk: 1408kB
47. 22.471 22.471 ↑ 1.0 42,340 1

Seq Scan on non_working_intervals_by_dates (cost=0.00..910.40 rows=42,340 width=20) (actual time=0.034..22.471 rows=42,340 loops=1)

48. 298.410 938.378 ↑ 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=30.299..938.378 rows=617,261 loops=1)

49. 381.611 639.968 ↑ 1.0 617,261 1

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

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

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

51. 5.682 29.496 ↓ 1.0 24,788 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1312kB
52. 8.240 23.814 ↓ 1.0 24,788 1

Hash Left Join (cost=182.58..1,402.63 rows=24,787 width=16) (actual time=1.209..23.814 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
53. 14.437 14.437 ↓ 1.0 24,788 1

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

54. 0.330 1.137 ↓ 1.0 2,666 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 137kB
55. 0.807 0.807 ↓ 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.006..0.807 rows=2,666 loops=1)

  • Filter: active
  • Rows Removed by Filter: 2766
56. 1.165 17.720 ↑ 1.0 5,232 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 294kB
57. 1.547 16.555 ↑ 1.0 5,232 1

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

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

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

59. 0.042 0.105 ↑ 1.0 299 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
60. 0.063 0.063 ↑ 1.0 299 1

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

61. 78.578 89.809 ↓ 253.9 956,962 1

Materialize (cost=782.37..867.18 rows=3,769 width=40) (actual time=8.494..89.809 rows=956,962 loops=1)

62. 1.810 11.231 ↑ 1.0 3,758 1

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

63. 2.817 9.421 ↑ 1.0 3,769 1

Sort (cost=782.37..791.80 rows=3,769 width=56) (actual time=8.490..9.421 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
64. 2.133 6.604 ↑ 1.0 3,769 1

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

65. 1.612 4.471 ↑ 1.0 3,769 1

Sort (cost=473.69..483.12 rows=3,769 width=56) (actual time=4.215..4.471 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
66. 1.061 2.859 ↑ 1.0 3,769 1

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

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
67. 0.546 0.546 ↑ 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.022..0.546 rows=3,776 loops=1)

68. 0.653 1.252 ↑ 1.0 3,698 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 257kB
69. 0.599 0.599 ↑ 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..0.599 rows=3,698 loops=1)

70. 6,326.442 6,593.551 ↓ 6,111.7 28,009,781 1

Sort (cost=8,159.84..8,171.30 rows=4,583 width=48) (actual time=314.641..6,593.551 rows=28,009,781 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: external sort Disk: 2224kB
71. 6.969 267.109 ↓ 8.3 38,019 1

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

72. 99.638 260.140 ↓ 8.3 38,019 1

HashAggregate (cost=7,766.57..7,835.32 rows=4,583 width=48) (actual time=205.155..260.140 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)
73. 34.636 160.502 ↑ 1.0 38,166 1

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

74. 63.731 125.866 ↑ 1.0 38,166 1

Sort (cost=5,570.65..5,666.12 rows=38,190 width=60) (actual time=112.838..125.866 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
75. 22.925 62.135 ↑ 1.0 38,166 1

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

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

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

77. 11.897 22.062 ↑ 1.0 40,890 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2773kB
78. 10.165 10.165 ↑ 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.006..10.165 rows=40,890 loops=1)

79. 12.816 559.861 ↓ 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=21.877..559.861 rows=13,893 loops=1)

80. 14.131 547.045 ↓ 12.6 13,893 1

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

81. 9.165 34.090 ↓ 1.2 10,844 1

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

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

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

83. 0.460 21.751 ↑ 1.2 1,588 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 93kB
84. 1.025 21.291 ↑ 1.2 1,588 1

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

  • Hash Cond: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
85. 0.459 0.459 ↑ 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.005..0.459 rows=3,776 loops=1)

86. 0.337 19.807 ↓ 69.0 1,588 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 72kB
87. 1.044 19.470 ↓ 69.0 1,588 1

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

  • Group Key: staff_activities_with_dates.link_id
88. 4.001 18.426 ↓ 3.8 1,588 1

Subquery Scan on staff_activities_with_dates (cost=782.37..914.29 rows=419 width=4) (actual time=12.784..18.426 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
89. 1.111 14.425 ↑ 1.0 3,758 1

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

90. 3.423 13.314 ↑ 1.0 3,769 1

Sort (cost=782.37..791.80 rows=3,769 width=28) (actual time=12.769..13.314 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
91. 3.608 9.891 ↑ 1.0 3,769 1

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

92. 2.158 6.283 ↑ 1.0 3,769 1

Sort (cost=473.69..483.12 rows=3,769 width=28) (actual time=5.787..6.283 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
93. 1.639 4.125 ↑ 1.0 3,769 1

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

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_3.id)
94. 0.487 0.487 ↑ 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.004..0.487 rows=3,776 loops=1)

95. 0.995 1.999 ↑ 1.0 3,698 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 214kB
96. 1.004 1.004 ↑ 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.004..1.004 rows=3,698 loops=1)

97. 118.828 498.824 ↑ 1.0 1 10,844

Nested Loop Left Join (cost=0.58..20.46 rows=1 width=24) (actual time=0.039..0.046 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
98. 21.688 86.752 ↑ 1.0 1 10,844

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

  • Join Filter: (vacations_1.staff_membership_id = staff_memberships_2.id)
99. 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)
100. 43.376 43.376 ↑ 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.003..0.004 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
101. 293.244 293.244 ↓ 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.004..0.021 rows=34 loops=13,964)

  • Index Cond: ((staff_memberships_2.account_id = account_id) AND (user_id = staff_memberships_2.user_id))
102. 0.007 1.153 ↑ 5.9 17 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
103. 1.146 1.146 ↑ 5.9 17 1

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

  • Filter: ((suspended_at)::date >= ('now'::cstring)::date)
  • Rows Removed by Filter: 282
104. 19,524.859 19,524.859 ↑ 1.0 1 673,271

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.019..0.029 rows=1 loops=673,271)

  • 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
105. 99.895 977.278 ↓ 4.4 981,268 1

Materialize (cost=44,579.10..45,698.99 rows=223,977 width=44) (actual time=769.172..977.278 rows=981,268 loops=1)

106. 510.028 877.383 ↑ 1.0 221,825 1

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

  • Sort Key: "*SELECT* 1".project_id, "*SELECT* 1".activity_id
  • Sort Method: external merge Disk: 12512kB
107. 25.638 367.355 ↑ 1.0 221,849 1

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

108. 0.025 20.035 ↓ 19.0 95 1

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

109. 0.095 20.010 ↓ 19.0 95 1

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

  • Merge Cond: (clients_1.brand_id = brands.id)
110. 0.061 19.841 ↓ 2.2 95 1

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

111. 0.006 4.426 ↑ 1.0 1 1

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

  • Merge Cond: (rate_cards.rateable_id = clients_1.brand_id)
112. 0.024 2.274 ↑ 32.3 3 1

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

113. 0.046 2.250 ↑ 32.3 3 1

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

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

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

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4229
115. 0.009 2.146 ↑ 1.0 1 1

Sort (cost=137.02..137.03 rows=1 width=8) (actual time=2.145..2.146 rows=1 loops=1)

  • Sort Key: clients_1.brand_id
  • Sort Method: quicksort Memory: 25kB
116. 0.006 2.137 ↑ 1.0 1 1

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

117. 0.008 2.109 ↑ 1.0 1 1

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

118. 2.069 2.069 ↑ 1.0 1 1

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

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

  • Index Cond: (id = pricing_models.project_id)
120. 0.022 0.022 ↑ 1.0 1 1

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

  • Index Cond: (id = projects_2.client_id)
121. 15.354 15.354 ↓ 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=3.450..15.354 rows=95 loops=1)

  • Index Cond: (rate_card_id = rate_cards.id)
122. 0.054 0.074 ↑ 1.0 97 1

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

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

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

124. 12.838 130.410 ↓ 2.6 75,640 1

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

125. 48.900 117.572 ↓ 2.6 75,640 1

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

  • Hash Cond: (rates_1.rate_card_id = rate_cards_with_dates.id)
126. 62.320 62.320 ↑ 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.024..62.320 rows=147,154 loops=1)

127. 0.170 6.352 ↑ 1.2 547 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
128. 0.193 6.182 ↑ 1.2 547 1

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

  • Hash Cond: (clients_2.office_id = rate_cards_with_dates.rateable_id)
129. 0.195 4.357 ↑ 1.0 516 1

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

  • Hash Cond: (clients_2.office_id = offices.id)
130. 0.206 3.967 ↑ 1.0 516 1

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

  • Hash Cond: (projects_3.client_id = clients_2.id)
131. 0.976 2.911 ↑ 1.0 516 1

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

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

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

133. 0.110 1.207 ↑ 1.0 516 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
134. 1.097 1.097 ↑ 1.0 516 1

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

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 4714
135. 0.393 0.850 ↑ 1.0 1,688 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 82kB
136. 0.457 0.457 ↑ 1.0 1,688 1

Seq Scan on clients clients_2 (cost=0.00..36.88 rows=1,688 width=8) (actual time=0.005..0.457 rows=1,688 loops=1)

137. 0.081 0.195 ↑ 1.0 375 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
138. 0.114 0.114 ↑ 1.0 375 1

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

139. 0.132 1.632 ↑ 1.0 420 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
140. 0.068 1.500 ↑ 1.0 420 1

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

141. 0.379 1.432 ↑ 1.0 420 1

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

142. 0.206 1.053 ↑ 1.0 420 1

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

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

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

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 3906
144. 13.669 99.544 ↑ 2.1 79,167 1

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

145. 53.507 85.875 ↑ 2.1 79,167 1

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

  • Hash Cond: (rates_2.rate_card_id = rate_cards_2.id)
146. 19.734 19.734 ↑ 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.004..19.734 rows=147,154 loops=1)

147. 1.027 12.634 ↑ 1.1 3,556 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 241kB
148. 1.366 11.607 ↑ 1.1 3,556 1

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

  • Hash Cond: (projects_4.client_id = clients_3.id)
149. 1.988 5.095 ↑ 1.0 3,273 1

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

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

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

151. 0.769 2.310 ↑ 1.0 3,273 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 148kB
152. 1.541 1.541 ↑ 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.009..1.541 rows=3,274 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 1956
153. 0.527 5.146 ↑ 1.0 1,650 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 113kB
154. 0.634 4.619 ↑ 1.0 1,650 1

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

  • Hash Cond: (rate_cards_2.rateable_id = clients_3.id)
155. 1.411 3.303 ↑ 1.0 1,651 1

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

156. 0.885 1.892 ↑ 1.0 1,651 1

Sort (cost=182.31..186.44 rows=1,651 width=29) (actual time=1.689..1.892 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
157. 1.007 1.007 ↑ 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.006..1.007 rows=1,651 loops=1)

  • Filter: ((rateable_type)::text = 'Client'::text)
  • Rows Removed by Filter: 2675
158. 0.329 0.682 ↑ 1.0 1,688 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 76kB
159. 0.353 0.353 ↑ 1.0 1,688 1

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

160. 11.803 91.728 ↓ 2.5 66,947 1

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

161. 51.635 79.925 ↓ 2.5 66,947 1

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

  • Hash Cond: (rates_3.rate_card_id = rate_cards_3.id)
162. 18.956 18.956 ↑ 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.004..18.956 rows=147,154 loops=1)

163. 0.467 9.334 ↓ 2.5 1,463 1

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

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

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

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

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

166. 0.438 6.937 ↓ 2.5 1,463 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 102kB
167. 0.684 6.499 ↓ 2.5 1,463 1

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

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
168. 1.819 4.173 ↑ 1.0 2,158 1

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

169. 1.305 2.354 ↑ 1.0 2,158 1

Sort (cost=213.58..218.97 rows=2,158 width=29) (actual time=2.071..2.354 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
170. 1.049 1.049 ↑ 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.007..1.049 rows=2,158 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2168
171. 0.348 1.642 ↑ 1.0 1,439 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 73kB
172. 1.294 1.294 ↑ 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.011..1.294 rows=1,439 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 3791
173. 48.819 49.001 ↓ 2,218.2 465,831 1

Sort (cost=15.63..16.15 rows=210 width=27) (actual time=0.268..49.001 rows=465,831 loops=1)

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

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

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
175. 3,472.862 3,473.931 ↓ 6,642.9 30,822,907 1

Sort (cost=386.97..398.57 rows=4,640 width=536) (actual time=10.956..3,473.931 rows=30,822,907 loops=1)

  • Sort Key: exchange_rates_by_months.account_id, exchange_rates_by_months.currency
  • Sort Method: quicksort Memory: 524kB
176. 1.069 1.069 ↑ 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.027..1.069 rows=4,248 loops=1)

177. 2,418.767 2,421.150 ↓ 6,642.9 30,822,907 1

Materialize (cost=0.28..393.48 rows=4,640 width=536) (actual time=0.029..2,421.150 rows=30,822,907 loops=1)

178. 2.383 2.383 ↑ 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=0.027..2.383 rows=4,248 loops=1)