Hello,
yes exactly in the previous analyses, as mentioned in the wiki, I ran EXPLAIN (ANALYZE, BUFFERS) query, which took much longer to complete (around 30 minutes) as showed in https://explain.depesz.com/s/gHrb and https://explain.depesz.com/s/X2as .
As you said, I did the new tests with EXPLAIN (ANALYZE, timing off BUFFERS) query, and these are the results:
- First execution: https://explain.depesz.com/s/ynAv
- Second execution: https://explain.depesz.com/s/z1eb
Now they are pretty aligned with the execution time of query (a few seconds more to complete) and the difference between the first and second execution is visible.
Also, from what I can see, the plans are different...
Il giorno mer 10 mar 2021 alle ore 09:27 Justin Pryzby <pryzby@xxxxxxxxxxxxx> ha scritto:
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
