Как оптимизировать запрос динамического списка со срезом последних при переходе на PostgreSQL?

← На главную

При миграции информационных баз 1С с СУБД Microsoft SQL Server на PostgreSQL разработчики часто сталкиваются с резким падением производительности динамических списков. Запрос, который быстро отрабатывал на MS SQL, в PostgreSQL может вызывать длительные блокировки или зависания. Особенно остро эта проблема проявляется при использовании виртуальных таблиц среза последних, подзапросов в секциях выборки и условий с логическим оператором ИЛИ.

Разберем детально, почему возникает эта проблема, как ведет себя оптимизатор PostgreSQL и какие шаги необходимо предпринять для ускорения работы динамического списка.

Анализ причин снижения производительности в PostgreSQL

Рассмотрим структуру типового проблемного запроса. Зачастую в динамическом списке используется вычисление признака через подзапрос к виртуальной таблице, а также фильтрация через необязательный параметр:


ВЫБОР
    КОГДА 1 В
            (ВЫБРАТЬ ПЕРВЫЕ 1
                1
            ИЗ
                РегистрСведений.СтатусыКонтрагентов.СрезПоследних(&ТекущаяДата) КАК СтатусыКонтрагентовСрезПоследних
            ГДЕ
                СтатусыКонтрагентовСрезПоследних.Статус = ЗНАЧЕНИЕ(Перечисление.СтатусыКонтрагентов.Потенциальный)
                И Контрагенты.Ссылка = СтатусыКонтрагентовСрезПоследних.Контрагент)
        ТОГДА ИСТИНА
    ИНАЧЕ ЛОЖЬ
КОНЕЦ

Проанализируем основные факторы, приводящие к деградации производительности:

Разворачивание виртуальной таблицы в тяжелый подзапрос: Если в параметрах виртуальной таблицы СрезПоследних(&ТекущаяДата) передается дата, платформа 1С не может прочитать данные из готовой физической таблицы итогов. Вместо этого генерируется сложный вложенный SQL-запрос с поиском максимальной даты и соединениями. Оптимизатор PostgreSQL не всегда может эффективно объединить такой коррелированный подзапрос с основной таблицей.
Использование логики НЕ &Флаг ИЛИ Условие: Наличие предиката ИЛИ в секции ГДЕ мешает СУБД применить сканирование по индексам (Index Scan) и приводит к полному последовательному сканированию таблиц (Seq Scan) для каждой строки.
Влияние ключевого слова ВЫБРАТЬ РАЗЛИЧНЫЕ (DISTINCT): Динамический список читает данные порциями с использованием LIMIT и OFFSET. Использование РАЗЛИЧНЫЕ принуждает СУБД сначала выполнить весь тяжелый запрос целиком, отсортировать все записи для удаления дубликатов и только затем отдать первые 20–50 строк на форму.
Группирующие подзапросы в соединениях: Левые соединения с подзапросами, вычисляющими агрегаты (например, максимальную дату версии объекта с группировкой по ссылке), приводят к расчету агрегатов по всему объему регистра при каждом скролле списка.

Способ 1. Использование физической таблицы итогов среза последних

Выясним, как заставить платформу 1С и PostgreSQL обращаться к готовой физической таблице итогов вместо расчета среза «на лету».

В дереве метаданных конфигуратора откроем свойства регистра сведений СтатусыКонтрагентов и убедимся, что включено свойство «Итоги срез последних».
В тексте запроса динамического списка уберем параметр &ТекущаяДата из круглых скобок вызова виртуальной таблицы: РегистрСведений.СтатусыКонтрагентов.СрезПоследних(). Если в регистре не хранятся записи с будущими датами, срез без указания даты автоматически обращается к физической таблице актуальных итогов, что исключает вложенные расчеты.

Способ 2. Отказ от условий с `ИЛИ` в тексте запроса

Вместо передачи булевых параметров в текст запроса через условие (НЕ &ОтборПоПотенциальным ИЛИ ...) настроим управление отбором через механизм компоновки данных платформы 1С. Это позволит платформе динамически исключать условие из результирующего SQL-запроса, когда отбор не используется.

В тексте запроса динамического списка укажем секцию расширения языка запросов:


