explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AWak

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 10,197.209 ↓ 14.0 14 1

GroupAggregate (cost=87,255.74..87,255.75 rows=1 width=140) (actual time=10,197.185..10,197.209 rows=14 loops=1)

  • Group Key: applications_with_stages.stage_order, applications_with_stages._godcm, applications_with_stages._godcm_value, applications_with_stages.stage_outcome, applications_with_stages.interview_stage_id, applications_with_stages.title
2.          

CTE relevant_applications

3. 0.030 331.470 ↓ 141.0 141 1

Nested Loop (cost=4.71..87,226.87 rows=1 width=804) (actual time=329.802..331.470 rows=141 loops=1)

4. 0.075 330.876 ↓ 141.0 141 1

Nested Loop (cost=4.63..87,226.72 rows=1 width=820) (actual time=329.785..330.876 rows=141 loops=1)

5. 0.122 330.425 ↓ 94.0 94 1

Nested Loop (cost=4.55..87,226.42 rows=1 width=820) (actual time=329.767..330.425 rows=94 loops=1)

6. 0.019 329.763 ↓ 135.0 135 1

Subquery Scan on "ANY_subquery" (cost=4.46..87,222.33 rows=1 width=16) (actual time=329.731..329.763 rows=135 loops=1)

7. 3.329 329.744 ↓ 135.0 135 1

HashSetOp Intersect (cost=4.46..87,222.33 rows=1 width=20) (actual time=329.730..329.744 rows=135 loops=1)

8. 2.302 326.415 ↓ 2.0 35,294 1

Append (cost=4.46..87,213.42 rows=17,813 width=20) (actual time=0.057..326.415 rows=35,294 loops=1)

9. 0.016 0.790 ↓ 139.0 139 1

Subquery Scan on "*SELECT* 8" (cost=4.46..388.03 rows=1 width=20) (actual time=0.056..0.790 rows=139 loops=1)

10. 0.070 0.774 ↓ 139.0 139 1

Nested Loop (cost=4.46..388.02 rows=1 width=16) (actual time=0.056..0.774 rows=139 loops=1)

11. 0.122 0.148 ↓ 27.8 139 1

Bitmap Heap Scan on process_user_roles (cost=4.38..367.57 rows=5 width=16) (actual time=0.038..0.148 rows=139 loops=1)

  • Recheck Cond: (user_id = '603ae474-de1c-466a-835e-da22ee5a43c8'::uuid)
  • Filter: (process_role_type_id = '9788ef71-46b1-494b-a9e5-c3d8d2d84733'::uuid)
  • Heap Blocks: exact=65
12. 0.026 0.026 ↑ 1.4 139 1

Bitmap Index Scan on process_user_roles_user_id_idx (cost=0.00..4.38 rows=195 width=0) (actual time=0.026..0.026 rows=139 loops=1)

  • Index Cond: (user_id = '603ae474-de1c-466a-835e-da22ee5a43c8'::uuid)
13. 0.556 0.556 ↑ 1.0 1 139

Index Scan using application_pkey on application application_1 (cost=0.08..4.09 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=139)

  • Index Cond: (id = process_user_roles.process_id)
  • Filter: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
14. 13.216 323.323 ↓ 2.0 35,155 1

HashAggregate (cost=86,745.24..86,798.68 rows=17,812 width=20) (actual time=319.088..323.323 rows=35,155 loops=1)

  • Group Key: ""*SELECT* 2"".id
15. 2.138 310.107 ↓ 2.0 35,155 1

Append (cost=0.08..86,736.34 rows=17,812 width=16) (actual time=89.960..310.107 rows=35,155 loops=1)

16. 3.508 97.195 ↓ 2.0 34,972 1

Result (cost=0.08..22,653.42 rows=17,450 width=16) (actual time=89.959..97.195 rows=34,972 loops=1)

17. 21.867 93.687 ↓ 2.0 34,972 1

HashSetOp Intersect (cost=0.08..22,601.07 rows=17,450 width=20) (actual time=89.958..93.687 rows=34,972 loops=1)

18. 4.431 71.820 ↑ 1.0 70,166 1

Append (cost=0.08..22,565.60 rows=70,939 width=20) (actual time=0.021..71.820 rows=70,166 loops=1)

19. 2.972 26.491 ↓ 2.0 35,011 1

Subquery Scan on "*SELECT* 2" (cost=0.08..17,673.29 rows=17,450 width=20) (actual time=0.021..26.491 rows=35,011 loops=1)

20. 23.519 23.519 ↓ 2.0 35,011 1

Index Scan using application_organization_id_idx on application application_2 (cost=0.08..17,620.94 rows=17,450 width=16) (actual time=0.021..23.519 rows=35,011 loops=1)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
  • Filter: COALESCE((status <> 'hired'::enum_application_status), true)
  • Rows Removed by Filter: 183
21. 2.965 40.898 ↑ 1.5 35,155 1

Subquery Scan on "*SELECT* 1" (cost=4,464.97..4,785.90 rows=53,489 width=20) (actual time=32.922..40.898 rows=35,155 loops=1)

22. 14.665 37.933 ↑ 1.5 35,155 1

HashAggregate (cost=4,464.97..4,625.43 rows=53,489 width=16) (actual time=32.921..37.933 rows=35,155 loops=1)

  • Group Key: application_3.id
23. 3.510 23.268 ↑ 1.5 35,155 1

Nested Loop (cost=1,043.72..4,438.22 rows=53,489 width=16) (actual time=0.402..23.268 rows=35,155 loops=1)

24. 0.447 1.388 ↑ 2.2 110 1

Hash Join (cost=1,043.63..1,201.07 rows=237 width=32) (actual time=0.386..1.388 rows=110 loops=1)

  • Hash Cond: (job.id = job_1.id)
25. 0.615 0.615 ↑ 1.0 3,962 1

Seq Scan on job (cost=0.00..155.28 rows=4,095 width=16) (actual time=0.008..0.615 rows=3,962 loops=1)

26. 0.017 0.326 ↑ 2.2 110 1

