Re: Query performance issue

Dheeraj Sonawane writes: > While executing the join query on the postgres database we have observed > sometimes randomly below query is being fired which is affecting our response > time. > Query randomly fired in the background:- > SELECT p.proname,p.oid FROM pg_catalog.pg_proc p, pg_catalog.p

Query performance issue

Hello all, While executing the join query on the postgres database we have observed sometimes randomly below query is being fired which is affecting our response time. Query randomly fired in the background:- SELECT p.proname,p.oid FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n WHERE p.p

Re: Occasional performance issue after changing table partitions

On Wed, Jul 13, 2022 at 03:13:46AM +1200, Nathan Ward wrote: > I have been stepping through the various statements which are different > between the two functions, and note that when I do math on a timestamp in a > SELECT statement (i.e. _event_timestamp - INTERVAL ‘1 hour’), > the planner takes

Re: Occasional performance issue after changing table partitions

Hi, I haven’t caught the issue yet with this debug etc. in place, but auto_explain (and some pg_stat_statements poking about) has helped me find something interesting that might(?) be related. My data ingest is in 2 functions, depending on the type of data: - RADIUS data with usage info - RADIU

Re: Oracle_FDW table performance issue

Understood thanks!! Will try to build dynamiq query to send ids across instead of join. On Mon, Jul 11, 2022 at 8:56 PM Laurenz Albe wrote: > On Mon, 2022-07-11 at 17:38 +0530, aditya desai wrote: > > I have one Oracle fdw table which is giving performance issue when joined > > l

Re: Oracle_FDW table performance issue

On Mon, 2022-07-11 at 17:38 +0530, aditya desai wrote: > I have one Oracle fdw table which is giving performance issue when joined > local temp table gives performance issue. > > select * from oracle_fdw_table where transaction_id in ( select > transaction_id from temp_table) >

Re: Oracle_FDW table performance issue

. On Mon, Jul 11, 2022 at 5:43 PM Justin Pryzby wrote: > On Mon, Jul 11, 2022 at 05:38:34PM +0530, aditya desai wrote: > > Hi, > > I have one Oracle fdw table which is giving performance issue when joined > > local temp table gives performance issue. > > > > sel

Re: Oracle_FDW table performance issue

