explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dtbI

Settings
# exclusive inclusive rows x rows loops node
1. 60,562.446 79,080.126 ↓ 10.7 846,452 1

Merge Left Join (cost=1,294,855.10..1,388,010.66 rows=79,356 width=164) (actual time=18,214.556..79,080.126 rows=846,452 loops=1)

  • Merge Cond: ((items.project_id = ""*SELECT* 1"".project_id) AND ((COALESCE(pa.activity_id, items.activity_id, staff_membership_activity_links.activity_id)) = ""*SELECT* 1"".activity_id))
  • Join Filter: ((items.date >= "*SELECT* 1".start_date) AND ((items.date <= "*SELECT* 1".end_date) OR ("*SELECT* 1".end_date IS NULL)))
  • Rows Removed by Join Filter: 244,719
2. 674.376 17,796.825 ↓ 10.7 846,452 1

Sort (cost=1,247,913.83..1,248,112.22 rows=79,356 width=644) (actual time=17,638.850..17,796.825 rows=846,452 loops=1)

  • Sort Key: items.project_id, (COALESCE(pa.activity_id, items.activity_id, staff_membership_activity_links.activity_id))
  • Sort Method: quicksort Memory: 143,609kB
3. 241.977 17,122.449 ↓ 10.7 846,452 1

Hash Join (cost=779,975.16..1,241,455.81 rows=79,356 width=644) (actual time=6,867.382..17,122.449 rows=846,452 loops=1)

  • Hash Cond: (items.account_id = accounts.id)
4. 138.973 16,880.354 ↓ 4.7 1,112,564 1

Append (cost=779,956.49..1,238,424.06 rows=238,067 width=644) (actual time=6,865.858..16,880.354 rows=1,112,564 loops=1)

5. 6,999.206 16,614.883 ↓ 4.6 1,095,353 1

Merge Left Join (cost=779,956.49..980,030.15 rows=238,066 width=103) (actual time=6,865.857..16,614.883 rows=1,095,353 loops=1)

  • Merge Cond: (items.project_plan_id = pa.project_plan_id)
  • Join Filter: ((items.date >= pa.start_date) AND (items.date <= pa.end_date) AND ((pa.plan_row_uuid = items.plan_row_uuid) OR (items.plan_row_uuid IS NULL)) AND ((pa.user_id = items.user_id) OR (items.user_id IS NULL)) AND ((pa.epic_id = items.epic_id) OR ((items.reportable_type = 'PlanItem'::text) AND (items.epic_id IS NULL)) OR ((items.reportable_type = 'TimeLog'::text) AND (pa.epic_id IS NULL))))
  • Rows Removed by Join Filter: 35,962,723
6. 1,237.120 6,868.348 ↓ 4.6 1,095,353 1

Sort (cost=770,889.61..771,484.77 rows=238,066 width=144) (actual time=6,656.984..6,868.348 rows=1,095,353 loops=1)

  • Sort Key: items.project_plan_id
  • Sort Method: external merge Disk: 115,368kB
7. 619.067 5,631.228 ↓ 4.6 1,095,353 1

Merge Left Join (cost=742,175.06..749,629.12 rows=238,066 width=144) (actual time=4,767.795..5,631.228 rows=1,095,353 loops=1)

  • Merge Cond: ((items.account_id = staff_memberships.account_id) AND (items.user_id = staff_memberships.user_id))
  • Join Filter: ((items.date >= (COALESCE(staff_membership_activity_links.start_date, staff_memberships.joined_at))) AND (items.date <= (COALESCE(lag((staff_membership_activity_links.start_date - 1), 1) OVER (?), staff_memberships.archived_at, '3000-01-01'::date))))
  • Rows Removed by Join Filter: 258,899
  • Filter: ((items.vacation_hours IS NULL) OR (items.vacation_hours < (COALESCE(staff_membership_activity_links.capacity, '8'::numeric))::double precision))
  • Rows Removed by Filter: 5,453
8. 1,258.380 4,903.631 ↓ 1.6 1,100,806 1

Sort (cost=741,327.92..743,095.73 rows=707,126 width=120) (actual time=4,754.475..4,903.631 rows=1,100,806 loops=1)

  • Sort Key: items.account_id, items.user_id
  • Sort Method: quicksort Memory: 184,058kB
9. 192.022 3,645.251 ↓ 1.6 1,100,806 1

