Date: Wed, 10 Mar 2021 02:27:53 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Francesco De Angelis <franc.tozzus@gmail.com>
Cc: pgsql-performance@lists.postgresql.org,
	Michael Lewis <mlewis@entrata.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Julien Rouhaud <rjuju123@gmail.com>,
	Michel SALAIS <msalais@msym.fr>
Subject: Re: Fwd: different execution time for the same query (and same DB
 status)
Message-ID: <20210310082753.GR2021@telsasoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAHWr2Cfhfx=9CUP1gKPY-9aw02UrijsE=YKD3FcmxfYZoSZTHA@mail.gmail.com>
 <CAHWr2CeC_Q+kOBY7O3HmX1qVRHch26yiDw6HeUZGGaBpANubVQ@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210310082753.GR2021%40telsasoft.com>
Precedence: bulk

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