explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0mvt

Settings
# exclusive inclusive rows x rows loops node
1. 0.318 17,353.800 ↓ 242.0 242 1

GroupAggregate (cost=93,307.91..93,307.92 rows=1 width=188) (actual time=17,353.439..17,353.800 rows=242 loops=1)

  • Group Key: applications_with_stages.stage_order, applications_with_stages._aihsf, applications_with_stages._jmtma, applications_with_stages._aihsf_value, applications_with_stages._jmtma_value, applications_with_stages.stage_outcome, applications_with_stages.interview_stage_id, applications_with_stages.title
2.          

CTE relevant_applications

3. 0.814 386.145 ↓ 1,297.0 1,297 1

Nested Loop (cost=92.39..93,272.94 rows=1 width=804) (actual time=348.930..386.145 rows=1,297 loops=1)

4. 2.936 379.307 ↓ 2,008.0 2,008 1

Nested Loop (cost=92.30..93,272.79 rows=1 width=820) (actual time=348.914..379.307 rows=2,008 loops=1)

5. 0.228 368.381 ↓ 3,995.0 3,995 1

Nested Loop (cost=92.22..93,272.49 rows=1 width=820) (actual time=348.838..368.381 rows=3,995 loops=1)

6. 0.531 349.881 ↓ 571.0 4,568 1

Subquery Scan on "ANY_subquery" (cost=92.13..93,239.76 rows=8 width=16) (actual time=348.808..349.881 rows=4,568 loops=1)

7. 5.636 349.350 ↓ 571.0 4,568 1

HashSetOp Intersect (cost=92.13..93,239.74 rows=8 width=20) (actual time=348.807..349.350 rows=4,568 loops=1)

8. 2.838 343.714 ↓ 2.2 39,743 1

Append (cost=92.13..93,230.83 rows=17,825 width=20) (actual time=0.391..343.714 rows=39,743 loops=1)

9. 0.516 17.979 ↓ 573.2 4,586 1

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

10. 1.732 17.463 ↓ 573.2 4,586 1

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

11. 1.654 1.973 ↓ 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.379..1.973 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=495
12. 0.001 0.319 ↓ 0.0 0 1

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

13. 0.150 0.150 ↓ 1.1 2,397 1

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

  • Index Cond: (user_id = 'd419fa47-df37-4c74-aa40-0f983629ab32'::uuid)
14. 0.121 0.121 ↑ 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.121..0.121 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.013 0.013 ↑ 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.013..0.013 rows=139 loops=1)

  • Index Cond: (user_id = '603ae474-de1c-466a-835e-da22ee5a43c8'::uuid)
17. 0.012 0.012 ↑ 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.012..0.012 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.008..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. 12.268 322.897 ↓ 2.0 35,157 1

HashAggregate (cost=86,765.79..86,819.24 rows=17,817 width=20) (actual time=318.710..322.897 rows=35,157 loops=1)

  • Group Key: ""*SELECT* 2"".id
21. 2.134 310.629 ↓ 2.0 35,157 1

Append (cost=0.08..86,756.88 rows=17,817 width=16) (actual time=87.611..310.629 rows=35,157 loops=1)

22. 3.185 93.669 ↓ 2.0 34,974 1

Result (cost=0.08..22,658.70 rows=17,455 width=16) (actual time=87.610..93.669 rows=34,974 loops=1)

23. 18.928 90.484 ↓ 2.0 34,974 1

HashSetOp Intersect (cost=0.08..22,606.34 rows=17,455 width=20) (actual time=87.609..90.484 rows=34,974 loops=1)

24. 5.484 71.556 ↑ 1.0 70,170 1

Append (cost=0.08..22,570.86 rows=70,959 width=20) (actual time=0.012..71.556 rows=70,170 loops=1)

25. 2.980 25.544 ↓ 2.0 35,013 1

Subquery Scan on "*SELECT* 2" (cost=0.08..17,677.73 rows=17,455 width=20) (actual time=0.012..25.544 rows=35,013 loops=1)

26. 22.564 22.564 ↓ 2.0 35,013 1

