explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NYsn

Settings
# exclusive inclusive rows x rows loops node
1. 0.036 3,175.944 ↑ 1.0 1 1

Result (cost=4,501.33..4,501.34 rows=1 width=65) (actual time=3,175.944..3,175.944 rows=1 loops=1)

2.          

CTE __local_0__

3. 1.282 3,167.596 ↓ 10.0 20 1

Subquery Scan on __local_1__ (cost=446.71..2,385.85 rows=2 width=64) (actual time=287.893..3,167.596 rows=20 loops=1)

4. 0.012 14.654 ↓ 10.0 20 1

Limit (cost=446.71..446.71 rows=2 width=1,171) (actual time=14.630..14.654 rows=20 loops=1)

5. 0.093 14.642 ↓ 10.0 20 1

Sort (cost=446.71..446.71 rows=2 width=1,171) (actual time=14.629..14.642 rows=20 loops=1)

  • Sort Key: ((SubPlan 25)), ((SubPlan 26)), __local_1___1.id
  • Sort Method: quicksort Memory: 31kB
6. 0.102 14.549 ↓ 11.5 23 1

Nested Loop (cost=69.78..446.70 rows=2 width=1,171) (actual time=0.850..14.549 rows=23 loops=1)

7. 3.842 3.950 ↓ 1.1 26 1

Bitmap Heap Scan on employee __local_1___1 (cost=69.50..239.15 rows=24 width=139) (actual time=0.350..3.950 rows=26 loops=1)

  • Recheck Cond: (company_id = '47d08a0b-2c1b-4591-acc0-4d521915c8ef'::uuid)
  • Filter: (active AND (is_archived IS FALSE) AND (((NOT is_archived) AND ((employment_end > now()) OR (employment_end IS NULL)) AND policy_role_check('employee'::text, 'general'::text, 'read'::text, user_id, departments, company_id)) OR ((is_archived OR (employment_end <= now())) AND policy_role_check('employee'::text, 'archived'::text, 'read'::text, user_id, departments, company_id))))
  • Heap Blocks: exact=6
8. 0.002 0.108 ↓ 0.0 0 1

BitmapAnd (cost=69.50..69.50 rows=80 width=0) (actual time=0.108..0.108 rows=0 loops=1)

9. 0.013 0.013 ↑ 1.0 165 1

Bitmap Index Scan on employee_company_id_index (cost=0.00..5.52 rows=165 width=0) (actual time=0.013..0.013 rows=165 loops=1)

  • Index Cond: (company_id = '47d08a0b-2c1b-4591-acc0-4d521915c8ef'::uuid)
10. 0.093 0.093 ↑ 1.0 3,125 1

Bitmap Index Scan on employee_active_index (cost=0.00..63.72 rows=3,125 width=0) (actual time=0.093..0.093 rows=3,125 loops=1)

  • Index Cond: (active = true)
11. 3.666 3.666 ↑ 1.0 1 26

Index Scan using user_pkey on "user" __local_22__ (cost=0.28..7.22 rows=1 width=16) (actual time=0.141..0.141 rows=1 loops=26)

  • Index Cond: (id = __local_1___1.user_id)
  • Filter: (active AND policy_role_check('employee'::text, 'general'::text, 'read'::text, id, departments, company_id) AND ((employment_end IS NULL) OR ((employment_end)::text > '2019-11-07T15:35:58.188Z'::text) OR ((employment_end)::text = ''::text)))
  • Rows Removed by Filter: 0
12.          

SubPlan (for Nested Loop)

13. 3.450 3.450 ↑ 1.0 1 23

Index Scan using user_id_active_employment_end on "user" user_2 (cost=0.28..8.55 rows=1 width=7) (actual time=0.150..0.150 rows=1 loops=23)

  • Index Cond: (__local_1___1.user_id = id)
  • Filter: policy_role_check('employee'::text, 'general'::text, 'read'::text, id, departments, company_id)
14. 3.381 3.381 ↑ 1.0 1 23

