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

On 3/22/22 13:57, Prajna Shetty wrote: > ++ [email protected]_ > >   > Hello Team, >   > There is change in query plan in 12.4 version and Version 13 resulting > in performance slowness post upgrade. >   > > * In 12.4 versio

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

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

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

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

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

pá 30. 7. 2021 v 10:12 odesílatel Daniel Westermann (DWE) < [email protected]> napsal: > > pá 30. 7. 2021 v 9:12 odesílatel Daniel Westermann (DWE) < > [email protected]> napsal: > > Hi, > > we have a customer which was migrated from Oracle to PostgreSQL 12.5 (I >

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

pá 30. 7. 2021 v 9:12 odesílatel Daniel Westermann (DWE) mailto:[email protected]>> 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 t

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

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

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

Hi pá 30. 7. 2021 v 9:12 odesílatel Daniel Westermann (DWE) < [email protected]> 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 simplifie

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

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

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

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

Re: Performance Issue on a table

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

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

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

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

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

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

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

"Gopisetty, Ramesh" writes: > Thanks for looking into the problem/issue.Let me give more details about > the functions...Yes, we are using row level security. Hm. If those expensive filter functions are being injected by RLS on the target tables (rather than by something like an interm

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

_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

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

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

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 th

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

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 bei

Re: Performance issue

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

Re: Performance issue

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