From: | "Kumar, Mukesh" <MKumar(at)peabodyenergy(dot)com> |
---|---|
To: | Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | RE: Never Ending query in PostgreSQL |
Date: | 2022-03-01 15:01:23 |
Message-ID: | CH0P221MB04741DF0AECB008C1B2C657EDE029@CH0P221MB0474.NAMP221.PROD.OUTLOOK.COM |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(dot)vondra(at)enterprisedb(dot)com>
Sent: Tuesday, March 1, 2022 7:35 PM
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>; Kumar, Mukesh <MKumar(at)peabodyenergy(dot)com>
Cc: pgsql-performance(at)postgresql(dot)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
> <MKumar(at)peabodyenergy(dot)com <mailto:MKumar(at)peabodyenergy(dot)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
Attachment | Content-Type | Size |
---|---|---|
Tuned_Query (002).sql | application/octet-stream | 2.1 KB |
View.sql | application/octet-stream | 2.8 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Zakharov | 2022-03-01 15:37:28 | Simple task with partitioning which I can't realize |
Previous Message | Tomas Vondra | 2022-03-01 14:05:12 | Re: Never Ending query in PostgreSQL |