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

Поиск медленных запросов в PostgreSQL: комплексный подход

Поиск и оптимизация медленных запросов — критически важная задача для поддержания производительности PostgreSQL. Вот полноценная стратегия, которую я применяю в работе:

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

PostgreSQL позволяет настроить автоматическое логирование запросов, превышающих заданный порог времени:

-- В postgresql.conf настройте:
log_min_duration_statement = 1000  -- Логировать запросы медленнее 1 секунды
log_statement = 'none'             -- Не логировать все запросы, только медленные

-- Альтернативно можно настроить в сессии:
SET log_min_duration_statement = 200;  -- 200 мс

Важные параметры:

• log_min_duration_statement — порог времени в миллисекундах
• log_duration — логировать время выполнения всех запросов
• log_statement — контролирует, какие запросы логировать

2. Использование встроенных представлений pg_stat_statements

Модуль pg_stat_statements — самый мощный инструмент для анализа производительности:

-- Активация модуля (требует перезагрузки):
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Топ-10 самых медленных запросов:
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows / calls AS avg_rows,
    shared_blks_hit,
    shared_blks_read
FROM pg_stat_statements 
ORDER BY mean_exec_time DESC 
LIMIT 10;

-- Запросы с наибольшим суммарным временем выполнения:
SELECT 
    LEFT(query, 100) AS query_part,
    total_exec_time,
    calls,
    total_exec_time / calls AS avg_time
FROM pg_stat_statements
WHERE calls > 0
ORDER BY total_exec_time DESC 
LIMIT 15;

Ключевые метрики pg_stat_statements:

• total_exec_time — общее время выполнения
• mean_exec_time — среднее время выполнения
• calls — количество вызовов
• rows — общее количество возвращённых строк
• shared_blks_hit/read — статистика по буферному кешу

3. Мониторинг активных запросов в реальном времени

Для анализа текущей нагрузки используйте системные представления:

-- Все активные запросы и их длительность:
SELECT 
    pid,
    now() - query_start AS duration,
    query,
    state,
    wait_event_type,
    wait_event
FROM pg_stat_activity 
WHERE state = 'active' 
ORDER BY duration DESC;

-- Детальная информация с блокировками:
SELECT 
    a.pid,
    a.query,
    a.query_start,
    now() - a.query_start AS running_for,
    l.locktype,
    l.mode,
    l.granted
FROM pg_stat_activity a
LEFT JOIN pg_locks l ON a.pid = l.pid
WHERE a.state = 'active'
ORDER BY running_for DESC;

4. Анализ планов выполнения с помощью EXPLAIN

Для глубокого анализа конкретного медленного запроса:

-- Получить подробный план выполнения:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM large_table WHERE created_at > '2024-01-01';

-- Варианты формата вывода:
EXPLAIN (ANALYZE, VERBOSE) ...  -- Детальная информация
EXPLAIN (ANALYZE, BUFFERS) ...  -- Статистика по буферам
EXPLAIN (ANALYZE, TIMING) ...   -- Временные метки

Ключевые моменты в плане выполнения:

• Seq Scan вместо Index Scan — признак отсутствия или неиспользования индекса
• Nested Loop с большим количеством строк — потенциальная проблема
• Высокое значение cost или actual time в определённых узлах
• Большое количество Buffers: shared read — чтение с диска вместо кеша

5. Использование расширений и инструментов

Дополнительные инструменты для профессионального анализа:

1. auto_explain — автоматическое логирование планов медленных запросов:

-- В postgresql.conf:
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '2s'
auto_explain.log_analyze = true

1. pgBadger или pgHero — инструменты для анализа логов
2. Percona Monitoring and Management (PMM) — комплексный мониторинг

6. Практическая стратегия поиска

Поэтапный подход, который я рекомендую:

1. Идентификация проблемных запросов через pg_stat_statements
2. Анализ частоты выполнения — иногда редко выполняемый, но очень медленный запрос хуже, чем частый умеренно медленный
3. Изучение планов выполнения для выявления узких мест
4. Проверка индексов — отсутствующие или неиспользуемые индексы
5. Анализ статистики — устаревшая статистика может привести к плохим планам
6. Проверка блокировок — long-running транзакции могут блокировать другие

7. Проактивный мониторинг

Настройте алертинг на основе метрик:

-- Запрос для мониторинговой системы:
SELECT 
    COUNT(*) AS slow_queries_count,
    MAX(now() - query_start) AS longest_query
FROM pg_stat_activity 
WHERE state = 'active' 
AND now() - query_start > INTERVAL '5 seconds';

Рекомендации по порогам:

• Предупреждение: запросы > 1 секунды
• Критично: запросы > 5 секунд
• Экстренно: запросы > 30 секунд

Заключение

Эффективный поиск медленных запросов требует комбинации инструментов: настройки логирования, использования pg_stat_statements для исторического анализа, мониторинга активных запросов в реальном времени и глубокого анализа через EXPLAIN. Важно не только найти медленные запросы, но и понять причину их медленной работы — будь то отсутствие индексов, проблемы с статистикой, блокировки или субоптимальная структура запроса. Регулярный мониторинг и проактивный анализ предотвратят большинство проблем с производительностью до их перерастания в инциденты.