День 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