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

From: Francesco De Angelis <franc(dot)tozzus(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Fwd: different execution time for the same query (and same DB status)
Date: 2021-03-06 21:40:00
Message-ID: CAHWr2CeC_Q+kOBY7O3HmX1qVRHch26yiDw6HeUZGGaBpANubVQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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 = on
- max_parallel_workers_per_gather = 64
- parallel_setup_cost = 1
- parallel_tuple_cost = 0.001
- work_mem = '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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Michel SALAIS 2021-03-07 14:51:05 RE: different execution time for the same query (and same DB status)
Previous Message val.janeiko 2021-03-05 17:55:37 Slow query performance inside a transaction on a clean database