Subquery Scan on items (cost=588,648.59..672,624.94 rows=707,126 width=120) (actual time=1,607.560..3,645.251 rows=1,100,806 loops=1)

10. 134.122 3,453.229 ↓ 1.6 1,100,806 1

Append (cost=588,648.59..665,553.68 rows=707,126 width=124) (actual time=1,607.558..3,453.229 rows=1,100,806 loops=1)

11.          

CTE items

12. 108.618 464.442 ↑ 1.6 405,261 1

Nested Loop (cost=2,125.79..504,914.10 rows=657,101 width=110) (actual time=24.529..464.442 rows=405,261 loops=1)

13. 10.235 87.651 ↓ 1.1 29,797 1

Hash Join (cost=2,125.78..3,875.34 rows=26,284 width=74) (actual time=24.503..87.651 rows=29,797 loops=1)

  • Hash Cond: (projects_5.client_id = clients_3.id)
14. 10.293 76.651 ↓ 1.1 29,797 1

Hash Join (cost=2,062.18..3,742.59 rows=26,284 width=70) (actual time=23.723..76.651 rows=29,797 loops=1)

  • Hash Cond: (project_plans.project_id = projects_5.id)
15. 14.046 63.750 ↓ 1.1 29,797 1

Hash Join (cost=1,833.41..3,444.77 rows=26,284 width=62) (actual time=21.082..63.750 rows=29,797 loops=1)

  • Hash Cond: (plan_items.plan_row_uuid = plan_rows_1.uuid)
16. 15.361 30.679 ↓ 1.1 29,797 1

Hash Join (cost=202.03..1,744.37 rows=26,284 width=50) (actual time=1.805..30.679 rows=29,797 loops=1)

  • Hash Cond: (plan_items.project_plan_id = project_plans.id)
17. 13.560 13.560 ↑ 1.0 55,664 1

