scary-production/claude-plugins

Тюнинг запросов PostgreSQL

Проверка утверждений — CHECKS.md. Всё ниже относится к обычному PostgreSQL. Сначала узнай версию (SHOW server_version): от неё зависят BUFFERS по умолчанию (PG18), skip scan (PG18) и дефолт hash_mem_multiplier (2.0 с PG15).

Главное правило

Не лечи наугад. Порядок такой:

1. где на самом деле уходит время;
2. какой запрос виноват и сколько раз он вызывается;
3. в каком узле план ошибся в оценке строк;
4. правка;
5. проверка по числу страниц на данных прод-объёма.

Индекс, work_mem и random_page_cost трогай, только если на них указал диагноз. Почему: без профиля чинят «страшный» запрос с долей 1/60 и ничего не выигрывают, а индекс, добавленный наугад, длинному запросу вредит.

Где уходит время

• Жалоба «медленно»: сначала сравни время в БД с тем, что видит пользователь. Если запросы в сумме занимают ~100 мс, а жалуются на минуту, база ни при чём. Запросы не трогай, ищи в приложении, сети, фронте.
• Операция из многих запросов: сними профиль через pg_stat_statements.
  1. Поставь pg_stat_statements.track = all. По умолчанию стоит top, и запросы внутри функций не видны.
  2. Вызови pg_stat_statements_reset(). Нужен суперпользователь или выданное на функцию право. Сброс стирает статистику всех, поэтому делай его только на стенде или в тихое время.
  3. Выполни операцию один раз и отсортируй по total_exec_time.
  4. Смотри calls, shared_blks_hit + shared_blks_read и toplevel. Время строк с toplevel = true уже включает вложенные запросы. Если сложить всё подряд, время посчитается дважды.
• Правь только то, что занимает основную долю. Выигрыш не больше этой доли: подзадача на 1 с из 60 даст максимум 1/60.
• Запросы в pg_stat_statements нормализованы: константы заменены на $1, по сеансам строки не разделить, хранятся агрегаты (среднее, min/max, stddev). Если кажется, что план зависит от значения (крупный клиент против мелкого), возьми конкретные значения и разбери EXPLAIN отдельно. С pg_stat_activity и EXPLAIN VERBOSE строки связываются по queryid, для этого нужен compute_query_id (с PG14; auto включает его, когда загружен pg_stat_statements).
• Через лог: в log_line_prefix нужны %p или %c и время %m. Вложенные запросы видны только через auto_explain. Задай log_min_duration (по умолчанию -1, то есть выключено), включи log_analyze и log_nested_statements (по умолчанию off; менять — суперпользователь или GRANT SET, с PG15). Лог разбирай pgBadger. Для одного сеанса хватит SET log_min_duration_statement (суперпользователь или GRANT SET ON PARAMETER) и LOAD 'auto_explain' (только суперпользователь).

На каких данных мерить

• Не делай выводов на dev-базе с игрушечными данными. План зависит от размера таблиц и статистики, а малая база целиком лежит в кеше. Если запрос быстрый на малой базе, напиши, что это ничего не доказывает.
• Объём и распределение — как в проде. Если в проде персональные данные, копию на стенд не бери. Генерируй синтетику той же формы: сколько строк в главных таблицах и как они распределены по клиентам. В прод ходи только за статистикой (pg_stat_statements, pg_class).
• Планы с константами из прода не вставляй во внешние визуализаторы: в константах бывают персональные данные. Пользуйся локальными инструментами.

Много быстрых вызовов (N+1)

• Если запрос уже быстрый, но за одну операцию вызывается десятки и сотни раз, не ускоряй его. Перепиши задачу в один запрос на всю пачку:

  • вместо цикла запросов по списку id в коде — WHERE id = ANY($1) или батч;
  • разрезы по категориям — count(*) FILTER (WHERE …);
  • чтобы не потерять строки без данных — LEFT JOIN.

  Почему: запрос за 1 мс уже не ускорить, а тысяча таких вызовов тормозит. Например, 27 вызовов, свёрнутые в один запрос, читают ≈60 тыс. страниц вместо ≈900 тыс.

• Если одним запросом не получается, пересматривай доступ к данным в сервисе, а не настройки базы.

Как сравнивать варианты

• Главная метрика — страницы. Запускай EXPLAIN (ANALYZE, BUFFERS) и смотри shared hit + read в верхнем узле: там уже сумма по дочерним. Складывай hit и read, потому что их соотношение зависит от кеша. В PG18 BUFFERS включается с ANALYZE сам.
• Время — только справка. Его сдвигают кеш, нагрузка, железо и сам замер ANALYZE. Второй прогон всегда быстрее и ничего не доказывает. «Стало быстрее» пиши только по нескольким прогонам.
• Страницы не видят нагрузку на процессор: сортировку в памяти, функции, JIT, параллельные планы. Если запрос больше считает, чем читает, проверяй время отдельно.
• Если нужны только строки и буферы, добавь TIMING FALSE: так не будет накладных расходов на замер.

Разбор плана