Index Scan using application_organization_id_idx on application application_2 (cost=0.08..17,625.36 rows=17,455 width=16) (actual time=0.011..22.564 rows=35,013 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.950 40.528 ↑ 1.5 35,157 1

Subquery Scan on "*SELECT* 1" (cost=4,465.67..4,786.69 rows=53,504 width=20) (actual time=32.842..40.528 rows=35,157 loops=1)

28. 14.957 37.578 ↑ 1.5 35,157 1

HashAggregate (cost=4,465.67..4,626.18 rows=53,504 width=16) (actual time=32.841..37.578 rows=35,157 loops=1)

  • Group Key: application_3.id
29. 3.423 22.621 ↑ 1.5 35,157 1

Nested Loop (cost=1,043.72..4,438.92 rows=53,504 width=16) (actual time=0.401..22.621 rows=35,157 loops=1)

30. 0.437 1.378 ↑ 2.2 110 1

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

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

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

32. 0.017 0.321 ↑ 2.2 110 1

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

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

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

  • Group Key: job_1.id
34. 0.008 0.253 ↑ 2.2 110 1

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

35. 0.071 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.024..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
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.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_3.path @> (SubPlan 1))
44. 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)

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

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.088 ↓ 0.0 0 1

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

59. 0.003 0.087 ↓ 0.0 0 1

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

60. 0.001 0.084 ↓ 2.0 4 1

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

61. 0.001 0.043 ↓ 4.0 4 1

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

62. 0.033 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
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.040 ↓ 0.0 0 1

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

65. 0.031 0.039 ↓ 0.0 0 1

Bitmap Heap Scan on job job_6 (cost=2.23..106.32 rows=1 width=16) (actual time=0.039..0.039 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. 17.820 17.820 ↓ 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.162 rows=320 loops=110)

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

Result (cost=54,013.62..64,071.45 rows=362 width=16) (actual time=214.798..214.826 rows=183 loops=1)

69. 3.036 214.810 ↑ 2.0 183 1

HashSetOp Intersect (cost=54,013.62..64,070.36 rows=362 width=20) (actual time=214.798..214.810 rows=183 loops=1)

70. 2.137 211.774 ↑ 1.5 35,340 1

Append (cost=54,013.62..64,043.43 rows=53,866 width=20) (actual time=101.892..211.774 rows=35,340 loops=1)

71. 0.071 101.916 ↑ 2.0 183 1

HashAggregate (cost=54,013.62..54,014.71 rows=362 width=20) (actual time=101.891..101.916 rows=183 loops=1)

  • Group Key: application_4.id
72. 0.014 101.845 ↑ 2.0 183 1

Append (cost=18,137.09..54,013.44 rows=362 width=16) (actual time=38.468..101.845 rows=183 loops=1)

73. 3.958 44.575 ↑ 2.0 2 1

Merge Join (cost=18,137.09..18,170.98 rows=4 width=16) (actual time=38.467..44.575 rows=2 loops=1)

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

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

Sort (cost=18,134.76..18,152.22 rows=34,911 width=32) (actual time=37.336..40.563 rows=32,477 loops=1)

  • Sort Key: application_4.candidate_id
  • Sort Method: quicksort Memory: 4,286kB
76. 20.866 20.866 ↓ 1.0 35,196 1

Index Scan using application_organization_id_idx on application application_4 (cost=0.08..17,607.91 rows=34,911 width=32) (actual time=0.014..20.866 rows=35,196 loops=1)

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

Result (cost=0.08..35,841.90 rows=358 width=16) (actual time=57.225..57.256 rows=181 loops=1)

78. 3.312 57.238 ↑ 2.0 181 1

HashSetOp Intersect (cost=0.08..35,840.83 rows=358 width=20) (actual time=57.224..57.238 rows=181 loops=1)

79. 2.271 53.926 ↓ 1.0 35,377 1

Append (cost=0.08..35,823.20 rows=35,265 width=20) (actual time=0.079..53.926 rows=35,377 loops=1)

80. 0.024 20.724 ↑ 2.0 183 1

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

81. 20.700 20.700 ↑ 2.0 183 1

