explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5bh7

Settings
# exclusive inclusive rows x rows loops node
1. 0.029 3,515.842 ↑ 142.9 7 1

Sort (cost=58,357.63..58,358.13 rows=1,000 width=24) (actual time=3,515.842..3,515.842 rows=7 loops=1)

  • Sort Key: expanded_series.expanded_series
  • Sort Method: quicksort Memory: 25kB
2.          

CTE filtered_objects

3. 0.006 1.951 ↓ 2.0 2 1

HashSetOp Intersect (cost=0.08..3,405.23 rows=1 width=20) (actual time=1.951..1.951 rows=2 loops=1)

4. 0.003 1.945 ↑ 1.7 41 1

Append (cost=0.08..3,405.20 rows=69 width=20) (actual time=0.037..1.945 rows=41 loops=1)

5. 0.001 0.385 ↓ 2.0 2 1

Subquery Scan on "*SELECT* 8" (cost=0.08..190.14 rows=1 width=20) (actual time=0.037..0.385 rows=2 loops=1)

6. 0.384 0.384 ↓ 2.0 2 1

Index Scan using application_organization_id_idx on application (cost=0.08..190.14 rows=1 width=16) (actual time=0.036..0.384 rows=2 loops=1)

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
  • Filter: (status = 'hired'::enum_application_status)
  • Rows Removed by Filter: 39
7. 0.013 1.557 ↑ 1.7 39 1

HashAggregate (cost=3,214.74..3,214.95 rows=68 width=20) (actual time=1.551..1.557 rows=39 loops=1)

  • Group Key: ""*SELECT* 2"".id
8. 0.003 1.544 ↑ 1.7 39 1

Append (cost=0.08..3,214.71 rows=68 width=16) (actual time=0.580..1.544 rows=39 loops=1)

9. 0.003 0.585 ↑ 1.8 37 1

Result (cost=0.08..872.42 rows=66 width=16) (actual time=0.579..0.585 rows=37 loops=1)

10. 0.016 0.582 ↑ 1.8 37 1

HashSetOp Intersect (cost=0.08..872.22 rows=66 width=20) (actual time=0.579..0.582 rows=37 loops=1)

11. 0.005 0.566 ↑ 84.5 78 1

Append (cost=0.08..868.92 rows=6,591 width=20) (actual time=0.015..0.566 rows=78 loops=1)

12. 0.004 0.039 ↑ 1.7 39 1

Subquery Scan on "*SELECT* 2" (cost=0.08..190.34 rows=66 width=20) (actual time=0.015..0.039 rows=39 loops=1)

13. 0.035 0.035 ↑ 1.7 39 1

Index Scan using application_organization_id_idx on application application_1 (cost=0.08..190.14 rows=66 width=16) (actual time=0.014..0.035 rows=39 loops=1)

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
  • Filter: COALESCE((status <> 'hired'::enum_application_status), true)
  • Rows Removed by Filter: 2
14. 0.004 0.522 ↑ 167.3 39 1

Subquery Scan on "*SELECT* 1" (cost=629.55..668.70 rows=6,525 width=20) (actual time=0.491..0.522 rows=39 loops=1)

15. 0.037 0.518 ↑ 167.3 39 1

HashAggregate (cost=629.55..649.12 rows=6,525 width=16) (actual time=0.491..0.518 rows=39 loops=1)

  • Group Key: application_2.id
16. 0.007 0.481 ↑ 167.3 39 1

Nested Loop (cost=185.59..626.28 rows=6,525 width=16) (actual time=0.295..0.481 rows=39 loops=1)

17. 0.006 0.330 ↑ 2.9 8 1

Nested Loop (cost=185.50..256.99 rows=23 width=32) (actual time=0.278..0.330 rows=8 loops=1)

18. 0.006 0.260 ↑ 2.9 8 1

HashAggregate (cost=185.44..185.51 rows=23 width=16) (actual time=0.258..0.260 rows=8 loops=1)

  • Group Key: job_1.id
19. 0.001 0.254 ↑ 2.9 8 1

Append (cost=2.07..185.43 rows=23 width=16) (actual time=0.034..0.254 rows=8 loops=1)

20. 0.060 0.081 ↑ 1.2 8 1

Bitmap Heap Scan on job job_1 (cost=2.07..20.25 rows=10 width=16) (actual time=0.034..0.081 rows=8 loops=1)

  • Recheck Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
  • Filter: (NOT confidential)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=7
21. 0.021 0.021 ↑ 1.0 10 1

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.07 rows=10 width=0) (actual time=0.021..0.021 rows=10 loops=1)

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
22. 0.000 0.087 ↓ 0.0 0 1

Nested Loop (cost=0.11..23.73 rows=1 width=16) (actual time=0.087..0.087 rows=0 loops=1)

