Re: Performance issue post upgrade on Version 13 - Incorrect Estimation Cost choosing Hash Aggregate-Nested Left Loop Join

[Tomas Vondra]

On 3/22/22 13:57, Prajna Shetty wrote:
> ++ _pgsql-performance@postgresql.org_
> 
>  
> Hello Team,
>  
> There is change in query plan in 12.4 version and Version 13 resulting
> in performance slowness post upgrade.
>  
> 
>   * In 12.4 version, Sort Operation Group Aggregate is selected which
> results to Merge Join. Query takes ~5 seconds.
>   * In 13.5 version, optimizer wrongly estimates and due to new Disk
> Based Hash Aggregate feature, it prefers Hash Aggregate instead of
> Sort Operation which finally blocks merge-join and chooses Nested
> Loop Left Join. Query takes ~5 minutes.
> 
>  
> *_NOTE: _*Disabling Hash Aggregate on instance level forces optimizer to
> choose merge operation but such instance level modification is not
> possible in terms of Application Functionality.
>  
> This performance issue is on all over most of queries. Attached one of
> the query and its plan in both version for reference in case that helps
> for recreating the issue.
>  

It's impossible to comment those other queries, but chances are the root
cause is the same.

> Version 13 query plan has lower estimated cost than that of 12.4 which
> implies 13.5 planner thought it found a better plan, but it is running
> slower and actual cost show more.
>  
> 12.4 Version:
> "Merge Right Join  (cost=*202198.78..295729.10* rows=1 width=8) (actual
> time=1399.727..*5224.574* rows=296 loops=1)"
>  
> 13.5 version:-
> "Nested Loop Left Join  (cost=*196360.90..287890.45* rows=1 width=8)
> (actual time=3209.577..*371300.693* rows=296 loops=1)"
>  

This is not a costing issue, the problem is that we expect 1 row and
calculate the cost for that, but then get 296. And unfortunately a
nested loop degrades much faster than a merge join.

I'm not sure why exactly 12.4 picked a merge join, chances are the
costing formular changed a bit somewhere. But as I said, the problem is
in bogus row cardinality estimates - 12.4 is simply lucky.

The problem most likely stems from this part:

  ->  GroupAggregate  (cost=0.43..85743.24 rows=1830 width=72) (actual
time=1.621..3452.034 rows=282179 loops=3)
  Group Key: student_class_detail.aamc_id
  Filter: (max((student_class_detail.class_level_cd)::text) = '4'::text)
  Rows Removed by Filter: 76060
  ->  Index Scan using uk_student_class_detail_aamcid_classlevelcd on
student_class_detail  (cost=0.43..74747.61 rows=1284079 width=6) (actual
time=1.570..2723.014 rows=1272390 loops=3)
Filter: (class_level_start_dt IS NOT NULL)
Rows Removed by Filter: 160402

The filter is bound to be misestimated, and the error then snowballs.
Try replacing this part with a temporary table (with pre-aggregated
results) - you can run analyze on it, etc. I'd bet that'll make the
issue go away.

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Performance issue post upgrade on Version 13 - Incorrect Estimation Cost choosing Hash Aggregate-Nested Left Loop Join

[Justin Pryzby]

On Tue, Mar 22, 2022 at 12:57:10PM +, Prajna Shetty wrote:
> 1.  We have performed Vacuum/Analyze/Reindex post Upgrade.
> 2.  Tweaked work_mem so it does not spill to Disk. We can Disk Usage But 
> it is still using Hash Aggregate and came down from 5 minutes to 20 seconds. 
> (Expected ~5 seconds). Attached plan after modifying work_mem
> 3.  Disabled Seqcan/ nestedloop
> 4.  Tweaked random_page_cost/seq_page_cost
> 5.  Set default_statistics_target=1000 and then run 
> vacuum(analyze,verbose) on selected tables.
> 6.  We have also tested performance by increasing resources up to 4 vCPU 
> and 32 GB RAM.

Would you provide your current settings ?
https://wiki.postgresql.org/wiki/Server_Configuration

Re: Performance issue with thousands of calls to procedures and functions?

[Daniel Westermann (DWE)]

>In your example, the bottleneck is calling the function f1. So you need to 
>check only this function. It is not important if other functions or 
>>procedures do database lookups.

>Or if it does just one database lookup, then you can use SQL language. I 
>repeat, PL/pgSQL is not good for ultra very frequent calls (where >there is 
>minimal other overhead).

>Generally, start of function or start of query are more expensive on Postgres 
>than on Oracle. Postgres is much more dynamic, and it needs >to do some 
>rechecks. The overhead is in nanoseconds, but nanoseconds x billions are lot 
>of seconds

Thank you Pavel, for all the information. That was very helpful.

Regards
Daniel

Re: Performance issue with thousands of calls to procedures and functions?

[Pavel Stehule]

pá 30. 7. 2021 v 10:12 odesílatel Daniel Westermann (DWE) <
daniel.westerm...@dbi-services.com> napsal:

