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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx>
Envoyé : samedi 6 mars 2021 22:40
À : pgsql-performance@xxxxxxxxxxxxxxxxxxxx
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 = 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

 

 

 

 

 


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux