explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4xF

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 5,977.326 38,981.752 ↓ 9.2 579,765 1

Merge Left Join (cost=2,057,364.50..2,058,894.88 rows=63,201 width=140) (actual time=30,605.341..38,981.752 rows=579,765 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: 34,221,967
2. 569.421 30,646.342 ↓ 9.2 579,765 1

Sort (cost=2,057,119.64..2,057,277.64 rows=63,201 width=176) (actual time=30,603.846..30,646.342 rows=579,765 loops=1)

  • Sort Key: projects.account_id, staff_memberships_1.currency
  • Sort Method: quicksort Memory: 169,315kB
3. 6,003.453 30,076.921 ↓ 9.2 579,765 1

Merge Left Join (cost=2,051,497.73..2,052,080.10 rows=63,201 width=176) (actual time=21,692.025..30,076.921 rows=579,765 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: 34,221,967
4. 504.539 21,737.416 ↓ 9.2 579,765 1

Sort (cost=2,051,252.87..2,051,410.87 rows=63,201 width=168) (actual time=21,689.868..21,737.416 rows=579,765 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".currency
  • Sort Method: quicksort Memory: 150,116kB
5. 228.845 21,232.877 ↓ 9.2 579,765 1

Merge Left Join (cost=2,029,863.28..2,046,213.33 rows=63,201 width=168) (actual time=20,346.682..21,232.877 rows=579,765 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: 573,282
6. 434.800 20,961.930 ↓ 9.2 579,690 1

Merge Join (cost=2,029,840.51..2,043,131.62 rows=63,201 width=164) (actual time=20,346.513..20,961.930 rows=579,690 loops=1)

  • Merge Cond: ((projects.id = ""*SELECT* 1"".project_id) AND ((COALESCE(""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id)) = ""*SELECT* 1"".activity_id))
  • Join Filter: (("*SELECT* 1_1".date >= "*SELECT* 1".start_date) AND (("*SELECT* 1_1".date <= "*SELECT* 1".end_date) OR ("*SELECT* 1".end_date IS NULL)))
  • Rows Removed by Join Filter: 208,764
7. 677.597 19,874.677 ↓ 9.0 743,026 1

Sort (cost=1,983,009.59..1,983,216.14 rows=82,617 width=136) (actual time=19,780.718..19,874.677 rows=743,026 loops=1)

  • Sort Key: projects.id, (COALESCE(""*SELECT* 1_1"".activity_id, staff_membership_activity_links.activity_id))
  • Sort Method: quicksort Memory: 133,078kB
8. 239.593 19,197.080 ↓ 9.3 771,563 1

Hash Join (cost=1,134,722.61..1,976,262.20 rows=82,617 width=136) (actual time=3,217.337..19,197.080 rows=771,563 loops=1)

  • Hash Cond: (projects.account_id = accounts.id)
9. 137.421 18,957.369 ↓ 4.1 1,009,568 1

Append (cost=1,134,703.94..1,973,106.61 rows=247,852 width=136) (actual time=3,217.203..18,957.369 rows=1,009,568 loops=1)

10. 734.664 18,526.696 ↓ 364.0 980,662 1

Hash Join (cost=1,134,703.94..1,481,415.57 rows=2,694 width=108) (actual time=3,217.202..18,526.696 rows=980,662 loops=1)

  • Hash Cond: (("*SELECT* 1_1".project_id = projects.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))) AND ((((""*SELECT* 1_1"".vacation_user_id)::double precision) IS NULL) OR (((""*SELECT* 1_1"".vacation_hours)::double precision) < (COALESCE(staff_membership_activity_links.capacity, accounts_1.default_capacity))::double precision)))
  • Rows Removed by Join Filter: 272,495
11. 146.684 16,604.702 ↑ 1.1 1,100,806 1

Append (cost=1,131,686.63..1,422,859.20 rows=1,199,950 width=116) (actual time=2,029.802..16,604.702 rows=1,100,806 loops=1)

12. 136.162 2,838.162 ↑ 1.2 396,845 1

Result (cost=1,131,686.63..1,226,655.23 rows=495,989 width=116) (actual time=2,029.801..2,838.162 rows=396,845 loops=1)

13. 46.649 2,702.000 ↑ 1.2 396,845 1

Append (cost=1,131,686.63..1,220,455.37 rows=495,989 width=140) (actual time=2,029.794..2,702.000 rows=396,845 loops=1)

14. 65.722 2,365.756 ↑ 1.4 338,629 1

Subquery Scan on *SELECT* 1_1 (cost=1,131,686.63..1,155,495.07 rows=457,239 width=114) (actual time=2,029.792..2,365.756 rows=338,629 loops=1)

15. 103.586 2,300.034 ↑ 1.4 338,629 1

Merge Left Join (cost=1,131,686.63..1,148,636.49 rows=457,239 width=134) (actual time=2,029.789..2,300.034 rows=338,629 loops=1)

  • Merge Cond: ((projects_1.account_id = summary_vacations_by_dates.account_id) AND (generate_series.generate_series = summary_vacations_by_dates.date) AND (plan_rows.user_id = summary_vacations_by_dates.user_id))
16. 117.615 2,053.841 ↑ 1.4 338,629 1

Merge Anti Join (cost=525,089.35..531,687.96 rows=457,239 width=66) (actual time=1,893.094..2,053.841 rows=338,629 loops=1)

  • Merge Cond: ((projects_1.account_id = non_working_intervals_by_dates.account_id) AND (generate_series.generate_series = non_working_intervals_by_dates.date) AND (plan_rows.user_id = non_working_intervals_by_dates.user_id))
17. 1,070.073 1,890.261 ↑ 1.7 346,275 1

Sort (cost=519,071.36..520,519.29 rows=579,175 width=66) (actual time=1,851.644..1,890.261 rows=346,275 loops=1)

  • Sort Key: projects_1.account_id, generate_series.generate_series, plan_rows.user_id
  • Sort Method: quicksort Memory: 60,983kB
18. 167.656 820.188 ↑ 1.7 346,275 1

Merge Left Join (cost=12,929.64..463,633.77 rows=579,175 width=66) (actual time=265.229..820.188 rows=346,275 loops=1)

  • Merge Cond: (plan_items.plan_row_uuid = pa.plan_row_uuid)
  • Join Filter: (((pa.epic_id = plan_rows.epic_id) OR (plan_rows.epic_id IS NULL)) AND (pa.project_plan_id = project_plans.id) AND (pa.user_id = plan_rows.user_id) AND ((generate_series.generate_series)::date >= pa.start_date) AND ((generate_series.generate_series)::date <= pa.end_date))
  • Rows Removed by Join Filter: 3,247
19. 81.815 408.905 ↑ 1.7 346,275 1

Nested Loop (cost=3,971.09..448,257.03 rows=579,175 width=70) (actual time=54.674..408.905 rows=346,275 loops=1)

20. 17.219 108.994 ↓ 1.2 27,262 1

Merge Join (cost=3,971.09..8,084.02 rows=23,167 width=70) (actual time=54.657..108.994 rows=27,262 loops=1)

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

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

  • Filter: (user_id IS NOT NULL)
  • Rows Removed by Filter: 5,762
22. 21.794 59.795 ↓ 1.1 29,797 1

Sort (cost=3,970.18..4,035.85 rows=26,267 width=58) (actual time=54.621..59.795 rows=29,797 loops=1)

  • Sort Key: plan_items.plan_row_uuid
  • Sort Method: quicksort Memory: 4,959kB
23. 8.422 38.001 ↓ 1.1 29,797 1

Hash Join (cost=430.80..2,042.06 rows=26,267 width=58) (actual time=4.104..38.001 rows=29,797 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
24. 14.089 27.217 ↓ 1.1 29,797 1

Hash Join (cost=202.03..1,744.28 rows=26,267 width=54) (actual time=1.680..27.217 rows=29,797 loops=1)

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

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

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
26. 0.465 1.640 ↑ 1.0 2,851 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3,188
28. 1.237 2.362 ↑ 1.0 5,812 1

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

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

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

30. 218.096 218.096 ↑ 1.9 13 27,262

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.004..0.008 rows=13 loops=27,262)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 5
31. 52.602 243.627 ↓ 77.4 384,529 1

Sort (cost=8,958.54..8,970.97 rows=4,971 width=36) (actual time=210.545..243.627 rows=384,529 loops=1)

  • Sort Key: pa.plan_row_uuid
  • Sort Method: quicksort Memory: 5,007kB
32. 6.938 191.025 ↓ 8.9 44,428 1

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

33. 76.946 184.087 ↓ 8.9 44,428 1

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

  • Group Key: plan_rows_1.project_plan_id, plan_rows_1.epic_id, plan_rows_1.user_id, plan_roles.activity_id, COALESCE(plan_roles.start_date, '1980-01-01'::date), COALESCE(lag((plan_roles.start_date - 1), 1) OVER (?), '3000-01-01'::date)
34. 33.253 107.141 ↑ 1.0 44,581 1

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

35. 28.599 73.888 ↑ 1.0 44,581 1

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

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 5,023kB
36. 17.789 45.289 ↑ 1.0 44,581 1

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

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

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

38. 12.641 22.653 ↑ 1.0 48,106 1

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

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

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

40. 35.777 45.965 ↑ 1.0 59,679 1

Sort (cost=6,017.99..6,167.27 rows=59,712 width=12) (actual time=41.443..45.965 rows=59,679 loops=1)

  • Sort Key: non_working_intervals_by_dates.account_id, non_working_intervals_by_dates.date, non_working_intervals_by_dates.user_id
  • Sort Method: quicksort Memory: 4,336kB
41. 10.188 10.188 ↑ 1.0 59,712 1

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

42. 46.600 142.607 ↑ 15.5 37,180 1

Sort (cost=606,597.28..608,034.78 rows=575,000 width=44) (actual time=136.685..142.607 rows=37,180 loops=1)

  • Sort Key: summary_vacations_by_dates.account_id, summary_vacations_by_dates.date, summary_vacations_by_dates.user_id
  • Sort Method: quicksort Memory: 2,844kB
43. 4.596 96.007 ↑ 17.8 32,266 1

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

44. 35.745 91.411 ↑ 17.8 32,266 1

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

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
45. 12.842 55.666 ↑ 134.2 32,422 1

Hash Join (cost=138.05..493,700.50 rows=4,351,382 width=21) (actual time=1.673..55.666 rows=32,422 loops=1)

  • Hash Cond: (vacations.staff_membership_id = staff_memberships.id)
  • Join Filter: ((generate_series_1.generate_series >= staff_memberships.joined_at) AND ((staff_memberships.archived_at IS NULL) OR (generate_series_1.generate_series <= staff_memberships.archived_at)))
  • Rows Removed by Join Filter: 1,908
46. 16.848 41.185 ↑ 634.8 34,330 1

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

47. 2.545 2.545 ↑ 1.0 21,792 1

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

48. 21.792 21.792 ↑ 500.0 2 21,792

Function Scan on generate_series generate_series_1 (cost=0.01..10.01 rows=1,000 width=8) (actual time=0.001..0.001 rows=2 loops=21,792)

49. 0.803 1.639 ↑ 1.0 4,002 1

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

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

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

51. 33.230 289.595 ↓ 1.5 58,216 1

Hash Join (cost=1,215.85..62,092.86 rows=38,750 width=114) (actual time=6.762..289.595 rows=58,216 loops=1)

  • Hash Cond: (project_plans_1.project_id = projects_1_1.id)
  • Join Filter: (NOT (SubPlan 1))
  • Rows Removed by Join Filter: 770
52. 11.707 77.354 ↑ 1.3 58,986 1

Nested Loop (cost=987.08..61,446.50 rows=77,500 width=58) (actual time=4.642..77.354 rows=58,986 loops=1)

53. 1.628 27.622 ↑ 1.2 2,535 1

Hash Join (cost=987.08..2,546.49 rows=3,100 width=58) (actual time=4.625..27.622 rows=2,535 loops=1)

  • Hash Cond: (plan_items_1.project_plan_id = project_plans_1.id)
54. 11.628 24.556 ↓ 1.2 7,876 1

Hash Join (cost=785.05..2,327.21 rows=6,567 width=54) (actual time=3.154..24.556 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.plan_row_uuid = plan_rows_2.uuid)
55. 9.828 9.828 ↓ 1.0 55,664 1

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

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
56. 1.280 3.100 ↓ 1.0 5,762 1

Hash (cost=714.07..714.07 rows=5,678 width=24) (actual time=3.100..3.100 rows=5,762 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 366kB
57. 1.634 1.820 ↓ 1.0 5,762 1

Bitmap Heap Scan on plan_rows plan_rows_2 (cost=108.29..714.07 rows=5,678 width=24) (actual time=0.222..1.820 rows=5,762 loops=1)

  • Recheck Cond: (user_id IS NULL)
  • Heap Blocks: exact=430
58. 0.186 0.186 ↓ 1.0 5,762 1

Bitmap Index Scan on index_plan_rows_on_user_id (cost=0.00..106.88 rows=5,678 width=0) (actual time=0.186..0.186 rows=5,762 loops=1)

  • Index Cond: (user_id IS NULL)
59. 0.403 1.438 ↑ 1.0 2,851 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3,188
61. 38.025 38.025 ↑ 1.1 23 2,535

Function Scan on generate_series generate_series_2 (cost=0.01..18.76 rows=25 width=8) (actual time=0.006..0.015 rows=23 loops=2,535)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 9
62. 0.910 2.053 ↑ 1.0 5,812 1

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

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

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

64.          

SubPlan (for Hash Join)

65. 58.986 176.958 ↓ 0.0 0 58,986

Nested Loop (cost=0.69..16.74 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=58,986)

66. 58.986 58.986 ↑ 1.0 1 58,986

Index Scan using clients_pkey on clients clients_3 (cost=0.28..8.29 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=58,986)

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

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

  • Index Cond: ((account_id = projects_1_1.account_id) AND (office_id = clients_3.office_id) AND (date = generate_series_2.generate_series))
  • Heap Fetches: 770
68. 12,832.839 13,619.856 ↑ 1.0 703,961 1

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

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

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

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

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

71. 26.969 67.036 ↑ 1.0 44,581 1

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

  • Sort Key: plan_roles_1.plan_row_uuid, plan_roles_1.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 5,023kB
72. 16.338 40.067 ↑ 1.0 44,581 1

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

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

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

74. 10.547 19.276 ↑ 1.0 48,106 1

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

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

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

76. 205.283 582.443 ↑ 1.0 703,961 1

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

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 62,366kB
77. 260.561 377.160 ↑ 1.0 703,961 1

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

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

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

79. 6.112 18.359 ↑ 1.0 26,435 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,381kB
80. 8.019 12.247 ↑ 1.0 26,435 1

Hash Left Join (cost=202.03..1,496.14 rows=26,435 width=16) (actual time=1.557..12.247 rows=26,435 loops=1)

  • Hash Cond: (epics.project_id = general_epic_plans.project_id)
  • Join Filter: (epics.project_plan_id IS NULL)
  • Rows Removed by Join Filter: 11,144
81. 2.704 2.704 ↑ 1.0 26,435 1

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

82. 0.430 1.524 ↑ 1.0 2,851 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3,188
84. 758.171 1,187.330 ↓ 27.2 1,899,868 1

Hash (cost=1,970.92..1,970.92 rows=69,760 width=49) (actual time=1,187.330..1,187.330 rows=1,899,868 loops=1)

  • Buckets: 2,097,152 (originally 131072) Batches: 1 (originally 1) Memory Usage: 179,638kB
85. 409.975 429.159 ↓ 27.2 1,899,868 1

Hash Join (cost=1,015.49..1,970.92 rows=69,760 width=49) (actual time=17.835..429.159 rows=1,899,868 loops=1)

  • Hash Cond: (projects.account_id = accounts_1.id)
86. 1.369 1.369 ↑ 1.0 5,812 1

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

87. 1.688 17.815 ↑ 1.0 4,221 1

Hash (cost=962.37..962.37 rows=4,249 width=49) (actual time=17.815..17.815 rows=4,221 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 429kB
88. 1.611 16.127 ↑ 1.0 4,221 1

Hash Join (cost=866.11..962.37 rows=4,249 width=49) (actual time=13.051..16.127 rows=4,221 loops=1)

  • Hash Cond: (staff_memberships_1.account_id = accounts_1.id)
89. 1.095 14.377 ↑ 1.0 4,221 1

Unique (cost=847.14..889.63 rows=4,249 width=65) (actual time=12.896..14.377 rows=4,221 loops=1)

90. 3.506 13.282 ↑ 1.0 4,249 1

Sort (cost=847.14..857.76 rows=4,249 width=65) (actual time=12.895..13.282 rows=4,249 loops=1)

  • Sort Key: staff_memberships_1.account_id, staff_memberships_1.user_id, (COALESCE(staff_membership_activity_links.start_date, staff_memberships_1.joined_at)), (COALESCE(lag((staff_membership_activity_links.start_date - 1), 1) OVER (?), staff_memberships_1.archived_at, '3000-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 790kB
91. 3.459 9.776 ↑ 1.0 4,249 1

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

92. 2.351 6.317 ↑ 1.0 4,249 1

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

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

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

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

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

95. 1.034 1.924 ↑ 1.0 4,002 1

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

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

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

97. 0.056 0.139 ↑ 1.0 354 1

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

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

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

99. 10.757 293.252 ↑ 8.5 28,906 1

Subquery Scan on *SELECT* 2_1 (cost=3,354.66..490,424.84 rows=245,158 width=136) (actual time=34.577..293.252 rows=28,906 loops=1)

100. 162.676 282.495 ↑ 8.5 28,906 1

Hash Anti Join (cost=3,354.66..487,360.36 rows=245,158 width=143) (actual time=34.573..282.495 rows=28,906 loops=1)

  • Hash Cond: ((sa.account_id = non_working_intervals_by_dates_1.account_id) AND (sa.user_id = non_working_intervals_by_dates_1.user_id))
  • Join Filter: (generate_series_3.generate_series = non_working_intervals_by_dates_1.date)
  • Rows Removed by Join Filter: 1,153,172
101. 18.535 98.374 ↑ 9.5 29,394 1

Merge Join (cost=1,177.86..468,661.53 rows=280,181 width=64) (actual time=13.034..98.374 rows=29,394 loops=1)

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

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

103. 10.439 10.439 ↑ 1.0 21,792 1

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

104. 43.584 43.584 ↑ 25.0 1 21,792

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

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

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

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

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

107. 0.687 10.955 ↑ 1.0 4,221 1

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

108. 2.324 10.268 ↑ 1.0 4,249 1

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

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

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

110. 1.978 5.427 ↑ 1.0 4,249 1

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

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

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

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

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

113. 0.837 1.619 ↑ 1.0 4,002 1

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

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

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

115. 11.211 21.445 ↑ 1.0 58,975 1

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

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

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

117. 0.004 0.118 ↑ 9.8 12 1

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

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

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 342
119. 375.780 652.453 ↓ 3.8 1,036,294 1

Sort (cost=46,830.92..47,512.59 rows=272,669 width=44) (actual time=565.538..652.453 rows=1,036,294 loops=1)

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

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

121. 0.093 16.279 ↓ 9.7 474 1

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

122. 0.123 16.186 ↓ 9.7 474 1

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

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

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

124. 0.010 1.261 ↓ 5.0 5 1

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

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

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

126. 0.030 0.489 ↑ 23.0 5 1

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

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

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

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

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

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

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

130. 0.010 0.711 ↑ 1.0 5 1

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

131. 0.676 0.676 ↑ 1.0 5 1

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

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

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

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

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

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

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

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

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

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

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

137. 12.733 73.452 ↓ 2.6 91,600 1

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

138. 37.937 60.719 ↓ 2.6 91,600 1

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

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

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

140. 0.157 5.006 ↑ 1.1 657 1

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

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

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

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

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

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

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

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

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

145. 0.102 0.853 ↑ 1.0 623 1

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

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

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

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

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

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

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

149. 0.118 1.563 ↑ 1.1 450 1

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

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

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

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

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

152. 0.198 0.830 ↑ 1.0 489 1

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

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

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

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

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

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

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

156. 14.789 84.780 ↑ 2.2 94,593 1

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

157. 42.543 69.991 ↑ 2.2 94,593 1

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

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

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

159. 0.927 9.165 ↑ 1.0 4,004 1

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

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

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

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

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

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

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

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

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

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

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

165. 0.347 3.271 ↑ 1.0 1,820 1

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

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

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

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

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

168. 0.559 1.163 ↑ 1.0 1,821 1

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

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

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

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

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

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

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

172. 11.806 72.362 ↓ 2.4 76,366 1

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

173. 36.859 60.556 ↓ 2.4 76,366 1

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

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

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

175. 0.340 6.880 ↓ 2.4 1,591 1

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

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

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

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

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

178. 0.373 5.138 ↓ 2.4 1,591 1

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

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

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

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

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

181. 1.014 1.728 ↑ 1.0 2,429 1

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

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

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

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

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

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

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

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4,249
185. 42.015 42.102 ↓ 1,904.7 582,849 1

Sort (cost=22.77..23.53 rows=306 width=26) (actual time=0.165..42.102 rows=582,849 loops=1)

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

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

  • Filter: (((discountable_type)::text = 'User'::text) OR ((discountable_type)::text = 'Epic'::text))
  • Rows Removed by Filter: 50
187. 2,335.157 2,336.052 ↓ 11,405.0 34,762,408 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.978..2,336.052 rows=34,762,408 loops=1)

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

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

189. 2,357.450 2,358.084 ↓ 11,405.1 34,762,629 1

Sort (cost=244.86..252.48 rows=3,048 width=24) (actual time=1.366..2,358.084 rows=34,762,629 loops=1)

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

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

Planning time : 10.769 ms
Execution time : 39,136.437 ms