explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xC9i

Settings
# exclusive inclusive rows x rows loops node
1. 0.338 32,174.454 ↓ 242.0 242 1

GroupAggregate (cost=93,285.67..93,285.68 rows=1 width=188) (actual time=32,174.053..32,174.454 rows=242 loops=1)

  • Group Key: applications_with_stages.stage_order, applications_with_stages._tyeqg, applications_with_stages._tszat, applications_with_stages._tyeqg_value, applications_with_stages._tszat_value, applications_with_stages.stage_outcome, applications_with_stages.interview_stage_id, applications_with_stages.title
2.          

CTE relevant_applications

3. 0.581 375.331 ↓ 1,297.0 1,297 1

Nested Loop (cost=92.39..93,252.37 rows=1 width=804) (actual time=338.544..375.331 rows=1,297 loops=1)

4. 2.758 368.726 ↓ 2,008.0 2,008 1

Nested Loop (cost=92.30..93,252.22 rows=1 width=820) (actual time=338.528..368.726 rows=2,008 loops=1)

5. 0.174 357.978 ↓ 3,995.0 3,995 1

Nested Loop (cost=92.22..93,251.92 rows=1 width=820) (actual time=338.453..357.978 rows=3,995 loops=1)

6. 0.502 339.532 ↓ 571.0 4,568 1

Subquery Scan on "ANY_subquery" (cost=92.13..93,219.19 rows=8 width=16) (actual time=338.425..339.532 rows=4,568 loops=1)

7. 6.125 339.030 ↓ 571.0 4,568 1

HashSetOp Intersect (cost=92.13..93,219.17 rows=8 width=20) (actual time=338.424..339.030 rows=4,568 loops=1)

8. 2.546 332.905 ↓ 2.2 39,741 1

Append (cost=92.13..93,210.26 rows=17,820 width=20) (actual time=0.427..332.905 rows=39,741 loops=1)

9. 0.521 18.033 ↓ 573.2 4,586 1

Subquery Scan on "*SELECT* 8" (cost=92.13..6,384.85 rows=8 width=20) (actual time=0.426..18.033 rows=4,586 loops=1)

10. 1.734 17.512 ↓ 573.2 4,586 1

Nested Loop (cost=92.13..6,384.83 rows=8 width=16) (actual time=0.425..17.512 rows=4,586 loops=1)

11. 1.665 2.020 ↓ 33.0 4,586 1

Bitmap Heap Scan on process_user_roles (cost=92.05..5,820.32 rows=139 width=16) (actual time=0.415..2.020 rows=4,586 loops=1)

  • Recheck Cond: ((user_id = 'd419fa47-df37-4c74-aa40-0f983629ab32'::uuid) OR (user_id = '44664ef6-dabe-4b95-95c8-41b130191b48'::uuid) OR (user_id = 'fd513730-0180-4c21-8977-2fef0c5528d7'::uuid) OR (user_id = '603ae474-de1c-466a-835e-da22ee5a43c8'::uuid) OR (user_id = 'b9ca40c2-e805-4ca9-bc0a-68a83e646a0c'::uuid) OR (user_id = 'a25e3978-1cfd-45f4-b2bf-0642249aaa31'::uuid))
  • Filter: (process_role_type_id = '9788ef71-46b1-494b-a9e5-c3d8d2d84733'::uuid)
  • Rows Removed by Filter: 9
  • Heap Blocks: exact=493
12. 0.001 0.355 ↓ 0.0 0 1

BitmapOr (cost=92.05..92.05 rows=4,997 width=0) (actual time=0.355..0.355 rows=0 loops=1)

13. 0.167 0.167 ↓ 1.1 2,395 1

Bitmap Index Scan on process_user_roles_user_id_idx (cost=0.00..39.47 rows=2,254 width=0) (actual time=0.167..0.167 rows=2,395 loops=1)

  • Index Cond: (user_id = 'd419fa47-df37-4c74-aa40-0f983629ab32'::uuid)
