Performance Issue (Not using Index when joining two tables).

["Gopisetty, Ramesh" <rameshg2@xxxxxxxxxxxx>]

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