Seq Scan on plan_items (cost=0.00..1,396.10 rows=55,675 width=46) (actual time=0.025..13.560 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
18. 0.512 1.758 ↑ 1.0 2,851 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3,188
20. 10.583 19.025 ↑ 1.0 48,106 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,117kB
21. 8.442 8.442 ↑ 1.0 48,106 1

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

22. 1.195 2.608 ↑ 1.0 5,812 1

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

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

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

24. 0.327 0.765 ↑ 1.0 1,849 1

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

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

Seq Scan on clients clients_3 (cost=0.00..40.49 rows=1,849 width=8) (actual time=0.005..0.438 rows=1,849 loops=1)

26. 268.173 268.173 ↑ 1.8 14 29,797

Function Scan on generate_series generate_series_1 (cost=0.01..18.76 rows=25 width=8) (actual time=0.004..0.009 rows=14 loops=29,797)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 5
27. 148.882 2,889.825 ↓ 120.8 396,845 1

Subquery Scan on *SELECT* 1_1 (cost=83,734.49..118,549.01 rows=3,286 width=124) (actual time=1,607.558..2,889.825 rows=396,845 loops=1)

28. 141.632 2,740.943 ↓ 120.8 396,845 1

Hash Left Join (cost=83,734.49..118,491.50 rows=3,286 width=141) (actual time=1,607.554..2,740.943 rows=396,845 loops=1)

  • Hash Cond: ((items_1.account_id = summary_vacations_by_dates.account_id) AND (items_1.date = summary_vacations_by_dates.date) AND (items_1.user_id = summary_vacations_by_dates.user_id))
  • Join Filter: (items_1.user_id IS NOT NULL)
29. 568.833 2,586.911 ↓ 120.8 396,845 1

Merge Left Join (cost=82,655.17..115,169.48 rows=3,286 width=128) (actual time=1,595.118..2,586.911 rows=396,845 loops=1)

  • Merge Cond: ((items_1.account_id = nw_intervals_by_dates_roles.account_id) AND (items_1.date = nw_intervals_by_dates_roles.date))
  • Join Filter: ((items_1.user_id IS NULL) AND (nw_intervals_by_dates_roles.office_id = items_1.office_id))
  • Rows Removed by Join Filter: 2,833,206
  • Filter: (COALESCE(non_working_intervals_by_dates.id, nw_intervals_by_dates_roles.id) IS NULL)
  • Rows Removed by Filter: 98,754
30. 151.079 1,798.198 ↑ 1.6 405,337 1

Merge Left Join (cost=82,654.88..89,830.45 rows=657,101 width=128) (actual time=1,595.084..1,798.198 rows=405,337 loops=1)

  • Merge Cond: ((items_1.account_id = non_working_intervals_by_dates.account_id) AND (items_1.date = non_working_intervals_by_dates.date) AND (items_1.user_id = non_working_intervals_by_dates.user_id))
  • Join Filter: (items_1.user_id IS NOT NULL)
31. 830.408 1,597.373 ↑ 1.6 405,261 1

Sort (cost=76,636.89..78,279.64 rows=657,101 width=124) (actual time=1,551.547..1,597.373 rows=405,261 loops=1)

  • Sort Key: items_1.account_id, items_1.date, items_1.user_id
  • Sort Method: quicksort Memory: 69,278kB
32. 766.965 766.965 ↑ 1.6 405,261 1

CTE Scan on items items_1 (cost=0.00..13,142.02 rows=657,101 width=124) (actual time=24.532..766.965 rows=405,261 loops=1)

33. 38.358 49.746 ↓ 1.0 62,049 1

Sort (cost=6,017.99..6,167.27 rows=59,712 width=16) (actual time=43.528..49.746 rows=62,049 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
34. 11.388 11.388 ↑ 1.0 59,712 1

Seq Scan on non_working_intervals_by_dates (cost=0.00..1,281.12 rows=59,712 width=16) (actual time=0.020..11.388 rows=59,712 loops=1)

35. 207.666 219.880 ↓ 49.3 2,946,721 1

Materialize (cost=0.29..3,349.01 rows=59,712 width=16) (actual time=0.028..219.880 rows=2,946,721 loops=1)

36. 12.214 12.214 ↑ 1.0 59,679 1

Index Scan using nw_account_id_date on non_working_intervals_by_dates nw_intervals_by_dates_roles (cost=0.29..3,199.73 rows=59,712 width=16) (actual time=0.018..12.214 rows=59,679 loops=1)

37. 7.604 12.400 ↑ 1.0 32,266 1

Hash (cost=514.66..514.66 rows=32,266 width=17) (actual time=12.400..12.400 rows=32,266 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,784kB
38. 4.796 4.796 ↑ 1.0 32,266 1

Seq Scan on summary_vacations_by_dates_m summary_vacations_by_dates (cost=0.00..514.66 rows=32,266 width=17) (actual time=0.016..4.796 rows=32,266 loops=1)

39. 301.167 429.282 ↓ 1.0 703,961 1

Hash Join (cost=2,124.60..31,516.54 rows=703,840 width=124) (actual time=29.619..429.282 rows=703,961 loops=1)

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

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

41. 7.110 29.433 ↓ 1.0 26,434 1

Hash (cost=1,794.22..1,794.22 rows=26,430 width=20) (actual time=29.433..29.433 rows=26,434 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,484kB
42. 6.725 22.323 ↓ 1.0 26,434 1

Hash Left Join (cost=430.80..1,794.22 rows=26,430 width=20) (actual time=3.973..22.323 rows=26,434 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
43. 8.998 13.623 ↓ 1.0 26,434 1

Hash Join (cost=228.77..917.57 rows=26,430 width=16) (actual time=1.971..13.623 rows=26,434 loops=1)

  • Hash Cond: (epics.project_id = projects.id)
44. 2.700 2.700 ↑ 1.0 26,435 1

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

45. 1.000 1.925 ↑ 1.0 5,812 1

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

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

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

47. 0.630 1.975 ↑ 1.0 2,851 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 144kB
48. 1.345 1.345 ↑ 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.009..1.345 rows=2,851 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3,188
49. 93.022 108.530 ↓ 295.4 1,255,026 1

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

50. 1.596 15.508 ↑ 1.0 4,218 1

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

51. 3.682 13.912 ↑ 1.0 4,246 1

Sort (cost=847.14..857.76 rows=4,249 width=65) (actual time=13.290..13.912 rows=4,246 loops=1)

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

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

53. 2.470 6.816 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=56) (actual time=6.475..6.816 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
54. 1.638 4.346 ↑ 1.0 4,249 1

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

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships.id)
55. 0.423 0.423 ↑ 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.019..0.423 rows=4,256 loops=1)

56. 1.281 2.285 ↑ 1.0 4,002 1

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

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

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

58. 2,556.092 2,747.329 ↓ 7,470.4 36,911,275 1

Sort (cost=9,066.88..9,079.24 rows=4,941 width=48) (actual time=208.852..2,747.329 rows=36,911,275 loops=1)

  • Sort Key: pa.project_plan_id
  • Sort Method: quicksort Memory: 5,645kB
59. 6.654 191.237 ↓ 9.0 44,428 1

Subquery Scan on pa (cost=8,640.22..8,763.74 rows=4,941 width=48) (actual time=141.701..191.237 rows=44,428 loops=1)

60. 77.037 184.583 ↓ 9.0 44,428 1

HashAggregate (cost=8,640.22..8,714.33 rows=4,941 width=48) (actual time=141.699..184.583 rows=44,428 loops=1)

  • Group Key: plan_rows.project_plan_id, plan_rows.epic_id, plan_rows.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)
61. 35.422 107.546 ↑ 1.0 44,581 1

WindowAgg (cost=6,076.81..7,079.88 rows=44,581 width=68) (actual time=65.409..107.546 rows=44,581 loops=1)

62. 27.503 72.124 ↑ 1.0 44,581 1

Sort (cost=6,076.81..6,188.26 rows=44,581 width=60) (actual time=65.398..72.124 rows=44,581 loops=1)

  • Sort Key: plan_roles.plan_row_uuid, plan_roles.start_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 7,154kB
63. 18.203 44.621 ↑ 1.0 44,581 1

Hash Join (cost=1,631.38..2,634.23 rows=44,581 width=60) (actual time=22.477..44.621 rows=44,581 loops=1)

  • Hash Cond: (plan_roles.plan_row_uuid = plan_rows.uuid)
64. 4.042 4.042 ↑ 1.0 44,581 1

Seq Scan on plan_roles (cost=0.00..885.81 rows=44,581 width=32) (actual time=0.009..4.042 rows=44,581 loops=1)

65. 12.767 22.376 ↑ 1.0 48,106 1

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

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

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

67. 5.584 126.498 ↓ 17,211.0 17,211 1

Subquery Scan on *SELECT* 2_1 (cost=247,975.87..254,822.92 rows=1 width=103) (actual time=84.255..126.498 rows=17,211 loops=1)

68. 19.498 120.914 ↓ 17,211.0 17,211 1

Merge Right Join (cost=247,975.87..254,822.90 rows=1 width=110) (actual time=84.248..120.914 rows=17,211 loops=1)

  • Merge Cond: ((non_working_intervals_by_dates_1.account_id = staff_memberships_1.account_id) AND (non_working_intervals_by_dates_1.date = generate_series.generate_series) AND (non_working_intervals_by_dates_1.user_id = staff_memberships_1.user_id))
  • Filter: (non_working_intervals_by_dates_1.id IS NULL)
  • Rows Removed by Filter: 389
69. 15.574 15.574 ↑ 1.0 59,663 1

Index Scan using account_id_user_id on non_working_intervals_by_dates non_working_intervals_by_dates_1 (cost=0.41..3,467.85 rows=59,712 width=16) (actual time=0.019..15.574 rows=59,663 loops=1)

70. 32.070 85.842 ↑ 16.4 17,600 1

Sort (cost=247,975.46..248,698.42 rows=289,183 width=64) (actual time=84.216..85.842 rows=17,600 loops=1)

  • Sort Key: staff_memberships_1.account_id, generate_series.generate_series, staff_memberships_1.user_id
  • Sort Method: quicksort Memory: 3,244kB
71. 3.217 53.772 ↑ 16.4 17,600 1

Nested Loop (cost=1,253.91..221,744.21 rows=289,183 width=64) (actual time=20.346..53.772 rows=17,600 loops=1)

72. 6.111 28.515 ↑ 1.0 11,020 1

Hash Join (cost=1,253.91..1,952.21 rows=11,568 width=64) (actual time=20.333..28.515 rows=11,020 loops=1)

  • Hash Cond: (vacations.staff_membership_id = staff_memberships_1.id)
73. 2.093 2.093 ↑ 1.0 21,792 1

Seq Scan on vacations (cost=0.00..500.92 rows=21,792 width=44) (actual time=0.008..2.093 rows=21,792 loops=1)

74. 0.480 20.311 ↑ 1.3 1,592 1

Hash (cost=1,227.36..1,227.36 rows=2,124 width=32) (actual time=20.311..20.311 rows=1,592 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 134kB
75. 0.500 19.831 ↑ 1.3 1,592 1

Hash Join (cost=1,107.35..1,227.36 rows=2,124 width=32) (actual time=18.167..19.831 rows=1,592 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.staff_membership_id = staff_memberships_1.id)
76. 0.858 17.175 ↑ 1.3 1,592 1

Hash Join (cost=969.31..1,083.71 rows=2,128 width=16) (actual time=15.983..17.175 rows=1,592 loops=1)

  • Hash Cond: (staff_membership_activity_links_1.id = staff_activities_with_dates.link_id)
77. 0.358 0.358 ↑ 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.008..0.358 rows=4,256 loops=1)

78. 0.336 15.959 ↓ 8.7 1,592 1

Hash (cost=967.01..967.01 rows=184 width=4) (actual time=15.959..15.959 rows=1,592 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 72kB
79. 0.627 15.623 ↓ 8.7 1,592 1

HashAggregate (cost=965.17..967.01 rows=184 width=4) (actual time=15.348..15.623 rows=1,592 loops=1)

  • Group Key: staff_activities_with_dates.link_id
80. 1.378 14.996 ↓ 3.4 1,592 1

Subquery Scan on staff_activities_with_dates (cost=836.52..963.99 rows=472 width=4) (actual time=12.485..14.996 rows=1,592 loops=1)

  • Filter: ((CURRENT_DATE >= staff_activities_with_dates.start_date) AND (CURRENT_DATE <= staff_activities_with_dates.end_date))
  • Rows Removed by Filter: 2,629
81. 0.838 13.618 ↑ 1.0 4,221 1

Unique (cost=836.52..879.01 rows=4,249 width=613) (actual time=12.476..13.618 rows=4,221 loops=1)

82. 3.831 12.780 ↑ 1.0 4,249 1

Sort (cost=836.52..847.14 rows=4,249 width=613) (actual time=12.475..12.780 rows=4,249 loops=1)

  • Sort Key: staff_memberships_2.account_id, staff_memberships_2.user_id, (COALESCE(staff_membership_activity_links_2.start_date, staff_memberships_2.joined_at)), (COALESCE(lag((staff_membership_activity_links_2.start_date - 1), 1) OVER (?), staff_memberships_2.archived_at, '3000-01-01'::date)) DESC
  • Sort Method: quicksort Memory: 524kB
83. 3.774 8.949 ↑ 1.0 4,249 1

WindowAgg (cost=484.85..580.45 rows=4,249 width=613) (actual time=4.778..8.949 rows=4,249 loops=1)

84. 1.806 5.175 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=28) (actual time=4.766..5.175 rows=4,249 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: 524kB
85. 1.233 3.369 ↑ 1.0 4,249 1

Hash Join (cost=138.05..228.79 rows=4,249 width=28) (actual time=1.788..3.369 rows=4,249 loops=1)

  • Hash Cond: (staff_membership_activity_links_2.staff_membership_id = staff_memberships_2.id)
86. 0.380 0.380 ↑ 1.0 4,256 1

Seq Scan on staff_membership_activity_links staff_membership_activity_links_2 (cost=0.00..79.56 rows=4,256 width=12) (actual time=0.002..0.380 rows=4,256 loops=1)

87. 0.870 1.756 ↑ 1.0 4,002 1

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

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

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

89. 1.072 2.156 ↑ 1.0 4,002 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 220kB
90. 1.084 1.084 ↑ 1.0 4,002 1

Seq Scan on staff_memberships staff_memberships_1 (cost=0.00..88.02 rows=4,002 width=16) (actual time=0.005..1.084 rows=4,002 loops=1)

91. 22.040 22.040 ↑ 12.5 2 11,020

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.001..0.002 rows=2 loops=11,020)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 0
92. 0.004 0.118 ↑ 11.8 10 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
93. 0.114 0.114 ↑ 11.8 10 1

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

  • Filter: ((suspended_at)::date >= CURRENT_DATE)
  • Rows Removed by Filter: 344
94. 437.917 720.855 ↓ 4.3 1,166,317 1

Sort (cost=46,941.27..47,625.25 rows=273,590 width=44) (actual time=574.661..720.855 rows=1,166,317 loops=1)

  • Sort Key: ""*SELECT* 1"".project_id, ""*SELECT* 1"".activity_id
  • Sort Method: quicksort Memory: 32,838kB
95. 30.912 282.938 ↑ 1.0 263,033 1

Append (cost=294.94..22,233.83 rows=273,590 width=44) (actual time=1.987..282.938 rows=263,033 loops=1)

96. 0.098 15.911 ↓ 9.7 474 1

Subquery Scan on *SELECT* 1 (cost=294.94..474.15 rows=49 width=44) (actual time=1.986..15.911 rows=474 loops=1)

97. 0.136 15.813 ↓ 9.7 474 1

Merge Join (cost=294.94..473.66 rows=49 width=612) (actual time=1.984..15.813 rows=474 loops=1)

  • Merge Cond: (clients.brand_id = brands.id)
98. 0.150 15.667 ↓ 9.7 474 1

Nested Loop (cost=294.80..1,672.00 rows=49 width=52) (actual time=1.971..15.667 rows=474 loops=1)

99. 0.009 1.287 ↓ 5.0 5 1

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

  • Merge Cond: (rate_cards.rateable_id = clients.brand_id)
100. 0.025 0.498 ↑ 28.8 4 1

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

101. 0.030 0.473 ↑ 23.0 5 1

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

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

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

  • Filter: ((rateable_type)::text = 'Brand'::text)
  • Rows Removed by Filter: 4,739
103. 0.006 0.780 ↑ 1.0 5 1

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

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

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

105. 0.007 0.753 ↑ 1.0 5 1

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

106. 0.726 0.726 ↑ 1.0 5 1

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

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

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

  • Index Cond: (id = pricing_models.project_id)
108. 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_1.client_id)
109. 14.230 14.230 ↓ 1.9 95 5

Index Scan using index_rates_on_activity_id_and_rate_card_id on rates (cost=0.42..1,376.84 rows=49 width=28) (actual time=0.645..2.846 rows=95 loops=5)

  • Index Cond: (rate_card_id = rate_cards.id)
110. 0.010 0.010 ↑ 38.3 3 1

Index Only Scan using brands_pkey on brands (cost=0.14..9.87 rows=115 width=4) (actual time=0.009..0.010 rows=3 loops=1)

  • Heap Fetches: 0
111. 13.697 77.135 ↓ 2.6 91,600 1

Subquery Scan on *SELECT* 2 (cost=583.65..5,487.33 rows=35,359 width=44) (actual time=12.305..77.135 rows=91,600 loops=1)

112. 42.774 63.438 ↓ 2.6 91,600 1

Hash Join (cost=583.65..5,133.74 rows=35,359 width=612) (actual time=12.304..63.438 rows=91,600 loops=1)

  • Hash Cond: (rates_1.rate_card_id = rate_cards_with_dates.id)
113. 15.911 15.911 ↑ 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.023..15.911 rows=174,437 loops=1)