Hash (cost=1,042.80..1,042.80 rows=237 width=16) (actual time=0.326..0.326 rows=110 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
27. 0.051 0.309 ↑ 2.2 110 1

HashAggregate (cost=1,041.38..1,042.09 rows=237 width=16) (actual time=0.294..0.309 rows=110 loops=1)

  • Group Key: job_1.id
28. 0.008 0.258 ↑ 2.2 110 1

Append (cost=2.24..1,041.26 rows=237 width=16) (actual time=0.023..0.258 rows=110 loops=1)

29. 0.073 0.088 ↑ 1.1 110 1

Bitmap Heap Scan on job job_1 (cost=2.24..106.27 rows=117 width=16) (actual time=0.023..0.088 rows=110 loops=1)

  • Recheck Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
  • Filter: (NOT confidential)
  • Rows Removed by Filter: 4
  • Heap Blocks: exact=26
30. 0.015 0.015 ↑ 1.0 114 1

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.23 rows=117 width=0) (actual time=0.015..0.015 rows=114 loops=1)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
31. 0.000 0.045 ↓ 0.0 0 1

Nested Loop (cost=0.08..15.83 rows=1 width=16) (actual time=0.045..0.045 rows=0 loops=1)

32. 0.001 0.045 ↓ 0.0 0 1

Nested Loop (cost=0.03..11.71 rows=1 width=16) (actual time=0.045..0.045 rows=0 loops=1)

33. 0.044 0.044 ↓ 0.0 0 1

Seq Scan on user_job_security_roles (cost=0.00..7.60 rows=1 width=32) (actual time=0.044..0.044 rows=0 loops=1)

  • Filter: (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid)
  • Rows Removed by Filter: 408
34. 0.000 0.000 ↓ 0.0 0

Index Scan using security_role_pkey on security_role (cost=0.03..4.03 rows=1 width=16) (never executed)

  • Index Cond: (id = user_job_security_roles.role_id)
  • Filter: can_see_candidates
35. 0.000 0.000 ↓ 0.0 0

Index Scan using job_pkey on job job_2 (cost=0.06..4.06 rows=1 width=16) (never executed)

  • Index Cond: (id = user_job_security_roles.job_id)
  • Filter: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
36. 0.001 0.013 ↓ 0.0 0 1

Nested Loop (cost=2.29..592.59 rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=1)

  • Join Filter: (user_team_security_roles.role_id = security_role_1.id)
37. 0.000 0.012 ↓ 0.0 0 1

Nested Loop (cost=2.29..587.85 rows=1 width=32) (actual time=0.012..0.012 rows=0 loops=1)

  • Join Filter: (team_3.path @> (SubPlan 1))
38. 0.000 0.012 ↓ 0.0 0 1

Nested Loop (cost=0.05..6.27 rows=1 width=86) (actual time=0.012..0.012 rows=0 loops=1)

39. 0.012 0.012 ↓ 0.0 0 1

Seq Scan on user_team_security_roles (cost=0.00..2.19 rows=1 width=32) (actual time=0.012..0.012 rows=0 loops=1)

  • Filter: (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid)
  • Rows Removed by Filter: 81
40. 0.000 0.000 ↓ 0.0 0

Index Scan using team_pkey on team team_3 (cost=0.05..4.06 rows=1 width=86) (never executed)

  • Index Cond: (id = user_team_security_roles.team_id)
41. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on job job_3 (cost=2.24..106.27 rows=117 width=32) (never executed)

  • Recheck Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
42. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.23 rows=117 width=0) (never executed)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
43.          

SubPlan (for Nested Loop)

44. 0.000 0.000 ↓ 0.0 0

Index Scan using team_pkey on team (cost=0.05..4.06 rows=1 width=70) (never executed)

  • Index Cond: (id = job_3.team_id)
45. 0.000 0.000 ↓ 0.0 0

Seq Scan on security_role security_role_1 (cost=0.00..4.34 rows=115 width=16) (never executed)

  • Filter: can_see_candidates
46. 0.000 0.012 ↓ 0.0 0 1

Nested Loop (cost=2.27..112.91 rows=117 width=16) (actual time=0.012..0.012 rows=0 loops=1)

47. 0.001 0.012 ↓ 0.0 0 1

Nested Loop (cost=0.03..6.29 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=1)

48. 0.011 0.011 ↓ 0.0 0 1

Seq Scan on user_team_security_roles user_team_security_roles_1 (cost=0.00..2.19 rows=1 width=16) (actual time=0.011..0.011 rows=0 loops=1)

  • Filter: ((team_id IS NULL) AND (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid))
  • Rows Removed by Filter: 81
49. 0.000 0.000 ↓ 0.0 0

Index Scan using security_role_pkey on security_role security_role_2 (cost=0.03..4.03 rows=1 width=16) (never executed)

  • Index Cond: (id = user_team_security_roles_1.role_id)
  • Filter: can_see_candidates
50. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on job job_4 (cost=2.24..106.27 rows=117 width=16) (never executed)

  • Recheck Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
51. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.23 rows=117 width=0) (never executed)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
52. 0.000 0.092 ↓ 0.0 0 1

Result (cost=2.23..212.59 rows=1 width=16) (actual time=0.092..0.092 rows=0 loops=1)

53. 0.003 0.092 ↓ 0.0 0 1

HashSetOp Intersect (cost=2.23..212.59 rows=1 width=20) (actual time=0.092..0.092 rows=0 loops=1)

54. 0.001 0.089 ↓ 2.0 4 1

Append (cost=2.23..212.59 rows=2 width=20) (actual time=0.021..0.089 rows=4 loops=1)

55. 0.002 0.044 ↓ 4.0 4 1

Subquery Scan on "*SELECT* 5" (cost=2.23..106.26 rows=1 width=20) (actual time=0.021..0.044 rows=4 loops=1)

56. 0.032 0.042 ↓ 4.0 4 1

Bitmap Heap Scan on job job_5 (cost=2.23..106.26 rows=1 width=16) (actual time=0.020..0.042 rows=4 loops=1)

  • Recheck Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
  • Filter: confidential
  • Rows Removed by Filter: 110
  • Heap Blocks: exact=26
57. 0.010 0.010 ↑ 1.0 114 1

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.23 rows=117 width=0) (actual time=0.009..0.010 rows=114 loops=1)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
58. 0.000 0.044 ↓ 0.0 0 1

