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

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(at)telsasoft(dot)com>
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
>