explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cWLc

Settings
# exclusive inclusive rows x rows loops node
1. 0.220 554,745.146 ↓ 158.0 158 1

GroupAggregate (cost=92,670.77..92,670.78 rows=1 width=188) (actual time=554,744.898..554,745.146 rows=158 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.699 364.038 ↓ 877.0 877 1

Nested Loop (cost=79.23..92,637.81 rows=1 width=804) (actual time=332.504..364.038 rows=877 loops=1)

4. 2.302 359.142 ↓ 1,399.0 1,399 1

Nested Loop (cost=79.15..92,637.66 rows=1 width=820) (actual time=332.486..359.142 rows=1,399 loops=1)

5. 3.647 349.444 ↓ 3,698.0 3,698 1

Nested Loop (cost=79.06..92,637.36 rows=1 width=820) (actual time=332.414..349.444 rows=3,698 loops=1)

6. 0.455 333.311 ↓ 594.6 4,162 1

Subquery Scan on "ANY_subquery" (cost=78.98..92,608.72 rows=7 width=16) (actual time=332.388..333.311 rows=4,162 loops=1)

7. 5.639 332.856 ↓ 594.6 4,162 1

HashSetOp Intersect (cost=78.98..92,608.70 rows=7 width=20) (actual time=332.387..332.856 rows=4,162 loops=1)

8. 2.532 327.217 ↓ 2.2 39,318 1

Append (cost=78.98..92,599.80 rows=17,803 width=20) (actual time=0.396..327.217 rows=39,318 loops=1)

9. 0.489 16.437 ↓ 596.3 4,174 1

Subquery Scan on "*SELECT* 8" (cost=78.98..5,852.07 rows=7 width=20) (actual time=0.395..16.437 rows=4,174 loops=1)

10. 1.519 15.948 ↓ 596.3 4,174 1

Nested Loop (cost=78.98..5,852.05 rows=7 width=16) (actual time=0.395..15.948 rows=4,174 loops=1)

11. 1.585 1.907 ↓ 33.9 4,174 1

Bitmap Heap Scan on process_user_roles (cost=78.89..5,352.98 rows=123 width=16) (actual time=0.384..1.907 rows=4,174 loops=1)

  • Recheck Cond: ((user_id = 'd419fa47-df37-4c74-aa40-0f983629ab32'::uuid) OR (user_id = '44664ef6-dabe-4b95-95c8-41b130191b48'::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: 5
  • Heap Blocks: exact=472
12. 0.001 0.322 ↓ 0.0 0 1

BitmapOr (cost=78.89..78.89 rows=4,413 width=0) (actual time=0.322..0.322 rows=0 loops=1)

13. 0.169 0.169 ↓ 1.1 2,391 1

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

  • Index Cond: (user_id = 'd419fa47-df37-4c74-aa40-0f983629ab32'::uuid)
14. 0.144 0.144 ↑ 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.144..0.144 rows=1,798 loops=1)

  • Index Cond: (user_id = '44664ef6-dabe-4b95-95c8-41b130191b48'::uuid)
15. 0.008 0.008 ↑ 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.008 rows=61 loops=1)

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

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,174)

  • Index Cond: (id = process_user_roles.process_id)
  • Filter: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
17. 13.194 308.248 ↓ 2.0 35,144 1

HashAggregate (cost=86,667.63..86,721.02 rows=17,796 width=20) (actual time=304.016..308.248 rows=35,144 loops=1)

  • Group Key: ""*SELECT* 2"".id
18. 2.175 295.054 ↓ 2.0 35,144 1

Append (cost=0.08..86,658.74 rows=17,796 width=16) (actual time=88.944..295.054 rows=35,144 loops=1)

19. 3.318 95.204 ↓ 2.0 34,961 1

Result (cost=0.08..22,633.00 rows=17,434 width=16) (actual time=88.943..95.204 rows=34,961 loops=1)

20. 21.167 91.886 ↓ 2.0 34,961 1

