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

With shared_buffers=16GB, pg_top shows:

last pid: 2613; load avg: 0.49, 0.45, 0.37; up 0+00:19:21
16:41:16
16 processes: 16 sleeping
CPU states: 9.2% user, 0.0% nice, 0.2% system, 90.4% idle, 0.1% iowait
Memory: 21G used, 42G free, 29M buffers, 18G cached
DB activity: 39 tps, 0 rollbs/s, 0 buffer r/s, 100 hit%, 3625 row
r/s, 11 row w/s
DB I/O: 0 reads/s, 0 KB/s, 0 writes/s, 0 KB/s
DB disk: 0.0 GB total, 0.0 GB free (100% used)
Swap: 32G free

PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
2114 postgres 20 0 17G 6378M sleep 2:11 0.77% 44.80% postgres:
words words_ru [local] idle
2012 postgres 20 0 17G 6005M sleep 0:55 0.45% 26.30% postgres:
words words_ru [local] idle
2107 postgres 20 0 17G 6175M sleep 1:16 0.02% 0.97% postgres:
words words_ru [local] idle
1184 postgres 20 0 17G 22M sleep 0:00 0.02% 0.97% postgres:
walwriter
2030 postgres 20 0 17G 5948M sleep 1:18 0.00% 0.00% postgres:
words words_ru [local] idle
2013 postgres 20 0 17G 5724M sleep 0:57 0.00% 0.00% postgres:
words words_ru [local] idle
2103 postgres 20 0 17G 5823M sleep 0:52 0.00% 0.00% postgres:
words words_ru [local] idle
2031 postgres 20 0 17G 5619M sleep 0:41 0.00% 0.00% postgres:
words words_ru [local] idle
2029 postgres 20 0 17G 1128M sleep 0:00 0.00% 0.00% postgres:
words words_ru [local] idle
2104 postgres 20 0 17G 948M sleep 0:00 0.00% 0.00% postgres:
words words_ru [local] idle
2106 postgres 20 0 17G 1257M sleep 0:00 0.00% 0.00% postgres:
words words_ru [local] idle
1182 postgres 20 0 17G 231M sleep 0:00 0.00% 0.00% postgres:
checkpointer
1183 postgres 20 0 17G 135M sleep 0:00 0.00% 0.00% postgres:
background writer
1185 postgres 20 0 17G 8696K sleep 0:00 0.00% 0.00% postgres:
autovacuum launcher
2614 postgres 20 0 17G 22M sleep 0:00 0.00% 0.00% postgres:
words words_ru [local] idle
1187 postgres 20 0 17G 6764K sleep 0:00 0.00% 0.00% postgres:
logical replication launcher

On Sat, Feb 20, 2021 at 4:15 PM Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
wrote:

> Thank you all, I will try at first
>
> shared_buffers = 16GB and
>
> index on words_scores(uid, length(word) desc)
>
>
>