В этой статье описаны действия по настройке и использованию мониторинга производительности БД с помощью расширения pg_stat_statements.

Настройка

1. Открыть в текстовом редакторе конфигурационный файл postgresql.conf
   sudo nano /etc/postgresql/[версия]/main/postgresql.conf
    (путь может отличаться в зависимости от ОС и версии PostgreSQL)
   
2. Найти строку с параметром shared_preload_libraries

   
3. Добавить параметру значение pg_stat_statements


   1. Если других расширений нет
      shared_preload_libraries = 'pg_stat_statements'


   2. Если уже есть расширения
      shared_preload_libraries = 'pgaudit, pg_stat_statements'

      
4. Сохранить изменения в файле
   
   
5. Перезапустить службу PostgreSQL 
   sudo systemctl restart postgresql

6. Подключиться к БД и создать расширение
   CREATE EXTENSION pg_stat_statements;


7. Проверить работу
   SELECT * FROM pg_stat_statements LIMIT 1;

Использование

Для получения статистики нужно подключиться к БД и выполнить один из запросов.

SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

SELECT 
    query,
    calls,
    shared_blks_read + shared_blks_written + temp_blks_read + temp_blks_written AS total_blocks,
    (shared_blks_read + shared_blks_written + temp_blks_read + temp_blks_written) * 8 / 1024 AS total_mb
FROM pg_stat_statements
ORDER BY total_blocks DESC
LIMIT 20;