Index Scan using application_organization_id_idx on application application_5 (cost=0.08..17,625.36 rows=358 width=16) (actual time=0.077..20.700 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,013
82. 3.028 30.931 ↓ 1.0 35,194 1

Subquery Scan on "*SELECT* 6" (cost=307.79..18,143.86 rows=34,907 width=20) (actual time=2.900..30.931 rows=35,194 loops=1)

83. 7.929 27.903 ↓ 1.0 35,194 1

Hash Anti Join (cost=307.79..18,039.14 rows=34,907 width=16) (actual time=2.899..27.903 rows=35,194 loops=1)

  • Hash Cond: (application_6.candidate_id = employee_1.candidate_id)
84. 17.123 17.123 ↓ 1.0 35,196 1

Index Scan using application_organization_id_idx on application application_6 (cost=0.08..17,607.91 rows=34,911 width=32) (actual time=0.015..17.123 rows=35,196 loops=1)

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

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

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

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

  • Filter: (NOT is_alumni)
  • Rows Removed by Filter: 21
87. 3.211 107.721 ↑ 1.5 35,157 1

Result (cost=4,465.67..9,947.92 rows=53,504 width=20) (actual time=102.614..107.721 rows=35,157 loops=1)

88. 16.046 104.510 ↑ 1.5 35,157 1

HashSetOp Intersect (cost=4,465.67..9,787.41 rows=53,504 width=20) (actual time=102.613..104.510 rows=35,157 loops=1)

89. 4.368 88.464 ↑ 1.5 70,314 1

Append (cost=4,465.67..9,733.91 rows=107,008 width=20) (actual time=34.231..88.464 rows=70,314 loops=1)

90. 3.048 42.362 ↑ 1.5 35,157 1

Subquery Scan on "*SELECT* 3" (cost=4,465.67..4,786.69 rows=53,504 width=20) (actual time=34.230..42.362 rows=35,157 loops=1)

91. 15.556 39.314 ↑ 1.5 35,157 1

HashAggregate (cost=4,465.67..4,626.18 rows=53,504 width=16) (actual time=34.228..39.314 rows=35,157 loops=1)

  • Group Key: application_7.id
92. 3.457 23.758 ↑ 1.5 35,157 1

Nested Loop (cost=1,043.72..4,438.92 rows=53,504 width=16) (actual time=0.417..23.758 rows=35,157 loops=1)

93. 0.453 1.381 ↑ 2.2 110 1

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

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

95. 0.017 0.329 ↑ 2.2 110 1

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

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

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

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

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

98. 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
99. 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)
100. 0.001 0.049 ↓ 0.0 0 1

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

101. 0.000 0.048 ↓ 0.0 0 1

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

102. 0.048 0.048 ↓ 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.048..0.048 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.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)

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

117. 0.009 0.009 ↓ 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.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
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.093 ↓ 0.0 0 1

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

122. 0.004 0.093 ↓ 0.0 0 1

HashSetOp Intersect (cost=2.23..212.59 rows=1 width=20) (actual time=0.092..0.093 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.023..0.089 rows=4 loops=1)

124. 0.002 0.046 ↓ 4.0 4 1

Subquery Scan on "*SELECT* 5_1" (cost=2.23..106.26 rows=1 width=20) (actual time=0.023..0.046 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.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
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.000 0.042 ↓ 0.0 0 1

Subquery Scan on "*SELECT* 6_2" (cost=2.23..106.32 rows=1 width=20) (actual time=0.042..0.042 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. 18.920 18.920 ↓ 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.172 rows=320 loops=110)

  • Index Cond: (job_id = job_7.id)
131. 2.952 41.734 ↑ 1.5 35,157 1

Subquery Scan on "*SELECT* 4" (cost=4,465.68..4,786.70 rows=53,504 width=20) (actual time=34.352..41.734 rows=35,157 loops=1)

132. 14.143 38.782 ↑ 1.5 35,157 1

HashAggregate (cost=4,465.68..4,626.19 rows=53,504 width=16) (actual time=34.351..38.782 rows=35,157 loops=1)

  • Group Key: application_8.id
133. 3.494 24.639 ↑ 1.5 35,157 1

Nested Loop (cost=1,043.73..4,438.93 rows=53,504 width=16) (actual time=0.397..24.639 rows=35,157 loops=1)

134. 0.439 1.345 ↑ 2.2 110 1

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

  • Hash Cond: (job_14.id = job_15.id)
135. 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.010..0.588 rows=3,962 loops=1)

136. 0.018 0.318 ↑ 2.2 110 1

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

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

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

  • Group Key: job_15.id
138. 0.009 0.250 ↑ 2.2 110 1

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

139. 0.063 0.081 ↑ 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.081 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.018 0.018 ↑ 1.0 114 1

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

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
141. 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)

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

143. 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
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.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))
148. 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)

149. 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
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.001 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.091 ↓ 0.0 0 1

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

164. 0.001 0.088 ↓ 2.0 4 1

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

165. 0.001 0.044 ↓ 4.0 4 1

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