23. 0.000 0.087 ↓ 0.0 0 1

Nested Loop (cost=0.05..18.94 rows=1 width=16) (actual time=0.087..0.087 rows=0 loops=1)

24. 0.087 0.087 ↓ 0.0 0 1

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

  • Filter: (user_id = 'a882c5c3-9fa7-4e0a-9da0-9545ad185e94'::uuid)
  • Rows Removed by Filter: 660
25. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = user_job_security_roles.role_id)
  • Filter: can_see_candidates
26. 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 = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
27. 0.000 0.028 ↓ 0.0 0 1

Nested Loop (cost=2.18..72.76 rows=1 width=16) (actual time=0.028..0.028 rows=0 loops=1)

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

Nested Loop (cost=0.11..11.88 rows=1 width=69) (actual time=0.028..0.028 rows=0 loops=1)

29. 0.001 0.028 ↓ 0.0 0 1

Nested Loop (cost=0.05..7.81 rows=1 width=16) (actual time=0.028..0.028 rows=0 loops=1)

30. 0.027 0.027 ↓ 0.0 0 1

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

  • Filter: (user_id = 'a882c5c3-9fa7-4e0a-9da0-9545ad185e94'::uuid)
  • Rows Removed by Filter: 160
31. 0.000 0.000 ↓ 0.0 0

Index Scan using security_role_pkey on security_role security_role_1 (cost=0.05..4.06 rows=1 width=16) (never executed)

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

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

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

Bitmap Heap Scan on job job_3 (cost=2.07..20.25 rows=10 width=32) (never executed)

  • Recheck Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
34. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
35.          

SubPlan (for Nested Loop)

36. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = job_3.team_id)
37. 0.000 0.015 ↓ 0.0 0 1

Nested Loop (cost=2.13..28.09 rows=10 width=16) (actual time=0.015..0.015 rows=0 loops=1)

38. 0.001 0.015 ↓ 0.0 0 1

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

39. 0.014 0.014 ↓ 0.0 0 1

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

  • Filter: ((team_id IS NULL) AND (user_id = 'a882c5c3-9fa7-4e0a-9da0-9545ad185e94'::uuid))
  • Rows Removed by Filter: 160
40. 0.000 0.000 ↓ 0.0 0

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

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

Bitmap Heap Scan on job job_4 (cost=2.07..20.25 rows=10 width=16) (never executed)

  • Recheck Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
42. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
43. 0.000 0.042 ↓ 0.0 0 1

Result (cost=2.07..40.51 rows=1 width=16) (actual time=0.042..0.042 rows=0 loops=1)

44. 0.007 0.042 ↓ 0.0 0 1

HashSetOp Intersect (cost=2.07..40.51 rows=1 width=20) (actual time=0.042..0.042 rows=0 loops=1)

45. 0.001 0.035 ↓ 2.5 5 1

Append (cost=2.07..40.51 rows=2 width=20) (actual time=0.017..0.035 rows=5 loops=1)

46. 0.001 0.018 ↓ 2.0 2 1

Subquery Scan on "*SELECT* 5" (cost=2.07..20.25 rows=1 width=20) (actual time=0.017..0.018 rows=2 loops=1)

47. 0.007 0.017 ↓ 2.0 2 1

Bitmap Heap Scan on job job_5 (cost=2.07..20.25 rows=1 width=16) (actual time=0.017..0.017 rows=2 loops=1)

  • Recheck Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
  • Filter: confidential
  • Rows Removed by Filter: 8
  • Heap Blocks: exact=7
48. 0.010 0.010 ↑ 1.0 10 1

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.07 rows=10 width=0) (actual time=0.010..0.010 rows=10 loops=1)

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
49. 0.001 0.016 ↓ 3.0 3 1

Subquery Scan on "*SELECT* 6_1" (cost=2.07..20.25 rows=1 width=20) (actual time=0.012..0.016 rows=3 loops=1)

50. 0.005 0.015 ↓ 3.0 3 1

Bitmap Heap Scan on job job_6 (cost=2.07..20.25 rows=1 width=16) (actual time=0.012..0.015 rows=3 loops=1)

  • Recheck Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
  • Filter: (author_id = 'a882c5c3-9fa7-4e0a-9da0-9545ad185e94'::uuid)
  • Rows Removed by Filter: 7
  • Heap Blocks: exact=7
51. 0.010 0.010 ↑ 1.0 10 1

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

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
52. 0.064 0.064 ↑ 1.0 1 8

Index Only Scan using job_pkey on job (cost=0.06..3.10 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=8)

  • Index Cond: (id = job_1.id)
  • Heap Fetches: 8
53. 0.144 0.144 ↑ 56.8 5 8

Index Scan using application_job_id_idx on application application_2 (cost=0.08..15.20 rows=284 width=32) (actual time=0.015..0.018 rows=5 loops=8)

  • Index Cond: (job_id = job.id)
