Date: Fri, 25 Mar 2022 11:39:15 +0100
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: "Kumar, Mukesh" <MKumar@peabodyenergy.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: View taking time to show records
Message-ID: <20220325103915.GA21555@depesz.com>
Reply-To: depesz@depesz.com
References: 
 <CH0P221MB04748BF130CDFF2625EDB396DE199@CH0P221MB0474.NAMP221.PROD.OUTLOOK.COM>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: 
 <CH0P221MB04748BF130CDFF2625EDB396DE199@CH0P221MB0474.NAMP221.PROD.OUTLOOK.COM>
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/20220325103915.GA21555%40depesz.com>
Precedence: bulk

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