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> 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 ( 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: 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 |
