При переходе с файловой версии или MS SQL Server на PostgreSQL программисты 1С часто сталкиваются с проблемой: запросы, которые раньше работали за секунды, начинают выполняться крайне медленно или вовсе «зависают». Рассмотрим типичную ситуацию, когда запрос к виртуальной таблице СрезПоследних с левым соединением приводит к катастрофическому падению производительности, и разберем по шагам, как оптимизировать такой код.
Прежде чем переходить к коду, проанализируем ситуацию. Основное различие заключается в работе планировщика запросов (Query Planner). PostgreSQL очень чувствителен к сложности структуры запроса. Когда мы используем виртуальные таблицы (которые сами по себе являются вложенными подзапросами) и соединяем их «на лету», планировщик может ошибиться в оценке количества строк и выбрать неэффективный метод соединения, например, Nested Loop (вложенный цикл) там, где эффективнее использовать Hash Join.
Также важную роль играет разыменование ссылок (обращение через «точку»). Каждое Поле.Свойство в языке запросов 1С превращается в полноценный LEFT JOIN на уровне SQL. Если таких обращений много, общее количество соединений в одном запросе превышает лимиты оптимизатора (например, join_collapse_limit), и он перестает искать оптимальный путь выполнения.
Самый эффективный способ помочь PostgreSQL — это «разрезать» один сложный запрос на несколько простых частей, используя временные таблицы. Разберем это на примере оптимизации запроса к регистру сведений УПЖКХ_СведенияОПроживающих.
Вместо того чтобы соединять две виртуальные таблицы в одном блоке ВЫБРАТЬ, мы сначала поместим данные каждой из них в отдельную временную таблицу. Рассмотрим, как это реализовано в итоговом успешном решении:
// Шаг 1: Выбираем данные первой виртуальной таблицы во временную таблицу
ВЫБРАТЬ
УПЖКХ_СведенияОПроживающихСрезПоследних.Жилец КАК Жилец,
УПЖКХ_СведенияОПроживающихСрезПоследних.Жилец.ФизЛицо КАК ЖилецФизЛицо,
УПЖКХ_СведенияОПроживающихСрезПоследних.ЛицевойСчет КАК ЛицевойСчет,
УПЖКХ_СведенияОПроживающихСрезПоследних.ЛицевойСчет.Адрес.Владелец КАК ЛицевойСчетАдресВладелец,
УПЖКХ_СведенияОПроживающихСрезПоследних.ЛицевойСчет.Адрес КАК ЛицевойСчетАдрес,
ЕСТЬNULL(УПЖКХ_СведенияОПроживающихСрезПоследних.ДатаИзменения, ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)) КАК ДатаИзменения
ПОМЕСТИТЬ СОтбором
ИЗ
РегистрСведений.УПЖКХ_СведенияОПроживающих.СрезПоследних(
&Период,
Проживает И ДатаИзменения <= &Период) КАК УПЖКХ_СведенияОПроживающихСрезПоследних;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Выбираем данные второй виртуальной таблицы во вторую временную таблицу
ВЫБРАТЬ
УПЖКХ_СведенияОПроживающихСрезПоследних.Жилец КАК Жилец,
УПЖКХ_СведенияОПроживающихСрезПоследних.Жилец.ФизЛицо КАК ЖилецФизЛицо,
УПЖКХ_СведенияОПроживающихСрезПоследних.ЛицевойСчет КАК ЛицевойСчет,
УПЖКХ_СведенияОПроживающихСрезПоследних.ЛицевойСчет.Адрес.Владелец КАК ЛицевойСчетАдресВладелец,
УПЖКХ_СведенияОПроживающихСрезПоследних.ЛицевойСчет.Адрес КАК ЛицевойСчетАдрес,
ЕСТЬNULL(УПЖКХ_СведенияОПроживающихСрезПоследних.ДатаИзменения, ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)) КАК ДатаИзменения
ПОМЕСТИТЬ БезОтбора
ИЗ
РегистрСведений.УПЖКХ_СведенияОПроживающих.SrezПоследних(&Период, НЕ Проживает) КАК УПЖКХ_СведенияОПроживающихСрезПоследних;
Почему это работает? Помещая данные в ПОМЕСТИТЬ, мы заставляем СУБД выполнить подзапросы независимо друг от друга. Теперь у PostgreSQL есть плоские таблицы с данными, по которым он может легко собрать статистику и выбрать правильный индекс для их последующего соединения.
Обратите внимание на важный нюанс в коде выше. Мы вынесли разыменование полей (например, ЛицевойСчет.Адрес.Владелец) на этап формирования временных таблиц. В финальном запросе, где происходит ЛЕВОЕ СОЕДИНЕНИЕ, мы используем уже готовые поля временной таблицы.
Разберем финальную часть запроса:
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Соединяем готовые временные таблицы
ВЫБРАТЬ РАЗРЕШЕННЫЕ
ЕСТЬNULL(СОтбором.Жилец, БезОтбора.Жилец) КАК Проживающий,
ЕСТЬNULL(СОтбором.ЖилецФизЛицо, БезОтбора.ЖилецФизЛицо) КАК ФизЛицо,
ЕСТЬNULL(СОтбором.ЛицевойСчет, БезОтбора.ЛицевойСчет) КАК ЛицевойСчет,
ЕСТЬNULL(СОтбором.ЛицевойСчетАдресВладелец, БезОтбора.ЛицевойСчетАдресВладелец) КАК Здание,
ЕСТЬNULL(СОтбором.ЛицевойСчетАдрес, БезОтбора.ЛицевойСчетАдрес) КАК Квартира,
СОтбором.ДатаИзменения КАК ДатаПрибытия,
ВЫБОР
КОГДА СОтбором.ДатаИзменения < БезОтбора.ДатаИзменения
ТОГДА БезОтбора.ДатаИзменения
ИНАЧЕ ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)
КОНЕЦ КАК ДатаВыбытия,
ИСТИНА КАК Проживает
ПОМЕСТИТЬ втПроживающих
ИЗ
СОтбором КАК СОтбором
ЛЕВОЕ СОЕДИНЕНИЕ БезОтбора КАК БезОтбора
ПО (БезОтбора.Жилец = СОтбором.Жилец);
Выясним причину ускорения: здесь ЛЕВОЕ СОЕДИНЕНИЕ происходит по простому условию Жилец = Жилец. Планировщику больше не нужно одновременно вычислять итоги среза последних и выполнять многоуровневые соединения ссылок. Это снижает нагрузку на процессор и память сервера СУБД.
При работе с ЛЕВОЕ СОЕДИНЕНИЕ на PostgreSQL важно избегать сравнения неопределенных значений (NULL) в секциях ГДЕ или ВЫБОР. Если планировщик видит потенциальный NULL, он может отказаться от использования индекса.
Рекомендации:
ЕСТЬNULL, если планируете использовать их в дальнейших вычислениях.ВЫБОР...КОНЕЦ обязательно добавляйте секцию ИНАЧЕ, чтобы результат поля был строго типизирован (например, пустая дата вместо значения NULL).В исключительных случаях, даже при использовании временных таблиц, стандартная виртуальная таблица СрезПоследних с параметром условия может работать неоптимально. Рассмотрим альтернативный подход: получение максимального периода вручную.
Проанализируем ситуацию на примере получения назначенных начислений:
// Сначала получаем последние периоды для каждой комбинации измерений
ВЫБРАТЬ
КВП_НазначенныеНачисления.Объект КАК Объект,
КВП_НазначенныеНачисления.Услуга КАК Услуга,
МАКСИМУМ(КВП_НазначенныеНачисления.Период) КАК Период
ПОМЕСТИТЬ ВсеНачисления
ИЗ
РегистрСведений.КВП_НазначенныеНачисления КАК КВП_НазначенныеНачисления
ГДЕ
КВП_НазначенныеНачисления.Период <= &ДатаНачала
СГРУППИРОВАТЬ ПО
КВП_НазначенныеНачисления.Объект,
КВП_НазначенныеНачисления.Услуга;
////////////////////////////////////////////////////////////////////////////////
// Затем соединяем с физической таблицей регистра для получения ресурсов
ВЫБРАТЬ
КВП_НазначенныеНачисления.Услуга КАК Услуга,
КВП_НазначенныеНачисления.Объект КАК Объект
ИЗ
ВсеНачисления КАК ВсеНачисления
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.КВП_НазначенныеНачисления КАК КВП_НазначенныеНачисления
ПО ВсеНачисления.Объект = КВП_НазначенныеНачисления.Объект
И ВсеНачисления.Услуга = КВП_НазначенныеНачисления.Услуга
И ВсеНачисления.Период = КВП_НазначенныеНачисления.Период
ГДЕ
КВП_НазначенныеНачисления.Действует = ИСТИНА;
Такой подход может показаться более громоздким, но на практике он часто работает в десятки раз быстрее виртуальной таблицы на PostgreSQL, так как позволяет СУБД использовать наиболее эффективные индексы по полям Период и Измерения.
Если программная оптимизация запросов не помогает, необходимо проверить системные настройки сервера. Посмотрим на критически важные параметры в файле postgresql.conf:
1.1. Стандартное значение 4.0 заставляет Postgres игнорировать индексы там, где они нужны.20 или 30 может помочь планировщику лучше обрабатывать запросы 1С с большим количеством соединений.Подведем итог: Для стабильной работы 1С на PostgreSQL следует придерживаться правила «один сложный запрос = пакет из нескольких простых временных таблиц». Это гарантирует предсказуемое время выполнения и стабильность системы даже при росте объема данных.