Обсуждение: Performance Issue (Not using Index when joining two tables).

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
isbeing 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_Valctc 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

Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:
> 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.

I think the OP is wishing that the filter functions for the larger table
would be postponed till after the join condition is applied.  I'm a
little dubious that that's going to save anything meaningful; but maybe
increasing the cost attributed to those functions would persuade the
planner to try it that way.

First though, does forcing a nestloop plan (turn off enable_hashjoin,
and enable_mergejoin too if needed) produce the shape of plan you
want?  And if so, is it actually faster?  Only if those things are
true is it going to be worth messing with costing parameters.

            regards, tom lane

"Gopisetty, Ramesh" <rameshg2@illinois.edu> writes:
> Thanks for looking into the problem/issue.    Let me give more details about the functions...    Yes,  we are using
rowlevel security. 

Hm.  If those expensive filter functions are being injected by RLS on the
target tables (rather than by something like an intermediate view), then
the planner is constrained to ensure that they execute before any query
conditions that it doesn't know to be "leakproof".  So unless your join
operator is leakproof, the shape of plan that you're hoping for will not
be allowed.  Since you haven't mentioned anything about data types, it's
hard to know whether that's the issue.  (The hash condition seems to be
texteq, which is leakproof, but there are also casts involved which
might not be.)

The two queries you provided explain plans for are not the same, so
comparing their plans is a fairly pointless activity.  *Of course*
the query runs faster when you restrict it to fetch fewer rows.  The
original query has no restriction clause that corresponds to the
clauses being used for index conditions in the second query, so it's
hardly a surprise that you do not get that plan, RLS or no RLS.

            regards, tom lane