Каким образом можно найти и проанализировать медленные запросы в PostgreSQL?

Существует несколько основных способов:

1. Включение логирования медленных запросов в конфигурации PostgreSQL.

   В файле postgresql.conf можно настроить следующие параметры:

   • log_min_duration_statement: Записывает в лог запросы, выполняющиеся дольше указанного времени (в миллисекундах).
   • log_statement: Определяет, какие запросы логировать ('none', 'ddl', 'mod', 'all'). Для анализа медленных запросов обычно используется в сочетании с log_min_duration_statement.

   После изменения postgresql.conf необходимо перезагрузить конфигурацию или сам сервер PostgreSQL. Журналы будут располагаться в каталоге данных PostgreSQL, путь к которому можно узнать из параметра log_directory.

   Анализировать логи можно вручную или с помощью утилит, таких как pgBadger, которая парсит логи и генерирует читабельные отчеты.

2. Использование команды EXPLAIN и EXPLAIN ANALYZE.

   • EXPLAIN: Показывает план выполнения запроса без его фактического выполнения. Это позволяет понять, как база данных планирует получить данные, какие индексы будут использоваться, какие операции будут выполнены (сканирование таблиц, соединения и т.д.).
   • EXPLAIN ANALYZE: Выполняет запрос и показывает фактический план выполнения, включая время, затраченное на каждый этап, количество строк, пройденных каждым узлом плана, и использование памяти. Это дает более точное представление о производительности запроса.

   При анализе вывода EXPLAIN ANALYZE следует обращать внимание на:

   • Типы сканирования (Seq Scan часто указывает на отсутствие нужного индекса).
   • Операции соединения (Nested Loop, Hash Join, Merge Join) и их эффективность.
   • Время, затраченное на каждый узел плана.
   • Использование буферов Shared Buffers.
   • Использование временных файлов.

3. Использование расширения pg_stat_statements.

   Это расширение, поставляемое с PostgreSQL, собирает статистику по выполненным запросам, включая их общее время выполнения, количество вызовов, среднее время выполнения и т.д.

   Чтобы использовать pg_stat_statements, его нужно включить в параметры shared_preload_libraries в postgresql.conf и перезагрузить сервер, а затем выполнить CREATE EXTENSION pg_stat_statements; в каждой базе данных, где необходимо собирать статистику.

   Данные статистики доступны через представление pg_stat_statements. Самый простой способ получить список самых медленных запросов:

   -- Выбрать 10 самых медленных запросов по среднему времени выполнения
   SELECT query, mean_exec_time, calls, total_exec_time
   FROM pg_stat_statements
   ORDER BY mean_exec_time DESC
   LIMIT 10;
   

   -- Выбрать 10 запросов с наибольшим общим временем выполнения
   SELECT query, total_exec_time, calls, mean_exec_time
   FROM pg_stat_statements
   ORDER BY total_exec_time DESC
   LIMIT 10;
   

   pg_stat_statements позволяет выявить запросы, которые чаще всего или дольше всего нагружают систему. Важно учитывать как среднее время выполнения, так и общее время выполнения (зависит от количества вызовов).

Комбинирование этих методов дает наиболее полное представление о производительности запросов в PostgreSQL и помогает выявить узкие места.