>
> pá 30. 7. 2021 v 9:12 odesílatel Daniel Westermann (DWE) <
> daniel.westerm...@dbi-services.com> napsal:
>
> Hi,
>
> we have a customer which was migrated from Oracle to PostgreSQL 12.5 (I
> know, the latest version is 12.7). The migration included a lot of PL/SQL
> code. Attached a very simplified test case. As you can see there are
> thousands, even nested calls to procedures and functions. The test case
> does not even touch any relation, in reality these functions and procedures
> perform selects, insert and updates.
>
> I've tested this on my local sandbox (Debian 11) and here are the results
> (three runs each):
>
> Head:
> Time: 97275.109 ms (01:37.275)
> Time: 103241.352 ms (01:43.241)
> Time: 104246.961 ms (01:44.247)
>
> 13.3:
> Time: 122179.311 ms (02:02.179)
> Time: 122622.859 ms (02:02.623)
> Time: 125469.711 ms (02:05.470)
>
> 12.7:
> Time: 182131.565 ms (03:02.132)
> Time: 177393.980 ms (02:57.394)
> Time: 177550.204 ms (02:57.550)
>
>
> It seems there are some optimizations in head, but 13.3 and 12.7 are
> noticeable slower.
>
>
> Question: Is it expected that this takes minutes sitting on the CPU or is
> there a performance issue? Doing the same in Oracle takes around 30
> seconds. I am not saying that this implementation is brilliant, but for the
> moment it is like it is.
>
>
> >Unfortunately yes, it is possible. PL/pgSQL is interpreted language
> without **any** compiler optimization. PL/SQL is now a fully compiled
> >language with a lot of compiler optimization. There is main overhead with
> repeated function's initialization and variable's initialization. Your
> >example is the worst case for PL/pgSQL - and I am surprised so the
> difference is only 3-4x.
>
> >Maybe (probably) Oracle does inlining of f1 function. You can get the
> same effect if you use SQL language for this function. PL/pgSQL is >bad
> language for one line functions. When I did it, then then I got 34 sec (on
> my comp against 272 sec)
>
> >and mark this function as immutable helps a lot of too - it takes 34 sec
> on my computer.
>
> Thank you, Pavel. As far as I understand the docs, I cannot use immutable
> as the "real" functions and procedures do database lookups.
>

In your example, the bottleneck is calling the function f1. So you need to
check only this function. It is not important if other functions or
procedures do database lookups.

Or if it does just one database lookup, then you can use SQL language. I
repeat, PL/pgSQL is not good for ultra very frequent calls (where there is
minimal other overhead).

Generally, start of function or start of query are more expensive on
Postgres than on Oracle. Postgres is much more dynamic, and it needs to do
some rechecks. The overhead is in nanoseconds, but nanoseconds x billions
are lot of seconds


> Regards
> Daniel
>
>
>
> Thanks for any inputs
> Regards
> Daniel
>
>

Re: Performance issue with thousands of calls to procedures and functions?

[Daniel Westermann (DWE)]

pá 30. 7. 2021 v 9:12 odesílatel Daniel Westermann (DWE) 
mailto:daniel.westerm...@dbi-services.com>> 
napsal:
Hi,

we have a customer which was migrated from Oracle to PostgreSQL 12.5 (I know, 
the latest version is 12.7). The migration included a lot of PL/SQL code. 
Attached a very simplified test case. As you can see there are thousands, even 
nested calls to procedures and functions. The test case does not even touch any 
relation, in reality these functions and procedures perform selects, insert and 
updates.

I've tested this on my local sandbox (Debian 11) and here are the results 
(three runs each):

Head:
Time: 97275.109 ms (01:37.275)
Time: 103241.352 ms (01:43.241)
Time: 104246.961 ms (01:44.247)

13.3:
Time: 122179.311 ms (02:02.179)
Time: 122622.859 ms (02:02.623)
Time: 125469.711 ms (02:05.470)

12.7:
Time: 182131.565 ms (03:02.132)
Time: 177393.980 ms (02:57.394)
Time: 177550.204 ms (02:57.550)


It seems there are some optimizations in head, but 13.3 and 12.7 are noticeable 
slower.

Question: Is it expected that this takes minutes sitting on the CPU or is there 
a performance issue? Doing the same in Oracle takes around 30 seconds. I am not 
saying that this implementation is brilliant, but for the moment it is like it 
is.

>Unfortunately yes, it is possible. PL/pgSQL is interpreted language without 
>**any** compiler optimization. PL/SQL is now a fully compiled >language with a 
>lot of compiler optimization. There is main overhead with repeated function's 
>initialization and variable's initialization. Your >example is the worst case 
>for PL/pgSQL - and I am surprised so the difference is only 3-4x.

>Maybe (probably) Oracle does inlining of f1 function. You can get the same 
>effect if you use SQL language for this function. PL/pgSQL is >bad language 
>for one line functions. When I did it, then then I got 34 sec (on my comp 
>against 272 sec)

>and mark this function as immutable helps a lot of too - it takes 34 sec on my 
>computer.

Thank you, Pavel. As far as I understand the docs, I cannot use immutable as 
the "real" functions and procedures do database lookups.

Regards
Daniel



Thanks for any inputs
Regards
Daniel

Re: Performance issue with thousands of calls to procedures and functions?

[Pavel Stehule]

