explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CrXr

Settings
# exclusive inclusive rows x rows loops node
1. 0.092 1,512.950 ↑ 1.0 1 1

Result (cost=4.44..254.54 rows=1 width=18) (actual time=1,107.556..1,512.950 rows=1 loops=1)

  • One-Time Filter: ((f_sel_policy_all('CHR_EMP_POSITION'::character varying))::text = (sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying))::text)
2. 0.003 1,512.858 ↑ 1.0 1 1

Nested Loop Left Join (cost=4.44..254.54 rows=1 width=18) (actual time=1,107.465..1,512.858 rows=1 loops=1)

3. 2.812 1,512.713 ↑ 1.0 1 1

Nested Loop (cost=3.02..183.35 rows=1 width=49) (actual time=1,107.321..1,512.713 rows=1 loops=1)

  • Join Filter: ((cep.rehire_reason_oid)::text = (tblvc_1.oid)::text)
  • Rows Removed by Join Filter: 85
4. 8.169 8.169 ↓ 86.0 86 1

Index Scan using pk_chr_reason_pl on chr_reason_pl tblvc_1 (cost=0.79..51.90 rows=1 width=31) (actual time=0.182..8.169 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))))
5. 0.172 1,501.732 ↑ 1.0 1 86

Nested Loop Left Join (cost=2.23..131.43 rows=1 width=53) (actual time=12.561..17.462 rows=1 loops=86)

6. 68.886 1,490.638 ↑ 1.0 1 86

Nested Loop (cost=1.32..60.75 rows=1 width=84) (actual time=12.433..17.333 rows=1 loops=86)

  • Join Filter: ((cep.loa_return_reason_oid)::text = (tblvc.oid)::text)
  • Rows Removed by Join Filter: 85
7. 682.152 682.152 ↓ 86.0 86 86

Index Scan using pk_chr_reason_pl on chr_reason_pl tblvc (cost=0.79..51.90 rows=1 width=31) (actual time=0.108..7.932 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))))
8. 739.600 739.600 ↑ 1.0 1 7,396

Index Scan using ix14_chr_emp_position on chr_emp_position cep (cost=0.53..8.83 rows=1 width=67) (actual time=0.100..0.100 rows=1 loops=7,396)

  • Index Cond: (((vpd_key)::text = (sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::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))))
9. 1.634 10.922 ↑ 5.0 1 86

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

  • Subplans Removed: 4
10. 6.880 9.288 ↑ 1.0 1 86

Result (cost=0.92..14.13 rows=1 width=27) (actual time=0.107..0.108 rows=1 loops=86)

  • One-Time Filter: ((f_sel_policy_all('CHR_EMP_POSITION'::character varying))::text = (sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying))::text)
11. 2.408 2.408 ↑ 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.027..0.028 rows=1 loops=86)

  • Index Cond: ((vpd_key = (tblvc.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.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
12. 0.023 0.142 ↑ 5.0 1 1

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

  • Subplans Removed: 4
13. 0.076 0.119 ↑ 1.0 1 1

Result (cost=0.92..14.13 rows=1 width=27) (actual time=0.118..0.119 rows=1 loops=1)

  • One-Time Filter: ((f_sel_policy_all('CHR_EMP_POSITION'::character varying))::text = (sys_context('ctx_ng_vpd'::character varying, 'ctx_vpd_key'::character varying))::text)
14. 0.043 0.043 ↑ 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.042..0.043 rows=1 loops=1)

  • Index Cond: ((vpd_key = (tblvc_1.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_1.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
Planning time : 2.963 ms
Execution time : 1,513.198 ms