166. 0.032 0.043 ↓ 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.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
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.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
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.800 19.800 ↓ 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.180 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_tbgoa (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_tbgoa (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_tbgoa.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_stage_outcomes

176. 3.704 252.261 ↓ 1,297.0 11,673 1

Hash Left Join (cost=16.83..20.47 rows=9 width=77) (actual time=120.388..252.261 rows=11,673 loops=1)

  • Hash Cond: ((interview_stage_group_type.id = application_with_stage_entries.group_id) AND (relevant_applications.id = application_with_stage_entries.application_id))
177. 1.679 198.460 ↓ 1,297.0 11,673 1

Nested Loop (cost=6.30..9.91 rows=9 width=53) (actual time=70.279..198.460 rows=11,673 loops=1)

178. 47.885 177.326 ↓ 1,297.0 1,297 1

Nested Loop (cost=6.30..6.32 rows=1 width=20) (actual time=70.268..177.326 rows=1,297 loops=1)

  • Join Filter: (relevant_applications.id = relevant_applications_1.id)
  • Rows Removed by Join Filter: 665,361
179. 0.838 71.359 ↓ 514.0 514 1

GroupAggregate (cost=6.30..6.31 rows=1 width=20) (actual time=70.150..71.359 rows=514 loops=1)

  • Group Key: relevant_applications_1.id
180. 2.386 70.521 ↓ 2,588.5 5,177 1

Sort (cost=6.30..6.31 rows=2 width=20) (actual time=70.143..70.521 rows=5,177 loops=1)

  • Sort Key: relevant_applications_1.id
  • Sort Method: quicksort Memory: 597kB
181. 2.470 68.135 ↓ 2,588.5 5,177 1

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

182. 0.386 60.488 ↓ 2,588.5 5,177 1

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

183. 1.135 47.272 ↓ 3,207.5 6,415 1

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

184. 38.355 38.355 ↓ 1,297.0 1,297 1

CTE Scan on relevant_applications relevant_applications_1 (cost=0.00..0.01 rows=1 width=16) (actual time=0.001..38.355 rows=1,297 loops=1)

185. 7.782 7.782 ↓ 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.003..0.006 rows=5 loops=1,297)

  • Index Cond: (application_id = relevant_applications_1.id)
186. 12.830 12.830 ↑ 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.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)
  • Rows Removed by Filter: 0
187. 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_1 (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.interview_stage_group_id)
  • Filter: is_relevant_for_funnel
188. 58.082 58.082 ↓ 1,297.0 1,297 514

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

189. 19.455 19.455 ↑ 1.0 9 1,297

Seq Scan on interview_stage_group_type (cost=0.00..3.56 rows=9 width=33) (actual time=0.002..0.015 rows=9 loops=1,297)

  • Filter: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
  • Rows Removed by Filter: 159
190. 0.458 50.097 ↓ 935.5 1,871 1

Hash (cost=10.52..10.52 rows=2 width=65) (actual time=50.097..50.097 rows=1,871 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 192kB
191. 0.273 49.639 ↓ 941.0 1,882 1

Subquery Scan on application_with_stage_entries (cost=10.51..10.52 rows=2 width=65) (actual time=48.307..49.639 rows=1,882 loops=1)

192. 0.708 49.366 ↓ 941.0 1,882 1

Unique (cost=10.51..10.51 rows=2 width=153) (actual time=48.305..49.366 rows=1,882 loops=1)

193. 4.565 48.658 ↓ 3,207.5 6,415 1

Sort (cost=10.51..10.51 rows=2 width=153) (actual time=48.304..48.658 rows=6,415 loops=1)

  • Sort Key: relevant_applications_2.id, interview_stage_group_type_2.id
  • Sort Method: quicksort Memory: 1,093kB
194. 5.283 44.093 ↓ 3,207.5 6,415 1

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

195. 2.881 32.395 ↓ 3,207.5 6,415 1

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

196. 5.859 23.099 ↓ 3,207.5 6,415 1

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

197. 1.740 10.825 ↓ 3,207.5 6,415 1

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

198. 1.069 2.600 ↓ 1,297.0 1,297 1

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

199. 0.234 0.234 ↓ 1,297.0 1,297 1

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

200. 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_2.archive_reason_id = id)
201. 6.485 6.485 ↓ 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.005 rows=5 loops=1,297)

  • Index Cond: (application_id = relevant_applications_2.id)
202. 6.415 6.415 ↑ 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.001..0.001 rows=1 loops=6,415)

  • Index Cond: (application_history_1.previous_interview_stage_id = id)
203. 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_1.interview_stage_group_id = id)
  • Filter: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
