Date: Sun, 6 Oct 2019 22:30:39 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: nikhil raj <nikhilraj474@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>,
	pgsql-performance@lists.postgresql.org
Subject: Re: Query went slow all of sudden. ON V 11.3
Message-ID: <20191006203039.66or5a6c7avjyhcc@development>
References: 
 <CAG1ps1yzDsdpkoasOgAQOm=TZksfDWS1d-DXt0ufKZ_7CabH=w@mail.gmail.com>
 <20191004132044.GB11241@telsasoft.com>
 <CAG1ps1wtQt=Z=jmXM45ET5zoBNZmAni_Z+7AdSoRpKEss7yDbA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Disposition: inline
In-Reply-To: 
 <CAG1ps1wtQt=Z=jmXM45ET5zoBNZmAni_Z+7AdSoRpKEss7yDbA@mail.gmail.com>
User-Agent: NeoMutt/20180716
Precedence: bulk

On Fri, Oct 04, 2019 at 07:28:54PM +0530, nikhil raj wrote:
>Hi Justin,
>
>Its been executing for 35 + mins due to statement time out its getting
>canceled.
>

Well, without a query plan it's really hard to give you any advice. We
need to see at least EXPLAIN output (without analyze) to get an idea of
how the query will be executed. Even better, disable the statement
timeout in the session and dive use EXPLAIN ANALYZE. Of course, it's
unclear how long it'll run.

Earlier you mentioned the query started running fast after you recreated
one of the tables. That likely means the table (or the indexes on it)
are getting bloated over time. Try looking at the sizes of those objects
(and maybe use pgstattuple to get more detailed statistics before
rebuilding it next time.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services