54. 0.001 0.956 ↑ 1.0 2 1

Result (cost=945.86..2,342.19 rows=2 width=16) (actual time=0.955..0.956 rows=2 loops=1)

55. 0.005 0.955 ↑ 1.0 2 1

HashSetOp Intersect (cost=945.86..2,342.19 rows=2 width=20) (actual time=0.955..0.955 rows=2 loops=1)

56. 0.003 0.950 ↑ 159.2 41 1

Append (cost=945.86..2,338.92 rows=6,527 width=20) (actual time=0.340..0.950 rows=41 loops=1)

57. 0.001 0.341 ↑ 1.0 2 1

Result (cost=945.86..945.87 rows=2 width=20) (actual time=0.340..0.341 rows=2 loops=1)

58. 0.001 0.340 ↑ 1.0 2 1

Unique (cost=945.86..945.86 rows=2 width=16) (actual time=0.339..0.340 rows=2 loops=1)

59. 0.012 0.339 ↑ 1.0 2 1

Sort (cost=945.86..945.86 rows=2 width=16) (actual time=0.339..0.339 rows=2 loops=1)

  • Sort Key: application_3.id
  • Sort Method: quicksort Memory: 25kB
60. 0.000 0.327 ↑ 1.0 2 1

Append (cost=191.07..945.86 rows=2 width=16) (actual time=0.327..0.327 rows=2 loops=1)

61. 0.013 0.202 ↓ 0.0 0 1

Merge Join (cost=191.07..194.05 rows=1 width=16) (actual time=0.202..0.202 rows=0 loops=1)

  • Merge Cond: (employee.candidate_id = application_3.candidate_id)
62. 0.131 0.131 ↑ 324.1 62 1

Index Scan using employee_candidate_id_idx on employee (cost=0.06..872.50 rows=20,093 width=16) (actual time=0.014..0.131 rows=62 loops=1)

  • Filter: ((NOT is_alumni) AND (user_id IS DISTINCT FROM 'a882c5c3-9fa7-4e0a-9da0-9545ad185e94'::uuid))
  • Rows Removed by Filter: 3
63. 0.025 0.058 ↑ 3.3 40 1

Sort (cost=191.00..191.07 rows=132 width=32) (actual time=0.056..0.058 rows=40 loops=1)

  • Sort Key: application_3.candidate_id
  • Sort Method: quicksort Memory: 28kB
64. 0.033 0.033 ↑ 3.2 41 1

Index Scan using application_organization_id_idx on application application_3 (cost=0.08..190.08 rows=132 width=32) (actual time=0.010..0.033 rows=41 loops=1)

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
65. 0.001 0.125 ↓ 2.0 2 1

Result (cost=0.08..751.80 rows=1 width=16) (actual time=0.125..0.125 rows=2 loops=1)

66. 0.006 0.124 ↓ 2.0 2 1

HashSetOp Intersect (cost=0.08..751.80 rows=1 width=20) (actual time=0.124..0.124 rows=2 loops=1)

67. 0.004 0.118 ↑ 3.1 43 1

Append (cost=0.08..751.73 rows=133 width=20) (actual time=0.011..0.118 rows=43 loops=1)

68. 0.000 0.032 ↓ 2.0 2 1

Subquery Scan on "*SELECT* 7" (cost=0.08..190.14 rows=1 width=20) (actual time=0.011..0.032 rows=2 loops=1)

69. 0.032 0.032 ↓ 2.0 2 1

Index Scan using application_organization_id_idx on application application_4 (cost=0.08..190.14 rows=1 width=16) (actual time=0.011..0.032 rows=2 loops=1)

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
  • Filter: (status = 'hired'::enum_application_status)
  • Rows Removed by Filter: 39
70. 0.005 0.082 ↑ 3.2 41 1

Subquery Scan on "*SELECT* 6" (cost=0.14..561.39 rows=132 width=20) (actual time=0.015..0.082 rows=41 loops=1)

71. 0.013 0.077 ↑ 3.2 41 1

Nested Loop Anti Join (cost=0.14..560.99 rows=132 width=16) (actual time=0.014..0.077 rows=41 loops=1)

72. 0.023 0.023 ↑ 3.2 41 1

Index Scan using application_organization_id_idx on application application_5 (cost=0.08..190.08 rows=132 width=32) (actual time=0.007..0.023 rows=41 loops=1)

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
73. 0.041 0.041 ↓ 0.0 0 41

Index Scan using employee_candidate_id_idx on employee employee_1 (cost=0.06..2.79 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=41)

  • Index Cond: (candidate_id = application_5.candidate_id)
  • Filter: (NOT is_alumni)
74. 0.004 0.606 ↑ 167.3 39 1