Subquery Scan on "*SELECT* 6_1" (cost=2.23..106.32 rows=1 width=20) (actual time=0.044..0.044 rows=0 loops=1)

59. 0.033 0.044 ↓ 0.0 0 1

Bitmap Heap Scan on job job_6 (cost=2.23..106.32 rows=1 width=16) (actual time=0.044..0.044 rows=0 loops=1)

  • Recheck Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
  • Filter: (author_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid)
  • Rows Removed by Filter: 114
  • Heap Blocks: exact=26
60. 0.011 0.011 ↑ 1.0 114 1

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.23 rows=117 width=0) (actual time=0.011..0.011 rows=114 loops=1)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
61. 18.370 18.370 ↓ 1.4 320 110

Index Scan using application_job_id_idx on application application_3 (cost=0.08..12.98 rows=226 width=32) (actual time=0.006..0.167 rows=320 loops=110)

  • Index Cond: (job_id = job.id)
62. 0.017 210.774 ↑ 2.0 183 1

Result (cost=54,000.09..64,056.20 rows=362 width=16) (actual time=210.746..210.774 rows=183 loops=1)

63. 3.110 210.757 ↑ 2.0 183 1

HashSetOp Intersect (cost=54,000.09..64,055.11 rows=362 width=20) (actual time=210.746..210.757 rows=183 loops=1)

64. 2.158 207.647 ↑ 1.5 35,338 1

Append (cost=54,000.09..64,028.19 rows=53,851 width=20) (actual time=98.645..207.647 rows=35,338 loops=1)

65. 0.077 98.668 ↑ 2.0 183 1

HashAggregate (cost=54,000.09..54,001.18 rows=362 width=20) (actual time=98.644..98.668 rows=183 loops=1)

  • Group Key: application_4.id
66. 0.013 98.591 ↑ 2.0 183 1

Append (cost=18,132.50..53,999.91 rows=362 width=16) (actual time=37.422..98.591 rows=183 loops=1)

67. 3.774 43.060 ↑ 2.0 2 1

Merge Join (cost=18,132.50..18,166.38 rows=4 width=16) (actual time=37.421..43.060 rows=2 loops=1)

  • Merge Cond: (employee.candidate_id = application_4.candidate_id)
68. 0.053 0.053 ↑ 378.8 36 1

