День 2604. #МоиИнструменты #PG Инструменты Оптимизации Запросов в PostgreSQL. Часть 4 4. pg_stat_statements (встроенное расширение PostgreSQL) Что даёт: отслеживание производительности запросов без использования внешних инструментов. Тип: Бесплатное (встроено в PostgreSQL). Зачем: Перед установкой внешних инструментов включите это расширение. Оно уже есть в PostgreSQL, просто не активировано по умолчанию. Отслеживает время выполнения каждого запроса, количество вызовов и использование ресурсов — важные данные для оптимизации. Настройка 1. Включаем (однократно) CREATE EXTENSION pg_stat_statements; 2. Добавляем в postgresql.conf shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.track = all pg_stat_statements.max = 10000 3. Перезагружаем Postgres. Использование Самые медленные по времени запросы: SELECT query, calls, total_exec_time, mean_exec_time, max_exec_time, stddev_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; Вернёт: query | calls | total_exec_time | mean_exec_time SELECT … FROM orders JOIN … | 50,423 | 8,234,567 ms | 163.4 ms UPDATE inventory SET … | 12,345 | 4,567,890 ms | 370.0 ms Запросы с большой вариативностью (непостоянная производительность): SELECT query, calls, mean_exec_time, stddev_exec_time, (stddev_exec_time / mean_exec_time) AS cv FROM pg_stat_statements WHERE calls > 100 ORDER BY cv DESC LIMIT 10; Высокий CV (коэффициент вариативности) означает периодические проблемы с производительностью (блокировки, промахи кэша и т.д.). Когда использовать - Любая БД Postgres (нет причин не включать); - Нужен лёгкий мониторинг; - Нужны метрики на уровне запросов; - Ограничен бюджет. Когда отказаться Особых причин не использовать нет. Скрытая функция Выявление запросов, вызывающих нагрузку на ввод-вывод (промахи кэша): SELECT query, calls, shared_blks_hit, shared_blks_read, (shared_blks_read::float / NULLIF(shared_blks_hit + shared_blks_read, 0)) AS cache_miss_ratio FROM pg_stat_statements WHERE shared_blks_read > 0 ORDER BY shared_blks_read DESC LIMIT 10; Высокий cache_miss_ratio означает, что запрос сканирует данные не в памяти. Оптимизация: улучшить индексы или увеличить размер shared_buffers. С осторожностью У pg_stat_statements буфер фиксированного размера. Проверяем, не достигаем ли мы лимита: sql SELECT pg_stat_statements_info.dealloc AS queries_evicted, pg_stat_statements_info.stats_reset AS last_reset FROM pg_stat_statements_info; При высоком queries_evicted, увеличьте в postgresql.conf pg_stat_statements.max = 50000 (с 10000), перезапустите Postgres. Замечание: увеличится расход памяти (~400 байт на запрос). Источник: https://medium.com/@reliabledataengineering/15-sql-optimization-tools-that-make-queries-10x-faster-8629ac451d97