From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Kumar, Mukesh <MKumar@peabodyenergy.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Never Ending query in PostgreSQL
Date: Tue, 1 Mar 2022 16:39:12 +0100
Message-ID: <7ea8e99b-7551-94a0-fb91-91f75e062c44@enterprisedb.com> (raw)
In-Reply-To: <CH0P221MB04741DF0AECB008C1B2C657EDE029@CH0P221MB0474.NAMP221.PROD.OUTLOOK.COM>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@enterprisedb.com, MKumar@peabodyenergy.com, jeff.janes@gmail.com
Subject: Re: Never Ending query in PostgreSQL
In-Reply-To: <7ea8e99b-7551-94a0-fb91-91f75e062c44@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox