From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Kumar, Mukesh <MKumar@peabodyenergy.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: View taking time to show records
Date: Fri, 25 Mar 2022 11:43:20 +0100
Message-ID: <3de146f0ea4ae3bab84be8e1c888d64d11ca6606.camel@cybertec.at> (raw)
In-Reply-To: <CH0P221MB04748BF130CDFF2625EDB396DE199@CH0P221MB0474.NAMP221.PROD.OUTLOOK.COM>
References: <CH0P221MB04748BF130CDFF2625EDB396DE199@CH0P221MB0474.NAMP221.PROD.OUTLOOK.COM>
On Thu, 2022-03-24 at 15:59 +0000, Kumar, Mukesh wrote:
> We have recently migrated from Oracle to PostgreSQL on version 11.4 on azure postgres PaaS instance.
>
> There is 1 query which is taking approx. 10 secs in Oracle and when we ran the same query it is taking approx. 1 min
>
> Can anyone suggest to improve the query as from application end 1 min time is not accepted by client.
>
> Please find the query and explain analyze report from below link
>
> https://explain.depesz.com/s/RLJn#stats
I would split the query in two parts: the one from line 3 to line 49 of your execution plan,
and the rest. The problem is the bad estimate of that first part, so execute only that, write
the result to a temporary table and ANALYZE that. Then execute the rest of the query using that
temporary table.
Perhaps it is also enough to blindly disable nested loop joins for the whole query, rather than
doing the right thing and fixing the estimates:
BEGIN;
SET LOCAL enable_nestloop = off;
SELECT ...;
COMMIT;
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
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: laurenz.albe@cybertec.at, MKumar@peabodyenergy.com
Subject: Re: View taking time to show records
In-Reply-To: <3de146f0ea4ae3bab84be8e1c888d64d11ca6606.camel@cybertec.at>
* 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