14. 0.142 0.142 ↑ 1.1 1,798 1

Bitmap Index Scan on process_user_roles_user_id_idx (cost=0.00..35.03 rows=1,964 width=0) (actual time=0.142..0.142 rows=1,798 loops=1)

  • Index Cond: (user_id = '44664ef6-dabe-4b95-95c8-41b130191b48'::uuid)
15. 0.013 0.013 ↑ 1.1 179 1

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

  • Index Cond: (user_id = 'fd513730-0180-4c21-8977-2fef0c5528d7'::uuid)
16. 0.012 0.012 ↑ 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.012..0.012 rows=139 loops=1)

  • Index Cond: (user_id = '603ae474-de1c-466a-835e-da22ee5a43c8'::uuid)
17. 0.011 0.011 ↑ 1.9 104 1

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

  • Index Cond: (user_id = 'b9ca40c2-e805-4ca9-bc0a-68a83e646a0c'::uuid)
18. 0.009 0.009 ↑ 3.2 61 1

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

  • Index Cond: (user_id = 'a25e3978-1cfd-45f4-b2bf-0642249aaa31'::uuid)
19. 13.758 13.758 ↑ 1.0 1 4,586

Index Scan using application_pkey on application application_1 (cost=0.08..4.06 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=4,586)

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

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

  • Group Key: ""*SELECT* 2"".id
21. 2.151 299.293 ↓ 2.0 35,155 1

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

22. 3.097 95.689 ↓ 2.0 34,972 1

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

23. 20.710 92.592 ↓ 2.0 34,972 1

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

24. 4.366 71.882 ↑ 1.0 70,166 1

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

25. 3.465 26.655 ↓ 2.0 35,011 1

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

26. 23.190 23.190 ↓ 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.011..23.190 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
27. 2.979 40.861 ↑ 1.5 35,155 1

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

28. 14.237 37.882 ↑ 1.5 35,155 1

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

  • Group Key: application_3.id
29. 3.767 23.645 ↑ 1.5 35,155 1

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

30. 0.458 1.398 ↑ 2.2 110 1

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

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

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

32. 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
33. 0.050 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
34. 0.010 0.259 ↑ 2.2 110 1

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

35. 0.072 0.089 ↑ 1.1 110 1

Bitmap Heap Scan on job job_1 (cost=2.24..106.27 rows=117 width=16) (actual time=0.025..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
36. 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)
37. 0.001 0.047 ↓ 0.0 0 1

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

38. 0.000 0.046 ↓ 0.0 0 1

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

39. 0.046 0.046 ↓ 0.0 0 1

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

  • Filter: (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid)
  • Rows Removed by Filter: 408
40. 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
41. 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)
42. 0.000 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)
43. 0.000 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_3.path @> (SubPlan 1))
44. 0.001 0.013 ↓ 0.0 0 1

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

45. 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
46. 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)
47. 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)
48. 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)
49.          

SubPlan (for Nested Loop)

50. 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)
51. 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
52. 0.001 0.010 ↓ 0.0 0 1

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

53. 0.000 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)

54. 0.009 0.009 ↓ 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.009..0.009 rows=0 loops=1)

  • Filter: ((team_id IS NULL) AND (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid))
  • Rows Removed by Filter: 81
55. 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
56. 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)
57. 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)
58. 0.001 0.090 ↓ 0.0 0 1

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

59. 0.003 0.089 ↓ 0.0 0 1

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

60. 0.001 0.086 ↓ 2.0 4 1

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

61. 0.001 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)

62. 0.034 0.043 ↓ 4.0 4 1

Bitmap Heap Scan on job job_5 (cost=2.23..106.26 rows=1 width=16) (actual time=0.021..0.043 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
63. 0.009 0.009 ↑ 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.009 rows=114 loops=1)

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

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

65. 0.032 0.040 ↓ 0.0 0 1

