Date: Sun, 13 Sep 2020 18:47:45 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: "Gopisetty, Ramesh" <rameshg2@illinois.edu>
Cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: Performance Issue (Not using Index when joining two tables).
Message-ID: <20200913164745.dfnoeiz54wgmgckn@development>
References: 
 <BL0PR11MB3153E3C88990EB0A7976688CEE220@BL0PR11MB3153.namprd11.prod.outlook.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline
In-Reply-To: 
 <BL0PR11MB3153E3C88990EB0A7976688CEE220@BL0PR11MB3153.namprd11.prod.outlook.com>
Precedence: bulk

On Sun, Sep 13, 2020 at 02:58:15PM +0000, Gopisetty, Ramesh wrote:
>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)
>

Most of the time (3460ms) is spent in the sequential scan on
chr_simple_val, and the seqscan on chr_emp_position is taking ~330ms).
Combined that's 3790ms out of 3797ms, so the join is pretty much
irrelevant.

Either the seqscans are causing a lot of I/O, or maybe the f_sel_*
functions in the filter are expensive. Judging by how few rows are in
the tables (not sure how large the tables are), I'd guess it's the
latter ... Hard to say without knowing what the functions do etc.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services