Thread: View taking time to show records

On Thu, Mar 24, 2022 at 03:59:54PM +0000, Kumar, Mukesh wrote:
> 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

It's hard to say for sure without seeing real query (query on view is
nice, but we can't tell what is going on there, really) - we'd need to
know definitions of all views and tables that are involved there.

for starters, I'd suggest adding indexes:
1. on so_vendor_address_base (ap_vendor_id_lf || ap_vendor_suffix_lf)
2. on so_vendor_address_base (vendor_type_f)

whether this will fix the problem, can't really tell.

Also - you might want to join slack/irc to have a conversation about it
- there are people in there who can help, and I think that
conversation-style help will be better suited for this particular
problem.

Best regards,

depesz

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

Hi Albe , 

Thanks for the below suggestion , When I ran the query with the parameter , it is taking only 1 sec.

So could you please let me know if I can put this parameter to OFF . at database and it will not create any issues to
queriesrunning in database.
 
 
Could you please share some light on it.

Thanks and Regards, 
Mukesh Kumar

-----Original Message-----
From: Laurenz Albe <laurenz.albe@cybertec.at> 
Sent: Friday, March 25, 2022 4:13 PM
To: Kumar, Mukesh <MKumar@peabodyenergy.com>; pgsql-performance@postgresql.org
Subject: Re: View taking time to show records

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://urldefense.com/v3/__https://explain.depesz.com/s/RLJn*stats__;
> Iw!!KupS4sW4BlfImQPd!Ln-8-n9OcKKifiwKjYcs_JOUo80VTTp5hA9V_-gYjOfDr3DDm
> psmbIY_MQxw5RwQ2ZQtMlobbmvex2CIaJtISv0ZkaSn5w$

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
isthe bad estimate of that first part, so execute only that, write the result to a temporary table and ANALYZE that.
Thenexecute 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
andfixing the estimates:
 

BEGIN;
SET LOCAL enable_nestloop = off;
SELECT ...;
COMMIT;

Yours,
Laurenz Albe
--
Cybertec |
https://urldefense.com/v3/__https://www.cybertec-postgresql.com__;!!KupS4sW4BlfImQPd!Ln-8-n9OcKKifiwKjYcs_JOUo80VTTp5hA9V_-gYjOfDr3DDmpsmbIY_MQxw5RwQ2ZQtMlobbmvex2CIaJtISv1qNNoktA$

On Fri, 2022-03-25 at 14:07 +0000, Kumar, Mukesh wrote:

> > [recommendation to fix the estimate]
> >
> > 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;
> 
> Thanks for the below suggestion , When I ran the query with the parameter , it is taking only 1 sec.
> 
> So could you please let me know if I can put this parameter to OFF . at database and it will not
> create any issues to queries running in database.

That will very likely cause problems in your database, because sometimes a nested loop join
is by far the most efficient way to run a query.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com