pá 30. 7. 2021 v 10:04 odesílatel Pavel Stehule 
napsal:

> Hi
>
> pá 30. 7. 2021 v 10:02 odesílatel Imre Samu  napsal:
>
>> Hi Daniel,
>>
>> side note:
>>
>> Maybe you can tune the "function" with some special query optimizer
>> attributes:
>>  IMMUTABLE | STABLE | VOLATILE |  PARALLEL SAFE
>>
>> so in your example:
>>  create or replace function f1(int) returns double precision as
>>
>> $$
>> declare
>> begin
>>   return 1;
>> end;
>> $$ language plpgsql *IMMUTABLE PARALLEL SAFE*;
>>
>>
> It cannot help in this case. PL/pgSQL routine (and expression
> calculations) is one CPU every time.
>

IMMUTABLE helps, surely, because it is translated to constant in this case.

Regards

Pavel


> Regards
>
> Pavel
>
>
>>
>> """  : https://www.postgresql.org/docs/13/sql-createfunction.html
>> PARALLEL SAFE :
>> * indicates that the function is safe to run in parallel mode without
>> restriction.*
>> IMMUTABLE *: indicates that the function cannot modify the database and
>> always returns the same result when given the same argument values; that
>> is, it does not do database lookups or otherwise use information not
>> directly present in its argument list. If this option is given, any call of
>> the function with all-constant arguments can be immediately replaced with
>> the function value.*
>> """
>>
>> Regards,
>>   Imre
>>
>> Daniel Westermann (DWE)  ezt írta
>> (időpont: 2021. júl. 30., P, 9:12):
>>
>>> Hi,
>>>
>>> we have a customer which was migrated from Oracle to PostgreSQL 12.5 (I
>>> know, the latest version is 12.7). The migration included a lot of PL/SQL
>>> code. Attached a very simplified test case. As you can see there are
>>> thousands, even nested calls to procedures and functions. The test case
>>> does not even touch any relation, in reality these functions and procedures
>>> perform selects, insert and updates.
>>>
>>> I've tested this on my local sandbox (Debian 11) and here are the
>>> results (three runs each):
>>>
>>> Head:
>>> Time: 97275.109 ms (01:37.275)
>>> Time: 103241.352 ms (01:43.241)
>>> Time: 104246.961 ms (01:44.247)
>>>
>>> 13.3:
>>> Time: 122179.311 ms (02:02.179)
>>> Time: 122622.859 ms (02:02.623)
>>> Time: 125469.711 ms (02:05.470)
>>>
>>> 12.7:
>>> Time: 182131.565 ms (03:02.132)
>>> Time: 177393.980 ms (02:57.394)
>>> Time: 177550.204 ms (02:57.550)
>>>
>>>
>>> It seems there are some optimizations in head, but 13.3 and 12.7 are
>>> noticeable slower.
>>>
>>> Question: Is it expected that this takes minutes sitting on the CPU or
>>> is there a performance issue? Doing the same in Oracle takes around 30
>>> seconds. I am not saying that this implementation is brilliant, but for the
>>> moment it is like it is.
>>>
>>> Thanks for any inputs
>>> Regards
>>> Daniel
>>>
>>>

Re: Performance issue with thousands of calls to procedures and functions?

[Pavel Stehule]

Hi

pá 30. 7. 2021 v 9:12 odesílatel Daniel Westermann (DWE) <
daniel.westerm...@dbi-services.com> napsal:

> Hi,
>
> we have a customer which was migrated from Oracle to PostgreSQL 12.5 (I
> know, the latest version is 12.7). The migration included a lot of PL/SQL
> code. Attached a very simplified test case. As you can see there are
> thousands, even nested calls to procedures and functions. The test case
> does not even touch any relation, in reality these functions and procedures
> perform selects, insert and updates.
>
> I've tested this on my local sandbox (Debian 11) and here are the results
> (three runs each):
>
> Head:
> Time: 97275.109 ms (01:37.275)
> Time: 103241.352 ms (01:43.241)
> Time: 104246.961 ms (01:44.247)
>
> 13.3:
> Time: 122179.311 ms (02:02.179)
> Time: 122622.859 ms (02:02.623)
> Time: 125469.711 ms (02:05.470)
>
> 12.7:
> Time: 182131.565 ms (03:02.132)
> Time: 177393.980 ms (02:57.394)
> Time: 177550.204 ms (02:57.550)
>
>
> It seems there are some optimizations in head, but 13.3 and 12.7 are
> noticeable slower.
>

> Question: Is it expected that this takes minutes sitting on the CPU or is
> there a performance issue? Doing the same in Oracle takes around 30
> seconds. I am not saying that this implementation is brilliant, but for the
> moment it is like it is.
>

Unfortunately yes, it is possible. PL/pgSQL is interpreted language without
**any** compiler optimization. PL/SQL is now a fully compiled language with
a lot of compiler optimization. There is main overhead with repeated
function's initialization and variable's initialization. Your example is
the worst case for PL/pgSQL - and I am surprised so the difference is only
3-4x.