Index Scan using employee_candidate_id_idx on employee (cost=0.06..597.86 rows=13,636 width=16) (actual time=0.021..0.053 rows=36 loops=1)

  • Filter: ((NOT is_alumni) AND (user_id IS DISTINCT FROM '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid))
69. 18.982 39.233 ↑ 1.1 32,475 1

Sort (cost=18,130.17..18,147.62 rows=34,900 width=32) (actual time=36.355..39.233 rows=32,475 loops=1)

  • Sort Key: application_4.candidate_id
  • Sort Method: quicksort Memory: 4,286kB
70. 20.251 20.251 ↓ 1.0 35,194 1

Index Scan using application_organization_id_idx on application application_4 (cost=0.08..17,603.49 rows=34,900 width=32) (actual time=0.014..20.251 rows=35,194 loops=1)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
71. 0.017 55.518 ↑ 2.0 181 1

Result (cost=0.08..35,832.98 rows=358 width=16) (actual time=55.488..55.518 rows=181 loops=1)

72. 3.302 55.501 ↑ 2.0 181 1

HashSetOp Intersect (cost=0.08..35,831.90 rows=358 width=20) (actual time=55.486..55.501 rows=181 loops=1)

73. 2.180 52.199 ↓ 1.0 35,375 1

Append (cost=0.08..35,814.28 rows=35,254 width=20) (actual time=0.066..52.199 rows=35,375 loops=1)

74. 0.027 20.524 ↑ 2.0 183 1

Subquery Scan on "*SELECT* 7" (cost=0.08..17,622.02 rows=358 width=20) (actual time=0.066..20.524 rows=183 loops=1)

75. 20.497 20.497 ↑ 2.0 183 1

Index Scan using application_organization_id_idx on application application_5 (cost=0.08..17,620.94 rows=358 width=16) (actual time=0.065..20.497 rows=183 loops=1)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
  • Filter: (status = 'hired'::enum_application_status)
  • Rows Removed by Filter: 35,011
76. 3.018 29.495 ↓ 1.0 35,192 1

Subquery Scan on "*SELECT* 6" (cost=307.79..18,139.38 rows=34,896 width=20) (actual time=3.043..29.495 rows=35,192 loops=1)

77. 7.807 26.477 ↓ 1.0 35,192 1

Hash Anti Join (cost=307.79..18,034.69 rows=34,896 width=16) (actual time=3.041..26.477 rows=35,192 loops=1)

  • Hash Cond: (application_6.candidate_id = employee_1.candidate_id)
78. 15.678 15.678 ↓ 1.0 35,194 1

Index Scan using application_organization_id_idx on application application_6 (cost=0.08..17,603.49 rows=34,900 width=32) (actual time=0.012..15.678 rows=35,194 loops=1)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
79. 0.658 2.992 ↑ 389.6 35 1

Hash (cost=259.97..259.97 rows=13,637 width=16) (actual time=2.992..2.992 rows=35 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 130kB
80. 2.334 2.334 ↑ 1.0 13,616 1

Seq Scan on employee employee_1 (cost=0.00..259.97 rows=13,637 width=16) (actual time=0.010..2.334 rows=13,616 loops=1)

  • Filter: (NOT is_alumni)
  • Rows Removed by Filter: 21
81. 3.506 106.821 ↑ 1.5 35,155 1

Result (cost=4,464.97..9,946.23 rows=53,489 width=20) (actual time=101.301..106.821 rows=35,155 loops=1)

82. 16.076 103.315 ↑ 1.5 35,155 1

HashSetOp Intersect (cost=4,464.97..9,785.77 rows=53,489 width=20) (actual time=101.299..103.315 rows=35,155 loops=1)

83. 4.320 87.239 ↑ 1.5 70,310 1

Append (cost=4,464.97..9,732.28 rows=106,978 width=20) (actual time=33.467..87.239 rows=70,310 loops=1)

84. 2.975 41.240 ↑ 1.5 35,155 1

Subquery Scan on "*SELECT* 3" (cost=4,464.97..4,785.90 rows=53,489 width=20) (actual time=33.466..41.240 rows=35,155 loops=1)

85. 15.388 38.265 ↑ 1.5 35,155 1

HashAggregate (cost=4,464.97..4,625.43 rows=53,489 width=16) (actual time=33.465..38.265 rows=35,155 loops=1)

  • Group Key: application_7.id
86. 3.466 22.877 ↑ 1.5 35,155 1

Nested Loop (cost=1,043.72..4,438.22 rows=53,489 width=16) (actual time=0.412..22.877 rows=35,155 loops=1)

87. 0.437 1.371 ↑ 2.2 110 1

Hash Join (cost=1,043.63..1,201.07 rows=237 width=32) (actual time=0.395..1.371 rows=110 loops=1)

  • Hash Cond: (job_7.id = job_8.id)
88. 0.599 0.599 ↑ 1.0 3,962 1

Seq Scan on job job_7 (cost=0.00..155.28 rows=4,095 width=16) (actual time=0.007..0.599 rows=3,962 loops=1)

89. 0.017 0.335 ↑ 2.2 110 1

Hash (cost=1,042.80..1,042.80 rows=237 width=16) (actual time=0.335..0.335 rows=110 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
90. 0.051 0.318 ↑ 2.2 110 1

HashAggregate (cost=1,041.38..1,042.09 rows=237 width=16) (actual time=0.301..0.318 rows=110 loops=1)

  • Group Key: job_8.id
91. 0.009 0.267 ↑ 2.2 110 1

Append (cost=2.24..1,041.26 rows=237 width=16) (actual time=0.028..0.267 rows=110 loops=1)

92. 0.070 0.089 ↑ 1.1 110 1

Bitmap Heap Scan on job job_8 (cost=2.24..106.27 rows=117 width=16) (actual time=0.027..0.089 rows=110 loops=1)

  • Recheck Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
  • Filter: (NOT confidential)
  • Rows Removed by Filter: 4
  • Heap Blocks: exact=26
93. 0.019 0.019 ↑ 1.0 114 1

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.23 rows=117 width=0) (actual time=0.019..0.019 rows=114 loops=1)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
94. 0.001 0.048 ↓ 0.0 0 1

Nested Loop (cost=0.08..15.83 rows=1 width=16) (actual time=0.048..0.048 rows=0 loops=1)

95. 0.000 0.047 ↓ 0.0 0 1

Nested Loop (cost=0.03..11.71 rows=1 width=16) (actual time=0.047..0.047 rows=0 loops=1)

96. 0.047 0.047 ↓ 0.0 0 1

Seq Scan on user_job_security_roles user_job_security_roles_1 (cost=0.00..7.60 rows=1 width=32) (actual time=0.047..0.047 rows=0 loops=1)

  • Filter: (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid)
  • Rows Removed by Filter: 408
97. 0.000 0.000 ↓ 0.0 0

Index Scan using security_role_pkey on security_role security_role_3 (cost=0.03..4.03 rows=1 width=16) (never executed)

  • Index Cond: (id = user_job_security_roles_1.role_id)
  • Filter: can_see_candidates
98. 0.000 0.000 ↓ 0.0 0

Index Scan using job_pkey on job job_9 (cost=0.06..4.06 rows=1 width=16) (never executed)

  • Index Cond: (id = user_job_security_roles_1.job_id)
  • Filter: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
99. 0.001 0.016 ↓ 0.0 0 1

Nested Loop (cost=2.29..592.59 rows=1 width=16) (actual time=0.016..0.016 rows=0 loops=1)

  • Join Filter: (user_team_security_roles_2.role_id = security_role_4.id)
100. 0.000 0.015 ↓ 0.0 0 1

Nested Loop (cost=2.29..587.85 rows=1 width=32) (actual time=0.015..0.015 rows=0 loops=1)

  • Join Filter: (team_4.path @> (SubPlan 2))
101. 0.000 0.015 ↓ 0.0 0 1

Nested Loop (cost=0.05..6.27 rows=1 width=86) (actual time=0.015..0.015 rows=0 loops=1)

102. 0.015 0.015 ↓ 0.0 0 1

Seq Scan on user_team_security_roles user_team_security_roles_2 (cost=0.00..2.19 rows=1 width=32) (actual time=0.015..0.015 rows=0 loops=1)

  • Filter: (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid)
  • Rows Removed by Filter: 81
103. 0.000 0.000 ↓ 0.0 0

Index Scan using team_pkey on team team_4 (cost=0.05..4.06 rows=1 width=86) (never executed)

  • Index Cond: (id = user_team_security_roles_2.team_id)
104. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on job job_10 (cost=2.24..106.27 rows=117 width=32) (never executed)

  • Recheck Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
105. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.23 rows=117 width=0) (never executed)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
106.          

SubPlan (for Nested Loop)

107. 0.000 0.000 ↓ 0.0 0

Index Scan using team_pkey on team team_1 (cost=0.05..4.06 rows=1 width=70) (never executed)

  • Index Cond: (id = job_10.team_id)
108. 0.000 0.000 ↓ 0.0 0

Seq Scan on security_role security_role_4 (cost=0.00..4.34 rows=115 width=16) (never executed)

  • Filter: can_see_candidates
109. 0.000 0.009 ↓ 0.0 0 1

Nested Loop (cost=2.27..112.91 rows=117 width=16) (actual time=0.009..0.009 rows=0 loops=1)

110. 0.001 0.009 ↓ 0.0 0 1

Nested Loop (cost=0.03..6.29 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=1)

111. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on user_team_security_roles user_team_security_roles_3 (cost=0.00..2.19 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=1)

  • Filter: ((team_id IS NULL) AND (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid))
  • Rows Removed by Filter: 81
112. 0.000 0.000 ↓ 0.0 0

Index Scan using security_role_pkey on security_role security_role_5 (cost=0.03..4.03 rows=1 width=16) (never executed)

  • Index Cond: (id = user_team_security_roles_3.role_id)
  • Filter: can_see_candidates
113. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on job job_11 (cost=2.24..106.27 rows=117 width=16) (never executed)

  • Recheck Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
114. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.23 rows=117 width=0) (never executed)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
115. 0.001 0.096 ↓ 0.0 0 1

Result (cost=2.23..212.59 rows=1 width=16) (actual time=0.096..0.096 rows=0 loops=1)

116. 0.005 0.095 ↓ 0.0 0 1

HashSetOp Intersect (cost=2.23..212.59 rows=1 width=20) (actual time=0.095..0.095 rows=0 loops=1)

117. 0.002 0.090 ↓ 2.0 4 1

Append (cost=2.23..212.59 rows=2 width=20) (actual time=0.023..0.090 rows=4 loops=1)

118. 0.001 0.045 ↓ 4.0 4 1

Subquery Scan on "*SELECT* 5_1" (cost=2.23..106.26 rows=1 width=20) (actual time=0.022..0.045 rows=4 loops=1)

119. 0.033 0.044 ↓ 4.0 4 1

Bitmap Heap Scan on job job_12 (cost=2.23..106.26 rows=1 width=16) (actual time=0.022..0.044 rows=4 loops=1)

  • Recheck Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
  • Filter: confidential
  • Rows Removed by Filter: 110
  • Heap Blocks: exact=26
120. 0.011 0.011 ↑ 1.0 114 1

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.23 rows=117 width=0) (actual time=0.011..0.011 rows=114 loops=1)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
121. 0.000 0.043 ↓ 0.0 0 1

