explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P8AH : Optimization for: plan #65rH

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 618.025 35,198.560 ↓ 2.7 1,583,781 1

Hash Left Join (cost=2,410,491.52..2,454,082.00 rows=590,704 width=108) (actual time=33,585.157..35,198.560 rows=1,583,781 loops=1)

  • Hash Cond: (projects.account_id = accounts_1.id)
  • Filter: ((((""*SELECT* 1"".vacation_user_id)::double precision) IS NULL) OR (((""*SELECT* 1"".vacation_hours)::double precision) < (COALESCE(staff_membership_activity_links.capacity, accounts_1.default_capacity))::double precision))
  • Rows Removed by Filter: 13,596
2. 628.858 34,580.377 ↑ 1.1 1,597,377 1

Merge Left Join (cost=2,410,472.56..2,427,225.99 rows=1,754,565 width=144) (actual time=33,584.973..34,580.377 rows=1,597,377 loops=1)

  • Merge Cond: ((projects.account_id = staff_memberships_1.account_id) AND ("*SELECT* 1".user_id = staff_memberships_1.user_id))
  • Join Filter: ((""*SELECT* 1"".date >= (COALESCE(staff_membership_activity_links.start_date, staff_memberships_1.joined_at))) AND (""*SELECT* 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: 258,899
3. 1,764.055 33,856.133 ↑ 1.1 1,597,377 1

Sort (cost=2,409,625.42..2,414,011.83 rows=1,754,565 width=120) (actual time=33,572.040..33,856.133 rows=1,597,377 loops=1)

  • Sort Key: projects.account_id, ""*SELECT* 1"".user_id
  • Sort Method: external merge Disk: 135,008kB
4. 376.857 32,092.078 ↑ 1.1 1,597,377 1

Hash Left Join (cost=1,703,355.02..2,119,706.50 rows=1,754,565 width=120) (actual time=2,144.953..32,092.078 rows=1,597,377 loops=1)

  • Hash Cond: ("*SELECT* 1".project_id = projects.id)
5. 184.732 31,713.534 ↑ 1.1 1,597,377 1

Append (cost=1,703,126.25..2,097,322.98 rows=1,754,565 width=116) (actual time=2,143.205..31,713.534 rows=1,597,377 loops=1)

6. 264.860 19,171.952 ↑ 1.2 893,416 1

Result (cost=1,703,126.25..1,897,849.97 rows=1,050,604 width=116) (actual time=2,143.204..19,171.952 rows=893,416 loops=1)

7. 96.370 18,907.092 ↑ 1.2 893,416 1

Append (cost=1,703,126.25..1,884,717.42 rows=1,050,604 width=140) (actual time=2,143.199..18,907.092 rows=893,416 loops=1)

8. 105.996 2,813.833 ↑ 1.5 661,732 1

Subquery Scan on *SELECT* 1 (cost=1,703,126.25..1,746,459.66 rows=968,516 width=114) (actual time=2,143.199..2,813.833 rows=661,732 loops=1)

9. 158.698 2,707.837 ↑ 1.5 661,732 1

Merge Left Join (cost=1,703,126.25..1,731,931.92 rows=968,516 width=134) (actual time=2,143.197..2,707.837 rows=661,732 loops=1)

  • Merge Cond: ((generate_series.generate_series = summary_vacations_by_dates.date) AND (projects_1.account_id = summary_vacations_by_dates.account_id) AND (plan_rows.user_id = summary_vacations_by_dates.user_id))
10. 183.414 2,436.698 ↑ 1.5 661,732 1

Merge Anti Join (cost=1,096,528.98..1,109,838.36 rows=968,516 width=66) (actual time=2,143.193..2,436.698 rows=661,732 loops=1)

  • Merge Cond: ((generate_series.generate_series = non_working_intervals_by_dates.date) AND (projects_1.account_id = non_working_intervals_by_dates.account_id) AND (plan_rows.user_id = non_working_intervals_by_dates.user_id))
11. 1,110.641 2,213.796 ↑ 1.8 669,378 1

Sort (cost=1,090,510.98..1,093,577.98 rows=1,226,800 width=66) (actual time=2,143.189..2,213.796 rows=669,378 loops=1)

  • Sort Key: generate_series.generate_series, projects_1.account_id, plan_rows.user_id
  • Sort Method: quicksort Memory: 118,708kB
12. 247.383 1,103.155 ↑ 1.8 669,378 1

Merge Left Join (cost=15,960.75..966,441.82 rows=1,226,800 width=66) (actual time=238.189..1,103.155 rows=669,378 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: 328,283
13. 122.523 632.196 ↑ 1.8 669,378 1

Nested Loop (cost=6,506.23..943,392.38 rows=1,226,800 width=70) (actual time=66.394..632.196 rows=669,378 loops=1)

14. 23.378 127.369 ↑ 1.0 47,788 1

Merge Join (cost=6,506.22..11,024.38 rows=49,072 width=70) (actual time=66.378..127.369 rows=47,788 loops=1)

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

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

  • Filter: (user_id IS NOT NULL)
  • Rows Removed by Filter: 5,762
16. 34.873 74.926 ↓ 1.0 55,664 1

Sort (cost=6,504.63..6,643.72 rows=55,639 width=58) (actual time=66.351..74.926 rows=55,664 loops=1)

  • Sort Key: plan_items.plan_row_uuid
  • Sort Method: quicksort Memory: 9,364kB
17. 11.478 40.053 ↓ 1.0 55,664 1

Hash Left Join (cost=430.80..2,119.21 rows=55,639 width=58) (actual time=3.714..40.053 rows=55,664 loops=1)

  • Hash Cond: (project_plans.project_id = projects_1.id)
18. 14.542 26.542 ↓ 1.0 55,664 1

Hash Left Join (cost=202.03..1,744.28 rows=55,639 width=54) (actual time=1.637..26.542 rows=55,664 loops=1)

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

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

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
20. 0.443 1.608 ↑ 1.0 2,851 1

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

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

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

  • Filter: active
  • Rows Removed by Filter: 3,188
22. 1.040 2.033 ↑ 1.0 5,812 1

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

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

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

24. 382.304 382.304 ↑ 1.8 14 47,788

Function Scan on generate_series (cost=0.01..18.76 rows=25 width=8) (actual time=0.003..0.008 rows=14 loops=47,788)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 5
25. 68.784 223.576 ↓ 141.0 700,994 1

Sort (cost=9,454.52..9,466.95 rows=4,971 width=36) (actual time=171.788..223.576 rows=700,994 loops=1)

  • Sort Key: pa.plan_row_uuid
  • Sort Method: quicksort Memory: 5,007kB
26. 5.645 154.792 ↓ 8.9 44,428 1

Subquery Scan on pa (cost=9,025.04..9,149.32 rows=4,971 width=36) (actual time=112.313..154.792 rows=44,428 loops=1)

27. 62.887 149.147 ↓ 8.9 44,428 1

HashAggregate (cost=9,025.04..9,099.61 rows=4,971 width=48) (actual time=112.312..149.147 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)
28. 27.329 86.260 ↑ 1.0 44,581 1

WindowAgg (cost=6,572.98..7,576.09 rows=44,583 width=68) (actual time=54.401..86.260 rows=44,581 loops=1)

29. 22.100 58.931 ↑ 1.0 44,581 1

Sort (cost=6,572.98..6,684.43 rows=44,583 width=52) (actual time=54.391..58.931 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
30. 15.075 36.831 ↑ 1.0 44,581 1

Hash Join (cost=1,631.38..3,130.23 rows=44,583 width=52) (actual time=17.945..36.831 rows=44,581 loops=1)

  • Hash Cond: (plan_roles.plan_row_uuid = plan_rows_1.uuid)
31. 4.058 4.058 ↑ 1.0 44,583 1

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

32. 9.735 17.698 ↑ 1.0 48,106 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,180kB
33. 7.963 7.963 ↑ 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.006..7.963 rows=48,106 loops=1)

34. 29.719 39.488 ↑ 1.0 59,712 1

Sort (cost=6,017.99..6,167.27 rows=59,712 width=12) (actual time=35.205..39.488 rows=59,712 loops=1)

  • Sort Key: non_working_intervals_by_dates.date, non_working_intervals_by_dates.account_id, non_working_intervals_by_dates.user_id
  • Sort Method: quicksort Memory: 4,336kB
35. 9.769 9.769 ↑ 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.016..9.769 rows=59,712 loops=1)

36. 28.910 112.441 ↑ 15.5 37,179 1

Sort (cost=606,597.28..608,034.78 rows=575,000 width=44) (actual time=107.082..112.441 rows=37,179 loops=1)

  • Sort Key: summary_vacations_by_dates.date, summary_vacations_by_dates.account_id, summary_vacations_by_dates.user_id
  • Sort Method: quicksort Memory: 2,844kB
37. 4.138 83.531 ↑ 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=62.591..83.531 rows=32,266 loops=1)

38. 31.843 79.393 ↑ 17.8 32,266 1

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

  • Group Key: generate_series_1.generate_series, staff_memberships.user_id, staff_memberships.account_id
39. 10.954 47.550 ↑ 134.2 32,422 1

Hash Join (cost=138.05..493,700.50 rows=4,351,382 width=21) (actual time=1.535..47.550 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
40. 11.011 35.095 ↑ 634.8 34,330 1

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

41. 2.292 2.292 ↑ 1.0 21,792 1

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

42. 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)

43. 0.778 1.501 ↑ 1.0 4,002 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 229kB
44. 0.723 0.723 ↑ 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.723 rows=4,002 loops=1)