Maybe (probably) Oracle does inlining of f1 function. You can get the same
effect if you use SQL language for this function. PL/pgSQL is bad language
for one line functions. When I did it, then then I got 34 sec (on my comp
against 272 sec)

and mark this function as immutable helps a lot of too - it takes 34 sec on
my computer.

Regards

Pavel






> Thanks for any inputs
> Regards
> Daniel
>
>

Re: Performance issue with thousands of calls to procedures and functions?

[Pavel Stehule]

Hi

pá 30. 7. 2021 v 10:02 odesílatel Imre Samu  napsal:

> Hi Daniel,
>
> side note:
>
> Maybe you can tune the "function" with some special query optimizer
> attributes:
>  IMMUTABLE | STABLE | VOLATILE |  PARALLEL SAFE
>
> so in your example:
>  create or replace function f1(int) returns double precision as
>
> $$
> declare
> begin
>   return 1;
> end;
> $$ language plpgsql *IMMUTABLE PARALLEL SAFE*;
>
>
It cannot help in this case. PL/pgSQL routine (and expression calculations)
is one CPU every time.

Regards

Pavel


>
> """  : https://www.postgresql.org/docs/13/sql-createfunction.html
> PARALLEL SAFE :
> * indicates that the function is safe to run in parallel mode without
> restriction.*
> IMMUTABLE *: indicates that the function cannot modify the database and
> always returns the same result when given the same argument values; that
> is, it does not do database lookups or otherwise use information not
> directly present in its argument list. If this option is given, any call of
> the function with all-constant arguments can be immediately replaced with
> the function value.*
> """
>
> Regards,
>   Imre
>
> Daniel Westermann (DWE)  ezt írta
> (időpont: 2021. júl. 30., P, 9:12):
>
>> Hi,
>>
>> we have a customer which was migrated from Oracle to PostgreSQL 12.5 (I
>> know, the latest version is 12.7). The migration included a lot of PL/SQL
>> code. Attached a very simplified test case. As you can see there are
>> thousands, even nested calls to procedures and functions. The test case
>> does not even touch any relation, in reality these functions and procedures
>> perform selects, insert and updates.
>>
>> I've tested this on my local sandbox (Debian 11) and here are the results
>> (three runs each):
>>
>> Head:
>> Time: 97275.109 ms (01:37.275)
>> Time: 103241.352 ms (01:43.241)
>> Time: 104246.961 ms (01:44.247)
>>
>> 13.3:
>> Time: 122179.311 ms (02:02.179)
>> Time: 122622.859 ms (02:02.623)
>> Time: 125469.711 ms (02:05.470)
>>
>> 12.7:
>> Time: 182131.565 ms (03:02.132)
>> Time: 177393.980 ms (02:57.394)
>> Time: 177550.204 ms (02:57.550)
>>
>>
>> It seems there are some optimizations in head, but 13.3 and 12.7 are
>> noticeable slower.
>>
>> Question: Is it expected that this takes minutes sitting on the CPU or is
>> there a performance issue? Doing the same in Oracle takes around 30
>> seconds. I am not saying that this implementation is brilliant, but for the
>> moment it is like it is.
>>
>> Thanks for any inputs
>> Regards
>> Daniel
>>
>>

Re: Performance issue with thousands of calls to procedures and functions?

[Imre Samu]

Hi Daniel,

side note:

Maybe you can tune the "function" with some special query optimizer
attributes:
 IMMUTABLE | STABLE | VOLATILE |  PARALLEL SAFE

so in your example:
 create or replace function f1(int) returns double precision as

$$
declare
begin
  return 1;
end;
$$ language plpgsql *IMMUTABLE PARALLEL SAFE*;


"""  : https://www.postgresql.org/docs/13/sql-createfunction.html
PARALLEL SAFE :
* indicates that the function is safe to run in parallel mode without
restriction.*
IMMUTABLE *: indicates that the function cannot modify the database and
always returns the same result when given the same argument values; that
is, it does not do database lookups or otherwise use information not
directly present in its argument list. If this option is given, any call of
the function with all-constant arguments can be immediately replaced with
the function value.*
"""

Regards,
  Imre

Daniel Westermann (DWE)  ezt írta
(időpont: 2021. júl. 30., P, 9:12):

> Hi,
>
> we have a customer which was migrated from Oracle to PostgreSQL 12.5 (I
> know, the latest version is 12.7). The migration included a lot of PL/SQL
> code. Attached a very simplified test case. As you can see there are
> thousands, even nested calls to procedures and functions. The test case
> does not even touch any relation, in reality these functions and procedures
> perform selects, insert and updates.
>
> I've tested this on my local sandbox (Debian 11) and here are the results
> (three runs each):
>
> Head:
> Time: 97275.109 ms (01:37.275)
> Time: 103241.352 ms (01:43.241)
> Time: 104246.961 ms (01:44.247)
>
> 13.3:
> Time: 122179.311 ms (02:02.179)
> Time: 122622.859 ms (02:02.623)
> Time: 125469.711 ms (02:05.470)
>
> 12.7:
> Time: 182131.565 ms (03:02.132)
> Time: 177393.980 ms (02:57.394)
> Time: 177550.204 ms (02:57.550)
>
>
> It seems there are some optimizations in head, but 13.3 and 12.7 are
> noticeable slower.
>
> Question: Is it expected that this takes minutes sitting on the CPU or is
> there a performance issue? Doing the same in Oracle takes around 30
> seconds. I am not saying that this implementation is brilliant, but for the
> moment it is like it is.
>
> Thanks for any inputs
> Regards
> Daniel
>
>