HashSetOp Intersect (cost=0.08..22,580.70 rows=17,434 width=20) (actual time=88.942..91.886 rows=34,961 loops=1)

21. 4.548 70.719 ↑ 1.0 70,144 1

Append (cost=0.08..22,545.26 rows=70,875 width=20) (actual time=0.017..70.719 rows=70,144 loops=1)

22. 3.000 25.463 ↓ 2.0 35,000 1

Subquery Scan on "*SELECT* 2" (cost=0.08..17,657.69 rows=17,434 width=20) (actual time=0.017..25.463 rows=35,000 loops=1)

23. 22.463 22.463 ↓ 2.0 35,000 1

Index Scan using application_organization_id_idx on application application_2 (cost=0.08..17,605.38 rows=17,434 width=16) (actual time=0.016..22.463 rows=35,000 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
24. 3.057 40.708 ↑ 1.5 35,144 1

Subquery Scan on "*SELECT* 1" (cost=4,460.62..4,781.26 rows=53,441 width=20) (actual time=32.403..40.708 rows=35,144 loops=1)

25. 14.874 37.651 ↑ 1.5 35,144 1

HashAggregate (cost=4,460.62..4,620.94 rows=53,441 width=16) (actual time=32.402..37.651 rows=35,144 loops=1)

  • Group Key: application_3.id
26. 3.653 22.777 ↑ 1.5 35,144 1

Nested Loop (cost=1,043.72..4,433.90 rows=53,441 width=16) (actual time=0.397..22.777 rows=35,144 loops=1)

27. 0.457 1.414 ↑ 2.2 110 1

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

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

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

29. 0.016 0.319 ↑ 2.2 110 1

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

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

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

  • Group Key: job_1.id
31. 0.010 0.255 ↑ 2.2 110 1

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

32. 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
33. 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)
34. 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)

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

36. 0.045 0.045 ↓ 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.045 rows=0 loops=1)

  • Filter: (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid)
  • Rows Removed by Filter: 408
37. 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
38. 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)
39. 0.000 0.012 ↓ 0.0 0 1

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

  • Join Filter: (user_team_security_roles.role_id = security_role_1.id)
40. 0.001 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))
41. 0.000 0.011 ↓ 0.0 0 1

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

42. 0.011 0.011 ↓ 0.0 0 1

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

  • Filter: (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid)
  • Rows Removed by Filter: 81
43. 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)
44. 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)
45. 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)
46.          

SubPlan (for Nested Loop)

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

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

51. 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
52. 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
53. 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)
54. 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)
55. 0.000 0.091 ↓ 0.0 0 1

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

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

57. 0.002 0.085 ↓ 2.0 4 1

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

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

59. 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
60. 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)
61. 0.000 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)

62. 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
63. 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)
64. 17.710 17.710 ↓ 1.4 319 110

Index Scan using application_job_id_idx on application application_3 (cost=0.08..12.97 rows=225 width=32) (actual time=0.006..0.161 rows=319 loops=110)

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

Result (cost=53,952.64..63,999.04 rows=362 width=16) (actual time=197.648..197.675 rows=183 loops=1)

66. 3.049 197.658 ↑ 2.0 183 1

HashSetOp Intersect (cost=53,952.64..63,997.95 rows=362 width=20) (actual time=197.647..197.658 rows=183 loops=1)

67. 2.155 194.609 ↑ 1.5 35,327 1

Append (cost=53,952.64..63,971.05 rows=53,803 width=20) (actual time=91.760..194.609 rows=35,327 loops=1)

68. 0.066 91.784 ↑ 2.0 183 1

HashAggregate (cost=53,952.64..53,953.72 rows=362 width=20) (actual time=91.759..91.784 rows=183 loops=1)

  • Group Key: application_4.id
69. 0.013 91.718 ↑ 2.0 183 1

Append (cost=18,116.44..53,952.45 rows=362 width=16) (actual time=36.286..91.718 rows=183 loops=1)

70. 3.455 41.129 ↑ 2.0 2 1