Result (cost=629.55..1,383.26 rows=6,525 width=20) (actual time=0.578..0.606 rows=39 loops=1)

75. 0.041 0.602 ↑ 167.3 39 1

HashSetOp Intersect (cost=629.55..1,363.69 rows=6,525 width=20) (actual time=0.577..0.602 rows=39 loops=1)

76. 0.006 0.561 ↑ 167.3 78 1

Append (cost=629.55..1,357.16 rows=13,050 width=20) (actual time=0.255..0.561 rows=78 loops=1)

77. 0.004 0.285 ↑ 167.3 39 1

Subquery Scan on "*SELECT* 3" (cost=629.55..668.70 rows=6,525 width=20) (actual time=0.255..0.285 rows=39 loops=1)

78. 0.038 0.281 ↑ 167.3 39 1

HashAggregate (cost=629.55..649.12 rows=6,525 width=16) (actual time=0.254..0.281 rows=39 loops=1)

  • Group Key: application_6.id
79. 0.006 0.243 ↑ 167.3 39 1

Nested Loop (cost=185.59..626.28 rows=6,525 width=16) (actual time=0.181..0.243 rows=39 loops=1)

80. 0.002 0.189 ↑ 2.9 8 1

Nested Loop (cost=185.50..256.99 rows=23 width=32) (actual time=0.172..0.189 rows=8 loops=1)

81. 0.004 0.163 ↑ 2.9 8 1

HashAggregate (cost=185.44..185.51 rows=23 width=16) (actual time=0.162..0.163 rows=8 loops=1)

  • Group Key: job_8.id
82. 0.001 0.159 ↑ 2.9 8 1

Append (cost=2.07..185.43 rows=23 width=16) (actual time=0.013..0.159 rows=8 loops=1)

83. 0.009 0.019 ↑ 1.2 8 1

Bitmap Heap Scan on job job_8 (cost=2.07..20.25 rows=10 width=16) (actual time=0.013..0.019 rows=8 loops=1)

  • Recheck Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
  • Filter: (NOT confidential)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=7
84. 0.010 0.010 ↑ 1.0 10 1

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.07 rows=10 width=0) (actual time=0.010..0.010 rows=10 loops=1)

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
85. 0.000 0.059 ↓ 0.0 0 1

Nested Loop (cost=0.11..23.73 rows=1 width=16) (actual time=0.059..0.059 rows=0 loops=1)

86. 0.000 0.059 ↓ 0.0 0 1

Nested Loop (cost=0.05..18.94 rows=1 width=16) (actual time=0.059..0.059 rows=0 loops=1)

87. 0.059 0.059 ↓ 0.0 0 1

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

  • Filter: (user_id = 'a882c5c3-9fa7-4e0a-9da0-9545ad185e94'::uuid)
  • Rows Removed by Filter: 660
88. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = user_job_security_roles_1.role_id)
  • Filter: can_see_candidates
89. 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 = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
90. 0.000 0.020 ↓ 0.0 0 1

Nested Loop (cost=2.18..72.76 rows=1 width=16) (actual time=0.020..0.020 rows=0 loops=1)

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

Nested Loop (cost=0.11..11.88 rows=1 width=69) (actual time=0.020..0.020 rows=0 loops=1)

92. 0.001 0.020 ↓ 0.0 0 1

Nested Loop (cost=0.05..7.81 rows=1 width=16) (actual time=0.020..0.020 rows=0 loops=1)

93. 0.019 0.019 ↓ 0.0 0 1

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

  • Filter: (user_id = 'a882c5c3-9fa7-4e0a-9da0-9545ad185e94'::uuid)
  • Rows Removed by Filter: 160
94. 0.000 0.000 ↓ 0.0 0

Index Scan using security_role_pkey on security_role security_role_4 (cost=0.05..4.06 rows=1 width=16) (never executed)

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

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

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

Bitmap Heap Scan on job job_10 (cost=2.07..20.25 rows=10 width=32) (never executed)

  • Recheck Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
97. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
98.          

SubPlan (for Nested Loop)

99. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = job_10.team_id)
100. 0.000 0.017 ↓ 0.0 0 1

Nested Loop (cost=2.13..28.09 rows=10 width=16) (actual time=0.017..0.017 rows=0 loops=1)

101. 0.001 0.017 ↓ 0.0 0 1

Nested Loop (cost=0.05..7.81 rows=1 width=0) (actual time=0.017..0.017 rows=0 loops=1)

102. 0.016 0.016 ↓ 0.0 0 1

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

  • Filter: ((team_id IS NULL) AND (user_id = 'a882c5c3-9fa7-4e0a-9da0-9545ad185e94'::uuid))
  • Rows Removed by Filter: 160
103. 0.000 0.000 ↓ 0.0 0

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

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