Subquery Scan on "*SELECT* 6_2" (cost=2.23..106.32 rows=1 width=20) (actual time=0.043..0.043 rows=0 loops=1)

122. 0.033 0.043 ↓ 0.0 0 1

Bitmap Heap Scan on job job_13 (cost=2.23..106.32 rows=1 width=16) (actual time=0.042..0.043 rows=0 loops=1)

  • Recheck Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
  • Filter: (author_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid)
  • Rows Removed by Filter: 114
  • Heap Blocks: exact=26
123. 0.010 0.010 ↑ 1.0 114 1

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.23 rows=117 width=0) (actual time=0.010..0.010 rows=114 loops=1)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
124. 18.040 18.040 ↓ 1.4 320 110

Index Scan using application_job_id_idx on application application_7 (cost=0.08..12.98 rows=226 width=32) (actual time=0.006..0.164 rows=320 loops=110)

  • Index Cond: (job_id = job_7.id)
125. 2.927 41.679 ↑ 1.5 35,155 1

Subquery Scan on "*SELECT* 4" (cost=4,464.98..4,785.91 rows=53,489 width=20) (actual time=34.326..41.679 rows=35,155 loops=1)

126. 14.286 38.752 ↑ 1.5 35,155 1

HashAggregate (cost=4,464.98..4,625.44 rows=53,489 width=16) (actual time=34.324..38.752 rows=35,155 loops=1)

  • Group Key: application_8.id
127. 3.640 24.466 ↑ 1.5 35,155 1

Nested Loop (cost=1,043.73..4,438.23 rows=53,489 width=16) (actual time=0.409..24.466 rows=35,155 loops=1)

128. 0.440 1.356 ↑ 2.2 110 1

Hash Join (cost=1,043.64..1,201.08 rows=237 width=32) (actual time=0.390..1.356 rows=110 loops=1)

  • Hash Cond: (job_14.id = job_15.id)
129. 0.588 0.588 ↑ 1.0 3,962 1

Seq Scan on job job_14 (cost=0.00..155.28 rows=4,095 width=16) (actual time=0.008..0.588 rows=3,962 loops=1)

130. 0.027 0.328 ↑ 2.2 110 1

Hash (cost=1,042.81..1,042.81 rows=237 width=16) (actual time=0.328..0.328 rows=110 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
131. 0.053 0.301 ↑ 2.2 110 1

HashAggregate (cost=1,041.39..1,042.10 rows=237 width=16) (actual time=0.284..0.301 rows=110 loops=1)

  • Group Key: job_15.id
132. 0.009 0.248 ↑ 2.2 110 1

Append (cost=2.24..1,041.27 rows=237 width=16) (actual time=0.027..0.248 rows=110 loops=1)

133. 0.057 0.074 ↑ 1.1 110 1

Bitmap Heap Scan on job job_15 (cost=2.24..106.27 rows=117 width=16) (actual time=0.026..0.074 rows=110 loops=1)

  • Recheck Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
  • Filter: (NOT confidential)
  • Rows Removed by Filter: 4
  • Heap Blocks: exact=26
134. 0.017 0.017 ↑ 1.0 114 1

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.23 rows=117 width=0) (actual time=0.017..0.017 rows=114 loops=1)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
135. 0.001 0.046 ↓ 0.0 0 1

Nested Loop (cost=0.08..15.98 rows=1 width=16) (actual time=0.046..0.046 rows=0 loops=1)

136. 0.000 0.045 ↓ 0.0 0 1

Nested Loop (cost=0.03..11.85 rows=1 width=16) (actual time=0.045..0.045 rows=0 loops=1)

137. 0.045 0.045 ↓ 0.0 0 1

Seq Scan on user_job_security_roles user_job_security_roles_2 (cost=0.00..7.60 rows=1 width=32) (actual time=0.045..0.045 rows=0 loops=1)

  • Filter: (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid)
  • Rows Removed by Filter: 408
138. 0.000 0.000 ↓ 0.0 0

Index Scan using security_role_pkey on security_role security_role_6 (cost=0.03..4.03 rows=1 width=16) (never executed)

  • Index Cond: (id = user_job_security_roles_2.role_id)
  • Filter: can_see_and_edit_hired_candidates
139. 0.000 0.000 ↓ 0.0 0

Index Scan using job_pkey on job job_16 (cost=0.06..4.06 rows=1 width=16) (never executed)

  • Index Cond: (id = user_job_security_roles_2.job_id)
  • Filter: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
140. 0.000 0.013 ↓ 0.0 0 1

Nested Loop (cost=2.29..592.32 rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=1)

  • Join Filter: (user_team_security_roles_4.role_id = security_role_7.id)
