Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lIukW-0005co-Fi for pgsql-performance@arkaria.postgresql.org; Sun, 07 Mar 2021 14:51:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lIukU-0000Y4-3P for pgsql-performance@arkaria.postgresql.org; Sun, 07 Mar 2021 14:51:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lIukT-0000Xu-RG for pgsql-performance@lists.postgresql.org; Sun, 07 Mar 2021 14:51:13 +0000 Received: from mout.kundenserver.de ([217.72.192.74]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lIukQ-000615-OV for pgsql-performance@lists.postgresql.org; Sun, 07 Mar 2021 14:51:12 +0000 Received: from ANAT ([78.197.128.121]) by mrelayeu.kundenserver.de (mreue107 [212.227.15.184]) with ESMTPSA (Nemesis) id 1McYP5-1ltLYk1dWv-00cxbQ; Sun, 07 Mar 2021 15:51:07 +0100 From: "Michel SALAIS" To: "'Francesco De Angelis'" , References: In-Reply-To: Subject: RE: different execution time for the same query (and same DB status) Date: Sun, 7 Mar 2021 15:51:05 +0100 Message-ID: <00ce01d71361$4dd83750$e988a5f0$@msym.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00CF_01D71369.AFA06FE0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQLZKg7bjmpg4yhi92osfZv4agi6WgD6PVIZqGybnzA= Content-Language: fr X-Provags-ID: V03:K1:9D2AwbGgZXVpKWU9XkCGZYdO/63EnSh3sdvqmLIRdkp8C9AANgH Y1Nm5y9ayxE0zv9uzPo54ty4QZOeScjeohCKyBTlz9n5UFFCbJScYmZsWwBlGKFwKURqi3M Z877pLD1px2ghyQH8S3lCuNzIeIu1w+a9GU3P6UtxPXvMADtvlKiVG3UAFzss4xT1bRQOdp 6/hqQSxYrHL6v7LpIxm8g== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:sCpdj1g/dr0=:66+eIAzsTYCGTW5Ekoz/yU EVadAHRtnUz1v1yzIx67qMNYLKJMKw1oTLwge09Hv551jEwMCuJ7zg5WSY9AH4KePqGt4AEKt j+okQrifaP7NnyCmlRmuGzrP+hOTcwqZqkTt9Z6BXsnuJvd1yJEBnqkdVRDuP2wn358q1LJA2 nP3XiF6gvrNDXYdXCZglUdbG1sQwCDx/gxM21keoIwI08coj/Ugwnm4xe/jqONQ9nBGoueqv2 friq5WQxHg62yC09QhCa3iIU3lJC764iU4B4cqDcDQh1ipMYirvgIQjZ5pIOhTA09ze6EnKXK q9H+k+ieWPPTwVafZz5K+ZTZ3qNnxE/l6PoRgaIKhLsrQAOdSN6/Af4cwo9/hBzD1aSAjiMq9 b/g8mbqcLp2MZ4ncYvM9H1bR47TioXIGL/tWSqUCwd7ybjN+QXYctvXjE+7AF List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_00CF_01D71369.AFA06FE0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hi, =20 Have you tried to use EXPLAIN ANALYZE at least? It could give valuable information about why this is occurring. =20 Michel SALAIS De : Francesco De Angelis =20 Envoy=C3=A9 : samedi 6 mars 2021 22:40 =C3=80 : pgsql-performance@lists.postgresql.org Objet : Fwd: different execution time for the same query (and same DB = status) =20 =20 Hi all, I would appreciate if somebody could help me understanding why the query = described below takes very different execution times to complete, almost = completely randomly. =20 I have two very "simple" tables, A and B: =20 CREATE TABLE A ( a1 varchar(10) NULL, a2 varchar(10) NULL, v int4 NULL ); CREATE TABLE B ( a1 varchar(10) NULL, a2 varchar(10) NULL, v int4 NULL ); =20 I load A and B with some random numbers (10^7 records each one) and, = when the loading is complete, I run a query that "essentially" counts = the number of the records obtained through a sequence of JOINs, which = has the following form: SELECT count(*) from (... A join B ....) =20 The query does not write/update any value, it is simply a SELECT applied = over the aforementioned tables (plus some unions and intersections). =20 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. My impression is that the "status" of the internal structures of the = DBMS is somehow affected by the first execution, but I cannot figure out = neither what nor how I can fix it. =20 To give some additional information, I can state the following facts: - There are no other processes reading or writing the tables on the = schema and the status of A and B is constant. - During the loading of A and B (process that takes more or less one = minute to complete), I get the following messages: LOG: checkpoints are occurring too frequently (29 seconds apart). HINT: Consider increasing the configuration parameter = "max_wal_size". LOG: checkpoints are occurring too frequently (18 seconds apart) HINT: Consider increasing the configuration parameter = "max_wal_size". - I am running my query through DBeaver and PostgreSQL runs in a Docker = container. =20 The version that I am using is the following: 13.2 (Debian = 13.2-1.pgdg100+1). The only configuration parameters I have changed are the following: - force_parallel_mode =3D on - max_parallel_workers_per_gather =3D 64 - parallel_setup_cost =3D 1 - parallel_tuple_cost =3D 0.001 - work_mem =3D '800MB' =20 I hope somebody could help me, as I really don't know why I am = experiencing such a strange behaviour.=20 =20 Thanks a lot. =20 Best regards, Francesco =20 =20 =20 =20 =20 ------=_NextPart_000_00CF_01D71369.AFA06FE0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Hi,

 

Have you tried to use EXPLAIN = ANALYZE at least?

It could give valuable = information about why this is occurring.

 

Michel = SALAIS

De : Francesco De = Angelis <franc.tozzus@gmail.com>
Envoy=C3=A9 : = samedi 6 mars 2021 22:40
=C3=80 : = pgsql-performance@lists.postgresql.org
Objet : Fwd: = different execution time for the same query (and same DB = status)

 

 

Hi all,

I would appreciate if somebody could help me = understanding why the query described below takes very different = execution times to complete, almost completely = randomly.

 

I = have two very "simple" tables, A and = B:

 

CREATE TABLE A (
a1 varchar(10) NULL,
a2 = varchar(10) NULL,
v int4 NULL
);
CREATE TABLE B (
a1 = varchar(10) NULL,
a2 varchar(10) NULL,
v int4 = NULL
);

 

I = load A and B with some random numbers (10^7 records each one) and, when = the loading is complete, I run a query that "essentially" = counts the number of the records obtained through a sequence of JOINs, = which has the following form:

    SELECT count(*) from (... A join B = ....)

 

The query does not write/update any value, it is = simply a SELECT applied over the aforementioned tables (plus some unions = and intersections).

 

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.

My = impression is that the "status" of the internal structures of = the DBMS is somehow affected by the first execution, but I cannot figure = out neither what nor how I can fix it.

 

To give some additional information, I can state the = following facts:

- There = are no other processes reading or writing the tables on the schema and = the status of A and B is constant.

- During the loading of A and B (process that takes = more or less one minute to complete),  I get the following = messages:
    LOG:  checkpoints are occurring too = frequently (29 seconds apart).

    HINT:  Consider increasing the = configuration parameter = "max_wal_size".

    LOG:  checkpoints are = occurring too frequently (18 seconds apart)

    HINT:  Consider increasing the = configuration parameter = "max_wal_size".

- I am running my query through DBeaver and PostgreSQL = runs in a Docker container.

 

The version that I am using is the following: 13.2 = (Debian 13.2-1.pgdg100+1).

The only configuration parameters I have changed are = the following:

- = force_parallel_mode =3D on

- max_parallel_workers_per_gather =3D = 64

- parallel_setup_cost = =3D 1

- = parallel_tuple_cost =3D 0.001

- work_mem =3D '800MB'

 

I = hope somebody could help me, as I really don't know why I am = experiencing such a strange behaviour.

 

Thanks a lot.

 

Best regards,

Francesco

 

 

 

 

 

------=_NextPart_000_00CF_01D71369.AFA06FE0--