Bitmap Heap Scan on job job_6 (cost=2.23..106.32 rows=1 width=16) (actual time=0.040..0.040 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
66. 0.008 0.008 ↑ 1.0 114 1

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

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
67. 18.480 18.480 ↓ 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.168 rows=320 loops=110)

  • Index Cond: (job_id = job.id)
68. 0.018 201.453 ↑ 2.0 183 1

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

69. 3.064 201.435 ↑ 2.0 183 1

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

70. 2.176 198.371 ↑ 1.5 35,338 1

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

71. 0.065 91.082 ↑ 2.0 183 1

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

  • Group Key: application_4.id
72. 0.013 91.017 ↑ 2.0 183 1

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

73. 3.381 39.743 ↑ 2.0 2 1

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

  • Merge Cond: (employee.candidate_id = application_4.candidate_id)
74. 0.044 0.044 ↑ 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.013..0.044 rows=36 loops=1)

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

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

  • Sort Key: application_4.candidate_id
  • Sort Method: quicksort Memory: 4,286kB
76. 18.424 18.424 ↓ 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.011..18.424 rows=35,194 loops=1)

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

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

78. 3.266 51.244 ↑ 2.0 181 1

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

79. 2.270 47.978 ↓ 1.0 35,375 1

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

80. 0.024 17.499 ↑ 2.0 183 1

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

81. 17.475 17.475 ↑ 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.042..17.475 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
82. 3.217 28.209 ↓ 1.0 35,192 1

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

83. 7.489 24.992 ↓ 1.0 35,192 1

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

  • Hash Cond: (application_6.candidate_id = employee_1.candidate_id)
84. 14.548 14.548 ↓ 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.009..14.548 rows=35,194 loops=1)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
85. 0.689 2.955 ↑ 389.6 35 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 130kB
86. 2.266 2.266 ↑ 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.266 rows=13,616 loops=1)

  • Filter: (NOT is_alumni)
  • Rows Removed by Filter: 21
87. 3.235 105.113 ↑ 1.5 35,155 1

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

88. 16.202 101.878 ↑ 1.5 35,155 1

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

89. 4.330 85.676 ↑ 1.5 70,310 1

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

90. 2.986 39.461 ↑ 1.5 35,155 1

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

91. 14.670 36.475 ↑ 1.5 35,155 1

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

  • Group Key: application_7.id
92. 3.628 21.805 ↑ 1.5 35,155 1

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

93. 0.433 1.347 ↑ 2.2 110 1

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

  • Hash Cond: (job_7.id = job_8.id)
94. 0.587 0.587 ↑ 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.587 rows=3,962 loops=1)

95. 0.017 0.327 ↑ 2.2 110 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
96. 0.048 0.310 ↑ 2.2 110 1

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

  • Group Key: job_8.id
97. 0.009 0.262 ↑ 2.2 110 1

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

98. 0.069 0.085 ↑ 1.1 110 1

Bitmap Heap Scan on job job_8 (cost=2.24..106.27 rows=117 width=16) (actual time=0.024..0.085 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
99. 0.016 0.016 ↑ 1.0 114 1

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

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

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

101. 0.000 0.046 ↓ 0.0 0 1

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

102. 0.046 0.046 ↓ 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.046..0.046 rows=0 loops=1)

  • Filter: (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid)
  • Rows Removed by Filter: 408
103. 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
104. 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)
105. 0.000 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_2.role_id = security_role_4.id)
106. 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_4.path @> (SubPlan 2))
107. 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)

108. 0.012 0.012 ↓ 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.012..0.012 rows=0 loops=1)

  • Filter: (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid)
  • Rows Removed by Filter: 81
109. 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)
110. 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)
111. 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)
112.          

SubPlan (for Nested Loop)

113. 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)
114. 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
115. 0.000 0.017 ↓ 0.0 0 1

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

116. 0.001 0.017 ↓ 0.0 0 1

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

117. 0.016 0.016 ↓ 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.016..0.016 rows=0 loops=1)

  • Filter: ((team_id IS NULL) AND (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid))
  • Rows Removed by Filter: 81
