Re: ETL - sql orchestrator is stuck when there is not sleep() between queries

Поиск
Список
Период
Сортировка
От Justin Pryzby
Тема Re: ETL - sql orchestrator is stuck when there is not sleep() between queries
Дата
Msg-id 20210708140603.GG22043@telsasoft.com
обсуждение исходный текст
Ответ на ETL - sql orchestrator is stuck when there is not sleep() between queries  (Allan Barrielle <allan.barrielle@gmail.com>)
Ответы Re: ETL - sql orchestrator is stuck when there is not sleep() between queries
Список pgsql-performance
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



В списке pgsql-performance по дате отправления:

Предыдущее
От: Allan Barrielle
Дата:
Сообщение: Re: ETL - sql orchestrator is stuck when there is not sleep() between queries
Следующее
От: Allan Barrielle
Дата:
Сообщение: Re: ETL - sql orchestrator is stuck when there is not sleep() between queries