Message-ID: <01028050-efd3-06af-6ffd-57722acb9051@enterprisedb.com>
Date: Tue, 1 Mar 2022 15:05:12 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.5.0
Subject: Re: Never Ending query in PostgreSQL
Content-Language: en-US
To: Jeff Janes <jeff.janes@gmail.com>,
 "Kumar, Mukesh" <MKumar@peabodyenergy.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
References: 
 <CH0P221MB04744E424D17609A1F8B814BDE009@CH0P221MB0474.NAMP221.PROD.OUTLOOK.COM>
 <CAMkU=1wy1ipWWLYKCDgZBU-+956Av8v_St=Nomk5sK59W=159g@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: 
 <CAMkU=1wy1ipWWLYKCDgZBU-+956Av8v_St=Nomk5sK59W=159g@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/01028050-efd3-06af-6ffd-57722acb9051%40enterprisedb.com>
Precedence: bulk

On 2/27/22 18:20, Jeff Janes wrote:
> 
> On Sun, Feb 27, 2022 at 7:09 AM Kumar, Mukesh <MKumar@peabodyenergy.com
> <mailto:MKumar@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: http://www.enterprisedb.com
The Enterprise PostgreSQL Company