Merge Join (cost=18,116.44..18,150.29 rows=4 width=16) (actual time=36.285..41.129 rows=2 loops=1)

  • Merge Cond: (employee.candidate_id = application_4.candidate_id)
71. 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.020..0.053 rows=36 loops=1)

  • Filter: ((NOT is_alumni) AND (user_id IS DISTINCT FROM '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid))
72. 18.165 37.621 ↑ 1.1 32,463 1

Sort (cost=18,114.11..18,131.54 rows=34,869 width=32) (actual time=35.402..37.621 rows=32,463 loops=1)

  • Sort Key: application_4.candidate_id
  • Sort Method: quicksort Memory: 4,285kB
73. 19.456 19.456 ↓ 1.0 35,183 1

Index Scan using application_organization_id_idx on application application_4 (cost=0.08..17,587.95 rows=34,869 width=32) (actual time=0.017..19.456 rows=35,183 loops=1)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
74. 0.016 50.576 ↑ 2.0 181 1

Result (cost=0.08..35,801.61 rows=358 width=16) (actual time=50.547..50.576 rows=181 loops=1)

75. 3.258 50.560 ↑ 2.0 181 1

HashSetOp Intersect (cost=0.08..35,800.54 rows=358 width=20) (actual time=50.547..50.560 rows=181 loops=1)

76. 2.319 47.302 ↓ 1.0 35,364 1

Append (cost=0.08..35,782.92 rows=35,223 width=20) (actual time=0.049..47.302 rows=35,364 loops=1)

77. 0.022 18.117 ↑ 2.0 183 1

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

78. 18.095 18.095 ↑ 2.0 183 1

Index Scan using application_organization_id_idx on application application_5 (cost=0.08..17,605.38 rows=358 width=16) (actual time=0.047..18.095 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,000
79. 3.063 26.866 ↓ 1.0 35,181 1

Subquery Scan on "*SELECT* 6" (cost=307.79..18,123.63 rows=34,865 width=20) (actual time=2.881..26.866 rows=35,181 loops=1)

80. 7.392 23.803 ↓ 1.0 35,181 1

Hash Anti Join (cost=307.79..18,019.04 rows=34,865 width=16) (actual time=2.880..23.803 rows=35,181 loops=1)

  • Hash Cond: (application_6.candidate_id = employee_1.candidate_id)
81. 13.567 13.567 ↓ 1.0 35,183 1

Index Scan using application_organization_id_idx on application application_6 (cost=0.08..17,587.95 rows=34,869 width=32) (actual time=0.012..13.567 rows=35,183 loops=1)

  • Index Cond: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
82. 0.652 2.844 ↑ 389.6 35 1

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

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

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

  • Filter: (NOT is_alumni)
  • Rows Removed by Filter: 21
84. 3.202 100.670 ↑ 1.5 35,144 1

Result (cost=4,460.62..9,936.63 rows=53,441 width=20) (actual time=95.542..100.670 rows=35,144 loops=1)

85. 15.577 97.468 ↑ 1.5 35,144 1

HashSetOp Intersect (cost=4,460.62..9,776.30 rows=53,441 width=20) (actual time=95.541..97.468 rows=35,144 loops=1)

86. 4.377 81.891 ↑ 1.5 70,288 1

Append (cost=4,460.62..9,722.86 rows=106,882 width=20) (actual time=31.048..81.891 rows=70,288 loops=1)

87. 2.940 38.356 ↑ 1.5 35,144 1

Subquery Scan on "*SELECT* 3" (cost=4,460.62..4,781.26 rows=53,441 width=20) (actual time=31.048..38.356 rows=35,144 loops=1)

88. 14.314 35.416 ↑ 1.5 35,144 1

HashAggregate (cost=4,460.62..4,620.94 rows=53,441 width=16) (actual time=31.047..35.416 rows=35,144 loops=1)

  • Group Key: application_7.id
89. 3.482 21.102 ↑ 1.5 35,144 1

Nested Loop (cost=1,043.72..4,433.90 rows=53,441 width=16) (actual time=0.404..21.102 rows=35,144 loops=1)

90. 0.433 1.340 ↑ 2.2 110 1

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

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

92. 0.016 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
93. 0.051 0.313 ↑ 2.2 110 1

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

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

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

95. 0.076 0.091 ↑ 1.1 110 1

Bitmap Heap Scan on job job_8 (cost=2.24..106.27 rows=117 width=16) (actual time=0.028..0.091 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
96. 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)
97. 0.000 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)

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

99. 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
100. 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
101. 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)
102. 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)
103. 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))
104. 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)