118. 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
119. 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)
120. 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)
121. 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)

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

123. 0.001 0.089 ↓ 2.0 4 1

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

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

125. 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.021..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
126. 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)
127. 0.001 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)

128. 0.032 0.042 ↓ 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.042 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
129. 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)
130. 16.830 16.830 ↓ 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.153 rows=320 loops=110)

  • Index Cond: (job_id = job_7.id)
131. 2.959 41.885 ↑ 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.098..41.885 rows=35,155 loops=1)

132. 14.720 38.926 ↑ 1.5 35,155 1

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

  • Group Key: application_8.id
133. 3.604 24.206 ↑ 1.5 35,155 1

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

134. 0.446 1.352 ↑ 2.2 110 1

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

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

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

136. 0.017 0.309 ↑ 2.2 110 1

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

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

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

  • Group Key: job_15.id
138. 0.010 0.241 ↑ 2.2 110 1

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

139. 0.055 0.072 ↑ 1.1 110 1

Bitmap Heap Scan on job job_15 (cost=2.24..106.27 rows=117 width=16) (actual time=0.023..0.072 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
140. 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)
141. 0.000 0.045 ↓ 0.0 0 1

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

142. 0.001 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)

143. 0.044 0.044 ↓ 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.044..0.044 rows=0 loops=1)

  • Filter: (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid)
  • Rows Removed by Filter: 408
144. 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
145. 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)
146. 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)
147. 0.000 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))
148. 0.000 0.013 ↓ 0.0 0 1

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

149. 0.013 0.013 ↓ 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.013..0.013 rows=0 loops=1)

  • Filter: (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid)
  • Rows Removed by Filter: 81
150. 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)
151. 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)
152. 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)
153.          

SubPlan (for Nested Loop)

154. 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)
155. 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
156. 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)

157. 0.000 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)

158. 0.009 0.009 ↓ 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.009..0.009 rows=0 loops=1)

  • Filter: ((team_id IS NULL) AND (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid))
  • Rows Removed by Filter: 81
159. 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
160. 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)
161. 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)
162. 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)

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

164. 0.001 0.089 ↓ 2.0 4 1

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

165. 0.001 0.045 ↓ 4.0 4 1

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

166. 0.033 0.044 ↓ 4.0 4 1

Bitmap Heap Scan on job job_19 (cost=2.23..106.26 rows=1 width=16) (actual time=0.021..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
167. 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)
168. 0.000 0.043 ↓ 0.0 0 1

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

169. 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.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
170. 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)
171. 19.250 19.250 ↓ 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.175 rows=320 loops=110)

  • Index Cond: (job_id = job_14.id)
172. 18.272 18.272 ↑ 1.0 1 4,568

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=4,568)

  • 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
173. 7.990 7.990 ↑ 3.0 1 3,995

Index Scan using scheduled_interview_event_application_id_idx on scheduled_interview_event scheduled_interview_event_wzzxi (cost=0.08..0.29 rows=3 width=32) (actual time=0.002..0.002 rows=1 loops=3,995)

  • Index Cond: (application_id = application.id)
174. 6.024 6.024 ↑ 1.0 1 2,008

Index Scan using interview_event_pkey on interview_event interview_event_wzzxi (cost=0.08..0.15 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=2,008)

  • Index Cond: (id = scheduled_interview_event_wzzxi.interview_event_id)
  • Filter: ((start_time >= '2020-01-01 08:00:00+00'::timestamp with time zone) AND (start_time <= '2021-01-01 07:59:59.999+00'::timestamp with time zone))
  • Rows Removed by Filter: 0
175.          

CTE applications_with_stages

176. 11.923 32,170.561 ↓ 943.0 943 1

Unique (cost=33.25..33.29 rows=1 width=177) (actual time=2,647.720..32,170.561 rows=943 loops=1)

177. 1,085.465 32,158.638 ↓ 64,824.0 64,824 1

