Руководство по Ревью SQL-запросов

Содержание

Руководство по Ревью SQL-запросов#

Ревью SQL-запросов должно начинаться с базовых проверок (явные поля вместо SELECT *, корректные типы, отсутствие ненужных операций) и постепенно переходить к сложным (индексы, JOIN, планы). В PostgreSQL критично держать статистику актуальной, и там, где доступен план, использовать EXPLAIN ANALYZE для выявления узких мест. Также важно учитывать масштабируемость запросов и выбирать подходящие стратегии (до- или пост-агрегация, декомпозиция). Ниже приведены подробные рекомендации и примеры, организованные от простого к сложному.

Общие моменты и плохие практики#

Указывать только нужные столбцы#

Избегайте SELECT *. Явно перечисляйте поля, которые реально нужны приложению.

Цель: Уменьшить объем передаваемых данных, снизить нагрузку на БД.

Оптимизация условий фильтрации#

Проверьте логические ошибки и функции в условиях. Наименее эффективно, если столбец сравнивается через функцию (например, WHERE LOWER(name) = 'abc'), — это разбивает использование индекса. Лучше либо передавать уже приведенное к регистру значение, либо создавать индекс на выражение (CREATE INDEX ON users(LOWER(name)).

Цель: Обеспечить использование индексов, избежать полного сканирования таблиц.

Соответствие типов#

Литералы должны соответствовать типу столбца. Запись column = '123' при column типа integer приведёт к неявному приведению и пропуску индекса. Убедитесь, что строковые литеры заключены в кавычки, а числа — нет. Сравнения с «не тем» типом (или приведение «в широкое numeric») часто отключают индекс или дают плохой план. Кастуйте к реальному типу колонки (bigint vs numeric) и сравнивайте даты как даты (не через to_char(date, 'yyyy').

Цель: Обеспечить использование индексов, избежать неявных приведений типов.

Обработка альтернативных условий#

Запросы вида WHERE cond1 OR cond2 часто приводят к полному сканированию и игнорированию индексов. Рассмотрите вариант с UNION ALL: разделите один запрос на два, каждый из которых может использовать свой индекс. Обязательно контролируйте план запрос при использовании OR, особенно в условиях джойнов.

Цель: Обеспечить использование индексов, избежать полного сканирования таблиц.

Безопасность отрицательных условий#

Лучше избегать WHERE x NOT IN (SELECT ...), т.к. он может вести себя не очевидно при NULL и плохо индексироваться. Используйте NOT EXISTS или LEFT JOIN ... IS NULL.

Коррелированные подзапросы#

Подзапросы в SELECT/WHERE, зависящие от каждой строки основной таблицы, приводят к множественным сканированиям. Например, SELECT COUNT(*) FROM orders o WHERE o.user_id=u.id) лучше заменить на JOIN и GROUP BY. Это устраняет повторное выполнение и часто даёт выигрыш в скорости.

Цель: Устранить повторное выполнение подзапросов, повысить производительность.

Эффективная пагинация данных#

Пагинация не используется в Global ERP из коробки, поэтому рекомендация про OFFSET предназначена для редких случаев. При необходимости разбиения на страницы не используйте OFFSET с большими значениями: чем больше OFFSET, тем дольше PostgreSQL читает (и пропускает) строки. Лучший подход — пагинация с использованием ключа: храните последнее ключевое значение предыдущей страницы и пишите WHERE key < last_key вместо OFFSET. Это почти не зависит от смещения и работает быстро.

Цель: Избежать деградации производительности при пагинации больших наборов данных.

Эффективность выборки первых записей#

При использовании LIMIT убедитесь, что используется индекс для ускорения поиска первых строк (B-tree может выдавать первые N по индексу), при условии что это в принципе возможно.

Цель: Обеспечить быстрое получение первых N строк запроса.

Контроль дублирования и сортировки#

Используйте DISTINCT или ORDER BY только когда нужно. Они могут добавить сортировку или хеширование большого набора. Если сортировка обязательна, то убедитесь, что сортируемые поля включены в индекс (или один из индексов), особенно если есть LIMIT. Обратите особое внимание на DISTINCT, во многих это ошибка проектирования запроса, т.к. следует избегать размножения данных в запросе.

Цель: Избежать излишней сортировки и хеширования, предотвратить размножение данных.

Баланс сортировки и производительности#

Особое место занимает сортировка в интерфейсах. Запросы списков зачастую требуют сортировки, особенно это касается карточных представлений, на закладках которых выводятся списки (например, товарные позиции документа). Без сортировки данные выводятся случайным образом, что создает дискомфорт для пользователя. С точки зрения быстродействия это минус, но принимать решение о сортировке следует исходя из компромисса между быстродействием и требованиями задачи.

Цель: Обеспечить удобный пользовательский опыт, найти баланс между производительностью и требованиями задачи.

Параметры вместо «врезки» значений в SQL#

Если в коде формируете ... IN (1,2,3) через конкатенацию/mkString, это часто ухудшает оптимизацию исполнения плана. Передавайте значения параметрами (в т.ч. массивами) и раскрывайте через =any или unnest(...).
В коде запрещена интерполяция SQL через string interpolation (s"...$id"). Параметры должны передаваться через метод on с использованием Symbol("paramName"). Это гарантирует корректную параметризацию и предотвращает SQL-инъекции.
При использовании Anorm параметры должны передаваться через Symbol("param"), а не через устаревшие символьные литералы param.

Цель: Обеспечить корректную параметризацию, предотвратить SQL-инъекции, улучшить оптимизацию плана.

Минимизируйте количество полей в GROUP BY#

Чем больше полей в GROUP BY, тем тяжелее группировка. Если часть полей «просто справочники/атрибуты» (функционально зависят от ключа), оставляйте в GROUP BY только ключ(и), а остальное берите агрегатами (max(...), min(...)).

Цель: Упростить группировку, снизить нагрузку на БД.

Фильтруйте по индексируемым «бизнес-полям», а не по служебным/производным#

Если в схеме есть «служебные» поля вида *_dz или производные представления данных, часто индекс стоит на «основном» поле (например, scode), а не на «витринном». В WHERE используйте индексируемое поле.

Цель: Обеспечить использование индексов, повысить производительность запросов.

Корректность соединения по ключам#

Не делайте coalesce(...), cast(...), substring(...), арифметику и прочие преобразования прямо в условии JOIN по ключам — это часто убивает использование индекса. Правильнее: нормальное сравнение ключей + отдельная обработка NULL-логики.

Цель: Обеспечить использование индексов при соединении таблиц.

Рекурсивные запросы: защита от циклов обязательна#

Любой WITH RECURSIVE должен иметь защиту от зацикливания: хранить путь (массив/список посещенных узлов) и отсеивать повторное посещение (... != any(path)).

Цель: Предотвратить бесконечные циклы в рекурсивных запросах.

Запрещено выполнять SQL-запросы в методе checkworkability#

Этот метод вызывается при построении интерфейса, и обращения к БД критично влияют на отзывчивость UI. Необходимые данные должны загружаться в onRefresh и передаваться через getVar.

Цель: Обеспечить отзывчивость UI, избежать блокировок интерфейса.

Запрещено выполнять SQL-запросы внутри вложенных циклов.#

Это приводит к экспоненциальному росту количества обращений к БД. Следует использовать один агрегирующий запрос или JOIN.

Цель: Избежать экспоненциального роста числа запросов к БД, сохранить производительность.

Приведение типов в соответствие с типом колонки#

Если значение приходит как текст (jsonb ->>, параметры text, CSV и т.п.), приводите его к типу той колонки, с которой сравниваете или соединяете. Идентификаторы имеют тип int8 (bigint), поэтому каст должен быть ::bigint. При несовпадении типов Postgres выполняет неявный каст колонки (например, e.id::numeric), что приводит к отказу от использования индекса по bigint.

  ```sql
  --  До: каст в numeric → ломает использование индекса по e.id (bigint)
  JOIN bs_employee e
  ON e.id = (t.jattrdatacr ->> 'emp_id')::numeric
  ```

  ```sql
  --  После: каст ровно в тип колонки (bigint)
  JOIN bs_employee e
  ON e.id = (t.jattrdatacr ->> 'emp_id')::bigint
  ```

  ```sql
  --  При риске нечисловых данных (защита от ошибки каста):
  JOIN bs_employee e
  ON e.id = CASE
              WHEN (t.jattrdatacr ->> 'emp_id') ~ '^\d+$'
              THEN (t.jattrdatacr ->> 'emp_id')::bigint
              END
  ```

Цель: Обеспечить использование индексов, избежать неявных приведений типов.

Использование многострочных комментариев `/* … */` вместо `--`#

В SQL-запросах предпочитайте многострочные комментарии /* … */, а не однострочные --. В отчётах PostgresPro и ряде систем мониторинга запросы часто «сплющиваются» в одну строку. В таком формате -- начинает комментировать всё до конца строки, то есть фактически «отрезает» половину запроса, что затрудняет дальнейший анализ и форматирование в DBeaver.

Цель: Сохранить исполняемость запросов при копировании из отчётов, облегчить анализ и форматирование.

Рекомендации:

Используйте /* … */, если не уверены, что переносы строк сохранятся при копировании/логировании.
- Для временного отключения частей запроса в процессе ревью используйте /* … */ — это не сломает исполняемость после копипаста из отчёта.
- Однострочные -- допустимы только в локальной разработке, где форматирование гарантированно сохраняется.

Примеры:

Плохо (после «сплющивания» всё после -- может стать комментарием):

SELECT a, b -- берём нужные поля
FROM t
WHERE id = :id;
### Индексы

После преобразования в одну строку:

SELECT a, b -- берём нужные поля FROM t WHERE id = :id;

Результат: FROM t WHERE... закомментирован, запрос не выполнится.

Хорошо (инлайновый комментарий):

SELECT a, b /* берём нужные поля */
FROM t
WHERE id = :id;

Хорошо (комментирование блока):

SELECT a, b
FROM t
/* временно отключено на период проверки:
JOIN x ON x.id = t.x_id
*/
WHERE id = :id;

Индексы#

Индексы по отдельным столбцам#

Убедитесь, что для часто используемых в WHERE столбцов есть индекс (обычно B-tree). Например, если почти все запросы фильтруют по btrans, создайте CREATE INDEX ON stm_order(btrans). Если индекс отсутствует, план покажет Seq Scan по этой таблице — это сигнал добавить индекс. Фреймворк по умолчанию генерирует индексы на все ссылочные поля, поэтому рекомендация касается в основном фильтров и жойнов по не ссылочным полям.

Цель:** Обеспечить использование индексов, избежать полного сканирования таблиц.

Составные индексы#

Когда запросы часто содержат несколько равенств, объедините их в один индекс. Например, WHERE idContras = 456 AND idUser = 1234, сделайте индекс CREATE INDEX ON table(idContras, idUser). Не перегружайте индекс: обычно достаточно 2–3 столбцов, дальше эффективность падает, но возрастает стоимость поддержки индекса.

Цель: Оптимизировать запросы с множественными условиями, снизить количество индексов.

Покрывающие индексы INCLUDE#

Если запрос выбирает столбцы, не участвующие в фильтре, можно добавить их в индекс через INCLUDE. Например: - CREATE INDEX ON stk_warrant(idContras) INCLUDE(nSum, idState); - Тогда запрос SELECT nSum, idState FROM stk_warrant WHERE idContras = ?` выполнится через Index-Only Scan (берет данные прямо из индекса без обращения к таблице). Такой подход ускоряет чтение, но увеличивает размер индекса.

Рекомендуется добавлять в INCLUDE только редко меняемые столбцы. Внимание. Следует изучить разницу между созданием составного индекса и использованием оператора INCLUDE и пользоваться этими инструментами с учетом их особенностей.

Цель: Ускорить чтение данных, избежать обращений к таблице при Index-Only Scan.

Частичные индексы#

Если запросы фильтруют по условию, которое затрагивает небольшую часть таблицы (например, status='active' для небольшого числа активных строк), создайте индекс с WHERE-предикатом. Например: - CREATE INDEX ON stk_warrant(idType) WHERE status = 'active'; - Такой индекс содержит только строки с status='active', он компактнее и быстрее обновляется (не обновляется при других значениях поля status). Но он будет работать только тогда, когда запрос точно включает условие status='active'. Если условие отличается, индекс игнорируется. Обычно частичные индексы эффективны для статичных фильтров.

Цель: Сократить размер индекса, ускорить обновление для фильтрованных данных.

Индексы на выражения#

При фильтрации по результату функции (f(col)) обычный индекс не работает. Создайте expression index: - CREATE INDEX ON stk_logs ((to_char(dcreatedate_dz, 'YYYY-MM'))); Пример: WHERE to_char(dcreatedate_dz,'YYYY-MM')='2026-02'.

Цель: Обеспечить использование индексов при фильтрации по функциям.

Полнотекстовые и триграмм-индексы#

Редко востребованные индексы, не следует их применять повсеместно, требуется проявлять особое внимание при их использовании. Для поиска подстроки (ILIKE '%зеленый%') обычный индекс бесполезен. В PostgreSQL есть GIN-индекс по to_tsvector (полнотекстовый поиск) и GIN-индекс с gin_trgm_ops (триграммы). Требует расширения pg_trgm. - CREATE INDEX ON mnf_products USING gin(sname gin_trgm_ops); - Это ускорит нечёткий поиск внутри текста. GIN/Trigram особенно важны для LIKE '%...%' и JSON. - GiST-индексы полезны для геометрии и range-типов, а BRIN — для очень больших таблиц со встроенным порядком (например, по дате).

Цель: Обеспечить эффективный поиск по тексту и нечёткий поиск.

Регулярное обновление статистики#

Если есть подозрения неактуальную статистику, то запросите выполнение ANALYZE по нужным таблицам: это даст планировщику верную информацию о распределении данных. Без актуальной статистики даже оптимальные индексы могут не использоваться эффективно.

Цель: Обеспечить актуальность статистики для оптимального плана выполнения.

Индексы: учитывайте блокировки при создании#

Создание индекса на большой таблице — длительная операция; чтение обычно возможно, но запись может блокироваться до окончания построения. Это надо учитывать при ревью (и заранее планировать способ создания индекса).

Цель: Избежать блокировок записи при создании индексов на больших таблицах.

JOIN-стратегии#

Индексы на ключах JOIN#

На столбцы, по которым происходит соединение таблиц, должны быть индексы. Это критично: без индексов соединение двух больших таблиц может стать очень дорогим.

Цель: Обеспечить эффективное соединение таблиц, избежать полного сканирования.

Порядок JOIN#

Если в запросе много таблиц, соединяйте сначала более узкие/фильтрованные таблицы, затем добавляйте большие. Это уменьшает размер промежуточных результатов. Оптимизатор Postgres обычно сам старается выбрать подходящий порядок, но при сложных схемах можно помогать ему: разбить запрос на подзапросы (CTE), вручную упорядочить JOIN. Оптимизатор не всесилен, после 8 таблиц в запросе он перестает переставлять таблицы в поисках оптимального плана исполнения и начинаются магия. Лучше разместить таблицы сверху вниз в том порядке, в каком вы сами планируете исполнение запроса.

Цель: Уменьшить размер промежуточных результатов, помочь оптимизатору выбрать эффективный план.

Оптимизация порядка операций#

По возможности сначала агрегируйте данные, а затем соединяйте. Например, вместо прямого JOIN и GROUP BY на результатах, сначала выполните подзапрос с GROUP BY, а потом JOIN уже по агрегированным данным. Это резко уменьшает объем соединяемых строк. Пример:

-- Неоптимально: агрегация после JOIN
SELECT r.idGds, g.sCaption, SUM(r.nQty) AS qty, SUM(r.nSum) AS sum
FROM btktst_regmat r
JOIN btktst_goods g ON g.id = r.idGds
WHERE r.idGds = :id
GROUP BY r.idGds, g.sCaption;
Лучше:
WITH gr AS (
SELECT idGds, SUM(nQty) AS qty, SUM(nSum) AS sum
FROM btktst_regmat
WHERE idGds = :id
GROUP BY idGds
)
SELECT gr.idGds, g.sCaption, gr.qty, gr.sum
FROM gr
JOIN btktst_goods g ON g.id = gr.idGds;

Во втором варианте JOIN выполняется только по итоговым строкам из подзапроса, что значительно снижает объем операций чтения.

Цель: Уменьшить объем соединяемых строк, снизить нагрузку на БД.

Работа с большими реестрами#

При обработке больших объёмов данных необходимо: - выполнять предварительный session.flush(), - переносить фильтрацию максимально на уровень SQL, - избегать загрузки больших наборов в память приложения.

Цель: Снизить нагрузку на память сервера приложений, повысить производительность за счёт фильтрации на уровне БД.

Коррелированный подзапрос с несколькими полями#

Если из подзапроса нужно вернуть 2+ полей (или эти поля потом участвуют в нескольких местах запроса), не пишите несколько коррелированных подзапросов в SELECT/WHERE. Заменяйте на LEFT JOIN LATERAL (...) x ON true, чтобы подзапрос выполнялся один раз «на родителя», а поля брались из x.*.

Цель: Устранить повторное выполнение подзапросов, повысить производительность.

«Последняя запись»#

Не используйте row_number() over (order by ... desc) и затем WHERE rn = 1 для выбора одной строки: это часто заставляет отсортировать/пройти большой набор, а потом выкинуть всё кроме одной. В таких случаях заменяйте на LEFT JOIN LATERAL ( ... ORDER BY ... DESC LIMIT 1 ), чтобы планировщик мог взять top-1 по индексу. - Индекс под ORDER BY ... LIMIT 1 обязателен - Для паттерна LATERAL + ORDER BY ... DESC LIMIT 1 проверяйте наличие индекса вида (FK, sort_col DESC); при необходимости добавляйте INCLUDE(возвращаемые_поля), чтобы снизить обращения к heap (и приблизиться к index-only).

Цель: Избежать сортировки больших наборов, использовать индекс для top-1.

Массовый «top-1 по ключу»#

Если нужно получить «последнюю строку на ключ» сразу по всей таблице (без parent-таблицы), рассмотрите DISTINCT ON (key) ... ORDER BY key, sort_col DESC как более простой и часто более быстрый вариант, чем row_number() по всей таблице. Пример «последний заказ на каждого клиента»: нужно взять по одной (самой свежей) записи на ключ сразу для всех ключей.

SELECT DISTINCT ON (o.idcustomer)
o.idcustomer,
o.id,
o.ddate,
o.status,
o.total
FROM stm_orders o
WHERE o.ddate >= :date_from
ORDER BY o.idcustomer,
o.ddate DESC,
o.id DESC;
В DISTINCT ON (...) указываете ключ группировки (например, customer_id).
В ORDER BY первым(и) должны идти те же поля, что в DISTINCT ON, а дальше — "кто лучший" (ddate desc) и идентификатор, иначе результат будет нестабилен при равных значениях.
Индекс под этот пример:
CREATE INDEX ON stm_orders(idcustomer, ddate DESC, id DESC)
INCLUDE (status, total);
INCLUDE(status, total) — чтобы вернуть поля без лишних походов в таблицу (если получится index-only).

Цель: Получить последнюю строку на ключ эффективно, использовать индекс для сортировки.

Валидация путей доступа (количество чтений)#

Проверяйте чтобы число операций чтения (буферов) росло линейно с числом возвращаемых строк. Например, регулярный запрос SELECT * FROM stk_warrant d WHERE d.idUser = :user AND d.dCreate BETWEEN :from AND :to должен иметь индекс по (idUser, dCreate). Без него при увеличении диапазона дат сложность может стать мультипликативной (например, O(N*D), где D – число дней) и со временем производительность упадет. Всегда требуйте в WHERE ключевых параметров, которые гарантируют использование индексов и линейный рост чтений.

Цель: Обеспечить линейный рост чтений, избежать мультипликативной сложности.

Партиционирование/секционирование#

Если таблицы разделены (partitioned), убедитесь, что соединение выполняется по партиционному ключу, иначе выполняется сканирование всех секций.

Цель: Избежать сканирования всех секций при партиционированных таблицах.

Денормализация#

Копирование данные в плоские структуры (денормализация) позволяет делать более эффективные выборки. Однако денормализация требует поддержания синхронности копий данных с оригинальными. Чаще всего денормализация используется для сверток в таком случае O( N / K) где N количество записей за период K коэффициент сжатия. В этом случае свертка чаще всего не будет создавать значимую нагрузку на систему. В случае, когда денормализация используется для индексного доступа, может быть значительное увеличение данных для записи, например O (N*U) где U - количество пользователей которым дано право на документ, N - количество документов в системе (растет бесконечно). Если U или N растет со временем жизни системы, такая денормализация легко может быть узким местом в системе. Особенно если необходим массовый пересчет в оперативном контуре (гигантские объемы). Следует всячески избегать денормализации хуже линейной в оперативном контуре, или в контуре без четких лимитов на вычислительные ресурсы. Следует рассмотреть возможность перевода денормализации на ленивый режим (выполнять при обращении к ресурсу) или рассмотреть другие способы оптимизации читающих запросов.

Цель: Избежать денормализации хуже линейной, обеспечить масштабируемость системы.

Фоновые задачи для больших JOIN#

Если соединение очень тяжелое и долгое, рассмотрите выполнение в виде фонового задания вне интерактивного режима. Это особенно актуально, если время выполнения превышает ~3 минуты.

Цель: Избежать блокировки интерактивного режима при тяжелых запросах.

Оптимизация больших запросов#

Декомпозиция и CTE.#

Разбейте большие запросы на шаги с помощью WITH (CTE) или временных таблиц. Помните: не рекурсивные CTE могут быть либо встраиваемыми, либо материализованными. Если подзапрос используется один раз и без побочных эффектов, оптимизатор часто встраивает его и применяет к нему фильтры. Если же CTE вызывается много раз, по умолчанию он материализуется (вычисляется один раз и хранит результат). Можно явно указать NOT MATERIALIZED или MATERIALIZED, чтобы контролировать поведение. NOT MATERIALIZED позволит применять индексы к исходному представлению, если это возможно, а MATERIALIZED — вычислить один раз, и индексов тут уже не будет. Использование материализованных CTE в подзапросах может кратно замедлить выполнение запроса, т.к. доступ к результатам не индексируется (получается обычная временная таблица без индексов). В этом случае следует рассмотреть разбиение запроса на части со вставкой в индексируемую временную таблицу.

Цель: Упростить сложные запросы, контролировать материализацию подзапросов.

Ранняя агрегация/фильтрация#

Применяйте WHERE и GROUP BY как можно раньше, даже перед JOIN-ами. Чем меньше строк передается дальше, тем быстрее JOIN. Используйте подзапросы или CTE для свертки данных перед их объединением. Применяйте фильтры данных как можно раньше, чтобы база не вращала огромные объекты, которые потом усекаются по полям, которые прошли все cte и группировки.

Цель: Уменьшить объем данных на ранних этапах, ускорить выполнение запроса.

Использование памяти#

Для сортировок и хешей на больших наборах увеличьте work_mem. Если данных больше, чем может поместиться в памяти, Postgres перейдет на временные файлы, и производительность сильно упадет. Увеличение work_mem (локально для запроса) позволяет выполнять сортировки/JOIN в памяти.
Пример:

BEGIN; --не обязательно так, главное начать транзакцию, чтобы захватить соединение с БД и настройка по памяти применилась к запросам в транзакции
SET LOCAL work_mem = '256MB';
SELECT ...
FROM ...
JOIN ...
ORDER BY ...;
COMMIT;
Или на всю сессию, если нет возможности начать транзакцию:
SET work_mem = '256MB';
SELECT ...;
RESET work_mem;

Цель: Избежать записи временных файлов на диск, ускорить сортировки и JOIN.

Мониторинг нагрузки#

Если запросы периодически испытывают пиковую нагрузку (упираются в CPU/IO), рассмотрите разбивку на куски или использование очередей задач. Например, суммирование очень большого массива данных можно делать пакетно.

Цель: Избежать пиковой нагрузки на CPU/IO, распределить нагрузку во времени.

Анализ плана выполнения#

Анализ выполнения запросов#

Запускайте EXPLAIN ANALYZE для ключевых запросов. Эта операция реально исполняет запрос и выдает актуальный план с учетом использованных параметров запроса (фильтров). Он покажет реальное время и число строк в каждом узле. Смотрите на «actual time» и «actual rows»: если они сильно отличаются от оценок, возможно, нужна переоценка статистики. Внимание, не выполняйте для insert update delete, т.к. рискуете изменить данные без возможности повторного выполнения запроса. Тестируйте только запросы под этими операторами. Изменения можно откатить, но риск порчи данных при несоблюдении правил сохраняется.
Пример:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) DELETE FROM ... WHERE ...;
ROLLBACK;

Цель: Выявить узкие места запроса, проверить актуальность статистики.

Проблемные узлы#

Ищите Seq Scan на больших таблицах (означает отсутствие индекса). Если Nested Loop соединяет много строк, вероятно, нужно добавить индекс или изменить порядок JOIN. Дорогие Sort говорят о необходимости индексации для ORDER BY или увеличения work_mem.

Цель: Выявить проблемные узлы плана, определить направления оптимизации.

Сравнение версий#

После изменения запроса обязательно сравните планы «до» и «после», убедитесь, что новые узлы (например, дополнительные индексы) действительно применяются.

Цель: Убедиться в эффективности внесённых изменений.

Мутации#

План запроса может показывать идеальную картинку на средах разработки и выполняться плохо на продуктивной среде. Обязательно ставьте подозрительные запросы на дополнительный контроль ответственными за постановку задачи.

Цель: Избежать деградации производительности на продуктивной среде.

Тестирование и мониторинг#

Проверка изменений#

Для каждого изменения перезапускайте запрос несколько раз и смотрите среднее время. Учитывайте что первые запуски подгружают данные в кэш, а также обращайте внимание на побочную нагрузку на БД.

Цель: Получить объективную оценку производительности запроса.

Выявление ресурсоемких запросов#

Используйте модуль pgpro_stats (pg_stat_statements), чтобы находить наиболее ресурсоемкие запросы в базе. Это помогает приоритизировать оптимизацию.

Цель: Выявить наиболее ресурсоемкие запросы для приоритизации оптимизации.

Нагрузочное тестирование#

Если запрос кажется опасным и профиль его использования говорит о высокой нагрузке, тестируйте его на объемах, близких к боевым. Проверяйте, как изменяется время при росте данных и числа пользователей.

Цель: Оценить производительность запроса на боевых объемах данных.

Ревью отчетов pgpro#

Проводите периодический анализ отчетов по БД с топ50-100 запросами, отслеживайте новые запросы, деградацию старых или изменение профиля нагрузки на хорошо работающие запросы. Проводите мероприятия по реорганизации индексов, ключей, неэффективных алгоритмов, ищите плохие архитектурные практики.

Цель: Своевременно выявлять деградацию производительности, поддерживать оптимальную работу БД.

Работа с ORM и OQuery#

Ленивые запросы OQuery#

Ленивые запросы OQuery выполняются только при итерации.

Цель: Избежать преждевременного выполнения запросов.

Условия, зависящие от данных в кеше#

При условиях, зависящих от данных в кеше, требуется:

session.flush();
либо фильтрация после получения результата;
либо использование транзакционного индекса.

Цель: Обеспечить актуальность данных при работе с кешем.

Кэшируемые запросы#

Для кэшируемых запросов использовать .unique() и tryCacheQueryResults()

Цель: Повысить производительность за счёт кэширования результатов запросов.

Особенности selectStatement и @FlushBefore#

При использовании selectStatement в детализациях документов необходимо учитывать аннотацию @FlushBefore.
В ряде случаев её требуется отключать, чтобы избежать преждевременной записи неполностью заполненного объекта в БД.

Цель: Предотвратить конфликты сессии и ошибки при инициализации документа.