1. Класс запроса.

   • Короткий: отдаёт мало строк, ответа ждёт человек. Например: карточка объекта, страница списка, поиск по ключу. Ожидай индексный доступ и nested loop. На время разбора поставь SET max_parallel_workers_per_gather = 0.
   • Длинный: читает таблицы почти целиком, сверху агрегация. Например: отчёты, аналитика, выгрузки. Ожидай seq scan и hash join, параллелизм тут нормален.

   Это ожидание, а не закон. Если план не совпал с ожиданием, ищи причину в оценках, а не добавляй индекс.

2. Оценка против факта. Сравни rows с actual rows по узлам. При loops > 1 значение actual rows дано на один запуск, умножай его на loops. Чини самый вложенный узел, где расхождение появилось впервые. Ошибка растёт вверх по плану и приводит к неверному способу доступа и неверному join.

3. Страницы против размера таблиц (для длинных запросов). Сделай ANALYZE. Потом сравни hit + read верхнего узла с суммой relpages таблиц запроса из pg_class. Индексы и TOAST прибавь отдельно, если запрос их читает.

   • Сопоставимо или меньше: данные читаются один раз, план разумный.
   • В разы больше: данные читаются повторно, например Index Scan внутри nested loop.

   Для коротких запросов по индексу этот критерий не годится.

4. Память. Если в BUFFERS есть temp read/written, Sort идёт на диск или у Hash Batches > 1, памяти не хватает. Что делать — в разделе «Что править».

Что править

Сначала оценки и сам запрос, потом память. Стоимости меняй в последнюю очередь.

• Функция над колонкой в условии (starts_at::date = …, date_trunc('day', starts_at) = …, round(col) …). Перепиши в полуоткрытый диапазон starts_at >= $1 AND starts_at < $2. Границы для timestamptz считай в часовом поясе пользователя, а не сервера. Почему: для выражения без статистики планировщик берёт фиксированную селективность (для = это 0,5% строк) и сильно ошибается. Диапазон по самой колонке оценивается по статистике и идёт по индексу. Индекс или статистику по выражению заводи, только если переписать нельзя.
• Проверь, какой индекс выбран. Если индекс составной, а фильтруемая колонка в нём не первая, то до PG18 он читается целиком. В PG18 помогает skip scan (в плане — Index Searches: N), но выгоден он, только когда у первой колонки мало разных значений. Иначе нужен индекс, где эта колонка первая.
• Объявляй составные FK (account_id, user_id), даже если приложение всё проверяет само, и пиши join по всем колонкам FK простыми равенствами. Планировщик по FK оценивает join по нескольким колонкам. Без FK он считает колонки независимыми и сильно ошибается (1000 строк вместо 100 тыс.), а потом выбирает nested loop вместо hash. Если join покрывает FK не целиком (одна колонка — выражением или в WHERE), оценка по FK не применяется. На целевой таблице нужен уникальный ключ по тем же колонкам. Цена — лишние расходы на запись.
• Лишний join в длинном запросе. Если таблица ничего не фильтрует, а связь гарантирована FK и NOT NULL, убери её: часто это самый большой выигрыш. Не рассчитывай, что планировщик уберёт inner join к другой таблице сам, даже при FK: он убирает LEFT JOIN к уникальному ключу, колонки которого запрос не читает, и с PG18 — соединение таблицы с самой собой по уникальному ключу. Без гарантии FK и NOT NULL такой join не трогай: изменится результат.
• Оценки неверны, а переписать запрос нельзя: заведи расширенную статистику или поставь ALTER TABLE … ALTER COLUMN … SET STATISTICS n (по умолчанию 100).
• Памяти не хватает (temp-файлы, Batches > 1). Подними work_mem или hash_mem_multiplier для сессии или роли (SET, ALTER ROLE … SET), но не глобально: work_mem выделяется на каждый узел в каждой сессии. Лимит под hash равен work_mem × hash_mem_multiplier. По умолчанию это 4MB × 2.0 = 8MB; до PG15 множитель был 1.0. Проверь SHOW и считай итоговый лимит.

Гипотеза «планировщик ошибся»

• Проверяй в транзакции: BEGIN; SET LOCAL enable_nestloop = off; (или enable_seqscan), затем EXPLAIN (ANALYZE, BUFFERS), затем ROLLBACK. Сравни по страницам — так видно, есть ли план лучше.
• Не оставляй enable_* ни в конфиге, ни в коде сервиса. Они только отговаривают планировщик от узла и задевают все запросы. Документация называет их грубым временным средством.
• Дальше два пути:
  • Оценки строк неверны — чини оценки (см. «Что править»).
  • Оценки верны, но нужный план систематически не выбирается на нескольких запросах — меняй стоимости. Если SSD или база почти целиком в кеше, снижай random_page_cost с 4.0 до 1.1–2, но не ниже seq_page_cost (1.0). В effective_cache_size ставь реальную оценку: shared_buffers плюс кеш ОС (по умолчанию 4GB). На всю базу меняй только после проверки на нескольких запросах.

Вне скилла

• pg_query_state — нужно патченное ядро.
• plprofiler и plpgsql_check — для логики на PL/pgSQL; здесь логика в приложении.
• Приёмы, которым нужны коммерческие сборки PostgreSQL.