105. 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
106. 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)
107. 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)
108. 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)
109.          

SubPlan (for Nested Loop)

110. 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)
111. 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
112. 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)

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

114. 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
115. 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
116. 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)
117. 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)
118. 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)

119. 0.003 0.093 ↓ 0.0 0 1

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

120. 0.001 0.090 ↓ 2.0 4 1

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

121. 0.001 0.047 ↓ 4.0 4 1

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

122. 0.036 0.046 ↓ 4.0 4 1

Bitmap Heap Scan on job job_12 (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
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. 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)

125. 0.033 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.041..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
126. 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)
127. 16.280 16.280 ↓ 1.4 319 110

Index Scan using application_job_id_idx on application application_7 (cost=0.08..12.97 rows=225 width=32) (actual time=0.006..0.148 rows=319 loops=110)

  • Index Cond: (job_id = job_7.id)
128. 3.062 39.158 ↑ 1.5 35,144 1

Subquery Scan on "*SELECT* 4" (cost=4,460.63..4,781.28 rows=53,441 width=20) (actual time=31.798..39.158 rows=35,144 loops=1)

129. 14.034 36.096 ↑ 1.5 35,144 1

HashAggregate (cost=4,460.63..4,620.95 rows=53,441 width=16) (actual time=31.797..36.096 rows=35,144 loops=1)

  • Group Key: application_8.id
130. 3.624 22.062 ↑ 1.5 35,144 1

Nested Loop (cost=1,043.73..4,433.91 rows=53,441 width=16) (actual time=0.389..22.062 rows=35,144 loops=1)

131. 0.438 1.278 ↑ 2.2 110 1

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

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

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

133. 0.028 0.314 ↑ 2.2 110 1

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

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

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

  • Group Key: job_15.id
135. 0.009 0.233 ↑ 2.2 110 1

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

136. 0.047 0.061 ↑ 1.1 110 1

Bitmap Heap Scan on job job_15 (cost=2.24..106.27 rows=117 width=16) (actual time=0.020..0.061 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
137. 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)
138. 0.000 0.042 ↓ 0.0 0 1

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

139. 0.000 0.042 ↓ 0.0 0 1

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

140. 0.042 0.042 ↓ 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.042..0.042 rows=0 loops=1)

  • Filter: (user_id = '5b7dc471-c25c-47a6-8c56-8d404754d00d'::uuid)
  • Rows Removed by Filter: 408
141. 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
142. 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)
143. 0.001 0.012 ↓ 0.0 0 1

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

  • Join Filter: (user_team_security_roles_4.role_id = security_role_7.id)
144. 0.000 0.011 ↓ 0.0 0 1

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

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

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

146. 0.011 0.011 ↓ 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.011..0.011 rows=0 loops=1)

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

SubPlan (for Nested Loop)

151. 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)
152. 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
153. 0.000 0.012 ↓ 0.0 0 1

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

154. 0.001 0.012 ↓ 0.0 0 1

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

155. 0.011 0.011 ↓ 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.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
156. 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
157. 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)
158. 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)
159. 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)

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

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

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

163. 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
164. 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)
165. 0.000 0.042 ↓ 0.0 0 1

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

166. 0.032 0.042 ↓ 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.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
167. 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)
168. 17.160 17.160 ↓ 1.4 319 110

Index Scan using application_job_id_idx on application application_8 (cost=0.08..12.97 rows=225 width=32) (actual time=0.005..0.156 rows=319 loops=110)

  • Index Cond: (job_id = job_14.id)