45. 176.543 15,996.889 ↓ 2.8 231,684 1

Hash Left Join (cost=2,062.19..132,183.85 rows=82,088 width=114) (actual time=20.748..15,996.889 rows=231,684 loops=1)

  • Hash Cond: (project_plans_1.project_id = projects_1_1.id)
  • Filter: (NOT (SubPlan 1))
  • Rows Removed by Filter: 770
46. 47.848 243.840 ↓ 1.4 232,454 1

Nested Loop (cost=1,833.42..128,784.81 rows=164,175 width=58) (actual time=18.601..243.840 rows=232,454 loops=1)

47. 2.719 54.224 ↓ 1.2 7,876 1

Hash Left Join (cost=1,833.41..4,011.80 rows=6,567 width=58) (actual time=18.573..54.224 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.project_plan_id = project_plans_1.id)
48. 22.189 50.034 ↓ 1.2 7,876 1

Hash Left Join (cost=1,631.38..3,792.52 rows=6,567 width=54) (actual time=17.075..50.034 rows=7,876 loops=1)

  • Hash Cond: (plan_items_1.plan_row_uuid = plan_rows_2.uuid)
  • Filter: (plan_rows_2.user_id IS NULL)
  • Rows Removed by Filter: 47,788
49. 10.843 10.843 ↓ 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.016..10.843 rows=55,664 loops=1)

  • Filter: (utilization > '0'::numeric)
  • Rows Removed by Filter: 264