{ГДЕ
    (ВЫБОР
        КОГДА 1 В
                (ВЫБРАТЬ ПЕРВЫЕ 1
                    1
                ИЗ
                    РегистрСведений.СтатусыКонтрагентов.СрезПоследних() КАК СтатусыКонтрагентовСрезПоследних
                ГДЕ
                    СтатусыКонтрагентовСрезПоследних.Статус = &Потенциальный
                    И Контрагенты.Ссылка = СтатусыКонтрагентовСрезПоследних.Контрагент)
            ТОГДА ИСТИНА
        ИНАЧЕ ЛОЖЬ
    КОНЕЦ) КАК ПотенциальныйКлиент}

В модуле формы динамически управляем параметром и его использованием:


Если ОтборПотенциальные = "Потенциальные" Тогда
    Список.Параметры.УстановитьЗначениеПараметра("Потенциальный", Перечисления.СтатусыКонтрагентов.Потенциальный);
    Список.Параметры.Элементы.Найти("Потенциальный").Использование = Истина;
Иначе
    Список.Параметры.Элементы.Найти("Потенциальный").Использование = Ложь;
КонецЕсли;

Также отбор можно накладывать непосредственно через коллекцию отборов списка:


ЭлементОтбора = Список.Отбор.Элементы.Добавить(Тип("ЭлементОтбораКомпоновкиДанных"));
ЭлементОтбора.ЛевоеЗначение = Новый ПолеКомпоновкиДанных("ПотенциальныйКлиент");
ЭлементОтбора.ВидСравнения = ВидСравненияКомпоновкиДанных.Равно;
ЭлементОтбора.ПравоеЗначение = Истина;
ЭлементОтбора.Использование = (ОтборПотенциальные = "Потенциальные");

Способ 3. Пакетные запросы и временные таблицы в динамическом списке

Начиная с версии платформы 8.3.8, в динамических списках поддерживается использование пакетных запросов и временных таблиц. Это позволяет заранее отобрать нужные данные во временную таблицу и проиндексировать их перед соединением с основной таблицей.

Рассмотрим структуру пакетного запроса:


ВЫБРАТЬ
    СтатусыКонтрагентовСрезПоследних.Контрагент КАК Контрагент
ПОМЕСТИТЬ ВТ_Потенциальные
ИЗ
    РегистрСведений.СтатусыКонтрагентов.СрезПоследних(, Статус = &СтатусПотенциальный) КАК СтатусыКонтрагентовСрезПоследних
ИНДЕКСИРОВАТЬ ПО
    Контрагент
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Контрагенты.Ссылка КАК Ссылка,
    Контрагенты.Наименование КАК Наименование,
    ВЫБОР
        КОГДА ВТ_Потенциальные.Контрагент ЕСТЬ НЕ NULL
            ТОГДА ИСТИНА
        ИНАЧЕ ЛОЖЬ
    КОНЕЦ КАК Потенциальный
ИЗ
    Справочник.Контрагенты КАК Контрагенты
    {ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Потенциальные КАК ВТ_Потенциальные
    ПО Контрагенты.Ссылка = ВТ_Потенциальные.Контрагент}
ГДЕ
    НЕ Контрагенты.ЭтоГруппа
{ГДЕ
    ВТ_Потенциальные.Контрагент ЕСТЬ НЕ NULL КАК ТолькоПотенциальные}

Заключение левого соединения в фигурные скобки {ЛЕВОЕ СОЕДИНЕНИЕ ...} делает его необязательным: соединение будет выполняться СУБД только тогда, когда поле выведено на форму или по нему установлен отбор.

Способ 4. Оптимизация структуры метаданных и настроек PostgreSQL

Помимо оптимизации текста запроса, важно проверить структуру индексов и параметры СУБД:

Индексация измерений регистра: В регистре сведений СтатусыКонтрагентов измерение Контрагент должно быть расположено первым по порядку либо для него должно быть установлено свойство «Ведущее». Это гарантирует построение эффективного кластерного индекса.
Денормализация: Если признак статуса или дата последнего изменения объекта критически важны в динамическом списке с миллионами записей, рекомендуется вынести их в реквизиты самого справочника Контрагенты и актуализировать при записи связанных объектов.
Параметр random_page_cost: В конфигурационном файле postgresql.conf при работе на твердотельных накопителях (SSD/NVMe) рекомендуется уменьшить параметр random_page_cost со стандартного 4.0 до 1.1–1.5. Это помогает планировщику PostgreSQL чаще выбирать индексный поиск вместо последовательного чтения.
Актуализация статистики: После миграции данных обязательно выполните команду ANALYZE для обновления статистики распределения данных в таблицах информационной базы.