Re: Auto explain after query timeout

On Tue Sep 20, 2022 at 11:34 AM PDT, James Coleman wrote:
> On Tue, Sep 20, 2022 at 2:12 PM Gurjeet <singh(dot)gurjeet(at)gmail(dot)com> wrote:
> >
> > For someone who would like to achieve this in the field today, I believe
> > they can set auto_explain.log_min_duration equal to, or less than,
> > statement_timeout.
>
> Either I'm missing something (and/or this was fixed in a later PG
> version), but I don't think this is how it works. We have this
> specific problem now: we set auto_explain.log_min_duration to 200 (ms)
> and statement_timeout set to 30s, but when a statement times out we do
> not get the plan logged with auto-explain.

My DBA skills are rusty, so I'll take your word for it.

If this is the current behaviour, I'm inclined to treat this as a bug,
or at least a desirable improvement, and see if auto_explain can be
improved to emit the plan on statment_timeout.

From what I undestand, the behaviour of auto_explain is that it waits
for the query to finish before it emits the plan. This information is
very useful for diagnosing long-running queries that are still running.
Many a times, you encounter such queries in production workloads, and
reproducing such a scenario later on is either undesirable, expensive, or
even impossible. So being able to see the plan of a query that has
crossed auto_explain.log_min_duration as soon as possible, would be highly
desirable.

Best regards,
Gurjeet
http://Gurje.et