114. 0.172 4.753 ↑ 1.1 657 1

Hash (cost=574.65..574.65 rows=720 width=24) (actual time=4.753..4.753 rows=657 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 47kB
115. 0.149 4.581 ↑ 1.1 657 1

Hash Join (cost=377.68..574.65 rows=720 width=24) (actual time=2.774..4.581 rows=657 loops=1)

  • Hash Cond: (clients_1.office_id = rate_cards_with_dates.rateable_id)
116. 0.145 3.308 ↑ 1.0 623 1

Hash Join (cost=227.94..415.36 rows=623 width=12) (actual time=1.646..3.308 rows=623 loops=1)

  • Hash Cond: (clients_1.office_id = offices.id)
117. 0.186 3.037 ↑ 1.0 623 1

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

  • Hash Cond: (projects_2.client_id = clients_1.id)
118. 0.869 2.235 ↑ 1.0 623 1

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

  • Hash Cond: (projects_2.id = pricing_models_1.project_id)
119. 0.502 0.502 ↑ 1.0 5,812 1

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

120. 0.107 0.864 ↑ 1.0 623 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
121. 0.757 0.757 ↑ 1.0 623 1

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

  • Filter: ((rates_type)::text = 'office'::text)
  • Rows Removed by Filter: 5,187
122. 0.296 0.616 ↑ 1.0 1,849 1

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

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

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

124. 0.069 0.126 ↑ 1.0 441 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
125. 0.057 0.057 ↑ 1.0 441 1

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

126. 0.083 1.124 ↑ 1.0 489 1

Hash (cost=143.63..143.63 rows=489 width=24) (actual time=1.124..1.124 rows=489 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
127. 0.064 1.041 ↑ 1.0 489 1

Subquery Scan on rate_cards_with_dates (cost=126.52..143.63 rows=489 width=24) (actual time=0.654..1.041 rows=489 loops=1)

128. 0.300 0.977 ↑ 1.0 489 1

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

129. 0.163 0.677 ↑ 1.0 489 1

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

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

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

  • Filter: ((rateable_type)::text = 'Office'::text)
  • Rows Removed by Filter: 4,365
131. 14.485 82.468 ↑ 2.2 94,593 1

Subquery Scan on *SELECT* 3 (cost=834.13..9,370.95 rows=206,113 width=44) (actual time=10.714..82.468 rows=94,593 loops=1)

132. 42.393 67.983 ↑ 2.2 94,593 1

Hash Join (cost=834.13..7,309.82 rows=206,113 width=612) (actual time=10.713..67.983 rows=94,593 loops=1)

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

134. 0.918 10.639 ↑ 1.0 4,004 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 299kB
135. 1.111 9.721 ↑ 1.0 4,004 1

Hash Join (cost=580.30..781.67 rows=4,197 width=24) (actual time=6.616..9.721 rows=4,004 loops=1)

  • Hash Cond: (projects_3.client_id = clients_2.id)
136. 1.085 4.258 ↑ 1.0 3,620 1

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

  • Hash Cond: (pricing_models_2.project_id = projects_3.id)
137. 0.967 0.967 ↑ 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.967 rows=3,621 loops=1)

  • Filter: ((rates_type)::text = 'client'::text)
  • Rows Removed by Filter: 2,189
138. 0.941 2.206 ↑ 1.0 5,812 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 292kB
139. 1.265 1.265 ↑ 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..1.265 rows=5,812 loops=1)