Nested Loop (cost=33.25..33.29 rows=1 width=177) (actual time=2,647.719..32,158.638 rows=64,824 loops=1)

  • Join Filter: (relevant_applications.id = relevant_applications_2.id)
  • Rows Removed by Join Filter: 16,930,901
178. 73.797 2,809.909 ↓ 64,824.0 64,824 1

Nested Loop Left Join (cost=26.94..26.97 rows=1 width=216) (actual time=2,618.214..2,809.909 rows=64,824 loops=1)

179. 42.022 2,671.288 ↓ 64,824.0 64,824 1

Merge Left Join (cost=26.91..26.93 rows=1 width=223) (actual time=2,618.204..2,671.288 rows=64,824 loops=1)

  • Merge Cond: ((relevant_applications.id = relevant_applications_1.id) AND (interview_stage_group_type_1.id = interview_stage_group_type_2.id))
180. 59.524 2,578.414 ↓ 64,824.0 64,824 1

Sort (cost=16.40..16.41 rows=1 width=206) (actual time=2,569.416..2,578.414 rows=64,824 loops=1)

  • Sort Key: application_9.id, interview_stage_group_type.id
  • Sort Method: quicksort Memory: 18,755kB
181. 40.003 2,518.890 ↓ 64,824.0 64,824 1

Nested Loop (cost=0.40..16.40 rows=1 width=206) (actual time=338.671..2,518.890 rows=64,824 loops=1)

182. 842.058 2,414.063 ↓ 64,824.0 64,824 1

Nested Loop (cost=0.37..14.37 rows=1 width=186) (actual time=338.659..2,414.063 rows=64,824 loops=1)

  • Join Filter: (application_history.application_id = relevant_applications.id)
  • Rows Removed by Join Filter: 10,523,884
183. 4.961 559.669 ↓ 8,164.0 8,164 1

Nested Loop Left Join (cost=0.37..14.36 rows=1 width=170) (actual time=338.656..559.669 rows=8,164 loops=1)

184. 7.812 538.380 ↓ 8,164.0 8,164 1

Nested Loop (cost=0.31..10.30 rows=1 width=126) (actual time=338.644..538.380 rows=8,164 loops=1)

185. 6.331 514.240 ↓ 8,164.0 8,164 1

Nested Loop (cost=0.26..10.19 rows=1 width=113) (actual time=338.630..514.240 rows=8,164 loops=1)

  • Join Filter: (interview_stage_group_type.""order"" <= current_stage_group_app_history.""order"")
  • Rows Removed by Join Filter: 15,116
186. 4.379 403.149 ↓ 3,880.0 3,880 1

Nested Loop (cost=0.26..6.45 rows=1 width=84) (actual time=338.613..403.149 rows=3,880 loops=1)

187. 6.512 394.890 ↓ 3,880.0 3,880 1

Nested Loop (cost=0.23..6.41 rows=1 width=96) (actual time=338.600..394.890 rows=3,880 loops=1)

188. 4.334 375.548 ↓ 6,415.0 6,415 1

Nested Loop (cost=0.17..6.35 rows=1 width=96) (actual time=338.587..375.548 rows=6,415 loops=1)

189. 2.800 351.969 ↓ 3,207.5 6,415 1

Nested Loop (cost=0.09..6.11 rows=2 width=64) (actual time=338.564..351.969 rows=6,415 loops=1)

190. 338.793 338.793 ↓ 1,297.0 1,297 1

CTE Scan on relevant_applications application_9 (cost=0.00..0.01 rows=1 width=32) (actual time=338.548..338.793 rows=1,297 loops=1)

191. 10.376 10.376 ↓ 2.5 5 1,297

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=1,297)

  • Index Cond: (application_id = application_9.id)
192. 19.245 19.245 ↑ 1.0 1 6,415

