explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4aCo

Settings
# exclusive inclusive rows x rows loops node
1. 0.040 3,757.078 ↑ 1.0 1 1

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

  • Output: COALESCE($52, '[]'::json), $57, $59
2.          

CTE __local_0__

3. 0.833 3,747.569 ↓ 10.0 20 1

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

  • Output: to_json(json_build_object('@node'::text, json_build_object('id'::text, __local_1__.id, 'userId'::text, __local_1__.user_id, 'isArchived'::text, __local_1__.is_archived, '@logo'::text, (SubPlan 1), 'jobPositionTitle'::text, __local_1__.job_position_title, 'jobPositionType'::text, __local_1__.job_position_type, 'jobPositionDescription'::text, __local_1__.job_position_description, '@latestFormFieldsEntries'::text, (SubPlan 4), '@user'::text, (SubPlan 21), '@employeeSensitiveInformationByEmployeeId'::text, (SubPlan 22)))), to_json(json_build_array('user_by_user_id__first_name_asc', 'user_by_user_id__last_name_asc', json_build_array((SubPlan 23), (SubPlan 24), __local_1__.id)))
4. 0.014 19.896 ↓ 10.0 20 1

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

  • Output: __local_1___1.id, __local_1___1.user_id, __local_1___1.job_position_title, __local_1___1.job_position_description, __local_1___1.job_position_type, __local_1___1.active, __local_1___1.created_at, __local_1___1.updated_at, __local_1___1.is_archived, __local_1___1.company_id, __local_1___1.departments, __local_1___1.employment_end, ((SubPlan 25)), ((SubPlan 26))
5. 0.085 19.882 ↓ 10.0 20 1

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

  • Output: __local_1___1.id, __local_1___1.user_id, __local_1___1.job_position_title, __local_1___1.job_position_description, __local_1___1.job_position_type, __local_1___1.active, __local_1___1.created_at, __local_1___1.updated_at, __local_1___1.is_archived, __local_1___1.company_id, __local_1___1.departments, __local_1___1.employment_end, ((SubPlan 25)), ((SubPlan 26))
  • Sort Key: ((SubPlan 25)), ((SubPlan 26)), __local_1___1.id
  • Sort Method: quicksort Memory: 31kB
6. 0.107 19.797 ↓ 11.5 23 1

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

  • Output: __local_1___1.id, __local_1___1.user_id, __local_1___1.job_position_title, __local_1___1.job_position_description, __local_1___1.job_position_type, __local_1___1.active, __local_1___1.created_at, __local_1___1.updated_at, __local_1___1.is_archived, __local_1___1.company_id, __local_1___1.departments, __local_1___1.employment_end, (SubPlan 25), (SubPlan 26)
  • Inner Unique: true
7. 4.941 5.258 ↓ 1.1 26 1

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

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

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

9. 0.135 0.135 ↑ 1.0 165 1

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

  • Index Cond: (__local_1___1.company_id = '47d08a0b-2c1b-4591-acc0-4d521915c8ef'::uuid)
10. 0.179 0.179 ↑ 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.178..0.179 rows=3,125 loops=1)

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

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

  • Output: __local_22__.id, __local_22__.company_id, __local_22__.first_name, __local_22__.last_name, __local_22__.email, __local_22__.username, __local_22__.phone, __local_22__.mobile, __local_22__.address, __local_22__.city, __local_22__.postal_code, __local_22__.country, __local_22__.birthdate, __local_22__.locale, __local_22__.active, __local_22__.delete_at, __local_22__.mobile_private, __local_22__.employment_start, __local_22__.employment_end, __local_22__.employment_end_reminded, __local_22__.last_login, __local_22__.email_work, __local_22__.departments
  • Index Cond: (__local_22__.id = __local_1___1.user_id)
  • Filter: (__local_22__.active AND policy_role_check('employee'::text, 'general'::text, 'read'::text, __local_22__.id, __local_22__.departments, __local_22__.company_id) AND ((__local_22__.employment_end IS NULL) OR ((__local_22__.employment_end)::text > '2019-11-07T15:45:08.391Z'::text) OR ((__local_22__.employment_end)::text = ''::text)))
  • Rows Removed by Filter: 0
12.          

SubPlan (for Nested Loop)

13. 4.830 4.830 ↑ 1.0 1 23

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

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

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

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

SubPlan (for Subquery Scan)

16. 1.100 1.100 ↑ 1.0 1 20

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

  • Output: to_json(__local_2__.__local_2__)
  • Function Call: employee_logo(ROW(__local_1__.id, __local_1__.user_id, __local_1__.job_position_title, __local_1__.job_position_description, __local_1__.job_position_type, __local_1__.active, __local_1__.created_at, __local_1__.updated_at, __local_1__.is_archived, __local_1__.company_id, __local_1__.departments, __local_1__.employment_end))
17. 0.060 3,701.240 ↑ 1.0 1 20

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

  • Output: COALESCE($25, '[]'::json)
18.          

Initplan (for Result)

19. 1.372 3,701.180 ↑ 1.0 1 20

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

  • Output: json_agg(json_build_object('id'::text, __local_4__.id, 'value'::text, __local_4__.value, 'formFieldId'::text, __local_4__.form_field_id, '@formField'::text, (SubPlan 2)))
20. 3,688.400 3,688.400 ↑ 11.1 9 20

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

  • Output: __local_4__.id, __local_4__.form_field_id, __local_4__.employee_id, __local_4__.value, __local_4__.created_at, __local_4__.updated_at, __local_4__.active, __local_4__.company_id, __local_4__.departments, __local_4__.sensitive, __local_4__.group_read, __local_4__.group_write
  • Function Call: unnest(employee_latest_form_fields_entries(ROW(__local_1__.id, __local_1__.user_id, __local_1__.job_position_title, __local_1__.job_position_description, __local_1__.job_position_type, __local_1__.active, __local_1__.created_at, __local_1__.updated_at, __local_1__.is_archived, __local_1__.company_id, __local_1__.departments, __local_1__.employment_end)))
21.          

SubPlan (for Aggregate)

22. 11.408 11.408 ↑ 1.0 1 184

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

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

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

  • Output: json_build_object('id'::text, __local_6__.id, 'firstName'::text, __local_6__.first_name, 'lastName'::text, __local_6__.last_name, '@fullName'::text, (SubPlan 5), 'email'::text, __local_6__.email, 'emailWork'::text, __local_6__.email_work, 'mobile'::text, __local_6__.mobile, 'phone'::text, __local_6__.phone, 'employmentStart'::text, __local_6__.employment_start, 'employmentEnd'::text, __local_6__.employment_end, 'birthdate'::text, __local_6__.birthdate, 'address'::text, __local_6__.address, 'postalCode'::text, __local_6__.postal_code, 'city'::text, __local_6__.city, 'mobilePrivate'::text, __local_6__.mobile_private, 'locale'::text, __local_6__.locale, '@adminDepartments'::text, (SubPlan 10), '@userDepartments'::text, (SubPlan 15), '@userGroups'::text, (SubPlan 20))
  • Index Cond: (__local_1__.user_id = __local_6__.id)
  • Filter: policy_role_check('employee'::text, 'general'::text, 'read'::text, __local_6__.id, __local_6__.departments, __local_6__.company_id)
24.          

SubPlan (for Index Scan)

25. 0.100 0.100 ↑ 1.0 1 20

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

  • Output: to_json(__local_7__.__local_7__)
  • Function Call: ((((__local_6__.*).first_name)::text || ' '::text) || ((__local_6__.*).last_name)::text)
26. 0.060 0.480 ↑ 1.0 1 20

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

  • Output: json_build_object('data'::text, COALESCE($31, '[]'::json))
27.          

CTE __local_8__

28. 0.020 0.320 ↓ 0.0 0 20

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

  • Output: to_json(json_build_object('@node'::text, json_build_object('departmentId'::text, __local_9__.department_id, '@department'::text, (SubPlan 6))))
29. 0.120 0.300 ↓ 0.0 0 20

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

  • Output: __local_9___1.user_id, __local_9___1.department_id
  • Sort Key: __local_9___1.department_id
  • Sort Method: quicksort Memory: 25kB