Index Scan using user_id_active_employment_end on "user" user_3 (cost=0.28..8.55 rows=1 width=7) (actual time=0.147..0.147 rows=1 loops=23)

  • Index Cond: (__local_1___1.user_id = id)
  • Filter: policy_role_check('employee'::text, 'general'::text, 'read'::text, id, departments, company_id)
15.          

SubPlan (for Subquery Scan)

16. 1.060 1.060 ↑ 1.0 1 20

Function Scan on employee_logo __local_2__ (cost=0.25..0.26 rows=1 width=32) (actual time=0.053..0.053 rows=1 loops=20)

17. 0.060 3,130.540 ↑ 1.0 1 20

Result (cost=881.77..881.78 rows=1 width=32) (actual time=156.527..156.527 rows=1 loops=20)

18.          

Initplan (for Result)

19. 0.892 3,130.480 ↑ 1.0 1 20

Aggregate (cost=881.76..881.77 rows=1 width=32) (actual time=156.524..156.524 rows=1 loops=20)

20. 3,120.020 3,120.020 ↑ 11.1 9 20

Function Scan on unnest __local_4__ (cost=0.25..1.25 rows=100 width=64) (actual time=156.000..156.001 rows=9 loops=20)

21.          

SubPlan (for Aggregate)

22. 9.568 9.568 ↑ 1.0 1 184

Index Scan using form_field_id_active on form_field __local_5__ (cost=0.28..8.80 rows=1 width=32) (actual time=0.051..0.052 rows=1 loops=184)

  • Index Cond: (__local_4__.form_field_id = id)
  • Filter: (((object_type = 'preboarding'::form_field_object_type) AND policy_role_check('app_builder'::text, 'templates'::text, 'read'::text, preboarding_assigned_user_id, departments, company_id)) OR ((object_type = 'employee'::form_field_object_type) AND policy_company_role_check('form_field'::text, 'general'::text, 'read'::text, company_id)))
23. 3.400 11.900 ↑ 1.0 1 20

Index Scan using user_id_active_employment_end on "user" __local_6__ (cost=0.28..61.85 rows=1 width=32) (actual time=0.595..0.595 rows=1 loops=20)

  • Index Cond: (__local_1__.user_id = id)
  • Filter: policy_role_check('employee'::text, 'general'::text, 'read'::text, id, departments, company_id)
24.          

SubPlan (for Index Scan)

25. 0.080 0.080 ↑ 1.0 1 20

Function Scan on __local_7__ (cost=0.01..0.02 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=20)

26. 0.060 0.440 ↑ 1.0 1 20

Result (cost=10.81..10.82 rows=1 width=32) (actual time=0.022..0.022 rows=1 loops=20)

27.          

CTE __local_8__

28. 0.020 0.280 ↓ 0.0 0 20

Subquery Scan on __local_9__ (cost=2.18..10.75 rows=1 width=32) (actual time=0.014..0.014 rows=0 loops=20)

29. 0.100 0.260 ↓ 0.0 0 20

Sort (cost=2.18..2.19 rows=1 width=32) (actual time=0.013..0.013 rows=0 loops=20)

  • Sort Key: __local_9___1.department_id
  • Sort Method: quicksort Memory: 25kB
30. 0.160 0.160 ↓ 0.0 0 20

Seq Scan on admin_department __local_9___1 (cost=0.00..2.17 rows=1 width=32) (actual time=0.008..0.008 rows=0 loops=20)

  • Filter: (false AND (user_id = __local_6__.id))
  • Rows Removed by Filter: 94
31.          

SubPlan (for Subquery Scan)

32. 0.000 0.000 ↓ 0.0 0

Index Scan using department_pkey on department __local_10__ (cost=0.28..8.55 rows=1 width=32) (never executed)

  • Index Cond: (__local_9__.department_id = id)
  • Filter: policy_role_check('department'::text, 'general'::text, 'read'::text, NULL::uuid, id, company_id)
33.          

CTE __local_11__

34. 0.040 0.340 ↑ 1.0 1 20

Aggregate (cost=0.03..0.04 rows=1 width=32) (actual time=0.017..0.017 rows=1 loops=20)

35. 0.300 0.300 ↓ 0.0 0 20

CTE Scan on __local_8__ (cost=0.00..0.02 rows=1 width=24) (actual time=0.015..0.015 rows=0 loops=20)