141. 0.001 0.013 ↓ 0.0 0 1

Nested Loop (cost=2.29..587.85 rows=1 width=32) (actual time=0.013..0.013 rows=0 loops=1)

  • Join Filter: (team_5.path @> (SubPlan 3))
142. 0.000 0.012 ↓ 0.0 0 1

Nested Loop (cost=0.05..6.27 rows=1 width=86) (actual time=0.012..0.012 rows=0 loops=1)

143. 0.012 0.012 ↓ 0.0 0 1

Seq Scan on user_team_security_roles user_team_security_roles_4 (cost=0.00..2.19 rows=1 width=32) (actual time=0.012..0.012 rows=0 loops=1)

  • Filter: (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid)
  • Rows Removed by Filter: 81
144. 0.000 0.000 ↓ 0.0 0

Index Scan using team_pkey on team team_5 (cost=0.05..4.06 rows=1 width=86) (never executed)

  • Index Cond: (id = user_team_security_roles_4.team_id)
145. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on job job_17 (cost=2.24..106.27 rows=117 width=32) (never executed)

  • Recheck Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
146. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.23 rows=117 width=0) (never executed)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
147.          

SubPlan (for Nested Loop)

148. 0.000 0.000 ↓ 0.0 0

Index Scan using team_pkey on team team_2 (cost=0.05..4.06 rows=1 width=70) (never executed)

  • Index Cond: (id = job_17.team_id)
149. 0.000 0.000 ↓ 0.0 0

Seq Scan on security_role security_role_7 (cost=0.00..4.34 rows=37 width=16) (never executed)

  • Filter: can_see_and_edit_hired_candidates
150. 0.000 0.009 ↓ 0.0 0 1

Nested Loop (cost=2.27..113.05 rows=117 width=16) (actual time=0.009..0.009 rows=0 loops=1)

151. 0.001 0.009 ↓ 0.0 0 1

Nested Loop (cost=0.03..6.43 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=1)

152. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on user_team_security_roles user_team_security_roles_5 (cost=0.00..2.19 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=1)

  • Filter: ((team_id IS NULL) AND (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid))
  • Rows Removed by Filter: 81
153. 0.000 0.000 ↓ 0.0 0

Index Scan using security_role_pkey on security_role security_role_8 (cost=0.03..4.03 rows=1 width=16) (never executed)

  • Index Cond: (id = user_team_security_roles_5.role_id)
  • Filter: can_see_and_edit_hired_candidates
154. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on job job_18 (cost=2.24..106.27 rows=117 width=16) (never executed)

  • Recheck Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
155. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.23 rows=117 width=0) (never executed)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
156. 0.000 0.097 ↓ 0.0 0 1

Result (cost=2.23..212.59 rows=1 width=16) (actual time=0.097..0.097 rows=0 loops=1)

157. 0.005 0.097 ↓ 0.0 0 1

HashSetOp Intersect (cost=2.23..212.59 rows=1 width=20) (actual time=0.097..0.097 rows=0 loops=1)

158. 0.001 0.092 ↓ 2.0 4 1

Append (cost=2.23..212.59 rows=2 width=20) (actual time=0.025..0.092 rows=4 loops=1)

159. 0.001 0.047 ↓ 4.0 4 1

Subquery Scan on "*SELECT* 5_2" (cost=2.23..106.26 rows=1 width=20) (actual time=0.025..0.047 rows=4 loops=1)

160. 0.032 0.046 ↓ 4.0 4 1

Bitmap Heap Scan on job job_19 (cost=2.23..106.26 rows=1 width=16) (actual time=0.024..0.046 rows=4 loops=1)

  • Recheck Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
  • Filter: confidential
  • Rows Removed by Filter: 110
  • Heap Blocks: exact=26
161. 0.014 0.014 ↑ 1.0 114 1

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.23 rows=117 width=0) (actual time=0.014..0.014 rows=114 loops=1)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
162. 0.001 0.044 ↓ 0.0 0 1

Subquery Scan on "*SELECT* 6_3" (cost=2.23..106.32 rows=1 width=20) (actual time=0.044..0.044 rows=0 loops=1)

163. 0.033 0.043 ↓ 0.0 0 1

Bitmap Heap Scan on job job_20 (cost=2.23..106.32 rows=1 width=16) (actual time=0.043..0.043 rows=0 loops=1)

  • Recheck Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
  • Filter: (author_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid)
  • Rows Removed by Filter: 114
  • Heap Blocks: exact=26
164. 0.010 0.010 ↑ 1.0 114 1

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.23 rows=117 width=0) (actual time=0.010..0.010 rows=114 loops=1)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
165. 19.470 19.470 ↓ 1.4 320 110

Index Scan using application_job_id_idx on application application_8 (cost=0.08..12.98 rows=226 width=32) (actual time=0.006..0.177 rows=320 loops=110)

  • Index Cond: (job_id = job_14.id)
166. 0.540 0.540 ↑ 1.0 1 135

Index Scan using application_pkey on application (cost=0.08..4.09 rows=1 width=804) (actual time=0.004..0.004 rows=1 loops=135)

  • Index Cond: (id = ""ANY_subquery"".id)
  • Filter: ((organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid) AND ((status = 'archived'::enum_application_status) OR (status = 'hired'::enum_application_status)))
  • Rows Removed by Filter: 0
167. 0.376 0.376 ↑ 1.5 2 94

Index Scan using scheduled_interview_event_application_id_idx on scheduled_interview_event scheduled_interview_event_hmpae (cost=0.08..0.29 rows=3 width=32) (actual time=0.003..0.004 rows=2 loops=94)

  • Index Cond: (application_id = application.id)
168. 0.564 0.564 ↑ 1.0 1 141

Index Scan using interview_event_pkey on interview_event interview_event_hmpae (cost=0.08..0.15 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=141)

  • Index Cond: (id = scheduled_interview_event_hmpae.interview_event_id)
  • Filter: ((start_time >= '2017-06-28 07:00:00+00'::timestamp with time zone) AND (start_time <= '2020-06-28 06:59:59.999+00'::timestamp with time zone))
169.          

CTE applications_with_stages

170. 0.551 10,197.034 ↓ 97.0 97 1