Re: Performance Issue on a table

[Justin Pryzby]

On Thu, Jul 22, 2021 at 01:54:25PM +, Manoj Kumar wrote:
> Recently we have noticed that in one of our DB instances there is a potential 
> delay in querying a table from java code. could you please check the attached 
> log and help understand what is the problem and which direction should be 
> look into solving this delay of 4 odd mins ?

I'm not familiar with the log, but it looks like the delay is in query parsing
(ParseComplete).  Which seems weird.  You might try running wireshark to verify
that.  Or check postgres logs, and make sure the query isn't being blocked by
DDL commands.  Make sure these are enabled:

log_lock_waits = 'on'
deadlock_timeout = '1s'

> 4:25:00 PM ... execute FINEST:   simple execute, 
> handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@13e344d, 
> maxRows=0, fetchSize=0, flags=1
> 4:25:00 PM ... sendSimpleQuery FINEST:  FE=> SimpleQuery(query="SAVEPOINT 
> PGJDBC_AUTOSAVE")
> 4:25:00 PM ... sendParse FINEST:  FE=> Parse(stmt=null,query="SELECT RECID 
> FROM TAFJ_HASHLOCKS WHERE RECID = $1 FOR UPDATE NOWAIT ",oids={1043})
> 4:25:00 PM ... sendBind FINEST:  FE=> 
> Bind(stmt=null,portal=null,$1=<'256292129'>,type=VARCHAR)
> 4:25:00 PM ... sendDescribePortal FINEST:  FE=> Describe(portal=null)
> 4:25:00 PM ... sendExecute FINEST:  FE=> Execute(portal=null,limit=0)
> 4:25:00 PM ... sendSync FINEST:  FE=> Sync
> 4:25:00 PM ... receiveCommandStatus FINEST:  <=BE CommandStatus(RELEASE)
> 4:25:00 PM ... receiveRFQ FINEST:  <=BE ReadyForQuery(T)
> 4:25:00 PM ... receiveCommandStatus FINEST:  <=BE CommandStatus(SAVEPOINT)
> 4:25:00 PM ... receiveRFQ FINEST:  <=BE ReadyForQuery(T)
> 4:29:20 PM ... processResults FINEST:  <=BE ParseComplete [null]
> 4:29:20 PM ... processResults FINEST:  <=BE BindComplete [unnamed]
> 4:29:20 PM ... receiveFields FINEST:  <=BE RowDescription(1)

Re: Performance issue when we use policies for Row Level Security along with functions

[Gopisetty, Ramesh]

Hi,

Thanks for providing the details.  But things didn't work out even after 
changing the functions to STABLE/IMMUTABLE.   If i don't use the function it 
works for RLS.  If i use functions it doesn't work.

I tried with both IMMUTABLE and STABLE.  Both didn't work.Is there a way to 
use function in RLS to have the index scan rather than the seq scan.   Please 
help me out if that works or not.

Currently, we are in the processes of converting oracle to postgres.  Under 
oracle we have used functions and there exists a lot of logic in it.

Thank you.

Function

drop function f_sel_1;
CREATE OR REPLACE FUNCTION f_sel_1(key character varying)
 RETURNS character varying
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
Declare
v_status boolean;
key_ctx varchar(4000);
BEGIN

   SELECT INTO key_ctx current_setting('key_header' || '.'||'ctx_key_fil');

if key = key_ctx then
return key_ctx;
end if;
return '';
exception when undefined_object then
return '';

END;
$function$
;



drop policy policy_sel on test1;
create policy policy_sel on test1 FOR
SELECT
to sch USING  ( key =
f_sel_1(key)
);

explain analyze select * from test1;
  QUERY PLAN
---
 Seq Scan on test1  (cost=0.00..1555.61 rows=25 width=555) (actual 
time=35.124..35.124 rows=0 loops=1)
   Filter: ((key)::text = (f_sel_1(key))::text)
   Rows Removed by Filter: 4909
 Planning Time: 0.070 ms
 Execution Time: 35.142 ms
(5 rows)



drop policy policy_sel on test1;
create policy policy_sel on test1 FOR
SELECT
to sch USING  (
 key =
(
current_setting('key_header'|| '.' || 'ctx_key_fil')
)
  );


explain analyze select * from test1;
  QUERY PLAN
---
 Bitmap Heap Scan on test1  (cost=9.78..270.01 rows=193 width=555) (actual 
time=0.040..0.069 rows=193 loops=1)
   Recheck Cond: ((key)::text = current_setting('key_header.ctx_key_fil'::text))
   Heap Blocks: exact=13
   ->  Bitmap Index Scan on test1_pkey  (cost=0.00..9.73 rows=193 width=0) 
(actual time=0.030..0.030 rows=193 loops=1)
 Index Cond: ((key)::text = 
current_setting('key_header.ctx_key_fil'::text))
 Planning Time: 0.118 ms
 Execution Time: 0.094 ms
(7 rows)


CREATE TABLE sch.test1 (
key varchar(50) NOT NULL,
id varchar(32) NOT NULL,
begin_date date NOT NULL,
eff_date_end date NULL,
code varchar(100) NULL,
CONSTRAINT test1_pkey PRIMARY KEY (vpd_key, id, begin_date)
);


Thank you.

Regards,
Ramesh G

From: Tom Lane 
Sent: Wednesday, September 16, 2020 10:17 AM
To: Gopisetty, Ramesh 
Cc: pgsql-performance@lists.postgresql.org 

Subject: Re: Performance issue when we use policies for Row Level Security 
along with functions

"Gopisetty, Ramesh"  writes:
> Policy
> create policy  policy_sel on test FOR SELECT to ram1 USING  (  testkey in 
> (f_sel_policy_test(testkey))  );
> Going to a Sequential scan instead of index scan.  Hence, performance issue.

> If i replace the policy with stright forward without function then it chooses 
> the index.   Not sure how i can implement with the function.
> create policy  policy_sel on test FOR SELECT to ram1 USING  ( testkey in 
> ('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil')));

" testkey in ('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil')) "
is an indexable condition on testkey, because it compares testkey to
a constant (or at least, a value that's fixed for the life of the query).

" testkey in (f_sel_policy_test(testkey)) "
is not an indexable condition on anything, because there are variables
on both sides of the condition.  So there's no fixed value that the
index can search on.

If you intend f_sel_policy_test() to be equivalent to the other condition,
why are you passing it an argument it doesn't need?

As Luis noted, there's also the problem that an indexable condition
can't be volatile.  I gather that SYS_CONTEXT ends up being a probe
of some GUC setting, which means that marking the function IMMUTABLE
would be a lie, but you ought to be able to mark it STABLE.

regards, tom lane

Re: Performance issue when we use policies for Row Level Security along with functions

[Tom Lane]

"Gopisetty, Ramesh"  writes:
> Policy
> create policy  policy_sel on test FOR SELECT to ram1 USING  (  testkey in 
> (f_sel_policy_test(testkey))  );
> Going to a Sequential scan instead of index scan.  Hence, performance issue.

> If i replace the policy with stright forward without function then it chooses 
> the index.   Not sure how i can implement with the function.
> create policy  policy_sel on test FOR SELECT to ram1 USING  ( testkey in 
> ('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil')));

" testkey in ('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil')) "
is an indexable condition on testkey, because it compares testkey to
a constant (or at least, a value that's fixed for the life of the query).

" testkey in (f_sel_policy_test(testkey)) "
is not an indexable condition on anything, because there are variables
on both sides of the condition.  So there's no fixed value that the
index can search on.

If you intend f_sel_policy_test() to be equivalent to the other condition,
why are you passing it an argument it doesn't need?

As Luis noted, there's also the problem that an indexable condition
can't be volatile.  I gather that SYS_CONTEXT ends up being a probe
of some GUC setting, which means that marking the function IMMUTABLE
would be a lie, but you ought to be able to mark it STABLE.

regards, tom lane

Re: Performance issue when we use policies for Row Level Security along with functions

[luis . roberto]

De: "Gopisetty, Ramesh"  
Para: "pgsql-performance"  
Enviadas: Quarta-feira, 16 de setembro de 2020 0:39:08 
Assunto: Performance issue when we use policies for Row Level Security along 
with functions 





BQ_BEGIN

Hi, 

I'm seeing a strange behavior when we implement policies (for RLS - Row level 
security) using functions. 

table test consists of columns testkey,oid,category,type,description... 

Policy 

create policy policy_sel on test FOR SELECT to ram1 USING ( testkey in 
(f_sel_policy_test(testkey)) ); 

Going to a Sequential scan instead of index scan. Hence, performance issue. 

pgwfc01q=> explain analyze select * from test; 
QUERY PLAN 

 
Seq Scan on test (cost=0.00..25713.12 rows=445 width=712) (actual 
time=1849.592..1849.592 rows=0 loops=1) 
Filter: (( testkey )::text = (f_sel_policy_test( testkey ))::text) 
Rows Removed by Filter: 88930 
Planning Time: 0.414 ms 
Execution Time: 1849.614 ms 
(5 rows) 


The function is 

CREATE OR REPLACE FUNCTION vpd_sec_usr.f_sel_policy_test(testkey character 
varying) 
RETURNS character varying 
LANGUAGE plpgsql 
AS $function$ 
Declare 
v_status character varying; 
BEGIN 

if vpd_key = 'COMMON' then 
return ''' COMMON '''; 
elsif vpd_key = (' COMMON_ ' || SYS_CONTEXT('ctx_ng', 'ctx_prod_locale')) then 
return ''' COMMON_ ' || SYS_CONTEXT('ctx_ng', 'ctx_prod_locale')||; 
elsif vpd_key = SYS_CONTEXT('ctx_ng_vpd', 'ctx_key_fil') then 
return '''co'','''||SYS_CONTEXT('ctx_ng', 'ctx_testkey_fil')||; 
end if; 
return 'false'; 
exception when undefined_object then 
return 'failed'; 
END; 
$function$ 
; 


If i replace the policy with stright forward without function then it chooses 
the index. Not sure how i can implement with the function. 

create policy policy_sel on test FOR SELECT to ram1 USING ( testkey in 
('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil'))); 

QUERY PLAN 


 
- 
Bitmap Heap Scan on test (cost=396.66..2966.60 rows=13396 width=712) (actual 
time=0.693..2.318 rows=13159 loops=1) 
Recheck Cond: (( testkey )::text = ANY ((ARRAY['COMMON'::character varying, 
(current_setting('ctx_vpd.ctx_key_fil'::text))::character varying])::text[])) 
Heap Blocks: exact=373 
-> Bitmap Index Scan on test_pkey (cost=0.00..393.31 rows=13396 width=0) 
(actual time=0.653..0.653 rows=13159 l 
oops=1) 
Index Cond: (( testkey )::text = ANY ((ARRAY['COMMON'::character varying, 
(current_setting('ctx_vpd.ctx 
_key_fil'::text))::character varying])::text[])) 
Planning Time: 0.136 ms 
Execution Time: 2.843 ms 
(7 rows) 


If i replace the policy with stright forward without function then it chooses 
the index. Not sure how i can implement with the function. I thought of 
creating the policy with a lot of business logic in the function. If i have the 
function then i notice going for full table scan instead of index. 

Please help me if i miss anything in writing a function or how to use functions 
in the policy. 

Thank you. 


Regards, 
Ramesh G 


BQ_END


You could try seeting the function as immutable. By default it is volatile. 

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

[Gopisetty, Ramesh]

mp_position as i 
observed here.

fyi.,

Running as a root user.

pgwfc01q=> explain analyze select 
cep.HOME_DEPT_OID,ctc.oid,ctc.category,ctc.code from .chr_emp_position cep 
inner join wfnsch001.chr_Simple_Val ctc on ctc.oid=cep.HOME_DEPT_OID and 
(ctc.vpd_key='COMMON' or ctc.vpd_key=cep.vpd_key) and cep.vpd_key='xx';

QUERY PLAN


---
 Hash Join  (cost=5503.95..6742.82 rows=453 width=42) (actual 
time=131.241..154.201 rows=228 loops=1)
   Hash Cond: ((cep.home_dept_oid)::text = (ctc.oid)::text)
   Join Filter: (((ctc.vpd_key)::text = 'NG_COMMON'::text) OR 
((ctc.vpd_key)::text = (cep.vpd_key)::text))
   Rows Removed by Join Filter: 19770
   ->  Bitmap Heap Scan on chr_emp_position cep  (cost=10.05..362.25 rows=228 
width=28) (actual time=0.056..0.253 ro
ws=228 loops=1)
 Recheck Cond: ((vpd_key)::text = 'xx'::text)
 Heap Blocks: exact=26
 ->  Bitmap Index Scan on uq1_chr_emp_position  (cost=0.00..9.99 
rows=228 width=0) (actual time=0.041..0.041
 rows=228 loops=1)
   Index Cond: ((vpd_key)::text = 'xx'::text)
   ->  Hash  (cost=3600.29..3600.29 rows=88929 width=48) (actual 
time=130.826..130.826 rows=88929 loops=1)
 Buckets: 65536 (originally 65536)  Batches: 4 (originally 2)  Memory 
Usage: 3585kB
 ->  Seq Scan on chr_simple_val ctc  (cost=0.00..3600.29 rows=88929 
width=48) (actual time=0.005..33.356 row
s=88929 loops=1)
 Planning Time: 3.977 ms
 Execution Time: 154.535 ms
(14 rows)

pgwfc01q=> select count(*) from wfnsch001.chr_emp_position;
 count
---
  3923
(1 row)

pgwfc01q=> select count(*) from wfnsch001.chr_Simple_Val;
 count
---
 88929
(1 row)



I'm not sure if i'm thinking in the right way or not. (As of safety purpose, i 
have  rebuilded indexes, analyzed, did vaccum on those tables).   Sorry for the 
lengthy email and i'm trying to explain my best on this.

Thank you.

Regards,
Ramesh G



From: Michael Lewis 
Sent: Sunday, September 13, 2020 10:51 PM
To: Tom Lane 
Cc: Tomas Vondra ; Gopisetty, Ramesh 
; pgsql-performance@lists.postgresql.org 

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

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))

This looks like some stuff for row level security perhaps. My understanding is 
limited, but perhaps those restrictions are influencing the planners access or 
reliance on stats.

Also, it would seem like you need the entire table since you don't have an 
explicit where clause. Why would scanning an index and then also visiting every 
row in the table be faster than just going directly to the table?

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

[Michael Lewis]

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))

This looks like some stuff for row level security perhaps. My understanding
is limited, but perhaps those restrictions are influencing the planners
access or reliance on stats.

Also, it would seem like you need the entire table since you don't have an
explicit where clause. Why would scanning an index and then also visiting
every row in the table be faster than just going directly to the table?

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

[Tom Lane]

Tomas Vondra  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

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

[Tomas Vondra]

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

Re: Performance issue

[Justin Pryzby]

On Sun, Jun 14, 2020 at 10:45:52PM +, Nagaraj Raj wrote:
> My PostgreSQL server 10.11 running on windows which are running very slow. DB 
> has two tables with ~200Mil records in each. user queries are very slow even 
> explain analyze also taking a longer.
> 
> Could you please help me to tune this query and any suggestions to improve 
> system performance?

> CREATE TABLE test1
> (
> individual_entity_proxy_id bigint NOT NULL,
...
> CONSTRAINT pk_i_entity_proxy_id PRIMARY KEY (individual_entity_proxy_id)
> 
> );
> CREATE INDEX indx_prospect_indv_entty_id ON test1 USING btree 
> (individual_entity_proxy_id )

This index is redundant with the primary key, which implicitly creates a unique
index.

The table structure seems strange: you have two tables with the same PK column,
which is how they're being joined.  It seems like that's better expressed as a
single table with all the columns rather than separate tables (but see below).

> explain analyze select COUNT(*) as "DII_1"
>   from ( select distinct table0."individual_entity_proxy_id" as 
> "INDIVIDUAL_ENTITY_PROXY_ID"
> from test1 table0
> inner join test2 table1

I think this may be better written as something like:

| SELECT COUNT(id) FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.id=t0.id AND 
...) AND ...

It's guaranteed to be distinct since it's a PK column, so it doesn't need a
"Unique" node.

I think it might prefer an seq scan on t0, which might be good since it seems
to be returning over 10% of records.

> Explain Analyze :
> 
> "Aggregate  (cost=5345632.91..5345632.92 rows=1 width=8) (actual 
> time=442688.462..442688.462 rows=1 loops=1)"
> "  ->  Unique  (cost=150.13..4943749.39 rows=32150682 width=8) (actual 
> time=0.022..439964.214 rows=32368180 loops=1)"
> "->  Merge Join  (cost=150.13..4863372.68 rows=32150682 width=8) 
> (actual time=0.021..435818.276 rows=32368180 loops=1)"
> "  Merge Cond: (table0.individual_entity_proxy_id = 
> table1.individual_entity_proxy_id)"
> "  ->  Index Scan using indx_prospect_indv_entty_id on test1 
> table0  (cost=0.56..2493461.92 rows=32233405 width=8) (actual 
> time=0.011..63009.551 rows=32368180 loops=1)"
> "Filter: ((direct_mail_preference IS NULL) AND 
> ((last_contacted_dm_datetime IS NULL) OR (last_contacted_dm_datetime < 
> '2020-03-15 00:00:00'::timestamp without time zone)) AND 
> (shared_paddr_with_customer_ind = 'N'::bpchar) AND (profane_wrd_ind = 
> 'N'::bpchar) AND (tmo_ofnsv_name_ind = 'N'::bpchar) AND 
> (has_individual_address = 'Y'::bpchar) AND (has_last_name = 'Y'::bpchar) AND 
> (has_first_name = 'Y'::bpchar))"
> "Rows Removed by Filter: 7709177"
> "  ->  Index Scan using pk_entity_proxy_id on test2 table1  
> (cost=0.56..1867677.94 rows=40071417 width=8) (actual time=0.008..363534.437 
> rows=40077727 loops=1)"
> "Filter: ((tax_bnkrpt_dcsd_ind = 'N'::bpchar) AND 
> (govt_prison_ind = 'N'::bpchar) AND ((cstmr_prspct_ind)::text = 
> 'Prospect'::text))"
> "Rows Removed by Filter: 94756"

It might help to show explain(ANALYZE,BUFFERS).

It looks like test2/table1 index scan is a lot slower than table0.
Maybe table1 gets lots of updates, so isn't clustered on its primary key, so
the index scan is highly random.  You could check the "correlation" of its PK
ID column:
https://wiki.postgresql.org/wiki/Slow_Query_Questions#Statistics:_n_distinct.2C_MCV.2C_histogram

If true, that would be a good reason to have separate tables.

> vCore: 32

Possibly it would be advantageous to use parallel query.
A better query+plan might allow that.

-- 
Justin

Re: Performance issue

[David Rowley]

On Mon, 15 Jun 2020 at 10:46, Nagaraj Raj  wrote:
> CREATE TABLE test1
> (
...

> CONSTRAINT pk_i_entity_proxy_id PRIMARY KEY (individual_entity_proxy_id)
>
> );

> CREATE TABLE test2
> (
...

> CONSTRAINT pk_entity_proxy_id PRIMARY KEY (individual_entity_proxy_id)
> );
>
>
> User query:
>
> explain analyze select COUNT(*) as "DII_1"
>   from ( select distinct table0."individual_entity_proxy_id" as 
> "INDIVIDUAL_ENTITY_PROXY_ID"
> from test1 table0
> inner join test2 table1
> on table0."individual_entity_proxy_id" = 
> table1."individual_entity_proxy_id"

Why do you use "select distinct". It seems to me that you're putting a
distinct clause on the primary key of test1 and joining to another
table in a way that cannot cause duplicates.

I imagine dropping that distinct will speed up the query quite a bit.

David