36.          

Initplan (for Result)

37. 0.380 0.380 ↑ 1.0 1 20

CTE Scan on __local_11__ (cost=0.00..0.02 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=20)

38. 0.060 7.560 ↑ 1.0 1 20

Result (cost=25.52..25.53 rows=1 width=32) (actual time=0.378..0.378 rows=1 loops=20)

39.          

CTE __local_12__

40. 0.162 7.320 ↑ 1.0 1 20

Subquery Scan on __local_13__ (cost=0.56..25.46 rows=1 width=32) (actual time=0.286..0.366 rows=1 loops=20)

41. 0.064 3.700 ↑ 1.0 1 20

Nested Loop (cost=0.56..16.89 rows=1 width=48) (actual time=0.146..0.185 rows=1 loops=20)

42. 0.100 0.100 ↑ 1.0 1 20

Index Only Scan using user_department_pkey on user_department __local_13___1 (cost=0.28..8.30 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=20)

  • Index Cond: (user_id = __local_6__.id)
  • Heap Fetches: 26
43. 3.536 3.536 ↑ 1.0 1 26

Index Scan using department_pkey on department __local_15__ (cost=0.28..8.54 rows=1 width=16) (actual time=0.136..0.136 rows=1 loops=26)

  • Index Cond: (id = __local_13___1.department_id)
  • Filter: (active AND policy_role_check('department'::text, 'general'::text, 'read'::text, NULL::uuid, id, company_id))
44.          

SubPlan (for Subquery Scan)

45. 3.458 3.458 ↑ 1.0 1 26

Index Scan using department_pkey on department __local_14__ (cost=0.28..8.55 rows=1 width=32) (actual time=0.133..0.133 rows=1 loops=26)

  • Index Cond: (__local_13__.department_id = id)
  • Filter: policy_role_check('department'::text, 'general'::text, 'read'::text, NULL::uuid, id, company_id)
46.          

CTE __local_16__

47. 0.080 7.460 ↑ 1.0 1 20

Aggregate (cost=0.03..0.04 rows=1 width=32) (actual time=0.373..0.373 rows=1 loops=20)

48. 7.380 7.380 ↑ 1.0 1 20

CTE Scan on __local_12__ (cost=0.00..0.02 rows=1 width=24) (actual time=0.289..0.369 rows=1 loops=20)

49.          

Initplan (for Result)

50. 7.500 7.500 ↑ 1.0 1 20

CTE Scan on __local_16__ (cost=0.00..0.02 rows=1 width=32) (actual time=0.375..0.375 rows=1 loops=20)

51. 0.060 0.420 ↑ 1.0 1 20

Result (cost=16.92..16.93 rows=1 width=32) (actual time=0.021..0.021 rows=1 loops=20)

52.          

CTE __local_17__

53. 0.055 0.260 ↓ 0.0 0 20

Subquery Scan on __local_19__ (cost=0.28..16.86 rows=1 width=32) (actual time=0.012..0.013 rows=0 loops=20)

54. 0.100 0.100 ↓ 0.0 0 20

Index Only Scan using user_group_pkey on user_group __local_19___1 (cost=0.28..8.29 rows=1 width=48) (actual time=0.005..0.005 rows=0 loops=20)

  • Index Cond: (user_id = __local_6__.id)
  • Heap Fetches: 7
55.          

SubPlan (for Subquery Scan)

56. 0.105 0.105 ↑ 1.0 1 7

Index Scan using group_pkey on "group" __local_18__ (cost=0.28..8.55 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=7)

  • Index Cond: (__local_19__.group_id = id)
  • Filter: (company_id = current_user_company())
57.          

CTE __local_20__

58. 0.040 0.320 ↑ 1.0 1 20

Aggregate (cost=0.03..0.04 rows=1 width=32) (actual time=0.016..0.016 rows=1 loops=20)

59. 0.280 0.280 ↓ 0.0 0 20

CTE Scan on __local_17__ (cost=0.00..0.02 rows=1 width=24) (actual time=0.014..0.014 rows=0 loops=20)

60.          