169. 12.486 12.486 ↑ 1.0 1 4,162

Index Scan using application_pkey on application (cost=0.08..4.09 rows=1 width=804) (actual time=0.003..0.003 rows=1 loops=4,162)

  • 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
170. 7.396 7.396 ↓ 0.0 0 3,698

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=0 loops=3,698)

  • Index Cond: (application_id = application.id)
171. 4.197 4.197 ↑ 1.0 1 1,399

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

  • 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
172.          

CTE applications_with_stages

173. 2.982 554,743.542 ↓ 669.0 669 1

Unique (cost=32.96..32.96 rows=1 width=177) (actual time=554,738.755..554,743.542 rows=669 loops=1)

174. 47.844 554,740.560 ↓ 35,619.0 35,619 1

Sort (cost=32.96..32.96 rows=1 width=177) (actual time=554,738.754..554,740.560 rows=35,619 loops=1)

  • Sort Key: application_9.id, interview_stage_group_type.id
  • Sort Method: quicksort Memory: 10,969kB
175. 76.073 554,692.716 ↓ 35,619.0 35,619 1

Nested Loop Left Join (cost=17.24..32.95 rows=1 width=177) (actual time=478.057..554,692.716 rows=35,619 loops=1)

176. 522.433 554,581.024 ↓ 35,619.0 35,619 1

Nested Loop Left Join (cost=17.21..32.92 rows=1 width=170) (actual time=478.041..554,581.024 rows=35,619 loops=1)

  • Join Filter: (relevant_applications.id = application_9.id)
  • Rows Removed by Join Filter: 4,111,714
177. 11.392 600.076 ↓ 4,729.0 4,729 1

Nested Loop Left Join (cost=0.37..14.36 rows=1 width=138) (actual time=332.605..600.076 rows=4,729 loops=1)

178. 13.088 565.039 ↓ 4,729.0 4,729 1

Nested Loop (cost=0.31..10.30 rows=1 width=94) (actual time=332.593..565.039 rows=4,729 loops=1)

179. 16.317 523.577 ↓ 4,729.0 4,729 1

Nested Loop (cost=0.26..10.19 rows=1 width=81) (actual time=332.579..523.577 rows=4,729 loops=1)

  • Join Filter: (interview_stage_group_type.""order"" <= current_stage_group_app_history.""order"")
  • Rows Removed by Join Filter: 10,043
180. 5.297 418.628 ↓ 2,462.0 2,462 1

Nested Loop (cost=0.26..6.45 rows=1 width=52) (actual time=332.565..418.628 rows=2,462 loops=1)

181. 7.616 408.407 ↓ 2,462.0 2,462 1

Nested Loop (cost=0.23..6.41 rows=1 width=64) (actual time=332.553..408.407 rows=2,462 loops=1)

182. 6.100 383.967 ↓ 4,206.0 4,206 1

Nested Loop (cost=0.17..6.35 rows=1 width=64) (actual time=332.538..383.967 rows=4,206 loops=1)

183. 4.276 348.425 ↓ 2,103.0 4,206 1

Nested Loop (cost=0.09..6.11 rows=2 width=64) (actual time=332.523..348.425 rows=4,206 loops=1)

184. 332.748 332.748 ↓ 877.0 877 1

CTE Scan on relevant_applications application_9 (cost=0.00..0.01 rows=1 width=32) (actual time=332.508..332.748 rows=877 loops=1)

185. 11.401 11.401 ↓ 2.5 5 877

Index Scan using application_history_application_id_idx on application_history (cost=0.09..6.09 rows=2 width=32) (actual time=0.006..0.013 rows=5 loops=877)

  • Index Cond: (application_id = application_9.id)
186. 29.442 29.442 ↑ 1.0 1 4,206

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.006..0.007 rows=1 loops=4,206)

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

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.004..0.004 rows=1 loops=4,206)

  • 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