On Mon, Jul 11, 2022 at 05:38:34PM +0530, aditya desai wrote: > Hi, > I have one Oracle fdw table which is giving performance issue when joined > local temp table gives performance issue. > > select * from oracle_fdw_table where transaction_id in ( select > transaction_id from t

Oracle_FDW table performance issue

Hi, I have one Oracle fdw table which is giving performance issue when joined local temp table gives performance issue. select * from oracle_fdw_table where transaction_id in ( select transaction_id from temp_table) 54 seconds. Seeing HASH SEMI JOIN in EXPLAIN PLAN. temp_table has only 74

Re: Occasional performance issue after changing table partitions

> On 11/07/2022, at 4:05 PM, Justin Pryzby wrote: > > On Mon, Jul 11, 2022 at 03:21:38PM +1200, Nathan Ward wrote: >>> Note that postgres doesn't automatically analyze parent tables, so you >>> should >>> maybe do that whenever the data changes enough for it to matter. >> >> Hmm. This raises

Re: Occasional performance issue after changing table partitions

On Mon, Jul 11, 2022 at 03:21:38PM +1200, Nathan Ward wrote: > > Note that postgres doesn't automatically analyze parent tables, so you > > should > > maybe do that whenever the data changes enough for it to matter. > > Hmm. This raises some stuff I’m not familiar with - does analysing a parent

Re: Occasional performance issue after changing table partitions

> On 11/07/2022, at 2:05 AM, Justin Pryzby wrote: > > On Sun, Jul 10, 2022 at 04:55:34PM +1200, Nathan Ward wrote: >> I am running Postgres 13 on CentOS 7, installed from the yum.postgresql.org >> repo. > > It doesn't sound relevant, but what kind of storage syste

Re: Occasional performance issue after changing table partitions

On Sun, Jul 10, 2022 at 04:55:34PM +1200, Nathan Ward wrote: > I am running Postgres 13 on CentOS 7, installed from the yum.postgresql.org > repo. It doesn't sound relevant, but what kind of storage systems is postgres using ? Filesystem, raid, device. Is the high CP

Occasional performance issue after changing table partitions

Hi, I am running Postgres 13 on CentOS 7, installed from the yum.postgresql.org repo. I have 3 servers in different sites, with asynchronous replication managed by pgpool 4.0.11. Highest latency is 16ms RTT - but shouldn’t matter as it’s async - but my application

Re: SQL performance issue after migration from Oracle to Aurora postgres

On 2022-04-14 Th 05:35, Goti wrote: > Hi All, > > We migrated from Oracle 12.1 to Aurora postgres 12.8.1. The query in > Oracle takes less than a millisecond however the same query in aurora > is taking more than a second. We have a larger number of executions > for the SQL which is causing an ov

SQL performance issue after migration from Oracle to Aurora postgres

Hi All, We migrated from Oracle 12.1 to Aurora postgres 12.8.1. The query in Oracle takes less than a millisecond however the same query in aurora is taking more than a second. We have a larger number of executions for the SQL which is causing an overall latency for the application. I am new to po

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

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

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

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

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 versi

Re: Need help identifying a periodic performance issue.

> On Nov 24, 2021, at 4:15 PM, Justin Pryzby wrote: > > This message originated outside your organization. > > On Wed, Nov 24, 2021 at 10:44:12PM +, Robert Creager wrote: >> I forgot, I had reloaded postgres, but had not re-started our app, so the >> connections wouldn’t have that plan sett

Re: Need help identifying a periodic performance issue.

On Wed, Nov 24, 2021 at 10:44:12PM +, Robert Creager wrote: > I forgot, I had reloaded postgres, but had not re-started our app, so the > connections wouldn’t have that plan setting on them. Re-doing now. Are you sure? GUC changes should be applied for existing sessions, right ? Would you s

Re: Need help identifying a periodic performance issue.

I forgot, I had reloaded postgres, but had not re-started our app, so the connections wouldn’t have that plan setting on them. Re-doing now. On Nov 24, 2021, at 2:13 PM, Robert Creager mailto:robe...@spectralogic.com>> wrote: On Nov 19, 2021, at 11:47 AM, Robert Creager mailto:robe...@spectr

Re: Need help identifying a periodic performance issue.

On Nov 19, 2021, at 11:47 AM, Robert Creager mailto:robe...@spectralogic.com>> wrote: On Nov 18, 2021, at 2:42 PM, Tom Lane mailto:t...@sss.pgh.pa.us>> wrote: This message originated outside your organization. Thomas Munro mailto:thomas.mu...@gmail.com>> writes: This is all guesswork thou

Re: Need help identifying a periodic performance issue.

On Nov 18, 2021, at 2:42 PM, Tom Lane mailto:t...@sss.pgh.pa.us>> wrote: This message originated outside your organization. Thomas Munro mailto:thomas.mu...@gmail.com>> writes: This is all guesswork though, since we don't know quite what's happening on Robert's system. It might be worth sett

Re: Need help identifying a periodic performance issue.

> On Nov 18, 2021, at 2:42 PM, Tom Lane wrote: > > This is all guesswork though, since we don't know quite what's > happening on Robert's system. It might be worth setting > "log_autovacuum_min_duration = 0" (either globally, or as a > reloption on the relevant tables), and seeing if there see

Re: Need help identifying a periodic performance issue.

On Nov 18, 2021, at 2:08 PM, Thomas Munro mailto:thomas.mu...@gmail.com>> wrote: This message originated outside your organization. On Fri, Nov 19, 2021 at 6:03 AM Robert Creager mailto:robe...@spectralogic.com>> wrote: Which would be better? Discard plans or forcing custom plans? Seems lik

Re: Need help identifying a periodic performance issue.

Thomas Munro writes: > I'm wondering if the thing that changed between 9.6 and 13 might be > the heuristics for when auto vacuum's background ANALYZE is triggered, > creating the unlucky timing required to get your system to this state > occasionally. > For a while now I have been wondering how w

Re: Need help identifying a periodic performance issue.

On Fri, Nov 19, 2021 at 6:03 AM Robert Creager wrote: > Which would be better? Discard plans or forcing custom plans? Seems like > wrapping a copy might be better than the Postgres.conf change as that would > affect all statements. What kind of performance hit would we be taking with > that

Re: Need help identifying a periodic performance issue.

> On Nov 17, 2021, at 10:42 PM, Justin Pryzby wrote: > > This message originated outside your organization. > > On Thu, Nov 18, 2021 at 04:39:42PM +1300, Thomas Munro wrote: >> On Thu, Nov 18, 2021 at 1:18 PM Robert Creager >> wrote: >>> So, how do I go about capturing more information for

Re: Need help identifying a periodic performance issue.

On Thu, Nov 18, 2021 at 04:39:42PM +1300, Thomas Munro wrote: > On Thu, Nov 18, 2021 at 1:18 PM Robert Creager > wrote: > > So, how do I go about capturing more information for the big brains (you > > guys) to help figure this out? I have all our resources at mine (and hence > > your) disposal

Re: Need help identifying a periodic performance issue.

On Thu, Nov 18, 2021 at 1:18 PM Robert Creager wrote: > So, how do I go about capturing more information for the big brains (you > guys) to help figure this out? I have all our resources at mine (and hence > your) disposal. As a workaround, does it help if you issue DISCARD PLANS before your C

Re: Need help identifying a periodic performance issue.

> On Nov 17, 2021, at 4:18 PM, Justin Pryzby wrote: > > This message originated outside your organization. > > On Wed, Nov 17, 2021 at 09:54:14PM +, Robert Creager wrote: > > We are able to move up to Postgres 13.5, in our ports tree, if that would > > help. We used pg_upgrade to get from

Re: Need help identifying a periodic performance issue.

On Wed, Nov 17, 2021 at 09:54:14PM +, Robert Creager wrote: > We are able to move up to Postgres 13.5, in our ports tree, if that would > help. We used pg_upgrade to get from 9.6 to 13.3, so that should work fine > going instead to 13.5. We’re almost branching/releasing our code, so it’s >

Re: Need help identifying a periodic performance issue.

On Thu, Nov 18, 2021 at 8:28 AM Tom Lane wrote: > Justin Pryzby writes: > > It shows that the process is running FK triggers. > > Indeed, and doing a seqscan therein. Normally I'd suppose that > this reflects a lack of an index, but RI_FKey_check should always > be doing something that matches t

Re: Need help identifying a periodic performance issue.

On Nov 17, 2021, at 12:00 PM, Justin Pryzby mailto:pry...@telsasoft.com>> wrote: This message originated outside your organization. On Wed, Nov 17, 2021 at 05:51:05PM +, Robert Creager wrote: postgres`HeapTupleSatisfiesVisibility+0x42 postgres`heapgetpage+0x237

Re: Need help identifying a periodic performance issue.

Justin Pryzby writes: > It shows that the process is running FK triggers. Indeed, and doing a seqscan therein. Normally I'd suppose that this reflects a lack of an index, but RI_FKey_check should always be doing something that matches the referenced table's unique constraint, so why isn't it usi

Re: Need help identifying a periodic performance issue.

On Wed, Nov 17, 2021 at 05:51:05PM +, Robert Creager wrote: > postgres`HeapTupleSatisfiesVisibility+0x42 > postgres`heapgetpage+0x237 > postgres`heapgettup_pagemode+0x5ad > postgres`heap_getnextslot+0x52 > postgres`SeqNext+0x

Re: Need help identifying a periodic performance issue.

On Nov 17, 2021, at 10:51 AM, Robert Creager mailto:robe...@spectralogic.com>> wrote: On Nov 15, 2021, at 10:50 PM, Thomas Munro mailto:thomas.mu...@gmail.com>> wrote: This message originated outside your organization. On Tue, Nov 16, 2021 at 5:43 PM Robert Creager mailto:robe...@spectral

Re: Need help identifying a periodic performance issue.

On Wed, Nov 17, 2021 at 11:51 AM Thomas Munro wrote: > It's the right output format, but isn't /pid == '$PID'/ only going to > match one single process called "postgres"? Maybe /execname == > "postgres"/ to catch them all? Oh, duh, it's the top CPU one. Makes sense. Never mind :-)

Re: Need help identifying a periodic performance issue.

On Wed, Nov 17, 2021 at 11:40 AM Robert Creager wrote: > Presuming this is the type of output you are expecting: > > CPU IDFUNCTION:NAME > 0 58709:tick-10s > > > postgres`AtEOXact_LargeObject+0x11 > postgres`CommitTrans

Re: Need help identifying a periodic performance issue.

On Nov 15, 2021, at 10:50 PM, Thomas Munro mailto:thomas.mu...@gmail.com>> wrote: This message originated outside your organization. On Tue, Nov 16, 2021 at 5:43 PM Robert Creager mailto:robe...@spectralogic.com>> wrote: One CPU is pegged, the data has been sent over STDIN, so Postgres is not

Re: Need help identifying a periodic performance issue.

On Nov 15, 2021, at 10:29 PM, Justin Pryzby mailto:pry...@telsasoft.com>> wrote: This message originated outside your organization. On Tue, Nov 16, 2021 at 04:43:25AM +, Robert Creager wrote: > We’re executing the following copy to fill a table with approximately 5k > records, then repeat

Re: Need help identifying a periodic performance issue.

On Tue, Nov 16, 2021 at 5:43 PM Robert Creager wrote: > One CPU is pegged, the data has been sent over STDIN, so Postgres is not > waiting for more, there are no other queries running using this select: So PostgreSQL is eating 100% CPU, with no value shown in wait_event_type, and small numbers o

Re: Need help identifying a periodic performance issue.

On Tue, Nov 16, 2021 at 04:43:25AM +, Robert Creager wrote: > We’re executing the following copy to fill a table with approximately 5k > records, then repeating for a total of 250k records. Normally, this copy > executes < 1 second, with the entire set taking a couple of minutes. The > prob

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?

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

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

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

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

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

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

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 t

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

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

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

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

Performance issue with thousands of calls to procedures and functions?

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

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

Performance Issue on a table

Dear Team, 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 ? The tabl

Re: SQL performance issue (postgresql chooses a bad plan when a better one is available)

When I do serious database development I try to use database functions as much as possible. You can attach any flag value to a function in which case it gets set when the function is running, In your case you could probably wrap your query into an set-returning `LANGUAGE SQL` function [1] and the

Re: SQL performance issue (postgresql chooses a bad plan when a better one is available)

"set enable_material=false;" produces an efficient plan. good to know there are *some* knobs to turn when the optimizer comes up with a bad plan. would be awesome if you could lock that plan into place w/out altering the variable. thanks for the help Hannu! On Mon, Mar 22, 2021 at 4:39 PM Hannu K

Re: SQL performance issue (postgresql chooses a bad plan when a better one is available)

you can play around various `enable_*` flags to see if disabling any of these will *maybe* yield the plan you were expecting, and then check the costs in EXPLAIN to see if the optimiser also thinks this plan is cheaper. On Mon, Mar 22, 2021 at 6:29 PM Chris Stephens wrote: > > we are but i was h

Re: SQL performance issue (postgresql chooses a bad plan when a better one is available)

we are but i was hoping to get a better understanding of where the optimizer is going wrong and what i can do about it. chris On Mon, Mar 22, 2021 at 9:54 AM Laurenz Albe wrote: > On Mon, 2021-03-22 at 08:10 -0500, Chris Stephens wrote: > > The following SQL takes ~25 seconds to run. I'm relat

Re: SQL performance issue (postgresql chooses a bad plan when a better one is available)

On Mon, 2021-03-22 at 08:10 -0500, Chris Stephens wrote: > The following SQL takes ~25 seconds to run. I'm relatively new to postgres > but the execution plan (https://explain.depesz.com/s/N4oR) looks like it's > materializing the entire EXISTS subquery for each row returned by the rest > of the

SQL performance issue (postgresql chooses a bad plan when a better one is available)

ince postgres to choose a better plan. any and all help/suggestions/explanations would be greatly appreciated. the rewritten SQL performs sufficiently well but i'd like to understand why postgres is doing this and what to do about it so i can't tackle the next SQL performance issue wi

Re: Query performance issue

What indexes exist on those tables? How many rows do you expect to get back in total? Is the last_contacted_anychannel_dttm clause restrictive, or does that include most of the prospect table (check pg_stats for the histogram if you don't know). and (a."shared_paddr_with_customer_ind" = 'N')

Re: Query performance issue

On 1/22/21 3:35 AM, Justin Pryzby wrote: On Fri, Jan 22, 2021 at 01:53:26AM +, Nagaraj Raj wrote: Tables ddl are attached in dbfiddle -- Postgres 11 | db<>fiddle Postgres 11 | db<>fiddle Server configuration is: Version: 10.11RAM - 320GBvCPU - 32 "maintenance_work_mem" 256MB"work_mem"  

Re: Query performance issue

On Fri, Jan 22, 2021 at 01:53:26AM +, Nagaraj Raj wrote: > Tables ddl are attached in dbfiddle -- Postgres 11 | db<>fiddle > Postgres 11 | db<>fiddle > Server configuration is: Version: 10.11RAM - 320GBvCPU - 32  > "maintenance_work_mem" 256MB"work_mem"             1GB"shared_buffers" 64GB > A

Query performance issue

Hi, I have a query performance issue, it takes a long time, and not even getting explain analyze the output. this query joining on 3 tables which have around a - 176223509 b - 286887780 c - 214219514 explainselect  Count(a."individual_entity_proxy_id")from "prospe

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

On Sunday, October 11, 2020, Gopisetty, Ramesh wrote: > > to sch USING ( key = > f_sel_1(key) > ); > As Tom said it doesn’t matter what you classify the function as (stable, etc) if your function call accepts a column reference as an input and compares its output to another colu

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 forw

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 im

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

stkey)) ); Going to a Sequential scan instead of index scan. Hence, performance issue. pgwfc01q=> explain analyze select * from test; QU

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

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

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 w

AW: Query performance issue

Hi %, in order to be able to readjust the effects of the stored procedure and, if necessary, to save turnaround times, different requests can be concatenated using semicolons for bundling several statements in one request. We did some tests against a postgres cluster. The results in terms of

Re: Query performance issue

Hi Michael, I created an index as suggested, it improved.  I was tried with partial index but the planner not using it. also, there is no difference even with timing OFF. ktbv : Optimization for: plan #HaOx | explain.depesz.com | | | | ktbv : Optimization for: plan #HaOx | explain.depesz

Re: Query performance issue

On Fri, Sep 4, 2020, 4:20 PM Nagaraj Raj wrote: > Hi Mechel, > > I added the index as you suggested and the planner going through the > bitmap index scan,heap and the new planner is, > HaOx | explain.depesz.com > > HaOx | explain.depesz.com > >

Re: Query performance issue

On Sat, 5 Sep 2020 at 10:20, Nagaraj Raj wrote: > I added the index as you suggested and the planner going through the bitmap > index scan,heap and the new planner is, > HaOx | explain.depesz.com In addition to that index, you could consider moving away from standard SQL and use DISTINCT ON, whi

Re: Query performance issue

Hi Mechel, I added the index as you suggested and the planner going through the bitmap  index scan,heap and the new planner is,HaOx | explain.depesz.com | | | | HaOx | explain.depesz.com | | | Mem config:  Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3,

Re: Query performance issue

"Subquery Scan on rec (cost=1628601.89..1676580.92 rows=7381 width=41) (actual time=22171.986..23549.079 rows=1236042 loops=1)" " Filter: (rec.mpos = 1)" " Rows Removed by Filter: 228737" " Buffers: shared hit=45 read=1166951" " I/O Timings: read=29.530" " -> WindowAgg (cost=1628601.89..1658127.45

Re: Query performance issue

Sorry, I have attached the wrong query planner, which executed in lower environment which has fewer resources: Updated one,eVFiF | explain.depesz.com | | | | eVFiF | explain.depesz.com | | | Thanks,RjOn Friday, September 4, 2020, 02:39:57 PM PDT, Michael Lewis wrote: CR

Re: Query performance issue

Note- you may need to vacuum* the table to get full benefit of index only scan by updating the visibility map. I think index only scan is skipped in favor of just checking visibility when the visibility map is stale. *NOT full

Re: Query performance issue

CREATE INDEX receiving_item_delivered_received ON receiving_item_delivered_received USING btree ( eventtype, replenishmenttype, serial_no, eventtime DESC ); > More work_mem as Tomas suggests, but also, the above index should find the candidate rows by the first two keys, and then be able to skip t

Re: Query performance issue

On Fri, Sep 04, 2020 at 09:18:41PM +, Nagaraj Raj wrote: I have a query which will more often run on DB and very slow and it is doing 'seqscan'. I was trying to optimize it by adding indexes in different ways but nothing helps. Any suggestions? 1) It's rather difficult to read the query

Re: Query performance issue

Nagaraj Raj schrieb am 04.09.2020 um 23:18: I have a query which will more often run on DB and very slow and it is doing 'seqscan'. I was trying to optimize it by adding indexes in different ways but nothing helps. EXPALIN ANALYZE select serial_no,receivingplant,sku,r3_eventtime from (select ser

Re: Query performance issue

query planner:SPJe | explain.depesz.com | | | | SPJe | explain.depesz.com | | | On Friday, September 4, 2020, 02:19:06 PM PDT, Nagaraj Raj wrote: I have a query which will more often run on DB and very slow and it is doing 'seqscan'. I was trying to optimize it by add

Query performance issue

I have a query which will more often run on DB and very slow and it is doing 'seqscan'. I was trying to optimize it by adding indexes in different ways but nothing helps. Any suggestions? Query: EXPALIN ANALYZE select serial_no,receivingplant,sku,r3_eventtime from (select serial_no,receivingpl

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

Performance issue

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

AW: Performance issue with order by clause on

to:csuszm...@gmail.com> Cc: pgsql-performa...@postgresql.org<mailto:pgsql-performa...@postgresql.org> Betreff: Re: Performance issue with order by clause on On Wed, Mar 20, 2019 at 9:36 AM Maracska Ádám mailto:csuszm...@gmail.com>> wrote: Hi, I would like to overcome an issue w

Re: Query Performance Issue

On Sat, 29 Dec 2018 at 20:15, Justin Pryzby wrote: > On Sat, Dec 29, 2018 at 07:58:28PM +1300, David Rowley wrote: > > Unfortunately, I don't think that'll help this situation. Extended > > statistics are currently only handled for base quals, not join quals. > > See dependency_is_compatible_claus

Re: Query Performance Issue

On Sat, Dec 29, 2018 at 1:58 AM David Rowley wrote: > On Sat, 29 Dec 2018 at 04:32, Justin Pryzby wrote: > > I think the solution is to upgrade (at least) to PG10 and CREATE > STATISTICS > > (dependencies). > > Unfortunately, I don't think that'll help this situation. Extended > statistics are c

Re: Query Performance Issue

On Thu, Dec 27, 2018 at 10:25:47PM +0300, neslişah demirci wrote: > Have this explain analyze output : > > *https://explain.depesz.com/s/Pra8a * On Sat, Dec 29, 2018 at 07:58:28PM +1300, David Rowley wrote: > On Sat, 29 Dec 2018 at 04:32, Justin Pryzby wrote:

Re: Query Performance Issue

On Sat, 29 Dec 2018 at 04:32, Justin Pryzby wrote: > I think the solution is to upgrade (at least) to PG10 and CREATE STATISTICS > (dependencies). Unfortunately, I don't think that'll help this situation. Extended statistics are currently only handled for base quals, not join quals. See dependenc

Re: Query Performance Issue

On Thu, Dec 27, 2018 at 10:25:47PM +0300, neslişah demirci wrote: > Have this explain analyze output : > > *https://explain.depesz.com/s/Pra8a * Row counts are being badly underestimated leading to nested loop joins: |Index Scan using product_content_recommend

Re: Query Performance Issue

*https://explain.depesz.com/s/Pra8a* Could you share the query itself please? And the tables definitions including indexes. work_mem : 8MB That's not a lot. The 16-batches hash join may have worked faster if you had resources to increase work_mem.

Query Performance Issue

Hi everyone , Have this explain analyze output : *https://explain.depesz.com/s/Pra8a * Appreciated for any help . *PG version* --- PostgreSQL 9.6.11 on x8