Initplan (for Result)

61. 0.360 0.360 ↑ 1.0 1 20

CTE Scan on __local_20__ (cost=0.00..0.02 rows=1 width=32) (actual time=0.018..0.018 rows=1 loops=20)

62. 2.820 2.820 ↑ 1.0 1 20

Index Scan using employee_sensitive_information_employee_id_index on employee_sensitive_information __local_21__ (cost=0.28..8.55 rows=1 width=32) (actual time=0.140..0.141 rows=1 loops=20)

  • Index Cond: (employee_id = __local_1__.id)
  • Filter: ((NOT (__local_21__.* IS NULL)) AND policy_role_check('employee'::text, 'sensitive_data'::text, 'read'::text, user_id, departments, company_id))
63. 2.760 2.760 ↑ 1.0 1 20

Index Scan using user_id_active_employment_end on "user" (cost=0.28..8.55 rows=1 width=7) (actual time=0.137..0.138 rows=1 loops=20)

  • Index Cond: (__local_1__.user_id = id)
  • Filter: policy_role_check('employee'::text, 'general'::text, 'read'::text, id, departments, company_id)
64. 2.580 2.580 ↑ 1.0 1 20

Index Scan using user_id_active_employment_end on "user" user_1 (cost=0.28..8.55 rows=1 width=7) (actual time=0.129..0.129 rows=1 loops=20)

  • Index Cond: (__local_1__.user_id = id)
  • Filter: policy_role_check('employee'::text, 'general'::text, 'read'::text, id, departments, company_id)
65.          

CTE __local_23__

66. 0.127 3,167.936 ↑ 1.0 1 1

Aggregate (cost=0.05..0.06 rows=1 width=32) (actual time=3,167.936..3,167.936 rows=1 loops=1)

67. 3,167.809 3,167.809 ↓ 10.0 20 1

CTE Scan on __local_0__ (cost=0.00..0.04 rows=2 width=24) (actual time=287.909..3,167.809 rows=20 loops=1)

68.          

Initplan (for Result)

69. 3,167.991 3,167.991 ↑ 1.0 1 1

CTE Scan on __local_23__ (cost=0.00..0.02 rows=1 width=32) (actual time=3,167.990..3,167.991 rows=1 loops=1)

70. 0.002 1.211 ↑ 1.0 1 1

Nested Loop (cost=69.83..1,702.89 rows=1 width=0) (actual time=1.211..1.211 rows=1 loops=1)

71. 0.348 1.045 ↑ 12.0 1 1

Bitmap Heap Scan on employee __local_1___2 (cost=69.55..1,608.20 rows=12 width=16) (actual time=1.045..1.045 rows=1 loops=1)

  • Recheck Cond: (company_id = '47d08a0b-2c1b-4591-acc0-4d521915c8ef'::uuid)
  • Filter: (active AND (is_archived IS FALSE) AND (((NOT is_archived) AND ((employment_end > now()) OR (employment_end IS NULL)) AND policy_role_check('employee'::text, 'general'::text, 'read'::text, user_id, departments, company_id)) OR ((is_archived OR (employment_end <= now())) AND policy_role_check('employee'::text, 'archived'::text, 'read'::text, user_id, departments, company_id))) AND (NOT (hashed SubPlan 32)))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=2
72. 0.001 0.112 ↓ 0.0 0 1

BitmapAnd (cost=69.50..69.50 rows=80 width=0) (actual time=0.112..0.112 rows=0 loops=1)

73. 0.015 0.015 ↑ 1.0 165 1

Bitmap Index Scan on employee_company_id_index (cost=0.00..5.52 rows=165 width=0) (actual time=0.015..0.015 rows=165 loops=1)

  • Index Cond: (company_id = '47d08a0b-2c1b-4591-acc0-4d521915c8ef'::uuid)
74. 0.096 0.096 ↑ 1.0 3,125 1

Bitmap Index Scan on employee_active_index (cost=0.00..63.72 rows=3,125 width=0) (actual time=0.096..0.096 rows=3,125 loops=1)

  • Index Cond: (active = true)
75.          

SubPlan (for Bitmap Heap Scan)