188. 4.924 4.924 ↑ 1.0 1 2,462

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=2,462)

  • Index Cond: (id = current_stage_app_history.interview_stage_group_id)
  • Filter: is_relevant_for_funnel
189. 88.632 88.632 ↑ 1.0 6 2,462

Seq Scan on interview_stage_group_type (cost=0.00..3.72 rows=6 width=33) (actual time=0.006..0.036 rows=6 loops=2,462)

  • 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
190. 28.374 28.374 ↑ 1.0 1 4,729

Index Scan using app_user_pkey on app_user app_user_itwug (cost=0.06..0.11 rows=1 width=29) (actual time=0.006..0.006 rows=1 loops=4,729)

  • Index Cond: (id = process_user_roles_itwug.user_id)
191. 23.645 23.645 ↑ 1.0 1 4,729

Index Scan using source_pkey on source source_mwrxm (cost=0.06..4.06 rows=1 width=44) (actual time=0.005..0.005 rows=1 loops=4,729)

  • Index Cond: (application_9.source_id = id)
192. 4,010.192 553,458.515 ↓ 877.0 877 4,729

Merge Left Join (cost=16.84..18.55 rows=1 width=64) (actual time=54.307..117.035 rows=877 loops=4,729)

  • 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: 1,260
193. 3,612.956 384,987.890 ↓ 877.0 877 4,729

Nested Loop (cost=6.33..8.03 rows=1 width=40) (actual time=20.642..81.410 rows=877 loops=4,729)

194. 127,706.645 377,227.601 ↓ 877.0 877 4,729

Nested Loop (cost=6.30..6.32 rows=1 width=20) (actual time=20.638..79.769 rows=877 loops=4,729)

  • Join Filter: (relevant_applications.id = relevant_applications_1.id)
  • Rows Removed by Join Filter: 347,292
195. 2,950.896 101,205.329 ↓ 397.0 397 4,729

GroupAggregate (cost=6.30..6.31 rows=1 width=20) (actual time=20.546..21.401 rows=397 loops=4,729)

  • Group Key: relevant_applications_1.id
196. 5,967.998 98,254.433 ↓ 1,669.5 3,339 4,729

Sort (cost=6.30..6.31 rows=2 width=20) (actual time=20.544..20.777 rows=3,339 loops=4,729)

  • Sort Key: relevant_applications_1.id
  • Sort Method: quicksort Memory: 357kB
197. 7,722.457 92,286.435 ↓ 1,669.5 3,339 4,729

Nested Loop (cost=0.17..6.30 rows=2 width=20) (actual time=0.018..19.515 rows=3,339 loops=4,729)

198. 718.808 68,773.847 ↓ 1,669.5 3,339 4,729

Nested Loop (cost=0.14..6.24 rows=2 width=32) (actual time=0.014..14.543 rows=3,339 loops=4,729)

199. 6,927.985 28,274.691 ↓ 2,103.0 4,206 4,729

Nested Loop (cost=0.09..6.11 rows=2 width=32) (actual time=0.008..5.979 rows=4,206 loops=4,729)

200. 610.041 610.041 ↓ 877.0 877 4,729

CTE Scan on relevant_applications relevant_applications_1 (cost=0.00..0.01 rows=1 width=16) (actual time=0.000..0.129 rows=877 loops=4,729)

201. 20,736.665 20,736.665 ↓ 2.5 5 4,147,333

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

  • Index Cond: (application_id = relevant_applications_1.id)
202. 39,780.348 39,780.348 ↑ 1.0 1 19,890,174

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=19,890,174)

  • Index Cond: (id = application_history_1.new_interview_stage_id)
  • Filter: (stage_type <> 'Archived'::enum_interview_stage_stage_type)
  • Rows Removed by Filter: 0
203. 15,790.131 15,790.131 ↑ 1.0 1 15,790,131

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=15,790,131)

  • Index Cond: (id = interview_stage.interview_stage_group_id)
  • Filter: is_relevant_for_funnel
204. 148,315.627 148,315.627 ↓ 877.0 877 1,877,413

