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
Date: Thu, 8 Jul 2021 09:06:03 -0500
Message-ID: <20210708140603.GG22043@telsasoft.com> (raw)
In-Reply-To: <CANrBv8rNm1bUcpPyUW0DsPyxdSCfTdDDAU3tUhSMB1YKcdSFQw@mail.gmail.com>
References: <CANrBv8rnhJkFySe8qqW=bXiBPKKDjhXpdh=airBPgex3bTp-0A@mail.gmail.com>
<20210708123320.GB22043@telsasoft.com>
<CANrBv8rNm1bUcpPyUW0DsPyxdSCfTdDDAU3tUhSMB1YKcdSFQw@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, allan.barrielle@gmail.com, pierrick@mart1.dev
Subject: Re: ETL - sql orchestrator is stuck when there is not sleep() between queries
In-Reply-To: <20210708140603.GG22043@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox