explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9lU2

Settings
# exclusive inclusive rows x rows loops node
1. 0.052 12,354.775 ↑ 1.0 1 1

Nested Loop Left Join (cost=14.20..1,205.13 rows=1 width=30) (actual time=8,913.878..12,354.775 rows=1 loops=1)

2. 0.065 12,354.697 ↑ 1.0 1 1

Nested Loop (cost=13.67..1,196.56 rows=1 width=40) (actual time=8,913.801..12,354.697 rows=1 loops=1)

3. 0.003 12,354.603 ↑ 1.0 1 1

Nested Loop Left Join (cost=13.14..1,187.99 rows=1 width=51) (actual time=8,913.707..12,354.603 rows=1 loops=1)

4. 2.972 12,354.516 ↑ 1.0 1 1

Nested Loop (cost=12.22..1,117.30 rows=1 width=82) (actual time=8,913.622..12,354.516 rows=1 loops=1)

  • Join Filter: ((employeepo0_.rehire_reason_oid)::text = (tblvc_11.oid)::text)
  • Rows Removed by Join Filter: 85
5. 8.136 8.136 ↓ 86.0 86 1

Index Scan using pk_chr_reason_pl on chr_reason_pl tblvc_11 (cost=0.79..51.90 rows=1 width=31) (actual time=0.184..8.136 rows=86 loops=1)

  • Index Cond: (((vpd_key)::text = ANY (string_to_array((f_sel_policy_ty_static())::text, ','::text))) AND ((vpd_key)::text = (sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying))::text))
  • Filter: ((("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[])) AND ((prod_locale_code)::text = ANY (string_to_array((f_sel_policy_prod_locale('CHR_REASON_PL'::character varying))::text, ','::text))))
6. 0.172 12,343.408 ↑ 1.0 1 86

Nested Loop Left Join (cost=11.43..1,065.38 rows=1 width=66) (actual time=103.840..143.528 rows=1 loops=86)

7. 403.942 12,336.700 ↑ 1.0 1 86

Nested Loop (cost=10.52..994.70 rows=1 width=97) (actual time=103.762..143.450 rows=1 loops=86)

  • Join Filter: ((employeepo0_.loa_return_reason_oid)::text = (tblvc_10.oid)::text)
  • Rows Removed by Join Filter: 85
8. 698.234 698.234 ↓ 86.0 86 86

Index Scan using pk_chr_reason_pl on chr_reason_pl tblvc_10 (cost=0.79..51.90 rows=1 width=31) (actual time=0.114..8.119 rows=86 loops=86)

  • Index Cond: (((vpd_key)::text = ANY (string_to_array((f_sel_policy_ty_static())::text, ','::text))) AND ((vpd_key)::text = (sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying))::text))
  • Filter: ((("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[])) AND ((prod_locale_code)::text = ANY (string_to_array((f_sel_policy_prod_locale('CHR_REASON_PL'::character varying))::text, ','::text))))
9. 236.672 11,234.524 ↑ 1.0 1 7,396

Nested Loop (cost=9.73..942.78 rows=1 width=80) (actual time=1.515..1.519 rows=1 loops=7,396)

10. 244.068 10,191.688 ↑ 1.0 1 7,396

Nested Loop (cost=8.81..849.38 rows=1 width=94) (actual time=1.375..1.378 rows=1 loops=7,396)

11. 229.276 9,134.060 ↑ 1.0 1 7,396

Nested Loop (cost=7.89..755.99 rows=1 width=108) (actual time=1.232..1.235 rows=1 loops=7,396)

12. 236.672 8,098.620 ↑ 1.0 1 7,396

Nested Loop (cost=6.97..662.59 rows=1 width=119) (actual time=1.092..1.095 rows=1 loops=7,396)

13. 229.276 7,055.784 ↑ 1.0 1 7,396

Nested Loop (cost=6.05..569.20 rows=1 width=132) (actual time=0.952..0.954 rows=1 loops=7,396)

14. 244.068 6,005.552 ↑ 1.0 1 7,396

Nested Loop (cost=5.13..475.80 rows=1 width=146) (actual time=0.809..0.812 rows=1 loops=7,396)

15. 244.068 4,962.716 ↑ 1.0 1 7,396

Nested Loop (cost=4.21..382.41 rows=1 width=159) (actual time=0.670..0.671 rows=1 loops=7,396)

16. 236.672 3,912.484 ↑ 1.0 1 7,396

Nested Loop (cost=3.29..289.01 rows=1 width=173) (actual time=0.528..0.529 rows=1 loops=7,396)

17. 244.068 2,862.252 ↑ 1.0 1 7,396

Nested Loop (cost=2.37..195.62 rows=1 width=187) (actual time=0.386..0.387 rows=1 loops=7,396)

18. 244.068 1,819.416 ↑ 1.0 1 7,396

Nested Loop (cost=1.45..102.22 rows=1 width=192) (actual time=0.246..0.246 rows=1 loops=7,396)

19. 761.788 761.788 ↑ 1.0 1 7,396

Index Scan using ix14_chr_emp_position on chr_emp_position employeepo0_ (cost=0.53..8.83 rows=1 width=198) (actual time=0.103..0.103 rows=1 loops=7,396)

  • Index Cond: (((vpd_key)::text = (f_sel_policy_all('CHR_EMP_POSITION'::character varying))::text) AND ((employee_oid)::text = 'CC1_95441'::text))
  • Filter: (('2020-10-13'::date >= eff_date) AND ('2020-10-13'::date <= eff_date_end) AND (("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[])) AND ((prod_locale_code)::text = ANY (string_to_array((f_sel_policy_prod_locale('CHR_EMP_POSITION'::character varying))::text, ','::text))))
20. 813.560 813.560 ↑ 1.0 1 7,396

Index Scan using ixt_chr_simple_val on chr_simple_val tblvc (cost=0.92..93.38 rows=1 width=8) (actual time=0.110..0.110 rows=1 loops=7,396)

  • Index Cond: (((vpd_key)::text = ANY (string_to_array((f_sel_policy_ty_static())::text, ','::text))) AND ((vpd_key)::text = ANY ((ARRAY[sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying), 'NG_COMMON'::character varying])::text[])) AND ((oid)::text = (employeepo0_.job_class_oid)::text))
  • Filter: ((("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[])) AND ((prod_locale_code)::text = ANY (string_to_array((f_sel_policy_prod_locale('CHR_SIMPLE_VAL'::character varying))::text, ','::text))))
21. 798.768 798.768 ↑ 1.0 1 7,396

Index Scan using ixt_chr_simple_val on chr_simple_val tblvc_1 (cost=0.92..93.38 rows=1 width=8) (actual time=0.108..0.108 rows=1 loops=7,396)

  • Index Cond: (((vpd_key)::text = ANY (string_to_array((f_sel_policy_ty_static())::text, ','::text))) AND ((vpd_key)::text = ANY ((ARRAY[sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying), 'NG_COMMON'::character varying])::text[])) AND ((oid)::text = (employeepo0_.job_change_reason_oid)::text))
  • Filter: ((("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[])) AND ((prod_locale_code)::text = ANY (string_to_array((f_sel_policy_prod_locale('CHR_SIMPLE_VAL'::character varying))::text, ','::text))))
22. 813.560 813.560 ↑ 1.0 1 7,396

Index Scan using ixt_chr_simple_val on chr_simple_val tblvc_2 (cost=0.92..93.38 rows=1 width=8) (actual time=0.109..0.110 rows=1 loops=7,396)

  • Index Cond: (((vpd_key)::text = ANY (string_to_array((f_sel_policy_ty_static())::text, ','::text))) AND ((vpd_key)::text = ANY ((ARRAY[sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying), 'NG_COMMON'::character varying])::text[])) AND ((oid)::text = (employeepo0_.job_function_oid)::text))
  • Filter: ((("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[])) AND ((prod_locale_code)::text = ANY (string_to_array((f_sel_policy_prod_locale('CHR_SIMPLE_VAL'::character varying))::text, ','::text))))
23. 806.164 806.164 ↑ 1.0 1 7,396

Index Scan using ixt_chr_simple_val on chr_simple_val tblvc_3 (cost=0.92..93.38 rows=1 width=8) (actual time=0.109..0.109 rows=1 loops=7,396)

  • Index Cond: (((vpd_key)::text = ANY (string_to_array((f_sel_policy_ty_static())::text, ','::text))) AND ((vpd_key)::text = ANY ((ARRAY[sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying), 'NG_COMMON'::character varying])::text[])) AND ((oid)::text = (employeepo0_.assigned_shift_oid)::text))
  • Filter: ((("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[])) AND ((prod_locale_code)::text = ANY (string_to_array((f_sel_policy_prod_locale('CHR_SIMPLE_VAL'::character varying))::text, ','::text))))
24. 798.768 798.768 ↑ 1.0 1 7,396

Index Scan using ixt_chr_simple_val on chr_simple_val tblvc_4 (cost=0.92..93.38 rows=1 width=8) (actual time=0.108..0.108 rows=1 loops=7,396)

  • Index Cond: (((vpd_key)::text = ANY (string_to_array((f_sel_policy_ty_static())::text, ','::text))) AND ((vpd_key)::text = ANY ((ARRAY[sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying), 'NG_COMMON'::character varying])::text[])) AND ((oid)::text = (employeepo0_.flsa_oid)::text))
  • Filter: ((("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[])) AND ((prod_locale_code)::text = ANY (string_to_array((f_sel_policy_prod_locale('CHR_SIMPLE_VAL'::character varying))::text, ','::text))))
25. 820.956 820.956 ↑ 1.0 1 7,396

Index Scan using ixt_chr_simple_val on chr_simple_val tblvc_5 (cost=0.92..93.38 rows=1 width=8) (actual time=0.111..0.111 rows=1 loops=7,396)

  • Index Cond: (((vpd_key)::text = ANY (string_to_array((f_sel_policy_ty_static())::text, ','::text))) AND ((vpd_key)::text = ANY ((ARRAY[sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying), 'NG_COMMON'::character varying])::text[])) AND ((oid)::text = (employeepo0_.naics_workers_comp_oid)::text))
  • Filter: ((("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[])) AND ((prod_locale_code)::text = ANY (string_to_array((f_sel_policy_prod_locale('CHR_SIMPLE_VAL'::character varying))::text, ','::text))))
26. 806.164 806.164 ↑ 1.0 1 7,396

Index Scan using ixt_chr_simple_val on chr_simple_val tblvc_6 (cost=0.92..93.38 rows=1 width=8) (actual time=0.109..0.109 rows=1 loops=7,396)

  • Index Cond: (((vpd_key)::text = ANY (string_to_array((f_sel_policy_ty_static())::text, ','::text))) AND ((vpd_key)::text = ANY ((ARRAY[sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying), 'NG_COMMON'::character varying])::text[])) AND ((oid)::text = (employeepo0_.home_cost_no_oid)::text))
  • Filter: ((("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[])) AND ((prod_locale_code)::text = ANY (string_to_array((f_sel_policy_prod_locale('CHR_SIMPLE_VAL'::character varying))::text, ','::text))))
27. 806.164 806.164 ↑ 1.0 1 7,396

Index Scan using ixt_chr_simple_val on chr_simple_val tblvc_7 (cost=0.92..93.38 rows=1 width=8) (actual time=0.108..0.109 rows=1 loops=7,396)

  • Index Cond: (((vpd_key)::text = ANY (string_to_array((f_sel_policy_ty_static())::text, ','::text))) AND ((vpd_key)::text = ANY ((ARRAY[sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying), 'NG_COMMON'::character varying])::text[])) AND ((oid)::text = (employeepo0_.home_dept_oid)::text))
  • Filter: ((("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[])) AND ((prod_locale_code)::text = ANY (string_to_array((f_sel_policy_prod_locale('CHR_SIMPLE_VAL'::character varying))::text, ','::text))))
28. 813.560 813.560 ↑ 1.0 1 7,396

Index Scan using ixt_chr_simple_val on chr_simple_val tblvc_8 (cost=0.92..93.38 rows=1 width=8) (actual time=0.110..0.110 rows=1 loops=7,396)

  • Index Cond: (((vpd_key)::text = ANY (string_to_array((f_sel_policy_ty_static())::text, ','::text))) AND ((vpd_key)::text = ANY ((ARRAY[sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying), 'NG_COMMON'::character varying])::text[])) AND ((oid)::text = (employeepo0_.rehire_status_oid)::text))
  • Filter: ((("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[])) AND ((prod_locale_code)::text = ANY (string_to_array((f_sel_policy_prod_locale('CHR_SIMPLE_VAL'::character varying))::text, ','::text))))
29. 806.164 806.164 ↑ 1.0 1 7,396

Index Scan using ixt_chr_simple_val on chr_simple_val tblvc_9 (cost=0.92..93.38 rows=1 width=8) (actual time=0.109..0.109 rows=1 loops=7,396)

  • Index Cond: (((vpd_key)::text = ANY (string_to_array((f_sel_policy_ty_static())::text, ','::text))) AND ((vpd_key)::text = ANY ((ARRAY[sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying), 'NG_COMMON'::character varying])::text[])) AND ((oid)::text = (employeepo0_.emp_type_oid)::text))
  • Filter: ((("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[])) AND ((prod_locale_code)::text = ANY (string_to_array((f_sel_policy_prod_locale('CHR_SIMPLE_VAL'::character varying))::text, ','::text))))
30. 3.698 6.536 ↑ 5.0 1 86

Append (cost=0.92..70.64 rows=5 width=28) (actual time=0.075..0.076 rows=1 loops=86)

  • Subplans Removed: 4
31. 2.838 2.838 ↑ 1.0 1 86

Index Only Scan using chr_val_desc_table_2_hash_p3_pkey on chr_val_desc_table_2_hash_p3 chr_val_desc_table (cost=0.92..14.13 rows=1 width=27) (actual time=0.032..0.033 rows=1 loops=86)

  • Index Cond: ((vpd_key = (tblvc_10.vpd_key)::text) AND (vpd_key = ANY (ARRAY['NG_COMMON'::text, (sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying))::text])) AND (vpd_key = (sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying))::text) AND (oid = (tblvc_10.oid)::text))
  • Filter: ((user_locale_code)::text = (CASE COALESCE(sys_context('ctx_app_ng'::character varying, 'ctx_bypass_user_locale'::character varying), '~'::character varying) WHEN 'AUTO'::text THEN sys_context('ctx_ng_vpd'::character varying, 'ctx_user_locale'::character varying) WHEN 'N'::text THEN sys_context('ctx_ng_vpd'::character varying, 'ctx_user_locale'::character varying) ELSE user_locale_code END)::text)
  • Heap Fetches: 86
32. 0.040 0.084 ↑ 5.0 1 1

Append (cost=0.92..70.64 rows=5 width=28) (actual time=0.083..0.084 rows=1 loops=1)

  • Subplans Removed: 4
33. 0.044 0.044 ↑ 1.0 1 1

Index Only Scan using chr_val_desc_table_2_hash_p3_pkey on chr_val_desc_table_2_hash_p3 chr_val_desc_table_1 (cost=0.92..14.13 rows=1 width=27) (actual time=0.044..0.044 rows=1 loops=1)

  • Index Cond: ((vpd_key = (tblvc_11.vpd_key)::text) AND (vpd_key = ANY (ARRAY['NG_COMMON'::text, (sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying))::text])) AND (vpd_key = (sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying))::text) AND (oid = (tblvc_11.oid)::text))
  • Filter: ((user_locale_code)::text = (CASE COALESCE(sys_context('ctx_app_ng'::character varying, 'ctx_bypass_user_locale'::character varying), '~'::character varying) WHEN 'AUTO'::text THEN sys_context('ctx_ng_vpd'::character varying, 'ctx_user_locale'::character varying) WHEN 'N'::text THEN sys_context('ctx_ng_vpd'::character varying, 'ctx_user_locale'::character varying) ELSE user_locale_code END)::text)
  • Heap Fetches: 1
34. 0.029 0.029 ↑ 1.0 1 1

Index Only Scan using ix7_chr_associate on chr_associate employeeli23_ (cost=0.53..8.56 rows=1 width=11) (actual time=0.029..0.029 rows=1 loops=1)

  • Index Cond: ((vpd_key = (f_sel_policy_all('CHR_ASSOCIATE'::character varying))::text) AND (oid = 'CC1_95441'::text))
  • Filter: (("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[]))
  • Heap Fetches: 1
35. 0.026 0.026 ↑ 1.0 1 1

Index Only Scan using ix_payroll_agreement on payroll_agreement payrollagr24_ (cost=0.53..8.56 rows=1 width=10) (actual time=0.025..0.026 rows=1 loops=1)

  • Index Cond: ((vpd_key = (f_sel_policy_all('PAYROLL_AGREEMENT'::character varying))::text) AND (paid = (employeepo0_.paid)::text))
  • Filter: (("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[]))
  • Heap Fetches: 1
Planning time : 19.706 ms
Execution time : 12,355.355 ms