Re: Fwd: different execution time for the same query (and same DB status)

On Sat, Mar 06, 2021 at 10:40:00PM +0100, Francesco De Angelis wrote:
> The problem is the following: the query can take between 20 seconds and 4
> minutes to complete. Most of times, when I run the query for the first time
> after the server initialisation, it takes 20 seconds; but if I re-run it
> again (without changing anything) right after the first execution, the
> probability to take more than 4 minutes is very high.

On Tue, Mar 09, 2021 at 11:58:05PM +0100, Francesco De Angelis wrote:
> With such a value, I noticed also the following phenomenon: in addition to
> variable execution times (as previusly stated, the range is between 20
> seconds and 4 minutes),

You said it takes between 20s and 4min (240s), but both the explain analyze
show ~1300s.

explain analyze can be slower than the query, due to timing overhead.
Is that what's happening here? You could try explain(analyze,timing off,buffers).
You should send a result for the "20sec" result, and one for the "4min" result,
to compare.

I assume the crash is a result of OOM - you could find the result in dmesg
output ("Out of memory: Killed process") or the postgres logfile will say
"terminated by signal 9: Killed". It's important to avoid setting work_mem so
high that the process is killed and has to go into recovery mode.

--
Justin