30. 0.180 0.180 ↓ 0.0 0 20

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

  • Output: __local_9___1.user_id, __local_9___1.department_id
  • Filter: (false AND (__local_9___1.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 public.department __local_10__ (cost=0.28..8.55 rows=1 width=32) (never executed)

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

CTE __local_11__

34. 0.020 0.380 ↑ 1.0 1 20

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

  • Output: json_agg(to_json(__local_8__.*))
35. 0.360 0.360 ↓ 0.0 0 20

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

  • Output: __local_8__.*
36.          

Initplan (for Result)

37. 0.420 0.420 ↑ 1.0 1 20

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

  • Output: __local_11__.data
38. 0.060 9.760 ↑ 1.0 1 20

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

  • Output: json_build_object('data'::text, COALESCE($37, '[]'::json))
39.          

CTE __local_12__

40. 0.198 9.480 ↑ 1.0 1 20

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

  • Output: to_json(json_build_object('@node'::text, json_build_object('departmentId'::text, __local_13__.department_id, '@department'::text, (SubPlan 11))))
41. 0.088 4.680 ↑ 1.0 1 20

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

  • Output: __local_13___1.user_id, __local_13___1.department_id, NULL::uuid
  • Inner Unique: true
42. 0.120 0.120 ↑ 1.0 1 20

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

  • Output: __local_13___1.user_id, __local_13___1.department_id
  • Index Cond: (__local_13___1.user_id = __local_6__.id)
  • Heap Fetches: 26
43. 4.472 4.472 ↑ 1.0 1 26

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

  • Output: __local_15__.id, __local_15__.company_id, __local_15__.main_department, __local_15__.alias, __local_15__.name, __local_15__.description, __local_15__.p_number, __local_15__.ean, __local_15__.active, __local_15__.delete_at, __local_15__.phone, __local_15__.email, __local_15__.address, __local_15__.city, __local_15__.postal_code, __local_15__.country, __local_15__.created_at, __local_15__.updated_at, __local_15__.cvr, __local_15__.parent_department_id
  • Index Cond: (__local_15__.id = __local_13___1.department_id)
  • Filter: (__local_15__.active AND policy_role_check('department'::text, 'general'::text, 'read'::text, NULL::uuid, __local_15__.id, __local_15__.company_id))
44.          

SubPlan (for Subquery Scan)

45. 4.602 4.602 ↑ 1.0 1 26

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

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

CTE __local_16__

47. 0.120 9.660 ↑ 1.0 1 20

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

  • Output: json_agg(to_json(__local_12__.*))
48. 9.540 9.540 ↑ 1.0 1 20

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

  • Output: __local_12__.*
49.          

Initplan (for Result)

50. 9.700 9.700 ↑ 1.0 1 20

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

  • Output: __local_16__.data
51. 0.060 0.440 ↑ 1.0 1 20

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

  • Output: json_build_object('data'::text, COALESCE($42, '[]'::json))
52.          

CTE __local_17__

53. 0.061 0.280 ↓ 0.0 0 20

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

  • Output: to_json(json_build_object('@node'::text, json_build_object('@group'::text, (SubPlan 16))))
54. 0.100 0.100 ↓ 0.0 0 20

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

  • Output: __local_19___1.user_id, __local_19___1.group_id, NULL::uuid
  • Index Cond: (__local_19___1.user_id = __local_6__.id)
  • Heap Fetches: 7
55.          

SubPlan (for Subquery Scan)

56. 0.119 0.119 ↑ 1.0 1 7

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

  • Output: json_build_object('id'::text, __local_18__.id, 'description'::text, __local_18__.description)
  • Index Cond: (__local_19__.group_id = __local_18__.id)
  • Filter: (__local_18__.company_id = current_user_company())
57.          

CTE __local_20__

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

  • Output: json_agg(to_json(__local_17__.*))
59. 0.300 0.300 ↓ 0.0 0 20

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

  • Output: __local_17__.*
60.          

Initplan (for Result)

61. 0.380 0.380 ↑ 1.0 1 20

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

  • Output: __local_20__.data
62. 3.420 3.420 ↑ 1.0 1 20

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

  • Output: CASE WHEN (__local_21__.* IS NULL) THEN NULL::json ELSE json_build_object('cpr'::text, __local_21__.cpr, 'bankRegistrationNumber'::text, __local_21__.bank_registration_number, 'bankAccountNumber'::text, __local_21__.bank_account_number) END
  • Index Cond: (__local_21__.employee_id = __local_1__.id)
  • Filter: ((NOT (__local_21__.* IS NULL)) AND policy_role_check('employee'::text, 'sensitive_data'::text, 'read'::text, __local_21__.user_id, __local_21__.departments, __local_21__.company_id))
63. 3.240 3.240 ↑ 1.0 1 20

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

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

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

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

CTE __local_23__

66. 0.160 3,747.962 ↑ 1.0 1 1

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

  • Output: json_agg(to_json(__local_0__.*))
67. 3,747.802 3,747.802 ↓ 10.0 20 1

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

  • Output: __local_0__.*
68.          

Initplan (for Result)

69. 3,747.985 3,747.985 ↑ 1.0 1 1

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

  • Output: __local_23__.data
70. 0.001 1.367 ↑ 1.0 1 1

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

  • Inner Unique: true
71. 0.385 1.175 ↑ 12.0 1 1

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

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

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

73. 0.016 0.016 ↑ 1.0 165 1

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

  • Index Cond: (__local_1___2.company_id = '47d08a0b-2c1b-4591-acc0-4d521915c8ef'::uuid)
74. 0.105 0.105 ↑ 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.105..0.105 rows=3,125 loops=1)

  • Index Cond: (__local_1___2.active = true)
75.          

SubPlan (for Bitmap Heap Scan)

76. 0.326 0.326 ↑ 1.0 1 2

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

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

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

  • Output: user_5.last_name
  • Index Cond: (__local_1___2.user_id = user_5.id)
  • Filter: policy_role_check('employee'::text, 'general'::text, 'read'::text, user_5.id, user_5.departments, user_5.company_id)
78. 0.018 0.018 ↓ 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.018 rows=20 loops=1)

  • Output: (__local_0___1.__cursor)::text
