Re: Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached

Yes, Michael, that I have noticed too, but should have written more in my
original mail.

The query when I try it does run in 15ms, but evening logs show the query
(I think only small percentage of it) running 1-3s.

At the same time my CentOS 8 server with 64 GB RAM is never loaded, the
load average show by top is 0.35-0.45.

What could be the reason, does PostgreSQL 13.2 has some scheduled task
maybe?

I have only changed few lines in the postgresql.conf:

# diff postgresql.conf.OLD postgresql.conf
64c64
< max_connections = 100 # (change requires restart)
---
> max_connections = 120 # (change requires restart)
130c130
< #work_mem = 4MB # min 64kB
---
> work_mem = 8MB # min 64kB
132c132
< #maintenance_work_mem = 64MB # min 1MB
---
> maintenance_work_mem = 128MB # min 1MB
393c393
< #effective_cache_size = 4GB
---
> effective_cache_size = 8GB
410,411c410,411
< #from_collapse_limit = 8
< #join_collapse_limit = 8 # 1 disables collapsing of explicit
---
> from_collapse_limit = 24
> join_collapse_limit = 24 # 1 disables collapsing of explicit
469c469
< #log_min_messages = warning # values in order of decreasing
detail:
---
> log_min_messages = notice # values in order of decreasing
detail:
497c497
< #log_min_duration_statement = -1 # -1 is disabled, 0 logs all
statements
---
> log_min_duration_statement = 1000 # -1 is disabled, 0 logs all
statements

And I have pgbouncer in front of the PostgreSQL:

diff pgbouncer.ini.OLD pgbouncer.ini
12a13,15
> words_de = host=/tmp user=xxx password=xxx dbname=words_de
> words_en = host=/tmp user=xxx password=xxx dbname=words_en
> words_ru = host=/tmp user=xxx password=xxx dbname=words_ru
115a119
> ;; auth_type = md5
148c152
< ;server_reset_query = DISCARD ALL
---
> server_reset_query = DISCARD ALL
156c160
< ;ignore_startup_parameters = extra_float_digits
---
> ignore_startup_parameters = extra_float_digits
180c184
< ;max_client_conn = 100
---
> max_client_conn = 1000
185c189
< ;default_pool_size = 20
---
> default_pool_size = 100

Or is maybe everything ok...? The daily gzipped dump grows big:

939343358 Dec 31 01:33 words_ru-Dec.sql.gz
1221682336 Jan 31 01:33 words_ru-Jan.sql.gz
1423324283 Feb 20 01:34 words_ru-Feb.sql.gz

and being an inexperienced pg admin I am a bit worried.

Thank you
Alex

>