Bitmap Heap Scan on job job_11 (cost=2.07..20.25 rows=10 width=16) (never executed)

  • Recheck Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
105. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
106. 0.000 0.043 ↓ 0.0 0 1

Result (cost=2.07..40.51 rows=1 width=16) (actual time=0.043..0.043 rows=0 loops=1)

107. 0.002 0.043 ↓ 0.0 0 1

HashSetOp Intersect (cost=2.07..40.51 rows=1 width=20) (actual time=0.042..0.043 rows=0 loops=1)

108. 0.000 0.041 ↓ 2.5 5 1

Append (cost=2.07..40.51 rows=2 width=20) (actual time=0.022..0.041 rows=5 loops=1)

109. 0.001 0.023 ↓ 2.0 2 1

Subquery Scan on "*SELECT* 5_1" (cost=2.07..20.25 rows=1 width=20) (actual time=0.022..0.023 rows=2 loops=1)

110. 0.006 0.022 ↓ 2.0 2 1

Bitmap Heap Scan on job job_12 (cost=2.07..20.25 rows=1 width=16) (actual time=0.022..0.022 rows=2 loops=1)

  • Recheck Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
  • Filter: confidential
  • Rows Removed by Filter: 8
  • Heap Blocks: exact=7
111. 0.016 0.016 ↑ 1.0 10 1

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.07 rows=10 width=0) (actual time=0.016..0.016 rows=10 loops=1)

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
112. 0.001 0.018 ↓ 3.0 3 1

Subquery Scan on "*SELECT* 6_2" (cost=2.07..20.25 rows=1 width=20) (actual time=0.014..0.018 rows=3 loops=1)

113. 0.008 0.017 ↓ 3.0 3 1

Bitmap Heap Scan on job job_13 (cost=2.07..20.25 rows=1 width=16) (actual time=0.013..0.017 rows=3 loops=1)

  • Recheck Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
  • Filter: (author_id = 'a882c5c3-9fa7-4e0a-9da0-9545ad185e94'::uuid)
  • Rows Removed by Filter: 7
  • Heap Blocks: exact=7
114. 0.009 0.009 ↑ 1.0 10 1

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.07 rows=10 width=0) (actual time=0.009..0.009 rows=10 loops=1)

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
115. 0.024 0.024 ↑ 1.0 1 8

Index Only Scan using job_pkey on job job_7 (cost=0.06..3.10 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=8)

  • Index Cond: (id = job_8.id)
  • Heap Fetches: 8
116. 0.048 0.048 ↑ 56.8 5 8

Index Scan using application_job_id_idx on application application_6 (cost=0.08..15.20 rows=284 width=32) (actual time=0.003..0.006 rows=5 loops=8)

  • Index Cond: (job_id = job_7.id)
117. 0.003 0.270 ↑ 167.3 39 1

Subquery Scan on "*SELECT* 4" (cost=629.74..668.89 rows=6,525 width=20) (actual time=0.240..0.270 rows=39 loops=1)

118. 0.037 0.267 ↑ 167.3 39 1

HashAggregate (cost=629.74..649.32 rows=6,525 width=16) (actual time=0.240..0.267 rows=39 loops=1)

  • Group Key: application_7.id
119. 0.007 0.230 ↑ 167.3 39 1

Nested Loop (cost=185.78..626.48 rows=6,525 width=16) (actual time=0.173..0.230 rows=39 loops=1)

120. 0.000 0.175 ↑ 2.9 8 1

Nested Loop (cost=185.70..257.18 rows=23 width=32) (actual time=0.160..0.175 rows=8 loops=1)

121. 0.003 0.151 ↑ 2.9 8 1

HashAggregate (cost=185.64..185.71 rows=23 width=16) (actual time=0.150..0.151 rows=8 loops=1)

  • Group Key: job_15.id
122. 0.002 0.148 ↑ 2.9 8 1

Append (cost=2.07..185.63 rows=23 width=16) (actual time=0.014..0.148 rows=8 loops=1)

123. 0.008 0.019 ↑ 1.2 8 1

Bitmap Heap Scan on job job_15 (cost=2.07..20.25 rows=10 width=16) (actual time=0.013..0.019 rows=8 loops=1)

  • Recheck Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
  • Filter: (NOT confidential)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=7
124. 0.011 0.011 ↑ 1.0 10 1

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.07 rows=10 width=0) (actual time=0.011..0.011 rows=10 loops=1)

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
125. 0.000 0.058 ↓ 0.0 0 1

Nested Loop (cost=0.11..23.79 rows=1 width=16) (actual time=0.058..0.058 rows=0 loops=1)

126. 0.000 0.058 ↓ 0.0 0 1

Nested Loop (cost=0.05..19.00 rows=1 width=16) (actual time=0.058..0.058 rows=0 loops=1)

