Есть ли способ спросить у PostgreSQL, с какими запросами у него возникают наибольшие сложности?

Question

[AntonKrygin]

Есть мобильное приложение, у него есть бэкэнд с PostgreSQL 9.5. Недавно стали заметны проблемы с производительностью. Хочется быстро найти наиболее проблемные места и выполнить точечные оптимизации.
Вывод top:

top - 18:35:10 up  1:16,  1 user,  load average: 8.77, 7.92, 7.53
Tasks: 121 total,  11 running, 110 sleeping,   0 stopped,   0 zombie
%Cpu(s): 98.3 us,  1.5 sy,  0.0 ni,  0.0 id,  0.0 wa,  0.0 hi,  0.2 si,  0.0 st
KiB Mem :  1015752 total,    85512 free,   558960 used,   371280 buff/cache
KiB Swap:        0 total,        0 free,        0 used.   222584 avail Mem 

PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                                                                             
2927 postgres  20   0  310480  80120  67948 R  19.9  7.9   1:18.87 postgres                                                                                                                            
2935 postgres  20   0  310464  79044  66924 R  19.9  7.8   0:57.41 postgres                                                                                                                            
2945 postgres  20   0  310760  77708  65424 R  19.9  7.7   0:45.32 postgres                                                                                                                            
2930 postgres  20   0  310592  79872  67476 R  19.6  7.9   1:10.93 postgres                                                                                                                            
2931 postgres  20   0  310736  80796  68288 R  19.6  8.0   1:04.56 postgres                                                                                                                            
2932 postgres  20   0  310600  79392  67112 R  19.6  7.8   0:59.41 postgres                                                                                                                            
2943 postgres  20   0  310048  77436  65952 R  19.6  7.6   0:51.44 postgres                                                                                                                            
2944 postgres  20   0  310472  79192  66936 R  19.6  7.8   0:47.25 postgres                                                                                                                            
2947 postgres  20   0  310512  77936  66032 R  19.6  7.7   0:41.64 postgres                                                                                                                            
2940 postgres  20   0  310408  78816  67040 R  19.3  7.8   0:55.47 postgres

Поигрался с shared_buffers и work_mem, но изменений не заметил.
Сейчас shared_buffers = 128мб, work_mem = 24мб.

Answer 1

[Сергей Горностаев]

Можно включить логгирование медленных запросов, а потом запустить эти запросы с EXPLAIN ANALYZE и посмотреть план выполнения.

Comments

[AntonKrygin]

Спасибо!

Answer 2

[Melkij]

Поставить pg_stat_statements, спустя какое-то время посмотреть что тот собрал. Например, нашим отчётом

Comments

[AntonKrygin]

Спасибо!

Answer 3

[vreitech]

самое простое для начала - установите и воспользуйтесь pg_top и/или pg_activity.
ещё можно воспользоваться языком запросов самой СУБД и через psql получить список долгих запросов, но это к гуглу или к тому, кто помнит его (нужный запрос) наизусть.

Comments

[AntonKrygin]

Спасибо!

Answer 4

[Vladimir Goncharov]

Можно ничего не ставя посмотреть какие запросы выполняются сейчас
select * from pg_stat_activity;
Выполнив запрос несколько раз можно понять какие запросы висят долго

Comments

[AntonKrygin]

Спасибо!