Hi,
Good Morning!
Postgres Version : 11.6 (AWS Native Postgres/AWS Aurora tried on both
flavours).
When i'm joining two tables the primary index is not being used. While is use
in clause with values then the index is being used. I have reindexed all the
tables, run the auto vaccum as well.
pgwfc01q=> select count(*) from chr_simple_val;
count
-------
13158
(1 row)
pgwfc01q=> select count(*) from chr_emp_position;
count
-------
228
(1 row)
The primary key for the table chr_Simple_val contains OID. Still not using
the index.
I'm sharing the explain plan over here..
pgwfc01q=> explain analyze select
cep.HOME_DEPT_OID,ctc.oid,ctc.category,ctc.code from chr_emp_position cep inner
join chr_Simple_Val ctc on ctc.oid=cep.HOME_DEPT_OID;
QUERY P
LAN
--------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Hash Join (cost=49299.91..51848.83 rows=651 width=42) (actual
time=3512.692..3797.583 rows=228 loops=1)
Hash Cond: ((cep.home_dept_oid)::text = (ctc.oid)::text)
-> Seq Scan on chr_emp_position cep (cost=0.00..2437.77 rows=436 width=11)
(actual time=44.713..329.435 rows=22
8 loops=1)
Filter: ((("current_user"())::text <> ANY
('{wfnadmin,skipvpd}'::text[])) AND f_sel_policy_all(vpd_key, 'CH
R_EMP_POSITION'::character varying) AND
f_sel_policy_prod_locale((cep.*)::character varying, prod_locale_code))
Rows Removed by Filter: 3695
-> Hash (cost=49176.40..49176.40 rows=9881 width=31) (actual
time=3467.907..3467.908 rows=13158 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 1031kB
-> Seq Scan on chr_simple_val ctc (cost=0.00..49176.40 rows=9881
width=31) (actual time=2.191..3460.929 r
ows=13158 loops=1)
Filter: ((("current_user"())::text <> ANY
('{wfnadmin,skipvpd}'::text[])) AND f_sel_policy_ty_static(
vpd_key) AND f_sel_policy_prod_locale((ctc.*)::character varying,
prod_locale_code))
Rows Removed by Filter: 75771
Planning Time: 0.297 ms
Execution Time: 3797.768 ms
(12 rows)
Thank you..
Regards,
Ramesh G