Index Scan using process_user_roles_process_id_idx on process_user_roles process_user_roles_itwug (cost=0.08..0.12 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=6,415)

  • Index Cond: (process_id = application_history.application_id)
  • Filter: (process_role_type_id = '9788ef71-46b1-494b-a9e5-c3d8d2d84733'::uuid)
  • Rows Removed by Filter: 0
193. 12.830 12.830 ↑ 1.0 1 6,415

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.002..0.002 rows=1 loops=6,415)

  • 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
194. 3.880 3.880 ↑ 1.0 1 3,880

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.001..0.001 rows=1 loops=3,880)

  • Index Cond: (id = current_stage_app_history.interview_stage_group_id)
  • Filter: is_relevant_for_funnel
195. 104.760 104.760 ↑ 1.0 6 3,880

Seq Scan on interview_stage_group_type (cost=0.00..3.72 rows=6 width=33) (actual time=0.004..0.027 rows=6 loops=3,880)

  • 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
196. 16.328 16.328 ↑ 1.0 1 8,164

Index Scan using app_user_pkey on app_user app_user_itwug (cost=0.06..0.11 rows=1 width=29) (actual time=0.002..0.002 rows=1 loops=8,164)

  • Index Cond: (id = process_user_roles_itwug.user_id)
197. 16.328 16.328 ↑ 1.0 1 8,164

Index Scan using source_pkey on source source_mwrxm (cost=0.06..4.06 rows=1 width=44) (actual time=0.002..0.002 rows=1 loops=8,164)

  • Index Cond: (application_9.source_id = id)
198. 1,012.336 1,012.336 ↓ 1,297.0 1,297 8,164

CTE Scan on relevant_applications (cost=0.00..0.01 rows=1 width=16) (actual time=0.000..0.124 rows=1,297 loops=8,164)

199. 64.824 64.824 ↑ 1.0 1 64,824

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=64,824)

  • Index Cond: (id = interview_stage_group_type.id)
  • Filter: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
200. 1.216 50.852 ↓ 940.0 1,880 1

Unique (cost=10.51..10.51 rows=2 width=153) (actual time=48.781..50.852 rows=1,880 loops=1)

201. 4.831 49.636 ↓ 3,204.0 6,408 1

Sort (cost=10.51..10.51 rows=2 width=153) (actual time=48.780..49.636 rows=6,408 loops=1)

  • Sort Key: relevant_applications_1.id, interview_stage_group_type_2.id
  • Sort Method: quicksort Memory: 1,093kB
202. 5.414 44.805 ↓ 3,207.5 6,415 1

Nested Loop Left Join (cost=0.29..10.51 rows=2 width=153) (actual time=0.048..44.805 rows=6,415 loops=1)

203. 2.894 32.976 ↓ 3,207.5 6,415 1

Nested Loop Left Join (cost=0.23..10.38 rows=2 width=65) (actual time=0.039..32.976 rows=6,415 loops=1)

204. 5.725 23.667 ↓ 3,207.5 6,415 1

Nested Loop Left Join (cost=0.20..10.31 rows=2 width=65) (actual time=0.032..23.667 rows=6,415 loops=1)

205. 1.196 11.527 ↓ 3,207.5 6,415 1

Nested Loop Left Join (cost=0.14..10.18 rows=2 width=65) (actual time=0.024..11.527 rows=6,415 loops=1)

206. 0.977 2.549 ↓ 1,297.0 1,297 1

Nested Loop Left Join (cost=0.05..4.08 rows=1 width=33) (actual time=0.013..2.549 rows=1,297 loops=1)

207. 0.275 0.275 ↓ 1,297.0 1,297 1

CTE Scan on relevant_applications relevant_applications_1 (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.275 rows=1,297 loops=1)

208. 1.297 1.297 ↑ 1.0 1 1,297

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

  • Index Cond: (relevant_applications_1.archive_reason_id = id)
209. 7.782 7.782 ↓ 2.5 5 1,297

Index Scan using application_history_application_id_idx on application_history application_history_1 (cost=0.09..6.09 rows=2 width=48) (actual time=0.003..0.006 rows=5 loops=1,297)

  • Index Cond: (application_id = relevant_applications_1.id)