127. 0.058 0.058 ↓ 0.0 0 1

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

  • Filter: (user_id = 'a882c5c3-9fa7-4e0a-9da0-9545ad185e94'::uuid)
  • Rows Removed by Filter: 660
128. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = user_job_security_roles_2.role_id)
  • Filter: can_see_and_edit_hired_candidates
129. 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 = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
130. 0.000 0.016 ↓ 0.0 0 1

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

  • Join Filter: (team_5.path @> (SubPlan 3))
131. 0.001 0.016 ↓ 0.0 0 1

Nested Loop (cost=0.11..11.95 rows=1 width=69) (actual time=0.016..0.016 rows=0 loops=1)

132. 0.000 0.015 ↓ 0.0 0 1

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

133. 0.015 0.015 ↓ 0.0 0 1

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

  • Filter: (user_id = 'a882c5c3-9fa7-4e0a-9da0-9545ad185e94'::uuid)
  • Rows Removed by Filter: 160
134. 0.000 0.000 ↓ 0.0 0

Index Scan using security_role_pkey on security_role security_role_7 (cost=0.05..4.06 rows=1 width=16) (never executed)

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

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

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

Bitmap Heap Scan on job job_17 (cost=2.07..20.25 rows=10 width=32) (never executed)

  • Recheck Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
137. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
138.          

SubPlan (for Nested Loop)

139. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = job_17.team_id)
140. 0.001 0.017 ↓ 0.0 0 1

Nested Loop (cost=2.13..28.15 rows=10 width=16) (actual time=0.017..0.017 rows=0 loops=1)

141. 0.000 0.016 ↓ 0.0 0 1

Nested Loop (cost=0.05..7.88 rows=1 width=0) (actual time=0.016..0.016 rows=0 loops=1)

142. 0.016 0.016 ↓ 0.0 0 1

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

  • Filter: ((team_id IS NULL) AND (user_id = 'a882c5c3-9fa7-4e0a-9da0-9545ad185e94'::uuid))
  • Rows Removed by Filter: 160
143. 0.000 0.000 ↓ 0.0 0

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

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

Bitmap Heap Scan on job job_18 (cost=2.07..20.25 rows=10 width=16) (never executed)

  • Recheck Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
145. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
146. 0.000 0.036 ↓ 0.0 0 1

Result (cost=2.07..40.51 rows=1 width=16) (actual time=0.036..0.036 rows=0 loops=1)

147. 0.003 0.036 ↓ 0.0 0 1

HashSetOp Intersect (cost=2.07..40.51 rows=1 width=20) (actual time=0.036..0.036 rows=0 loops=1)

148. 0.001 0.033 ↓ 2.5 5 1

Append (cost=2.07..40.51 rows=2 width=20) (actual time=0.016..0.033 rows=5 loops=1)

149. 0.000 0.016 ↓ 2.0 2 1

Subquery Scan on "*SELECT* 5_2" (cost=2.07..20.25 rows=1 width=20) (actual time=0.015..0.016 rows=2 loops=1)

150. 0.006 0.016 ↓ 2.0 2 1

Bitmap Heap Scan on job job_19 (cost=2.07..20.25 rows=1 width=16) (actual time=0.015..0.016 rows=2 loops=1)

  • Recheck Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
  • Filter: confidential
  • Rows Removed by Filter: 8
  • Heap Blocks: exact=7
151. 0.010 0.010 ↑ 1.0 10 1

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.07 rows=10 width=0) (actual time=0.010..0.010 rows=10 loops=1)

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
152. 0.001 0.016 ↓ 3.0 3 1

Subquery Scan on "*SELECT* 6_3" (cost=2.07..20.25 rows=1 width=20) (actual time=0.012..0.016 rows=3 loops=1)

153. 0.006 0.015 ↓ 3.0 3 1

Bitmap Heap Scan on job job_20 (cost=2.07..20.25 rows=1 width=16) (actual time=0.011..0.015 rows=3 loops=1)

  • Recheck Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
  • Filter: (author_id = 'a882c5c3-9fa7-4e0a-9da0-9545ad185e94'::uuid)
  • Rows Removed by Filter: 7
  • Heap Blocks: exact=7
154. 0.009 0.009 ↑ 1.0 10 1

Bitmap Index Scan on job_organization_id_idx (cost=0.00..2.07 rows=10 width=0) (actual time=0.009..0.009 rows=10 loops=1)

  • Index Cond: (organization_id = '2294acfa-c951-45ff-b790-5ed1426d4c1e'::uuid)
155. 0.024 0.024 ↑ 1.0 1 8

Index Only Scan using job_pkey on job job_14 (cost=0.06..3.10 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=8)

  • Index Cond: (id = job_15.id)
  • Heap Fetches: 8
156. 0.048 0.048 ↑ 56.8 5 8

