Message-ID: <7ea8e99b-7551-94a0-fb91-91f75e062c44@enterprisedb.com>
Date: Tue, 1 Mar 2022 16:39: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: "Kumar, Mukesh" <MKumar@peabodyenergy.com>,
 Jeff Janes <jeff.janes@gmail.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>
 <01028050-efd3-06af-6ffd-57722acb9051@enterprisedb.com>
 <CH0P221MB04741DF0AECB008C1B2C657EDE029@CH0P221MB0474.NAMP221.PROD.OUTLOOK.COM>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: 
 <CH0P221MB04741DF0AECB008C1B2C657EDE029@CH0P221MB0474.NAMP221.PROD.OUTLOOK.COM>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/7ea8e99b-7551-94a0-fb91-91f75e062c44%40enterprisedb.com>
Precedence: bulk

On 3/1/22 16:01, Kumar, Mukesh wrote:
> 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
> 

Queries without explain (or even better "explain analyze") are useless.
We don't have the data, we don't know what the executed plan is, we
don't know what plan might be a better one.

There's a wiki page about reporting slow queries (what info to include,
etc):

https://wiki.postgresql.org/wiki/Slow_Query_Questions

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

You need to add individual tables, not a view which is itself a join of
10+ tables. The idea is that you start with a fast query, add tables one
by one (in the join order from the explain). You'll be able to do
EXPLAIN ANALYZE and watch estimate accuracy, and then at some point it
gets much slower, which is the join that causes trouble. But you might
still be able to do explain analyze.

So looking at the explain plan you shared before, you'd start with a
join of so_vendor_address_base + so_vendor_base, and then you'd add

- sapecc_lfa1_assoc
- lms_payment_item_vendor_base
- lms_payment_line_item_base
- lms_payment_check_request
- lms_pay_line_item_acct_base
- ...

(in this order). I'd bet "lms_payment_check_request" is where things
start to go south.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company