Date: Thu, 8 Jul 2021 09:06:03 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Allan Barrielle <allan.barrielle@gmail.com>
Cc: pgsql-performance@lists.postgresql.org,
	Pierrick Martin <pierrick@mart1.dev>
Subject: Re: ETL - sql orchestrator is stuck when there is not sleep()
 between queries
Message-ID: <20210708140603.GG22043@telsasoft.com>
References: 
 <CANrBv8rnhJkFySe8qqW=bXiBPKKDjhXpdh=airBPgex3bTp-0A@mail.gmail.com>
 <20210708123320.GB22043@telsasoft.com>
 <CANrBv8rNm1bUcpPyUW0DsPyxdSCfTdDDAU3tUhSMB1YKcdSFQw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CANrBv8rNm1bUcpPyUW0DsPyxdSCfTdDDAU3tUhSMB1YKcdSFQw@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210708140603.GG22043%40telsasoft.com>
Precedence: bulk

On Thu, Jul 08, 2021 at 03:49:12PM +0200, Allan Barrielle wrote:
> > Is it true that the SELECTs have no joins in them ?
> 
> Yes there is a lot of LEFT JOIN.
> 
> > It'd be very useful to get "explain analyze" for a working query and for a
> > stuck query.  It sound like the stuck query never finishes, so maybe the second
> > part is impossible (?)
> 
> We run an explain analysis and we see some very interesting stuff going on.
> It seems without explicitly adding a `ANALYZE`, the query has a cost of
> over billions, so the query is not stuck but took forever.
> When I run the same scripts with an ANALYZE right before running the query,
> the query is exec is 50secondes and the cost is normal

It sounds like sometimes autoanalyze processes important tables being queried,
but sometimes it doesn't.

Since there are JOINs involved, you should analyze the tables after populating
them and before querying them.  The same as if it were a temp table, or
anything else.

> The configuration is tuned by aws aurora, [...]

> fsync,off
> full_page_writes,off

really?

> vacuum_cleanup_index_scale_factor,0.1

also interesting