RE: Never Ending query in PostgreSQL
Hi Tomas , Thanks for replying , We have identified a Join condition which is creating a problem for that query. Accept my apologies for pasting the plan twice. I am attaching the query again in this mail We have found that by evicting the View paymenttransdetails_view from the attached query runs in approx. 10 secs and the view contains multiple conditions and 1 jojn as well. I am attaching the View definition as well. Please suggest if there is a work around for this query to run faster without evicting the above from the query. Thanks and Regards, Mukesh Kumar -Original Message- From: Tomas Vondra Sent: Tuesday, March 1, 2022 7:35 PM To: Jeff Janes ; Kumar, Mukesh Cc: pgsql-performa...@postgresql.org Subject: Re: Never Ending query in PostgreSQL On 2/27/22 18:20, Jeff Janes wrote: > > On Sun, Feb 27, 2022 at 7:09 AM Kumar, Mukesh > mailto:mku...@peabodyenergy.com>> wrote: > > Hi Team, > > Can you please help in tunning the attached query as , i am trying > to run this query and it runs for several hours and it did not give > any output. > > > Several hours is not all that long. Without an EXPLAIN ANALYZE, we > could easily spend several hours scratching our heads and still get > nowhere. So unless having this running cripples the rest of your > system, please queue up another one and let it go longer. But first, > do an ANALYZE (and preferably a VACUUM ANALYZE) on all the tables. If > you have a test db which is a recent clone of production, you could do > it there so as not to slow down production. The problem is that the > row estimates must be way off (otherwise, it shouldn't take long) and > if that is the case, we can't use the plan to decide much of anything, > since we don't trust it. > I'd bet Jeff is right and poor estimates are the root cause. The pattern with a cascade of "nested loop" in the explain is fairly typical. This is likely due to the complex join conditions and correlation. > In parallel you could start evicting table joins from the query to > simplify it until it gets to the point where it will run, so you can > then see the actual row counts. To do that it does help if you know > what the intent of the query is (or for that matter, the text of the > query--you attached the plan twice). > Right, simplify the query. Or maybe do it the other way around - start with the simplest query (the inner-most part of the explain) and add joins one by one (by following the explains) until it suddenly starts being much slower. regards -- Tomas Vondra EnterpriseDB: https://urldefense.com/v3/__http://www.enterprisedb.com__;!!KupS4sW4BlfImQPd!P_2LgOrDOnTxBqFECBDdQolWyDNytft5mDbiJF_Bn827W6GdEOflXZ8a-NWSzdi6nJgewzgEJom8uFDBFgGKSETUD5VHA38$ The Enterprise PostgreSQL Company Tuned_Query (002).sql Description: Tuned_Query (002).sql View.sql Description: View.sql
View taking time to show records
Hi Team and All , Greeting for the day. We have recently migrated from Oracle to PostgreSQL on version 11.4 on azure postgres PaaS instance. There is 1 query which is taking approx. 10 secs in Oracle and when we ran the same query it is taking approx. 1 min Can anyone suggest to improve the query as from application end 1 min time is not accepted by client. Please find the query and explain analyze report from below link https://explain.depesz.com/s/RLJn#stats Thanks and Regards, Mukesh Kumar
RE: View taking time to show records
Hi Albe , Thanks for the below suggestion , When I ran the query with the parameter , it is taking only 1 sec. So could you please let me know if I can put this parameter to OFF . at database and it will not create any issues to queries running in database. Could you please share some light on it. Thanks and Regards, Mukesh Kumar -Original Message- From: Laurenz Albe Sent: Friday, March 25, 2022 4:13 PM To: Kumar, Mukesh ; pgsql-performa...@postgresql.org Subject: Re: View taking time to show records On Thu, 2022-03-24 at 15:59 +, Kumar, Mukesh wrote: > We have recently migrated from Oracle to PostgreSQL on version 11.4 on azure > postgres PaaS instance. > > There is 1 query which is taking approx. 10 secs in Oracle and when we > ran the same query it is taking approx. 1 min > > Can anyone suggest to improve the query as from application end 1 min time is > not accepted by client. > > Please find the query and explain analyze report from below link > > https://urldefense.com/v3/__https://explain.depesz.com/s/RLJn*stats__; > Iw!!KupS4sW4BlfImQPd!Ln-8-n9OcKKifiwKjYcs_JOUo80VTTp5hA9V_-gYjOfDr3DDm > psmbIY_MQxw5RwQ2ZQtMlobbmvex2CIaJtISv0ZkaSn5w$ I would split the query in two parts: the one from line 3 to line 49 of your execution plan, and the rest. The problem is the bad estimate of that first part, so execute only that, write the result to a temporary table and ANALYZE that. Then execute the rest of the query using that temporary table. Perhaps it is also enough to blindly disable nested loop joins for the whole query, rather than doing the right thing and fixing the estimates: BEGIN; SET LOCAL enable_nestloop = off; SELECT ...; COMMIT; Yours, Laurenz Albe -- Cybertec | https://urldefense.com/v3/__https://www.cybertec-postgresql.com__;!!KupS4sW4BlfImQPd!Ln-8-n9OcKKifiwKjYcs_JOUo80VTTp5hA9V_-gYjOfDr3DDmpsmbIY_MQxw5RwQ2ZQtMlobbmvex2CIaJtISv1qNNoktA$
Performance for SQL queries on Azure PostgreSQL PaaS instance
Hi Team, Greetings !! We have recently done the migration from Oracle Database Version 12C to Azure PostgreSQL PaaS instance version 11.4 and most of the application functionality testing has been over and tested successfully However, there is 1 process at application level which is taking approx. 10 mins in PostgreSQL and in oracle it is taking only 3 mins. After investigating further we identified that process which is executed from application end contains 500 to 600 no of short SQL queries into the database. We tried to run the few queries individually on database and they are taking less than sec in Postgres Database to execute, and we noticed that in Oracle taking half of the time as is taking in PostgreSQL. for ex . in oracle same select statement is taking 300 millisecond and in PostgreSQL it is taking approx. 600 millisecond which over increases the execution of the process. Oracle Database are hosted on ON- Prem DC with dedicated application server on OnPrem and same for PostgreSQL. We are using below specifications for PostgreSQL PostgreSQL Azure PaaS instance -Single Server (8cvore with 1 TB storage on general purpose tier ) = 8 Core and 40 Gb of Memory PostgreSQL version - 11.4 We have tried running maintenance Jobs like vaccum, analyze, creating indexes, increasing compute but no sucess I am happy to share my server parameter for PostgreSQL for more information. Please let us know if this is expected behavior in PostgreSQL or is there any way i can decrease the time for the SQL queries and make it a comparison with Oracle Regards, Mukesh Kumar
RE: Performance for SQL queries on Azure PostgreSQL PaaS instance
Hi Albe , I mean to say that , we have everything hosted on Oracle is on On - Prem DC and everything hosted on Azure PostgreSQL on Microsoft Azure Cloud like Application Server and PaaS Instance, Please revert in case of any query Thanks and Regards, Mukesh Kumar -Original Message- From: Laurenz Albe Sent: Wednesday, April 13, 2022 2:04 PM To: Kumar, Mukesh ; pgsql-performa...@postgresql.org; MUKESH KUMAR Subject: Re: Performance for SQL queries on Azure PostgreSQL PaaS instance On Tue, 2022-04-12 at 09:10 +, Kumar, Mukesh wrote: > We have recently done the migration from Oracle Database Version 12C > to Azure PostgreSQL PaaS instance version 11.4 and most of the > application functionality testing has been over and tested > successfully > > However, there is 1 process at application level which is taking > approx. 10 mins in PostgreSQL and in oracle it is taking only 3 mins. > > After investigating further we identified that process which is > executed from application end contains 500 to 600 no of short SQL queries > into the database. > We tried to run the few queries individually on database and they are > taking less than sec in Postgres Database to execute, and we noticed > that in Oracle taking half of the time as is taking in PostgreSQL. for > ex . in oracle same select statement is taking 300 millisecond and in > PostgreSQL it is taking approx. 600 millisecond which over increases the > execution of the process. > > Oracle Database are hosted on ON- Prem DC with dedicated application > server on OnPrem and same for PostgreSQL. How can a database hosted with Microsoft be on your permises? Apart from all other things, compare the network latency. If a single request results in 500 database queries, you will be paying 1000 times the network latency per request. Yours, Laurenz Albe -- Cybertec | https://urldefense.com/v3/__https://www.cybertec-postgresql.com__;!!KupS4sW4BlfImQPd!Na6zYPRuqYDPkzxkeKGFLkUk5TtVvDNeBotFXA-DpoSA8sO0hMkFnUll1op05OICvy74bGAGSzuTfzBWN-4PfzlYkK0vvQ$
Query Tunning related to function
Hi Team,
We are running the below query in PostgreSQL and its taking approx. 8 to 9 sec
to run the query.
Query - 1
Select * from
(
Select payment_sid_c,
lms_app.translate_payment_status(payment_sid_c) AS paymentstatus
from
lms_app.lms_payment_check_request
group by payment_sid_c) a
where paymentstatus in ('PAID', 'MANUALLYPAID')
The explain plan and other details are placed at below link for more
information. We have checked the indexes on column but in the explain plan it
is showing as Seq Scan which we have to find out.
https://explain.depesz.com/s/Jsiw#stats
This query is using a function translate_payment_status on column payment_sid_c
whose script is attached in this mail
Could please anyone help or suggest how to improve the query performance.
Thanks and Regards,
Mukesh Kumar
function.sql
Description: function.sql
RE: Query Tunning related to function
Hi Rainer ,
We tried to create the partial ‘index on table but it did not help, and it is
taking approx. 7 sec now.
Also we tried to force the query to use the index by enabling the parameter at
session level
set enable_seqscan=false;
and it is still taking the time below is the explain plan for the same
https://explain.depesz.com/s/YRWIW#stats
Also we running the query which is actually used in application and above query
is used in below query. Below is the explain plan for same.
https://explain.depesz.com/s/wktl#stats
Please assist
Thanks and Regards,
Mukesh Kuma
From: Ranier Vilela
Sent: Thursday, April 14, 2022 7:56 PM
To: Kumar, Mukesh
Cc: pgsql-performa...@postgresql.org; MUKESH KUMAR
Subject: Re: Query Tunning related to function
Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh
mailto:mku...@peabodyenergy.com>> escreveu:
Hi Team,
We are running the below query in PostgreSQL and its taking approx. 8 to 9 sec
to run the query.
Query – 1
Select * from
(
Select payment_sid_c,
lms_app.translate_payment_status(payment_sid_c) AS paymentstatus
from
lms_app.lms_payment_check_request
group by payment_sid_c) a
where paymentstatus in ('PAID', 'MANUALLYPAID')
The explain plan and other details are placed at below link for more
information. We have checked the indexes on column but in the explain plan it
is showing as Seq Scan which we have to find out.
https://explain.depesz.com/s/Jsiw#stats<https://urldefense.com/v3/__https:/explain.depesz.com/s/Jsiw*stats__;Iw!!KupS4sW4BlfImQPd!M8K66GpB-7DvYJA0HYFVpY9mtO6TaqIGRjTLI2G1WNjwK8KA9I8JaEr9OWwGy5F6fC4Ed5dwEjCf_1rBCDg9rA$>
This query is using a function translate_payment_status on column payment_sid_c
whose script is attached in this mail
Could please anyone help or suggest how to improve the query performance.
You can try create a partial index that help this filter:
Filter: ((lms_app.translate_payment_status(payment_sid_c))::text = ANY
('{PAID,MANUALLYPAID}'::text[]))
See at:
https://www.postgresql.org/docs/current/indexes-partial.html<https://urldefense.com/v3/__https:/www.postgresql.org/docs/current/indexes-partial.html__;!!KupS4sW4BlfImQPd!M8K66GpB-7DvYJA0HYFVpY9mtO6TaqIGRjTLI2G1WNjwK8KA9I8JaEr9OWwGy5F6fC4Ed5dwEjCf_1quLi3m8Q$>
regards,
Ranier Vilela
RE: Query Tunning related to function
Hi Michael ,
We tried dropping the below values from the function, but it did not help.
Also, the values PAID and MANUALLY PAID constitutes about 60 % of the values in
table , and infact we tried creating the partial index and it did not help.
The Strange thing is that we are trying to run this in oracle as we have done
the migration recently and it is running in less than second with same indexes
and other database objects . I can understand that comparing to oracle is
stupidity, but this is only thing where we can compare.
Below is the query we are running on oracle and comparing in postgres
Below is the query and plan for same
https://explain.depesz.com/s/wktl#stats<https://urldefense.com/v3/__https:/explain.depesz.com/s/wktl*stats__;Iw!!KupS4sW4BlfImQPd!OE7VRYuxv81xKZski81jR9U-OFWiC5_KPW02j0u9iHLcaEbtUo5u_sIfi8VFrToyBiI2A_69MqYrJe97dsUq$>
Any help would be appreciated.
Thanks and Regards,
Mukesh Kumar
From: Michel SALAIS
Sent: Thursday, April 14, 2022 11:45 PM
To: Kumar, Mukesh ; 'Ranier Vilela'
Cc: pgsql-performa...@postgresql.org; 'MUKESH KUMAR'
Subject: RE: Query Tunning related to function
Hi,
This part of the function is odd and must be dropped:
IF (ret_status = payment_rec)
THEN
ret_status := payment_rec;
I didn’t look really the function code and stopped on the view referenced by
the cursor.
The view (we know it just by its name) used in the function is a black box for
us. Perhaps it is important to begin optimization there!
If values 'PAID' and 'MANUALLYPAID' are an important percentage of table rows
forcing index use is not a good thing especially when it is done with a
non-optimized function.
If rows with values 'PAID' and 'MANUALLYPAID' constitute a little percentage
of the table, then the partial index plus rewriting the query would be much
more efficient
Select
payment_sid_c,
lms_app.translate_payment_status(payment_sid_c) as paymentstatus
from
lms_app.lms_payment_check_request
where
lms_app.translate_payment_status(payment_sid_c) IN ('PAID', 'MANUALLYPAID')
group by
payment_sid_c
If not, you can gain some performance if you rewrite your query to be like this:
Select
payment_sid_c,
lms_app.translate_payment_status(payment_sid_c) as paymentstatus
from
lms_app.lms_payment_check_request
group by
payment_sid_c
having
lms_app.translate_payment_status(payment_sid_c) IN ('PAID', 'MANUALLYPAID')
And you can also try to write the query like this:
Select t.payment_sid_c, lms_app.translate_payment_status(t.payment_sid_c)
From
(
Select
payment_sid_c
from
lms_app.lms_payment_check_request
group by
payment_sid_c
having
lms_app.translate_payment_status(payment_sid_c) IN ('PAID', 'MANUALLYPAID')
) t
Regards
Michel SALAIS
De : Kumar, Mukesh mailto:mku...@peabodyenergy.com>>
Envoyé : jeudi 14 avril 2022 16:45
À : Ranier Vilela mailto:ranier...@gmail.com>>
Cc : pgsql-performa...@postgresql.org<mailto:pgsql-performa...@postgresql.org>;
MUKESH KUMAR mailto:mukesh.kuma...@tcs.com>>
Objet : RE: Query Tunning related to function
Hi Rainer ,
We tried to create the partial ‘index on table but it did not help, and it is
taking approx. 7 sec now.
Also we tried to force the query to use the index by enabling the parameter at
session level
set enable_seqscan=false;
and it is still taking the time below is the explain plan for the same
https://explain.depesz.com/s/YRWIW#stats<https://urldefense.com/v3/__https:/explain.depesz.com/s/YRWIW*stats__;Iw!!KupS4sW4BlfImQPd!OE7VRYuxv81xKZski81jR9U-OFWiC5_KPW02j0u9iHLcaEbtUo5u_sIfi8VFrToyBiI2A_69MqYrJVb2g-4s$>
Also we running the query which is actually used in application and above query
is used in below query. Below is the explain plan for same.
https://explain.depesz.com/s/wktl#stats<https://urldefense.com/v3/__https:/explain.depesz.com/s/wktl*stats__;Iw!!KupS4sW4BlfImQPd!OE7VRYuxv81xKZski81jR9U-OFWiC5_KPW02j0u9iHLcaEbtUo5u_sIfi8VFrToyBiI2A_69MqYrJe97dsUq$>
Please assist
Thanks and Regards,
Mukesh Kuma
From: Ranier Vilela mailto:ranier...@gmail.com>>
Sent: Thursday, April 14, 2022 7:56 PM
To: Kumar, Mukesh mailto:mku...@peabodyenergy.com>>
Cc: pgsql-performa...@postgresql.org<mailto:pgsql-performa...@postgresql.org>;
MUKESH KUMAR mailto:mukesh.kuma...@tcs.com>>
Subject: Re: Query Tunning related to function
Em qui., 14 de abr. de 2022 às 08:01, Kumar, Mukesh
mailto:mku...@peabodyenergy.com>> escreveu:
Hi Team,
We are running the below query in PostgreSQL and its taking approx. 8 to 9 sec
to run the query.
Query – 1
Select * from
(
Select payment_sid_c,
lms_app.translate_payment_status(payment_sid_c) AS paymentstatus
from
lms_app.lms_payment_check_request
group by payment_sid_c) a
where paymentstatus in ('PAID', 'MANUALL
RE: Query Tunning related to function
Hi Babu ,
Please find below the script for the function from Oracle
Hi babu ,
Please find attached the script for function from Oracle .
Please revert in case of any query.
Thanks and Regards,
Mukesh Kumar
From: Bhupendra Babu
Sent: Friday, April 15, 2022 3:44 AM
To: Kumar, Mukesh
Cc: Michel SALAIS ; Ranier Vilela ;
postgres performance list ; MUKESH KUMAR
; heda.giri...@tcs.com
Subject: Re: Query Tunning related to function
Can you paste from oracle for
Set lines 1
Select text from dba_source
Where name =
UPPER('translate_payment_status')
And owner = 'IMS_APP'
Thanks.
On Thu, Apr 14, 2022, 12:07 PM Kumar, Mukesh
mailto:mku...@peabodyenergy.com>> wrote:
Hi Michael ,
We tried dropping the below values from the function, but it did not help.
Also, the values PAID and MANUALLY PAID constitutes about 60 % of the values in
table , and infact we tried creating the partial index and it did not help.
The Strange thing is that we are trying to run this in oracle as we have done
the migration recently and it is running in less than second with same indexes
and other database objects . I can understand that comparing to oracle is
stupidity, but this is only thing where we can compare.
Below is the query we are running on oracle and comparing in postgres
Below is the query and plan for same
https://explain.depesz.com/s/wktl#stats<https://urldefense.com/v3/__https:/explain.depesz.com/s/wktl*stats__;Iw!!KupS4sW4BlfImQPd!OE7VRYuxv81xKZski81jR9U-OFWiC5_KPW02j0u9iHLcaEbtUo5u_sIfi8VFrToyBiI2A_69MqYrJe97dsUq$>
Any help would be appreciated.
Thanks and Regards,
Mukesh Kumar
From: Michel SALAIS mailto:msal...@msym.fr>>
Sent: Thursday, April 14, 2022 11:45 PM
To: Kumar, Mukesh mailto:mku...@peabodyenergy.com>>;
'Ranier Vilela' mailto:ranier...@gmail.com>>
Cc: pgsql-performa...@postgresql.org<mailto:pgsql-performa...@postgresql.org>;
'MUKESH KUMAR' mailto:mukesh.kuma...@tcs.com>>
Subject: RE: Query Tunning related to function
Hi,
This part of the function is odd and must be dropped:
IF (ret_status = payment_rec)
THEN
ret_status := payment_rec;
I didn’t look really the function code and stopped on the view referenced by
the cursor.
The view (we know it just by its name) used in the function is a black box for
us. Perhaps it is important to begin optimization there!
If values 'PAID' and 'MANUALLYPAID' are an important percentage of table rows
forcing index use is not a good thing especially when it is done with a
non-optimized function.
If rows with values 'PAID' and 'MANUALLYPAID' constitute a little percentage
of the table, then the partial index plus rewriting the query would be much
more efficient
Select
payment_sid_c,
lms_app.translate_payment_status(payment_sid_c) as paymentstatus
from
lms_app.lms_payment_check_request
where
lms_app.translate_payment_status(payment_sid_c) IN ('PAID', 'MANUALLYPAID')
group by
payment_sid_c
If not, you can gain some performance if you rewrite your query to be like this:
Select
payment_sid_c,
lms_app.translate_payment_status(payment_sid_c) as paymentstatus
from
lms_app.lms_payment_check_request
group by
payment_sid_c
having
lms_app.translate_payment_status(payment_sid_c) IN ('PAID', 'MANUALLYPAID')
And you can also try to write the query like this:
Select t.payment_sid_c, lms_app.translate_payment_status(t.payment_sid_c)
From
(
Select
payment_sid_c
from
lms_app.lms_payment_check_request
group by
payment_sid_c
having
lms_app.translate_payment_status(payment_sid_c) IN ('PAID', 'MANUALLYPAID')
) t
Regards
Michel SALAIS
De : Kumar, Mukesh mailto:mku...@peabodyenergy.com>>
Envoyé : jeudi 14 avril 2022 16:45
À : Ranier Vilela mailto:ranier...@gmail.com>>
Cc : pgsql-performa...@postgresql.org<mailto:pgsql-performa...@postgresql.org>;
MUKESH KUMAR mailto:mukesh.kuma...@tcs.com>>
Objet : RE: Query Tunning related to function
Hi Rainer ,
We tried to create the partial ‘index on table but it did not help, and it is
taking approx. 7 sec now.
Also we tried to force the query to use the index by enabling the parameter at
session level
set enable_seqscan=false;
and it is still taking the time below is the explain plan for the same
https://explain.depesz.com/s/YRWIW#stats<https://urldefense.com/v3/__https:/explain.depesz.com/s/YRWIW*stats__;Iw!!KupS4sW4BlfImQPd!OE7VRYuxv81xKZski81jR9U-OFWiC5_KPW02j0u9iHLcaEbtUo5u_sIfi8VFrToyBiI2A_69MqYrJVb2g-4s$>
Also we running the query which is actually used in application and above query
is used in below query. Below is the explain plan for same.
https://explain.depesz.com/s/wktl#stats<https://urldefense.com/v3/__https:/explain.depesz.com/s/wktl*stats__;Iw!!KupS4sW4BlfImQPd!OE7VRYuxv81xKZski81jR9U-OFWiC5_KPW02j0
RE: Query Tunning related to function
Hi All ,
We request you to please provide some assistance on below issue and it is
impacting the migration project.
Thanks and Regards,
Mukesh Kumar
From: Kumar, Mukesh
Sent: Friday, April 15, 2022 11:43 AM
To: Bhupendra Babu
Cc: Michel SALAIS ; Ranier Vilela ;
postgres performance list ; MUKESH KUMAR
; heda.giri...@tcs.com
Subject: RE: Query Tunning related to function
Hi Babu ,
Please find below the script for the function from Oracle
Hi babu ,
Please find attached the script for function from Oracle .
Please revert in case of any query.
Thanks and Regards,
Mukesh Kumar
From: Bhupendra Babu mailto:bbab...@gmail.com>>
Sent: Friday, April 15, 2022 3:44 AM
To: Kumar, Mukesh mailto:mku...@peabodyenergy.com>>
Cc: Michel SALAIS mailto:msal...@msym.fr>>; Ranier Vilela
mailto:ranier...@gmail.com>>; postgres performance list
mailto:pgsql-performa...@postgresql.org>>;
MUKESH KUMAR mailto:mukesh.kuma...@tcs.com>>;
heda.giri...@tcs.com<mailto:heda.giri...@tcs.com>
Subject: Re: Query Tunning related to function
Can you paste from oracle for
Set lines 1
Select text from dba_source
Where name =
UPPER('translate_payment_status')
And owner = 'IMS_APP'
Thanks.
On Thu, Apr 14, 2022, 12:07 PM Kumar, Mukesh
mailto:mku...@peabodyenergy.com>> wrote:
Hi Michael ,
We tried dropping the below values from the function, but it did not help.
Also, the values PAID and MANUALLY PAID constitutes about 60 % of the values in
table , and infact we tried creating the partial index and it did not help.
The Strange thing is that we are trying to run this in oracle as we have done
the migration recently and it is running in less than second with same indexes
and other database objects . I can understand that comparing to oracle is
stupidity, but this is only thing where we can compare.
Below is the query we are running on oracle and comparing in postgres
Below is the query and plan for same
https://explain.depesz.com/s/wktl#stats<https://urldefense.com/v3/__https:/explain.depesz.com/s/wktl*stats__;Iw!!KupS4sW4BlfImQPd!OE7VRYuxv81xKZski81jR9U-OFWiC5_KPW02j0u9iHLcaEbtUo5u_sIfi8VFrToyBiI2A_69MqYrJe97dsUq$>
Any help would be appreciated.
Thanks and Regards,
Mukesh Kumar
From: Michel SALAIS mailto:msal...@msym.fr>>
Sent: Thursday, April 14, 2022 11:45 PM
To: Kumar, Mukesh mailto:mku...@peabodyenergy.com>>;
'Ranier Vilela' mailto:ranier...@gmail.com>>
Cc: pgsql-performa...@postgresql.org<mailto:pgsql-performa...@postgresql.org>;
'MUKESH KUMAR' mailto:mukesh.kuma...@tcs.com>>
Subject: RE: Query Tunning related to function
Hi,
This part of the function is odd and must be dropped:
IF (ret_status = payment_rec)
THEN
ret_status := payment_rec;
I didn’t look really the function code and stopped on the view referenced by
the cursor.
The view (we know it just by its name) used in the function is a black box for
us. Perhaps it is important to begin optimization there!
If values 'PAID' and 'MANUALLYPAID' are an important percentage of table rows
forcing index use is not a good thing especially when it is done with a
non-optimized function.
If rows with values 'PAID' and 'MANUALLYPAID' constitute a little percentage
of the table, then the partial index plus rewriting the query would be much
more efficient
Select
payment_sid_c,
lms_app.translate_payment_status(payment_sid_c) as paymentstatus
from
lms_app.lms_payment_check_request
where
lms_app.translate_payment_status(payment_sid_c) IN ('PAID', 'MANUALLYPAID')
group by
payment_sid_c
If not, you can gain some performance if you rewrite your query to be like this:
Select
payment_sid_c,
lms_app.translate_payment_status(payment_sid_c) as paymentstatus
from
lms_app.lms_payment_check_request
group by
payment_sid_c
having
lms_app.translate_payment_status(payment_sid_c) IN ('PAID', 'MANUALLYPAID')
And you can also try to write the query like this:
Select t.payment_sid_c, lms_app.translate_payment_status(t.payment_sid_c)
From
(
Select
payment_sid_c
from
lms_app.lms_payment_check_request
group by
payment_sid_c
having
lms_app.translate_payment_status(payment_sid_c) IN ('PAID', 'MANUALLYPAID')
) t
Regards
Michel SALAIS
De : Kumar, Mukesh mailto:mku...@peabodyenergy.com>>
Envoyé : jeudi 14 avril 2022 16:45
À : Ranier Vilela mailto:ranier...@gmail.com>>
Cc : pgsql-performa...@postgresql.org<mailto:pgsql-performa...@postgresql.org>;
MUKESH KUMAR mailto:mukesh.kuma...@tcs.com>>
Objet : RE: Query Tunning related to function
Hi Rainer ,
We tried to create the partial ‘index on table but it did not help, and it is
taking approx. 7 sec now.
Also we tried to force the query to use the index by enabling the parameter at
session level
set en
Need help on Query Tunning and Not using the Index Scan
Hi Team, We are facing an issue in running the query which takes at least 30 sec to run in PostgreSQL. We have tried to create the indexes and done the maintenance and still that query is taking same time. Below are the explain plan for the query. https://explain.depesz.com/s/sPo2#html We have noticed that maximum time it is takin is do a Seq Scan on Table ps_delay_statistic which consist of approx. 35344812 records . Can anyone please help on the above issue. Thanks and Regards, Mukesh Kumar
RE: Slow Running Queries in Azure PostgreSQL
Hi Justin , Thanks for your help , After committing 1 parameter , the whole query executed in less than 1 min. Thanks and Regards, Mukesh Kumar -Original Message- From: Justin Pryzby Sent: Wednesday, February 23, 2022 2:57 AM To: Kumar, Mukesh Cc: pgsql-performa...@postgresql.org Subject: Re: Slow Running Queries in Azure PostgreSQL On Tue, Feb 22, 2022 at 02:11:58PM +, Kumar, Mukesh wrote: > -> Hash Join (cost=6484.69..43117.63 rows=1 width=198) (actual > time=155.508..820.705 rows=52841 loops=1)" >Hash Cond: (((lms_doc_property_rights_assoc.doc_sid_c)::text = > (lms_doc_propright_status_assoc.doc_sid_c)::text) AND > ((lms_property_rights_base.property_sid_k)::text = > (lms_doc_propright_status_assoc.property_sid_c)::text))" Your problem seems to start here. It thinks it'll get one row but actually gets 53k. You can join those two tables on their own to understand the problem better. Is either or both halves of the AND estimated well ? If both halves are individually estimated well, but estimated poorly together with AND, then you have correlation. Are either of those conditions redundant with the other ? Half of the AND might be unnecessary and could be removed. -- Justin
Never Ending query in PostgreSQL
Hi Team, Can you please help in tunning the attached query as , i am trying to run this query and it runs for several hours and it did not give any output. I am not able to generate the explain analyze plan as well and it keeps on running for several hours and did not give output. I have attached the query and explain plan without analyze. Please help if nayone has any idea how to tune that query. Regards, Mukesh Kumar qyery.sql Description: qyery.sql "Nested Loop Left Join (cost=74158.47..94986.58 rows=2 width=347)" " -> Nested Loop (cost=74158.19..94985.45 rows=2 width=248)" "-> Nested Loop (cost=74157.77..94984.55 rows=2 width=266)" " -> Nested Loop (cost=74157.35..94983.64 rows=2 width=248)" "Join Filter: ((paybase.payment_sid_k)::text = (a.payment_sid_c)::text)" "-> Nested Loop (cost=74156.93..94983.04 rows=1 width=327)" " Join Filter: (((paygroup.doc_sid_f)::text = (paygroup_1.doc_sid_f)::text) AND ((paygroup.payment_group_sid_k)::text = (paygroup_1.payment_group_sid_k)::text) AND ((paybase.payment_sid_k)::text = (paybase_1.payment_sid_k)::text) AND ((docidassoc.land_contract_id)::text = (docidassoc_1.land_contract_id)::text))" " -> Nested Loop (cost=17409.66..34604.72 rows=1 width=250)" "Join Filter: ((paygroup.doc_sid_f)::text = (docidassoc.doc_sid_c)::text)" "-> Nested Loop (cost=17409.37..34604.37 rows=1 width=218)" " Join Filter: ((paygroup.doc_sid_f)::text = (lms_doc_acquisition_base.doc_sid_c)::text)" " -> Nested Loop (cost=14955.89..31181.19 rows=1 width=160)" "-> Nested Loop (cost=14955.75..31181.03 rows=1 width=162)" " -> Nested Loop (cost=14955.47..31180.73 rows=1 width=123)" "-> Nested Loop (cost=14955.05..31180.28 rows=1 width=86)" " Join Filter: (((itemvendorbase.payment_sid_c)::text = (lineitemacct.payment_sid_c)::text) AND (lineitem.line_item_seq_k = (lineitemacct.line_item_seq_c)::numeric))" " -> Nested Loop (cost=14954.63..31171.38 rows=18 width=64)" "-> Hash Join (cost=14954.21..29828.50 rows=2860 width=55)" " Hash Cond: (((itemvendorbase.payment_sid_c)::text = (lineitem.payment_sid_c)::text) AND ((itemvendorbase.line_item_seq_c)::numeric = lineitem.line_item_seq_k))" " -> Hash Left Join (cost=6942.01..19960.79 rows=345211 width=31)" " Hash Cond: (((itemvendorbase.vendor_number_c)::text || (itemvendorbase.vendor_suffix_c)::text) = ((so_vendor_address_base.ap_vendor_id_lf)::text || (so_vendor_address_base.ap_vendor_suffix_lf)::text))" " -> Seq Scan on lms_payment_item_vendor_base itemvendorbase (cost=0.00..7409.11 rows=345211 width=31)" " -> Hash (cost=5971.87..5971.87 rows=77611 width=10)" " -> Hash Left Join (cost=2617.45..5971.87 rows=77611 width=10)" " Hash Cond: (((so_vendor_address_base.ap_vendor_id_lf)::text || (so_vendor_address_base.ap_vendor_suffix_lf)::text) = (c.lfa1_emnfr)::text)" " -> Hash Join (cost=2306.88..4992.73 rows=77611 width=10)" " Hash Cond: ((so_vendor_address_base.vendor_sid_lf)::text = (so_vendor_base.vendor_sid_k)::text)" " -> Seq Scan on so_vendor_address_base (cost=0.00..2482.11 rows=77611 width=28)" " -> Hash (cost=1517.50..1517.50 rows=63150 width=17)" " -> Seq Scan on so_vendor_base (cost=0.00..1517.50 rows=63150 width=17)" " -> Hash (cost=202.48..202.48 rows=8648 width