210. 6.415 6.415 ↑ 1.0 1 6,415

Index Scan using interview_stage_pkey on interview_stage (cost=0.06..0.06 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=6,415)

  • Index Cond: (application_history_1.previous_interview_stage_id = id)
211. 6.415 6.415 ↑ 1.0 1 6,415

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=16) (actual time=0.001..0.001 rows=1 loops=6,415)

  • Index Cond: (interview_stage.interview_stage_group_id = id)
  • Filter: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
212. 6.415 6.415 ↑ 1.0 1 6,415

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.001..0.001 rows=1 loops=6,415)

  • Index Cond: (id = application_history_1.new_interview_stage_id)
  • Heap Fetches: 6,415
213. 64.824 64.824 ↑ 1.0 1 64,824

Index Scan using source_type_pkey on source_type source_type_mwrxm (cost=0.03..0.04 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=64,824)

  • Index Cond: (source_mwrxm.source_type_id = id)
214. 19,900.968 28,263.264 ↓ 262.0 262 64,824

GroupAggregate (cost=6.30..6.31 rows=1 width=20) (actual time=0.001..0.436 rows=262 loops=64,824)

  • Group Key: relevant_applications_2.id
215. 8,334.448 8,362.296 ↓ 1,291.5 2,583 64,824

Sort (cost=6.30..6.31 rows=2 width=20) (actual time=0.001..0.129 rows=2,583 loops=64,824)

  • Sort Key: relevant_applications_2.id
  • Sort Method: quicksort Memory: 597kB
216. 2.146 27.848 ↓ 2,588.5 5,177 1

Nested Loop (cost=0.17..6.30 rows=2 width=20) (actual time=0.020..27.848 rows=5,177 loops=1)

217. 0.000 20.525 ↓ 2,588.5 5,177 1

Nested Loop (cost=0.14..6.24 rows=2 width=32) (actual time=0.015..20.525 rows=5,177 loops=1)

218. 1.457 8.193 ↓ 3,207.5 6,415 1

Nested Loop (cost=0.09..6.11 rows=2 width=32) (actual time=0.008..8.193 rows=6,415 loops=1)

219. 0.251 0.251 ↓ 1,297.0 1,297 1

CTE Scan on relevant_applications relevant_applications_2 (cost=0.00..0.01 rows=1 width=16) (actual time=0.000..0.251 rows=1,297 loops=1)

220. 6.485 6.485 ↓ 2.5 5 1,297

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

  • Index Cond: (application_id = relevant_applications_2.id)
221. 12.830 12.830 ↑ 1.0 1 6,415

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=6,415)

  • Index Cond: (id = application_history_2.new_interview_stage_id)
  • Filter: (stage_type <> 'Archived'::enum_interview_stage_stage_type)
  • Rows Removed by Filter: 0
222. 5.177 5.177 ↑ 1.0 1 5,177

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=20) (actual time=0.001..0.001 rows=1 loops=5,177)

  • Index Cond: (id = interview_stage_1.interview_stage_group_id)
  • Filter: is_relevant_for_funnel
223. 2.484 32,174.116 ↓ 943.0 943 1

Sort (cost=0.01..0.01 rows=1 width=196) (actual time=32,174.046..32,174.116 rows=943 loops=1)

  • Sort Key: applications_with_stages.stage_order, applications_with_stages._tyeqg, applications_with_stages._tszat, applications_with_stages._tyeqg_value, applications_with_stages._tszat_value, applications_with_stages.stage_outcome, applications_with_stages.interview_stage_id, applications_with_stages.title
  • Sort Method: quicksort Memory: 274kB
224. 32,171.632 32,171.632 ↓ 943.0 943 1

CTE Scan on applications_with_stages (cost=0.00..0.01 rows=1 width=196) (actual time=2,647.723..32,171.632 rows=943 loops=1)