explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TmSF : ddd

Settings
# exclusive inclusive rows x rows loops node
1. 19.078 3,542.736 ↓ 95.0 95 1

Nested Loop Left Join (cost=9.97..4,999.25 rows=1 width=6,338) (actual time=31.991..3,542.736 rows=95 loops=1)

  • Join Filter: (((tblvc_3.vpd_key)::text = (chr_val_desc_table.vpd_key)::text) AND ((tblvc_3.oid)::text = (chr_val_desc_table.oid)::text))
  • Rows Removed by Join Filter: 80,085
2. 3.655 60.623 ↓ 95.0 95 1

Nested Loop (cost=8.80..4,905.05 rows=1 width=6,371) (actual time=0.894..60.623 rows=95 loops=1)

3. 3.719 46.328 ↓ 19.0 95 1

Nested Loop (cost=7.88..4,709.48 rows=5 width=6,371) (actual time=0.749..46.328 rows=95 loops=1)

4. 4.803 32.159 ↓ 4.5 95 1

Nested Loop (cost=6.96..3,888.05 rows=21 width=6,371) (actual time=0.606..32.159 rows=95 loops=1)

5. 0.475 15.101 ↓ 1.0 95 1

Nested Loop (cost=6.04..328.55 rows=91 width=6,371) (actual time=0.440..15.101 rows=95 loops=1)

  • Join Filter: ((employeepo0_.union_local_code_oid)::text = (tblvc_3.oid)::text)
6. 0.238 0.238 ↑ 1.0 1 1

Index Scan using pk_chr_union_local_pl on chr_union_local_pl tblvc_3 (cost=0.77..27.59 rows=1 width=33) (actual time=0.237..0.238 rows=1 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_UNION_LOCAL_PL'::character varying))::text, ','::text))))
7. 14.368 14.388 ↓ 1.0 95 1

Bitmap Heap Scan on chr_emp_position employeepo0_ (cost=5.27..299.80 rows=93 width=6,338) (actual time=0.121..14.388 rows=95 loops=1)

  • Recheck Cond: ((vpd_key)::text = (f_sel_policy_all('CHR_EMP_POSITION'::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_EMP_POSITION'::character varying))::text, ','::text))))
  • Heap Blocks: exact=24
8. 0.020 0.020 ↑ 1.0 95 1

Bitmap Index Scan on ix24_chr_emp_pos_prod_locale (cost=0.00..5.25 rows=95 width=0) (actual time=0.020..0.020 rows=95 loops=1)

  • Index Cond: ((vpd_key)::text = (f_sel_policy_all('CHR_EMP_POSITION'::character varying))::text)
9. 12.255 12.255 ↑ 1.0 1 95

Index Scan using pk_chr_simple_val on chr_simple_val tblvc_2 (cost=0.92..39.11 rows=1 width=8) (actual time=0.128..0.129 rows=1 loops=95)

  • 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))))
10. 10.450 10.450 ↑ 1.0 1 95

Index Scan using pk_chr_simple_val on chr_simple_val tblvc (cost=0.92..39.11 rows=1 width=8) (actual time=0.109..0.110 rows=1 loops=95)

  • 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))))
11. 10.640 10.640 ↑ 1.0 1 95

Index Scan using pk_chr_simple_val on chr_simple_val tblvc_1 (cost=0.92..39.11 rows=1 width=8) (actual time=0.110..0.112 rows=1 loops=95)

  • 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))))
12. 3,463.035 3,463.035 ↓ 844.0 844 95

Index Only Scan using pk_chr_val_desc_table on chr_val_desc_table (cost=1.17..94.18 rows=1 width=28) (actual time=0.052..36.453 rows=844 loops=95)

  • Index Cond: ((vpd_key = ANY (string_to_array((f_sel_policy_ty_static())::text, ','::text))) AND (vpd_key = ANY ((ARRAY['NG_COMMON'::character varying, 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))
  • Filter: ((("current_user"())::text <> ANY ('{wfnadmin,skipvpd}'::text[])) AND ((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))
  • Rows Removed by Filter: 1,315
  • Heap Fetches: 0
Planning time : 2.917 ms
Execution time : 3,543.054 ms