79. 0.191 0.191 ↑ 1.0 1 1

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

  • Output: __local_22___1.id, __local_22___1.company_id, __local_22___1.first_name, __local_22___1.last_name, __local_22___1.email, __local_22___1.username, __local_22___1.phone, __local_22___1.mobile, __local_22___1.address, __local_22___1.city, __local_22___1.postal_code, __local_22___1.country, __local_22___1.birthdate, __local_22___1.locale, __local_22___1.active, __local_22___1.delete_at, __local_22___1.mobile_private, __local_22___1.employment_start, __local_22___1.employment_end, __local_22___1.employment_end_reminded, __local_22___1.last_login, __local_22___1.email_work, __local_22___1.departments
  • Index Cond: (__local_22___1.id = __local_1___2.user_id)
  • Filter: (__local_22___1.active AND policy_role_check('employee'::text, 'general'::text, 'read'::text, __local_22___1.id, __local_22___1.departments, __local_22___1.company_id) AND ((__local_22___1.employment_end IS NULL) OR ((__local_22___1.employment_end)::text > '2019-11-07T15:45:08.391Z'::text) OR ((__local_22___1.employment_end)::text = ''::text)))
80. 0.013 7.686 ↑ 1.0 1 1

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

  • Output: json_build_object('totalCount'::text, count(1))
81. 0.011 7.673 ↓ 11.5 23 1

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

  • Inner Unique: true
82. 3.771 3.892 ↓ 1.1 26 1

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

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

BitmapAnd (cost=69.50..69.50 rows=80 width=0) (actual time=0.121..0.121 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: (__local_1___3.company_id = '47d08a0b-2c1b-4591-acc0-4d521915c8ef'::uuid)
85. 0.105 0.105 ↑ 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.105..0.105 rows=3,125 loops=1)

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

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

  • Output: __local_22___2.id, __local_22___2.company_id, __local_22___2.first_name, __local_22___2.last_name, __local_22___2.email, __local_22___2.username, __local_22___2.phone, __local_22___2.mobile, __local_22___2.address, __local_22___2.city, __local_22___2.postal_code, __local_22___2.country, __local_22___2.birthdate, __local_22___2.locale, __local_22___2.active, __local_22___2.delete_at, __local_22___2.mobile_private, __local_22___2.employment_start, __local_22___2.employment_end, __local_22___2.employment_end_reminded, __local_22___2.last_login, __local_22___2.email_work, __local_22___2.departments
  • Index Cond: (__local_22___2.id = __local_1___3.user_id)
  • Filter: (__local_22___2.active AND policy_role_check('employee'::text, 'general'::text, 'read'::text, __local_22___2.id, __local_22___2.departments, __local_22___2.company_id) AND ((__local_22___2.employment_end IS NULL) OR ((__local_22___2.employment_end)::text > '2019-11-07T15:45:08.391Z'::text) OR ((__local_22___2.employment_end)::text = ''::text)))
  • Rows Removed by Filter: 0