140. 0.515 4.352 ↑ 1.0 1,820 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 123kB
141. 0.757 3.837 ↑ 1.0 1,820 1

Merge Join (cost=203.56..328.77 rows=1,821 width=28) (actual time=1.247..3.837 rows=1,820 loops=1)

  • Merge Cond: (rate_cards_2.rateable_id = clients_2.id)
142. 1.429 2.777 ↑ 1.0 1,821 1

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

143. 0.710 1.348 ↑ 1.0 1,821 1

Sort (cost=203.29..207.84 rows=1,821 width=29) (actual time=1.196..1.348 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
144. 0.638 0.638 ↑ 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.010..0.638 rows=1,821 loops=1)

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

Index Only Scan using clients_pkey on clients clients_2 (cost=0.28..60.01 rows=1,849 width=4) (actual time=0.034..0.303 rows=1,849 loops=1)

  • Heap Fetches: 0
146. 11.884 76.512 ↓ 2.4 76,366 1

Subquery Scan on *SELECT* 4 (cost=695.56..5,533.45 rows=32,069 width=44) (actual time=8.035..76.512 rows=76,366 loops=1)

147. 40.880 64.628 ↓ 2.4 76,366 1

Hash Join (cost=695.56..5,212.76 rows=32,069 width=612) (actual time=8.033..64.628 rows=76,366 loops=1)

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

