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 <tomas.vondra@xxxxxxxxxxxxxxxx> Sent: Tuesday, March 1, 2022 7:35 PM To: Jeff Janes <jeff.janes@xxxxxxxxx>; Kumar, Mukesh <MKumar@xxxxxxxxxxxxxxxxx> Cc: pgsql-performance@xxxxxxxxxxxxxx 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 > <MKumar@xxxxxxxxxxxxxxxxx <mailto:MKumar@xxxxxxxxxxxxxxxxx>> 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
Attachment:
Tuned_Query (002).sql
Description: Tuned_Query (002).sql
Attachment:
View.sql
Description: View.sql