76. 0.288 0.288 ↑ 1.0 1 2

Index Scan using user_id_active_employment_end on "user" user_4 (cost=0.28..8.55 rows=1 width=7) (actual time=0.143..0.144 rows=1 loops=2)

  • Index Cond: (__local_1___2.user_id = id)
  • Filter: policy_role_check('employee'::text, 'general'::text, 'read'::text, id, departments, company_id)
77. 0.280 0.280 ↑ 1.0 1 2

Index Scan using user_id_active_employment_end on "user" user_5 (cost=0.28..8.55 rows=1 width=7) (actual time=0.140..0.140 rows=1 loops=2)

  • Index Cond: (__local_1___2.user_id = id)
  • Filter: policy_role_check('employee'::text, 'general'::text, 'read'::text, id, departments, company_id)
78. 0.017 0.017 ↓ 10.0 20 1

CTE Scan on __local_0__ __local_0___1 (cost=0.00..0.05 rows=2 width=32) (actual time=0.002..0.017 rows=20 loops=1)

79. 0.164 0.164 ↑ 1.0 1 1

Index Scan using user_pkey on "user" __local_22___1 (cost=0.28..7.89 rows=1 width=16) (actual time=0.164..0.164 rows=1 loops=1)

  • Index Cond: (id = __local_1___2.user_id)
  • Filter: (active AND policy_role_check('employee'::text, 'general'::text, 'read'::text, id, departments, company_id) AND ((employment_end IS NULL) OR ((employment_end)::text > '2019-11-07T15:35:58.188Z'::text) OR ((employment_end)::text = ''::text)))
80. 0.015 6.706 ↑ 1.0 1 1

Aggregate (cost=412.50..412.51 rows=1 width=32) (actual time=6.706..6.706 rows=1 loops=1)

81. 0.016 6.691 ↓ 11.5 23 1

Nested Loop (cost=69.78..412.50 rows=2 width=0) (actual time=0.452..6.691 rows=23 loops=1)

82. 3.239 3.347 ↓ 1.1 26 1

Bitmap Heap Scan on employee __local_1___3 (cost=69.50..239.15 rows=24 width=16) (actual time=0.282..3.347 rows=26 loops=1)

  • Recheck Cond: (company_id = '47d08a0b-2c1b-4591-acc0-4d521915c8ef'::uuid)
  • Filter: (active AND (is_archived IS FALSE) AND (((NOT is_archived) AND ((employment_end > now()) OR (employment_end IS NULL)) AND policy_role_check('employee'::text, 'general'::text, 'read'::text, user_id, departments, company_id)) OR ((is_archived OR (employment_end <= now())) AND policy_role_check('employee'::text, 'archived'::text, 'read'::text, user_id, departments, company_id))))
  • Heap Blocks: exact=6
83. 0.002 0.108 ↓ 0.0 0 1

BitmapAnd (cost=69.50..69.50 rows=80 width=0) (actual time=0.108..0.108 rows=0 loops=1)

84. 0.013 0.013 ↑ 1.0 165 1

Bitmap Index Scan on employee_company_id_index (cost=0.00..5.52 rows=165 width=0) (actual time=0.013..0.013 rows=165 loops=1)

  • Index Cond: (company_id = '47d08a0b-2c1b-4591-acc0-4d521915c8ef'::uuid)
85. 0.093 0.093 ↑ 1.0 3,125 1

Bitmap Index Scan on employee_active_index (cost=0.00..63.72 rows=3,125 width=0) (actual time=0.093..0.093 rows=3,125 loops=1)

  • Index Cond: (active = true)
86. 3.328 3.328 ↑ 1.0 1 26

Index Scan using user_pkey on "user" __local_22___2 (cost=0.28..7.22 rows=1 width=16) (actual time=0.128..0.128 rows=1 loops=26)

  • Index Cond: (id = __local_1___3.user_id)
  • Filter: (active AND policy_role_check('employee'::text, 'general'::text, 'read'::text, id, departments, company_id) AND ((employment_end IS NULL) OR ((employment_end)::text > '2019-11-07T15:35:58.188Z'::text) OR ((employment_end)::text = ''::text)))