Re: AW: postgresql long running query

On Wed, Dec 8, 2021 at 10:19 AM liam saffioti <liam(dot)saffiotti(at)gmail(dot)com>
wrote:

> Hi everyone,
>
> Thank you for your help. But my query is not always slow. It works fast
> under normal conditions, but in a way that I don't understand it works very
> slow at some times.
> I think there may be a different reason why a query that takes 800ms, in
> general, takes 18 minutes at a time.
>

As previously asked, do you have log_lock_waits turned on? If so, do you
see entries in the db server's log file?

Also, we really could use the plan capture of the slow execution, not of
the same query except when it is not slow. The only way of to get that
reliably while also getting the original output of the query (which I
assume you need) is with auto_explain, with settings like:

auto_explain.log_min_duration = '600s'
auto_explain.log_analyze=on
auto_explain.log_buffers=on
auto_explain.log_nested_statements=on

Then go trolling through the log file. Note that this does cause every
query run on your system to take detailed timings of each step, which can
slow down the entire system, especially if you are on a system with slow
user access to the clock (common with old hardware or old kernels). But I
think the extra info you get is well worth the extra load. You could
leave auto_explain.log_analyze=off
globally, then turn it on only in the specific script file you are seeing
the problem with.

Cheers,

Jeff