50. 9.165 17.002 ↑ 1.0 48,106 1

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

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

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

52. 0.422 1.471 ↑ 1.0 2,851 1

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

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

  • Filter: active
  • Rows Removed by Filter: 3,188
54. 141.768 141.768 ↓ 1.2 30 7,876

Function Scan on generate_series generate_series_2 (cost=0.01..18.76 rows=25 width=8) (actual time=0.007..0.018 rows=30 loops=7,876)

  • Filter: (date_part('dow'::text, generate_series) = ANY ('{1,2,3,4,5}'::double precision[]))
  • Rows Removed by Filter: 12
55. 0.956 2.088 ↑ 1.0 5,812 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 314kB
56. 1.132 1.132 ↑ 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.005..1.132 rows=5,812 loops=1)

57.          

SubPlan (for Hash Left Join)

58. 120.086 15,574.418 ↓ 0.0 0 232,454

Nested Loop (cost=0.69..90.49 rows=1 width=0) (actual time=0.067..0.067 rows=0 loops=232,454)

  • Join Filter: (non_working_intervals_by_dates_1.office_id = clients.office_id)
  • Rows Removed by Join Filter: 1
59. 0.000 0.000 ↓ 0.0 0 232,454

Index Scan using clients_pkey on clients (cost=0.28..8.29 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=232,454)

  • Index Cond: (projects_1_1.client_id = id)