Unique (cost=28.86..28.86 rows=1 width=129) (actual time=10,196.150..10,197.034 rows=97 loops=1)

171. 5.305 10,196.483 ↓ 6,658.0 6,658 1

Sort (cost=28.86..28.86 rows=1 width=129) (actual time=10,196.149..10,196.483 rows=6,658 loops=1)

  • Sort Key: application_9.id, interview_stage_group_type.id
  • Sort Method: quicksort Memory: 1,129kB
172. 17.863 10,191.178 ↓ 6,658.0 6,658 1

Nested Loop Left Join (cost=17.16..28.85 rows=1 width=129) (actual time=341.372..10,191.178 rows=6,658 loops=1)

  • Join Filter: (relevant_applications.id = application_9.id)
  • Rows Removed by Join Filter: 115,730
173. 1.612 357.971 ↓ 868.0 868 1

Nested Loop (cost=0.31..10.30 rows=1 width=78) (actual time=329.908..357.971 rows=868 loops=1)

174. 1.704 353.755 ↓ 868.0 868 1

Nested Loop (cost=0.26..10.19 rows=1 width=65) (actual time=329.894..353.755 rows=868 loops=1)

  • Join Filter: (interview_stage_group_type.""order"" <= current_stage_group_app_history.""order"")
  • Rows Removed by Join Filter: 1,442
175. 0.425 338.961 ↓ 385.0 385 1

Nested Loop (cost=0.26..6.45 rows=1 width=36) (actual time=329.869..338.961 rows=385 loops=1)

176. 0.455 337.766 ↓ 385.0 385 1

Nested Loop (cost=0.23..6.41 rows=1 width=48) (actual time=329.857..337.766 rows=385 loops=1)

177. 0.585 335.307 ↓ 668.0 668 1

Nested Loop (cost=0.17..6.35 rows=1 width=48) (actual time=329.842..335.307 rows=668 loops=1)

178. 0.412 331.382 ↓ 334.0 668 1

Nested Loop (cost=0.09..6.11 rows=2 width=48) (actual time=329.823..331.382 rows=668 loops=1)

179. 329.842 329.842 ↓ 141.0 141 1

CTE Scan on relevant_applications application_9 (cost=0.00..0.01 rows=1 width=16) (actual time=329.805..329.842 rows=141 loops=1)

180. 1.128 1.128 ↓ 2.5 5 141

Index Scan using application_history_application_id_idx on application_history (cost=0.09..6.09 rows=2 width=32) (actual time=0.004..0.008 rows=5 loops=141)

  • Index Cond: (application_id = application_9.id)
181. 3.340 3.340 ↑ 1.0 1 668

Index Scan using process_user_roles_process_id_idx on process_user_roles process_user_roles_roydr (cost=0.08..0.12 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=668)

  • Index Cond: (process_id = application_history.application_id)
  • Filter: (process_role_type_id = '9788ef71-46b1-494b-a9e5-c3d8d2d84733'::uuid)
  • Rows Removed by Filter: 1
182. 2.004 2.004 ↑ 1.0 1 668

Index Scan using interview_stage_pkey on interview_stage current_stage_app_history (cost=0.06..0.06 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=668)

  • Index Cond: (id = application_history.new_interview_stage_id)
  • Filter: ((stage_type <> 'Archived'::enum_interview_stage_stage_type) AND (stage_type <> 'PreInterviewScreen'::enum_interview_stage_stage_type))
  • Rows Removed by Filter: 0
183. 0.770 0.770 ↑ 1.0 1 385

Index Scan using interview_stage_group_type_pkey on interview_stage_group_type current_stage_group_app_history (cost=0.03..0.03 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=385)

  • Index Cond: (id = current_stage_app_history.interview_stage_group_id)
  • Filter: is_relevant_for_funnel
184. 13.090 13.090 ↑ 1.0 6 385

Seq Scan on interview_stage_group_type (cost=0.00..3.72 rows=6 width=33) (actual time=0.005..0.034 rows=6 loops=385)

  • Filter: (is_relevant_for_funnel AND (stage_type IS DISTINCT FROM 'Archived'::enum_interview_stage_group_type_stage_type) AND (stage_type IS DISTINCT FROM 'PreInterviewScreen'::enum_interview_stage_group_type_stage_type) AND (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid))
  • Rows Removed by Filter: 162
185. 2.604 2.604 ↑ 1.0 1 868

Index Scan using app_user_pkey on app_user app_user_roydr (cost=0.06..0.11 rows=1 width=29) (actual time=0.003..0.003 rows=1 loops=868)

  • Index Cond: (id = process_user_roles_roydr.user_id)
186. 110.236 9,815.344 ↓ 141.0 141 868

Merge Left Join (cost=16.84..18.55 rows=1 width=64) (actual time=9.346..11.308 rows=141 loops=868)

  • Merge Cond: (relevant_applications.id = relevant_applications_2.id)
  • Join Filter: (interview_stage_group_type_1.id = interview_stage_group_type_3.id)
  • Rows Removed by Join Filter: 230
187. 96.348 4,502.316 ↓ 141.0 141 868

Nested Loop (cost=6.33..8.03 rows=1 width=40) (actual time=3.528..5.187 rows=141 loops=868)

188. 539.028 4,283.580 ↓ 141.0 141 868

Nested Loop (cost=6.30..6.32 rows=1 width=20) (actual time=3.526..4.935 rows=141 loops=868)

  • Join Filter: (relevant_applications.id = relevant_applications_1.id)
  • Rows Removed by Join Filter: 6,768
189. 78.988 3,149.104 ↓ 49.0 49 868

GroupAggregate (cost=6.30..6.31 rows=1 width=20) (actual time=3.511..3.628 rows=49 loops=868)

  • Group Key: relevant_applications_1.id
190. 167.524 3,070.116 ↓ 263.0 526 868

Sort (cost=6.30..6.31 rows=2 width=20) (actual time=3.505..3.537 rows=526 loops=868)

  • Sort Key: relevant_applications_1.id
  • Sort Method: quicksort Memory: 66kB
191. 318.556 2,902.592 ↓ 263.0 526 868

Nested Loop (cost=0.17..6.30 rows=2 width=20) (actual time=0.011..3.344 rows=526 loops=868)

