.NET Разработчик: “День 2674. #МоиИнструменты #PG Инструменты Оптимизации Запросов в Post…”

День 2674. #МоиИнструменты #PG
Инструменты Оптимизации Запросов в PostgreSQL. Часть 10

10. pg_qualstats (Статистика Предикатов Запросов)
Что даёт: статистику, какие условия WHERE больше всего выиграют от индексов.

Зачем нужен
Создание индексов — это гадание на кофейной гуще без данных. Какие столбцы на самом деле запрашиваются чаще всего? Какие предикаты больше всего нагружают базу? pg_qualstats отслеживает использование условий WHERE во всех запросах, точно показывая, какие индексы окажут максимальное влияние.

Установка
1. Устанавливаем расширение

CREATE EXTENSION pg_qualstats;

2. Добавляем в postgresql.conf

shared_preload_libraries = 'pg_stat_statements,pg_qualstats'
pg_qualstats.enabled = on
pg_qualstats.track_constants = on

3. Перезапускаем PostgreSQL.

Использование
После работы производственной базы (часы/дни), выполняем запрос на поиск наиболее используемых предикатов:

SELECT 
  qualid,
  queryid,
  userid,
  dbid,
  lrelid::regclass AS table_name,
  lattnum AS column_number,
  opno::regoperator AS operator,
  eval_type,
  count AS execution_count
FROM pg_qualstats
ORDER BY count DESC
LIMIT 20;

Пример вывода:

table_name | column   | operator | execution_count
orders     | customer_id | =     | 450,234
orders     | order_date  | >=    | 234,567
orders     | status      | =     | 123,456
customers  | email       | =     | 89,234

Т.е. "customer_id = ?" использовалось 450 тыс раз. Это явный кандидат на индекс.

Предложения индексов

SELECT 
  v.relname,
  v.attnames,
  sum(v.execution_count) as execution_count,
  sum(v.nbfiltered) as rows_filtered
FROM (
  SELECT 
    lrelid::regclass AS relname,
    array_agg(DISTINCT attname) AS attnames,
    count AS execution_count,
    nbfiltered
  FROM pg_qualstats
  JOIN pg_attribute ON (attrelid = lrelid AND attnum = lattnum)
  WHERE nbfiltered > 100  -- Только избирательные предикаты
  GROUP BY lrelid, lattnum, count, nbfiltered
) v
GROUP BY v.relname, v.attnames
ORDER BY execution_count DESC;

Вывод: предложения в порядке значимости
1. orders(customer_id) – 450 тыс раз, фильтрует 99.9% строк
2. orders(order_date) - 234 тыс раз, фильтрует 95% строк
3. orders(status) - 123 тыс раз, фильтрует 80% строк

Когда использовать
- Большая БД с неясными потребностями в индексировании;
- Необходимы обоснованные решения по индексам;
- Кампания по оптимизации индексов.

Когда отказаться
- Небольшая БД (достаточно ручного анализа);
- Индексы уже хорошо оптимизированы;
- Не используете PostgreSQL.

Скрытая функция
Выявление неиспользуемых индексов (противоположный вариант использования). Объедините pg_qualstats с pg_stat_user_indexes, чтобы найти индексы, которые можно удалить.

SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan AS index_scans,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- не используется
  AND indexrelid NOT IN (
    -- Исключаем индексы из pg_qualstats
    SELECT DISTINCT lrelid 
    FROM pg_qualstats 
    WHERE count > 0
  )
ORDER BY pg_relation_size(indexrelid) DESC;

Вывод: индексы, занимающие место, но не используемые, которые можно удалить.

С осторожностью
Настройки по умолчанию могут вызывать накладные расходы в размере 5-10% при очень высокой частоте запросов в секунду. Решение: снижение накладных расходов путём выборки. В postgresql.conf:

# Отслеживаем 10% запросов
pg_qualstats.sample_rate = 0.1
# Исключаем пользователей/базы
pg_qualstats.exclude_users = 'readonly_user,report_user'

Проверка нагрузки

SELECT 
  pg_stat_statements.query,
  pg_qualstats.count AS qualstats_calls,
  pg_stat_statements.calls AS total_calls,
  (pg_qualstats.count::float / pg_stat_statements.calls) AS tracking_ratio
FROM pg_qualstats
JOIN pg_stat_statements USING (queryid);

Если tracking_ratio > 0.1 на частых запросах, уменьшите sample_rate.

Источник: https://medium.com/@reliabledataengineering/15-sql-optimization-tools-that-make-queries-10x-faster-8629ac451d97