60. 15,454.332 15,454.332 ↑ 3.7 3 58,986

Index Scan using non_working_intervals_by_dates_idx on non_working_intervals_by_dates non_working_intervals_by_dates_1 (cost=0.41..82.06 rows=11 width=4) (actual time=0.255..0.262 rows=3 loops=58,986)

  • Index Cond: ((projects_1_1.account_id = account_id) AND (generate_series_2.generate_series = date))
61. 11,686.013 12,356.850 ↑ 1.0 703,961 1

Hash Right Join (cost=45,525.20..183,660.58 rows=703,961 width=116) (actual time=611.672..12,356.850 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
62. 89.074 175.823 ↓ 8.9 44,428 1

HashAggregate (cost=9,025.04..9,099.61 rows=4,971 width=48) (actual time=113.096..175.823 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)
63. 27.536 86.749 ↑ 1.0 44,581 1

WindowAgg (cost=6,572.98..7,576.09 rows=44,583 width=68) (actual time=54.307..86.749 rows=44,581 loops=1)

64. 22.508 59.213 ↑ 1.0 44,581 1

Sort (cost=6,572.98..6,684.43 rows=44,583 width=52) (actual time=54.297..59.213 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
65. 15.080 36.705 ↑ 1.0 44,581 1

Hash Join (cost=1,631.38..3,130.23 rows=44,583 width=52) (actual time=17.665..36.705 rows=44,581 loops=1)

  • Hash Cond: (plan_roles_1.plan_row_uuid = plan_rows_3.uuid)
66. 4.205 4.205 ↑ 1.0 44,583 1

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

67. 9.359 17.420 ↑ 1.0 48,106 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,180kB
68. 8.061 8.061 ↑ 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.061 rows=48,106 loops=1)

69. 176.328 495.014 ↑ 1.0 703,961 1

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

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 62,366kB
70. 226.620 318.686 ↑ 1.0 703,961 1

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

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

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

72. 4.745 15.676 ↑ 1.0 26,435 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,381kB
73. 6.861 10.931 ↑ 1.0 26,435 1

Hash Left Join (cost=202.03..1,496.14 rows=26,435 width=16) (actual time=1.517..10.931 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
74. 2.586 2.586 ↑ 1.0 26,435 1

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

75. 0.429 1.484 ↑ 1.0 2,851 1

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

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

  • Filter: active
  • Rows Removed by Filter: 3,188
77. 0.848 1.687 ↑ 1.0 5,812 1

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

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

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

79. 80.826 95.386 ↓ 295.4 1,255,026 1

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

80. 1.186 14.560 ↑ 1.0 4,218 1

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

81. 3.307 13.374 ↑ 1.0 4,246 1

Sort (cost=847.14..857.76 rows=4,249 width=65) (actual time=12.922..13.374 rows=4,246 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
82. 3.170 10.067 ↑ 1.0 4,249 1

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

83. 2.359 6.897 ↑ 1.0 4,249 1

Sort (cost=484.85..495.47 rows=4,249 width=56) (actual time=6.541..6.897 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
84. 1.828 4.538 ↑ 1.0 4,249 1

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

  • Hash Cond: (staff_membership_activity_links.staff_membership_id = staff_memberships_1.id)
85. 0.478 0.478 ↑ 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.013..0.478 rows=4,256 loops=1)

86. 1.166 2.232 ↑ 1.0 4,002 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 276kB
87. 1.066 1.066 ↑ 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.008..1.066 rows=4,002 loops=1)

88. 0.057 0.158 ↑ 1.0 354 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
89. 0.101 0.101 ↑ 1.0 354 1

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

Planning time : 6.618 ms
Execution time : 35,342.839 ms