Переход с MS SQL Server на PostgreSQL часто сопровождается неприятными сюрпризами в виде резкого падения производительности некоторых запросов. Ситуация, когда простейшая выборка из справочника с использованием конструкции В ИЕРАРХИИ или соединение таблиц занимают секунду на одной СУБД и растягиваются на минуты на другой, является классической проблемой при неоптимальной настройке сервера или специфическом написании кода запроса. Разберем подробно, почему так происходит, и как заставить PostgreSQL работать быстро.
Рассмотрим ситуацию, когда при программном выполнении схемы компоновки данных (СКД) и выводе результата в ТаблицаЗначений процесс занимает неоправданно много времени. Проанализируем пример кода, который часто становится источником проблем:
ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(Макет, , , Истина);
ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВКоллекциюЗначений;
ТабЗначений = Новый ТаблицаЗначений;
ПроцессорВывода.УстановитьОбъект(ТабЗначений);
ПроцессорВывода.Вывести(ПроцессорКомпоновки);
На первый взгляд кажется, что тормозит именно метод Вывести(). Однако, если мы проанализируем ситуацию глубже, то выясним, что основное время тратится на выполнение самого запроса в базе данных. Процессор вывода лишь инициирует получение данных, и если СУБД не может быстро вернуть результат, 1С ожидает ответа, что и выглядит как "тормоза при выводе".
Выясним главную причину деградации производительности в предоставленном примере запроса. Проанализируем фрагмент условия соединения:
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура.ДополнительныеРеквизиты КАК НоменклатураДополнительныеРеквизиты
ПО спрНоменклатура.Ссылка.НаименованиеПолное = НоменклатураДополнительныеРеквизиты.Ссылка.НаименованиеПолное
Это классический антипаттерн для PostgreSQL. Соединение по полю НаименованиеПолное (тип Строка неограниченной длины или просто длинная строка) крайне ресурсоемко по нескольким причинам:
Решение: Всегда заменяйте соединения по строкам на соединения по ссылочным полям (Ссылка). Если данные в разных таблицах связаны только по строковому представлению, рассмотрите возможность добавления реквизита с типом Ссылка или, в крайнем случае, индексированного строкового реквизита ограниченной длины с предварительно рассчитанным хешем.
Конструкция В ИЕРАРХИИ — еще одно узкое место. В MS SQL Server она транслируется в достаточно эффективные рекурсивные запросы. В PostgreSQL платформа 1С также использует рекурсивные CTE (Common Table Expressions), но планировщик PostgreSQL не всегда может адекватно оценить количество строк (cardinality), которое вернет рекурсивная часть.
Рассмотрим, как это исправить:
ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Это позволит PostgreSQL построить более точный план выполнения.autovacuum. Если данные часто меняются, планировщик может использовать устаревшие данные о распределении значений, что приведет к выбору неверного метода соединения (например, замена Index Scan на Hash Join там, где это не нужно).Одной из причин "плавающей" производительности (когда запрос то работает 1 секунду, то 5 минут) является механизм JIT (Just-In-Time) compilation. PostgreSQL пытается скомпилировать план запроса в машинный код для ускорения выполнения.
Однако на сложных запросах 1С, содержащих множество соединений и подзапросов, время, затраченное на саму компиляцию плана, может в десятки раз превышать время его выполнения. В новых версиях (PostgreSQL 12-17) JIT включен по умолчанию.
Рекомендация: Попробуйте отключить JIT в файле конфигурации postgresql.conf, установив параметр jit = off. Для многих баз 1С это действие мгновенно убирает необъяснимые задержки на сложных отчетах.
Планировщик PostgreSQL принимает решение о выборе индекса или полном сканировании таблицы на основе "стоимости" операций. По умолчанию настройки ориентированы на медленные HDD-диски, что заставляет сервер избегать использования индексов в сложных ситуациях.
Проанализируем и изменим следующие параметры в postgresql.conf для современного сервера:
random_page_cost = 1.1 (вместо стандартных 4.0). Это скажет серверу, что чтение из произвольного места диска (по индексу) почти так же быстро, как и последовательное чтение. Это критично для SSD и NVMe накопителей.effective_cache_size — установите значение, равное примерно 50-75% от всей доступной оперативной памяти. Это поможет планировщику понять, какой объем индексов может быть закеширован.plan_cache_mode = force_custom_plan. Этот параметр заставляет PostgreSQL пересчитывать план запроса для каждого нового набора параметров (например, для разных групп номенклатуры в отборе), что предотвращает использование "усредненного" неэффективного плана.Посмотрим на финальный текст запроса и выделим моменты для исправления:
ВЫБРАТЬ
спрНоменклатура.Ссылка КАК НоменклатураСсылка,
ЕСТЬNULL(НоменклатураДополнительныеРеквизиты.Значение, ЛОЖЬ) = ИСТИНА КАК УчаствуетВОбмене
ИЗ
Справочник.Номенклатура КАК спрНоменклатура
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ГруппыТоваровУчаствующиеВОбмене КАК ГруппыТоваровУчаствующиеВОбмене
ПО спрНоменклатура.Родитель = ГруппыТоваровУчаствующиеВОбмене.ГруппаТоваров
Обратите внимание на соединение спрНоменклатура.Родитель = ГруппыТоваровУчаствующиеВОбмене.ГруппаТоваров. Если в регистре сведений указана только группа верхнего уровня, то этот запрос не найдет товары во вложенных папках. Именно здесь программисты часто добавляют В ИЕРАРХИИ, что и вызывает замедление.
Рассмотрим правильный алгоритм оптимизации такого запроса:
В ИЕРАРХИИ (только по группам!) получаем плоский список всех подчиненных групп во вторую временную таблицу и индексируем её по полю Ссылка.Справочник.Номенклатура с нашей временной таблицей групп по полю Родитель. Это будет работать мгновенно, так как используется соединение по индексированным ссылочным полям.Подводя итог, отметим, что PostgreSQL требует более дисциплинированного подхода к написанию запросов, чем MS SQL. Отказ от соединений по строкам, контроль за JIT-компиляцией и правильная настройка стоимостных коэффициентов планировщика позволяют добиться производительности, не уступающей, а порой и превосходящей коммерческие СУБД.