149. 0.370 8.009 ↓ 2.4 1,591 1

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

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
150. 1.088 7.639 ↓ 2.4 1,591 1

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

  • Hash Cond: (projects_4.id = pricing_models_3.project_id)
151. 0.500 0.500 ↑ 1.0 5,812 1

Seq Scan on projects projects_4 (cost=0.00..156.12 rows=5,812 width=4) (actual time=0.003..0.500 rows=5,812 loops=1)

152. 0.390 6.051 ↓ 2.4 1,591 1

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

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 110kB
153. 0.556 5.661 ↓ 2.4 1,591 1

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

  • Hash Cond: (rate_cards_3.rateable_id = pricing_models_3.id)
154. 1.749 4.100 ↑ 1.0 2,429 1

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

155. 1.326 2.351 ↑ 1.0 2,429 1

Sort (cost=241.26..247.33 rows=2,429 width=29) (actual time=2.161..2.351 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
156. 1.025 1.025 ↑ 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.005..1.025 rows=2,429 loops=1)

  • Filter: ((rateable_type)::text = 'PricingModel'::text)
  • Rows Removed by Filter: 2,425
157. 0.240 1.005 ↑ 1.0 1,561 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 77kB
158. 0.765 0.765 ↑ 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.007..0.765 rows=1,561 loops=1)

  • Filter: ((rates_type)::text = 'custom'::text)
  • Rows Removed by Filter: 4,249
Planning time : 7.509 ms
Execution time : 79,293.717 ms