CTE Scan on relevant_applications (cost=0.00..0.01 rows=1 width=16) (actual time=0.000..0.079 rows=877 loops=1,877,413)

205. 4,147.333 4,147.333 ↑ 1.0 1 4,147,333

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

  • Index Cond: (id = interview_stage_group_type.id)
  • Filter: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
206. 988.361 164,460.433 ↓ 1,048.0 2,096 4,729

Materialize (cost=10.51..10.52 rows=2 width=65) (actual time=33.659..34.777 rows=2,096 loops=4,729)

207. 2,879.961 163,472.072 ↓ 706.0 1,412 4,729

Unique (cost=10.51..10.51 rows=2 width=153) (actual time=33.657..34.568 rows=1,412 loops=4,729)

208. 13,387.799 160,592.111 ↓ 2,100.0 4,200 4,729

Sort (cost=10.51..10.51 rows=2 width=153) (actual time=33.657..33.959 rows=4,200 loops=4,729)

  • Sort Key: relevant_applications_2.id, interview_stage_group_type_3.id
  • Sort Method: quicksort Memory: 784kB
209. 19,805.052 147,204.312 ↓ 2,103.0 4,206 4,729

Nested Loop Left Join (cost=0.29..10.51 rows=2 width=153) (actual time=0.026..31.128 rows=4,206 loops=4,729)

210. 10,905.074 107,509.086 ↓ 2,103.0 4,206 4,729

Nested Loop Left Join (cost=0.23..10.38 rows=2 width=65) (actual time=0.021..22.734 rows=4,206 loops=4,729)

211. 20,391.448 76,713.838 ↓ 2,103.0 4,206 4,729

Nested Loop Left Join (cost=0.20..10.31 rows=2 width=65) (actual time=0.018..16.222 rows=4,206 loops=4,729)

212. 6,644.245 36,432.216 ↓ 2,103.0 4,206 4,729

Nested Loop Left Join (cost=0.14..10.18 rows=2 width=65) (actual time=0.013..7.704 rows=4,206 loops=4,729)

213. 4,227.726 9,051.306 ↓ 877.0 877 4,729

Nested Loop Left Join (cost=0.05..4.08 rows=1 width=33) (actual time=0.007..1.914 rows=877 loops=4,729)

214. 676.247 676.247 ↓ 877.0 877 4,729

CTE Scan on relevant_applications relevant_applications_2 (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.143 rows=877 loops=4,729)

215. 4,147.333 4,147.333 ↑ 1.0 1 4,147,333

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

  • Index Cond: (relevant_applications_2.archive_reason_id = id)
216. 20,736.665 20,736.665 ↓ 2.5 5 4,147,333

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

  • Index Cond: (application_id = relevant_applications_2.id)
217. 19,890.174 19,890.174 ↑ 1.0 1 19,890,174

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=19,890,174)

  • Index Cond: (application_history_2.previous_interview_stage_id = id)
218. 19,890.174 19,890.174 ↑ 1.0 1 19,890,174

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=19,890,174)

  • Index Cond: (interview_stage_1.interview_stage_group_id = id)
  • Filter: (organization_id = '399dff6f-ad8f-4e70-b082-b547b26c8fe4'::uuid)
219. 19,890.174 19,890.174 ↑ 1.0 1 19,890,174

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=19,890,174)

  • Index Cond: (id = application_history_2.new_interview_stage_id)
  • Heap Fetches: 19,890,174
220. 35.619 35.619 ↑ 1.0 1 35,619

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=35,619)

  • Index Cond: (source_mwrxm.source_type_id = id)
221. 1.086 554,744.926 ↓ 669.0 669 1

Sort (cost=0.01..0.01 rows=1 width=196) (actual time=554,744.892..554,744.926 rows=669 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: 202kB
222. 554,743.840 554,743.840 ↓ 669.0 669 1

CTE Scan on applications_with_stages (cost=0.00..0.01 rows=1 width=196) (actual time=554,738.759..554,743.840 rows=669 loops=1)