204. 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
205.          

CTE applications_with_stages

206. 5.512 17,351.431 ↓ 943.0 943 1

Unique (cost=14.49..14.49 rows=1 width=177) (actual time=17,342.551..17,351.431 rows=943 loops=1)

207. 48.324 17,345.919 ↓ 64,824.0 64,824 1

Sort (cost=14.49..14.49 rows=1 width=177) (actual time=17,342.550..17,345.919 rows=64,824 loops=1)

  • Sort Key: application_9.id, interview_stage_group_type_3.id
  • Sort Method: quicksort Memory: 18,717kB
208. 7,833.260 17,297.595 ↓ 64,824.0 64,824 1

Nested Loop (cost=0.40..14.49 rows=1 width=177) (actual time=603.884..17,297.595 rows=64,824 loops=1)

  • Join Filter: ((application_history_2.application_id = applications_with_stage_outcomes.application_id) AND (interview_stage_group_type_3.id = applications_with_stage_outcomes.stage_group_id))
  • Rows Removed by Join Filter: 95,233,548
209. 7.899 606.395 ↓ 8,164.0 8,164 1

Nested Loop (cost=0.40..14.40 rows=1 width=163) (actual time=349.056..606.395 rows=8,164 loops=1)

210. 10.543 582.168 ↓ 8,164.0 8,164 1

Nested Loop Left Join (cost=0.34..14.29 rows=1 width=150) (actual time=349.042..582.168 rows=8,164 loops=1)

211. 9.884 563.461 ↓ 8,164.0 8,164 1

Nested Loop Left Join (cost=0.31..14.25 rows=1 width=157) (actual time=349.029..563.461 rows=8,164 loops=1)

212. 8.201 537.249 ↓ 8,164.0 8,164 1

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

  • Join Filter: (interview_stage_group_type_3.""order"" <= current_stage_group_app_history.""order"")
  • Rows Removed by Join Filter: 15,116
213. 1.841 424.288 ↓ 3,880.0 3,880 1

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

214. 3.382 414.687 ↓ 3,880.0 3,880 1

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

215. 1.230 392.060 ↓ 6,415.0 6,415 1

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

216. 2.743 365.170 ↓ 3,207.5 6,415 1

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

217. 349.457 349.457 ↓ 1,297.0 1,297 1

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

218. 12.970 12.970 ↓ 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.005..0.010 rows=5 loops=1,297)

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

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

  • Index Cond: (process_id = application_history_2.application_id)
  • Filter: (process_role_type_id = '9788ef71-46b1-494b-a9e5-c3d8d2d84733'::uuid)
  • Rows Removed by Filter: 0
220. 19.245 19.245 ↑ 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.003..0.003 rows=1 loops=6,415)

  • Index Cond: (id = application_history_2.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
221. 7.760 7.760 ↑ 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.002..0.002 rows=1 loops=3,880)

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

Seq Scan on interview_stage_group_type interview_stage_group_type_3 (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
223. 16.328 16.328 ↑ 1.0 1 8,164

Index Scan using source_pkey on source source_logug (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)
224. 8.164 8.164 ↑ 1.0 1 8,164

Index Scan using source_type_pkey on source_type source_type_logug (cost=0.03..0.04 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=8,164)

  • Index Cond: (source_logug.source_type_id = id)
225. 16.328 16.328 ↑ 1.0 1 8,164

Index Scan using app_user_pkey on app_user app_user_vomtl (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_vomtl.user_id)
226. 8,857.940 8,857.940 ↓ 1,297.0 11,673 8,164

CTE Scan on applications_with_stage_outcomes (cost=0.00..0.05 rows=9 width=64) (actual time=0.015..1.085 rows=11,673 loops=8,164)

227. 1.612 17,353.482 ↓ 943.0 943 1

Sort (cost=0.01..0.01 rows=1 width=196) (actual time=17,353.430..17,353.482 rows=943 loops=1)

  • Sort Key: applications_with_stages.stage_order, applications_with_stages._aihsf, applications_with_stages._jmtma, applications_with_stages._aihsf_value, applications_with_stages._jmtma_value, applications_with_stages.stage_outcome, applications_with_stages.interview_stage_id, applications_with_stages.title
  • Sort Method: quicksort Memory: 274kB
228. 17,351.870 17,351.870 ↓ 943.0 943 1

CTE Scan on applications_with_stages (cost=0.00..0.01 rows=1 width=196) (actual time=17,342.555..17,351.870 rows=943 loops=1)