Index Scan using application_job_id_idx on application application_7 (cost=0.08..15.20 rows=284 width=32) (actual time=0.004..0.006 rows=5 loops=8)

  • Index Cond: (job_id = job_14.id)
157.          

CTE object_with_selected_fields

158. 0.006 3,515.747 ↓ 2.0 2 1

GroupAggregate (cost=54,208.72..54,208.73 rows=1 width=64) (actual time=3,515.746..3,515.747 rows=2 loops=1)

  • Group Key: application_8.id, application_history_zoipi.entered_stage_at
159. 0.018 3,515.741 ↓ 2.0 2 1

Sort (cost=54,208.72..54,208.72 rows=1 width=48) (actual time=3,515.740..3,515.741 rows=2 loops=1)

  • Sort Key: application_8.id, application_history_zoipi.entered_stage_at
  • Sort Method: quicksort Memory: 25kB
160. 0.006 3,515.723 ↓ 2.0 2 1

Nested Loop (cost=53,451.78..54,208.72 rows=1 width=48) (actual time=3,455.681..3,515.723 rows=2 loops=1)

161. 0.007 3,515.701 ↓ 4.0 4 1

Nested Loop (cost=53,451.73..54,208.66 rows=1 width=64) (actual time=3,455.673..3,515.701 rows=4 loops=1)

  • Join Filter: (filtered_objects.id = application_history_zoipi.application_id)
162. 0.006 3,515.668 ↓ 2.0 2 1

Nested Loop (cost=53,451.64..54,208.49 rows=1 width=72) (actual time=3,455.652..3,515.668 rows=2 loops=1)

163. 0.013 3,515.642 ↓ 2.0 4 1

Nested Loop (cost=53,451.58..54,208.36 rows=2 width=88) (actual time=3,455.637..3,515.642 rows=4 loops=1)

  • Join Filter: (filtered_objects.id = application_history_rxoyj.application_id)
164. 17.070 3,515.583 ↓ 2.0 2 1

Merge Left Join (cost=53,451.50..54,208.13 rows=1 width=48) (actual time=3,455.599..3,515.583 rows=2 loops=1)

  • Merge Cond: (application_8.id = application_9.id)
165. 0.014 2.008 ↓ 2.0 2 1

Sort (cost=4.10..4.10 rows=1 width=40) (actual time=2.007..2.008 rows=2 loops=1)

  • Sort Key: application_8.id
  • Sort Method: quicksort Memory: 25kB
166. 0.001 1.994 ↓ 2.0 2 1

Nested Loop (cost=0.08..4.10 rows=1 width=40) (actual time=1.975..1.994 rows=2 loops=1)

167. 1.953 1.953 ↓ 2.0 2 1

CTE Scan on filtered_objects (cost=0.00..0.01 rows=1 width=16) (actual time=1.952..1.953 rows=2 loops=1)

168. 0.040 0.040 ↑ 1.0 1 2

Index Scan using application_pkey on application application_8 (cost=0.08..4.09 rows=1 width=24) (actual time=0.020..0.020 rows=1 loops=2)

  • Index Cond: (id = filtered_objects.id)
169. 34.040 3,496.505 ↓ 18.2 189,730 1

Finalize GroupAggregate (cost=53,447.40..54,167.45 rows=10,453 width=24) (actual time=3,330.819..3,496.505 rows=189,730 loops=1)

  • Group Key: application_9.id
170. 76.300 3,462.465 ↓ 31.6 194,096 1

