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