192. 155.372 2,127.468 ↓ 263.0 526 868

Nested Loop (cost=0.14..6.24 rows=2 width=32) (actual time=0.009..2.451 rows=526 loops=868)

193. 180.544 812.448 ↓ 334.0 668 868

Nested Loop (cost=0.09..6.11 rows=2 width=32) (actual time=0.005..0.936 rows=668 loops=868)

194. 19.964 19.964 ↓ 141.0 141 868

CTE Scan on relevant_applications relevant_applications_1 (cost=0.00..0.01 rows=1 width=16) (actual time=0.000..0.023 rows=141 loops=868)

195. 611.940 611.940 ↓ 2.5 5 122,388

Index Scan using application_history_application_id_idx on application_history application_history_1 (cost=0.09..6.09 rows=2 width=32) (actual time=0.003..0.005 rows=5 loops=122,388)

  • Index Cond: (application_id = relevant_applications_1.id)
196. 1,159.648 1,159.648 ↑ 1.0 1 579,824

Index Scan using interview_stage_pkey on interview_stage (cost=0.06..0.06 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=579,824)

  • Index Cond: (id = application_history_1.new_interview_stage_id)
  • Filter: (stage_type <> 'Archived'::enum_interview_stage_stage_type)
  • Rows Removed by Filter: 0
197. 456.568 456.568 ↑ 1.0 1 456,568

Index Scan using interview_stage_group_type_pkey on interview_stage_group_type interview_stage_group_type_2 (cost=0.03..0.03 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=456,568)

  • Index Cond: (id = interview_stage.interview_stage_group_id)
  • Filter: is_relevant_for_funnel
198. 595.448 595.448 ↓ 141.0 141 42,532

CTE Scan on relevant_applications (cost=0.00..0.01 rows=1 width=16) (actual time=0.000..0.014 rows=141 loops=42,532)

199. 122.388 122.388 ↑ 1.0 1 122,388

Index Scan using interview_stage_group_type_pkey on interview_stage_group_type interview_stage_group_type_1 (cost=0.03..1.70 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=122,388)

  • Index Cond: (id = interview_stage_group_type.id)
  • Filter: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
200. 29.512 5,202.792 ↓ 167.5 335 868

Materialize (cost=10.51..10.52 rows=2 width=65) (actual time=5.812..5.994 rows=335 loops=868)

201. 91.140 5,173.280 ↓ 92.5 185 868

Unique (cost=10.51..10.51 rows=2 width=153) (actual time=5.810..5.960 rows=185 loops=868)

202. 399.280 5,082.140 ↓ 333.5 667 868

Sort (cost=10.51..10.51 rows=2 width=153) (actual time=5.810..5.855 rows=667 loops=868)

  • Sort Key: relevant_applications_2.id, interview_stage_group_type_3.id
  • Sort Method: quicksort Memory: 118kB
203. 92.008 4,682.860 ↓ 334.0 668 868

Nested Loop Left Join (cost=0.29..10.51 rows=2 width=153) (actual time=0.018..5.395 rows=668 loops=868)

204. 435.736 3,431.204 ↓ 334.0 668 868

Nested Loop Left Join (cost=0.23..10.38 rows=2 width=65) (actual time=0.014..3.953 rows=668 loops=868)

205. 138.012 2,415.644 ↓ 334.0 668 868

Nested Loop Left Join (cost=0.20..10.31 rows=2 width=65) (actual time=0.012..2.783 rows=668 loops=868)

206. 212.660 1,117.984 ↓ 334.0 668 868

Nested Loop Left Join (cost=0.14..10.18 rows=2 width=65) (actual time=0.009..1.288 rows=668 loops=868)

207. 21.700 293.384 ↓ 141.0 141 868

Nested Loop Left Join (cost=0.05..4.08 rows=1 width=33) (actual time=0.005..0.338 rows=141 loops=868)

208. 26.908 26.908 ↓ 141.0 141 868

CTE Scan on relevant_applications relevant_applications_2 (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.031 rows=141 loops=868)

209. 244.776 244.776 ↑ 1.0 1 122,388

Index Scan using archive_reason_pkey on archive_reason (cost=0.05..4.06 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=122,388)

  • Index Cond: (relevant_applications_2.archive_reason_id = id)
210. 611.940 611.940 ↓ 2.5 5 122,388

Index Scan using application_history_application_id_idx on application_history application_history_2 (cost=0.09..6.09 rows=2 width=48) (actual time=0.003..0.005 rows=5 loops=122,388)

  • Index Cond: (application_id = relevant_applications_2.id)
211. 1,159.648 1,159.648 ↑ 1.0 1 579,824

Index Scan using interview_stage_pkey on interview_stage interview_stage_1 (cost=0.06..0.06 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=579,824)

  • Index Cond: (application_history_2.previous_interview_stage_id = id)
212. 579.824 579.824 ↑ 1.0 1 579,824

Index Scan using interview_stage_group_type_pkey on interview_stage_group_type interview_stage_group_type_3 (cost=0.03..0.03 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=579,824)

  • Index Cond: (interview_stage_1.interview_stage_group_id = id)
  • Filter: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
213. 1,159.648 1,159.648 ↑ 1.0 1 579,824

Index Only Scan using interview_stage_pkey on interview_stage next_interview_stage (cost=0.06..0.06 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=579,824)

  • Index Cond: (id = application_history_2.new_interview_stage_id)
  • Heap Fetches: 579,824
214. 0.108 10,197.178 ↓ 97.0 97 1

Sort (cost=0.01..0.01 rows=1 width=148) (actual time=10,197.173..10,197.178 rows=97 loops=1)

  • Sort Key: applications_with_stages.stage_order, applications_with_stages._godcm, applications_with_stages._godcm_value, applications_with_stages.stage_outcome, applications_with_stages.interview_stage_id, applications_with_stages.title
  • Sort Method: quicksort Memory: 38kB
215. 10,197.070 10,197.070 ↓ 97.0 97 1

CTE Scan on applications_with_stages (cost=0.00..0.01 rows=1 width=148) (actual time=10,196.153..10,197.070 rows=97 loops=1)