Gather Merge (cost=53,447.40..54,129.94 rows=6,149 width=24) (actual time=3,330.813..3,462.465 rows=194,096 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
171. 46.462 3,386.165 ↓ 15.9 97,652 2 / 2

Partial GroupAggregate (cost=52,447.40..52,475.07 rows=6,149 width=24) (actual time=3,319.656..3,386.165 rows=97,652 loops=2)

  • Group Key: application_9.id
172. 353.277 3,339.703 ↓ 16.6 102,216 2 / 2

Sort (cost=52,447.40..52,450.47 rows=6,149 width=24) (actual time=3,319.648..3,339.703 rows=102,216 loops=2)

  • Sort Key: application_9.id
  • Sort Method: quicksort Memory: 32,977kB
  • Worker 0: Sort Method: quicksort Memory: 34,501kB
173. 432.036 2,986.426 ↓ 71.3 438,726 2 / 2

Nested Loop (cost=1,129.15..52,370.01 rows=6,149 width=24) (actual time=17.450..2,986.426 rows=438,726 loops=2)

174. 485.059 799.486 ↓ 71.3 438,726 2 / 2

Hash Join (cost=1,129.06..51,788.70 rows=6,149 width=24) (actual time=17.374..799.486 rows=438,726 loops=2)

  • Hash Cond: (application_history.new_interview_stage_id = interview_stage.id)
175. 297.121 297.121 ↑ 1.2 1,047,030 2 / 2

Parallel Seq Scan on application_history (cost=0.00..50,013.39 rows=1,230,796 width=40) (actual time=0.016..297.121 rows=1,047,030 loops=2)

176. 1.590 17.306 ↓ 19.1 4,977 2 / 2

Hash (cost=1,128.15..1,128.15 rows=260 width=16) (actual time=17.306..17.306 rows=4,977 loops=2)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 298kB
177. 15.716 15.716 ↓ 19.1 4,977 2 / 2

Seq Scan on interview_stage (cost=0.00..1,128.15 rows=260 width=16) (actual time=0.020..15.716 rows=4,977 loops=2)

  • Filter: (stage_type = 'Archived'::enum_interview_stage_stage_type)
  • Rows Removed by Filter: 46,983
178. 1,754.904 1,754.904 ↑ 1.0 1 877,452 / 2

Index Only Scan using application_pkey on application application_9 (cost=0.08..0.09 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=877,452)

  • Index Cond: (id = application_history.application_id)
  • Heap Fetches: 469,403
179. 0.046 0.046 ↑ 1.5 2 2

Index Scan using application_history_application_id_idx on application_history application_history_rxoyj (cost=0.09..0.22 rows=3 width=40) (actual time=0.022..0.023 rows=2 loops=2)

  • Index Cond: (application_id = application_8.id)
180. 0.020 0.020 ↓ 0.0 0 4

Index Scan using interview_stage_pkey on interview_stage interview_stage_rxoyj (cost=0.06..0.06 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=4)

  • Index Cond: (id = application_history_rxoyj.new_interview_stage_id)
  • Filter: (stage_type = 'Hired'::enum_interview_stage_stage_type)
  • Rows Removed by Filter: 0
181. 0.026 0.026 ↑ 1.5 2 2

Index Scan using application_history_application_id_idx on application_history application_history_zoipi (cost=0.09..0.15 rows=3 width=40) (actual time=0.010..0.013 rows=2 loops=2)

  • Index Cond: (application_id = application_history_rxoyj.application_id)
182. 0.016 0.016 ↓ 0.0 0 4

Index Scan using interview_stage_pkey on interview_stage interview_stage_zoipi (cost=0.06..0.06 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=4)

  • Index Cond: (id = application_history_zoipi.new_interview_stage_id)
  • Filter: (stage_type = 'Hired'::enum_interview_stage_stage_type)
  • Rows Removed by Filter: 0
183.          

CTE result_set

184. 0.007 3,515.790 ↑ 111.0 1 1

GroupAggregate (cost=8.26..8.87 rows=111 width=24) (actual time=3,515.790..3,515.790 rows=1 loops=1)

  • Group Key: series.series
185. 0.012 3,515.783 ↑ 55.5 2 1

Sort (cost=8.26..8.32 rows=111 width=24) (actual time=3,515.783..3,515.783 rows=2 loops=1)

  • Sort Key: series.series
  • Sort Method: quicksort Memory: 25kB
186. 0.007 3,515.771 ↑ 55.5 2 1

Nested Loop (cost=0.00..7.51 rows=111 width=24) (actual time=3,515.766..3,515.771 rows=2 loops=1)

  • Join Filter: ((object_with_selected_fields._eiskd >= series.series) AND (object_with_selected_fields._eiskd < (series.series + '1 mon'::interval)))
  • Rows Removed by Join Filter: 12
187. 3,515.750 3,515.750 ↓ 2.0 2 1

CTE Scan on object_with_selected_fields (cost=0.00..0.01 rows=1 width=24) (actual time=3,515.748..3,515.750 rows=2 loops=1)

188. 0.014 0.014 ↑ 142.9 7 2

Function Scan on generate_series series (cost=0.00..3.00 rows=1,000 width=8) (actual time=0.006..0.007 rows=7 loops=2)

189. 0.006 3,515.813 ↑ 142.9 7 1

Nested Loop Left Join (cost=0.00..724.83 rows=1,000 width=24) (actual time=3,515.809..3,515.813 rows=7 loops=1)

  • Join Filter: (NOT (result_set.series IS DISTINCT FROM expanded_series.expanded_series))
  • Rows Removed by Join Filter: 6
190. 0.015 0.015 ↑ 142.9 7 1

Function Scan on generate_series expanded_series (cost=0.00..3.00 rows=1,000 width=8) (actual time=0.014..0.015 rows=7 loops=1)

191. 3,515.792 3,515.792 ↑ 111.0 1 7

CTE Scan on result_set (cost=0.00..0.67 rows=111 width=24) (actual time=502.256..502.256 rows=1 loops=7)

Planning time : 11.510 ms