Почему тормозит отчет после перехода с файловой базы 1С на PostgreSQL и как оптимизировать запрос?

← На главную

Перевод информационной базы 1С с файлового режима работы на клиент-серверный вариант под управлением СУБД PostgreSQL нередко преподносит разработчикам неприятный сюрприз: отчеты и алгоритмы, которые мгновенно отрабатывали в файловой базе, начинают выполняться значительно дольше. Разберем по шагам, почему возникает деградация производительности, как особенности трансляции запросов платформой 1С влияют на план выполнения в PostgreSQL и как правильно оптимизировать проблемный код.

Анализ проблемы: почему файловая база прощает ошибки, а PostgreSQL — нет?

В файловом режиме СУБД 1С все операции выполняются локально в едином адресном пространстве процесса без сетевых издержек, передачи промежуточных наборов данных и сложного построения планов выполнения. Когда запрос переносится в клиент-серверную архитектуру, он транслируется платформой 1С в реальный SQL-запрос и передается планировщику PostgreSQL.

Если запрос написан неоптимально, планировщик PostgreSQL может выбрать крайне неэффективный план выполнения (например, последовательное чтение всей физической таблицы Seq Scan вместо поиска по индексам Index Scan или вложенные циклы Nested Loop вместо хеширования). Рассмотрим исходный запрос, вызвавший проблемы:


ВЫБРАТЬ
    ТоварныйЧекТовары.Ссылка КАК Документ,
    ТоварныйЧекТовары.Номенклатура КАК Номенклатура,
    СУММА(ТоварныйЧекТовары.Количество) КАК КоличествоПоДокументу,
    СУММА(ТоварныйЧекТовары.Сумма) КАК СуммаПоДокументу,
    ТоварныйЧекТовары.Номенклатура.Родитель.Наименование КАК ГруппаНоменклатуры
ПОМЕСТИТЬ ВремТовЧек
ИЗ
    Документ.ТоварныйЧек.Товары КАК ТоварныйЧекТовары
ГДЕ
    ТоварныйЧекТовары.Ссылка.Дата МЕЖДУ &НачДата И &КонДата
    И ТоварныйЧекТовары.Ссылка.Проведен = ИСТИНА
    И ТоварныйЧекТовары.Ссылка.СкладОтправитель = &Склад
СГРУППИРОВАТЬ ПО
    ТоварныйЧекТовары.Номенклатура,
    ТоварныйЧекТовары.Ссылка,
    ТоварныйЧекТовары.Номенклатура.Родитель.Наименование
;

ВЫБРАТЬ
    ВремТовЧек.Документ,
    ВремТовЧек.Номенклатура,
    СУММА(ВремТовЧек.КоличествоПоДокументу) КАК КоличествоПоДокументу,
    СУММА(ВремТовЧек.СуммаПоДокументу) КАК СуммаПоДокументу,
    ВремТовЧек.ГруппаНоменклатуры,
    -СУММА(ЕСТЬNULL(ХозрасчетныйОборотыДтКт.СуммаОборот, 0)) КАК СтоимостьПартии,
    -СУММА(ЕСТЬNULL(ХозрасчетныйОборотыДтКт.КоличествоОборот, 0)) КАК КоличествоПоРегистру
ИЗ
    ВремТовЧек КАК ВремТовЧек
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрБухгалтерии.Хозрасчетный.Обороты(
            &НачДата,
            &КонДата,
            Регистратор,
            Счет В ИЕРАРХИИ (&Счет41),
            &ВидыСубконто,
            Организация = &Организация И Субконто2 = &Склад,
            КорСчет В ИЕРАРХИИ (&Счет41),
            &ВидыСубконто
        ) КАК ХозрасчетныйОборотыДтКт
        ПО ВремТовЧек.Документ = ХозрасчетныйОборотыДтКт.Регистратор
            И ВремТовЧек.Номенклатура = ХозрасчетныйОборотыДтКт.Субконто1
СГРУППИРОВАТЬ ПО
    ВремТовЧек.Документ,
    ВремТовЧек.Номенклатура,
    ВремТовЧек.ГруппаНоменклатуры

Ключевая причина: условие «В ИЕРАРХИИ» для плана счетов

Главным узким местом в запросе автора темы стало использование конструкции Счет В ИЕРАРХИИ (&Счет41) и КорСчет В ИЕРАРХИИ (&Счет41) внутри параметров виртуальной таблицы.

Выясним причину: при трансляции выражения В ИЕРАРХИИ платформа 1С строит подзапрос, выбирающий все подчиненные счета из иерархии плана счетов. В реляционных бадах данных и особенно в PostgreSQL это порождает дополнительные вызовы или неоптимальные условия фильтрации. Когда такое условие накладывается одновременно на дебетовый и кредитовый счет внутри виртуальной таблицы оборотов с детализацией по регистратору, планировщик запросов не может точно оценить кардинальность и селективность выборки, что приводит к многократному утяжелению плана выполнения.

Решение: Заменить иерархический отбор либо на точное равенство (если группа не содержит субсчетов или требуется конкретный счет), либо на передачу предопределенного фиксированного массива счетов через условие Счет В (&МассивСчетов):


// В коде модуля перед выполнением запроса:
МассивСчетов = Новый Массив;
МассивСчетов.Добавить(ПланыСчетов.Хозрасчетный.Товары);
МассивСчетов.Добавить(ПланыСчетов.Хозрасчетный.ТоварыВРознице);
Запрос.УстановитьПараметр("МассивСчетов", МассивСчетов);

В тексте запроса в параметрах виртуальной таблицы указываем список:


РегистрБухгалтерии.Хозрасчетный.Обороты(
    &НачДата,
    &КонДата,
    Регистратор,
    Счет В (&МассивСчетов),
    &ВидыСубконто,
    Организация = &Организация И Субконто2 = &Склад,
    КорСчет В (&МассивСчетов),
    &ВидыСубконто
)

Дополнительные критические ошибки и пути их устранения

Помимо условия иерархии, в запросе присутствуют классические архитектурные антипаттерны, усугубляющие поведение СУБД в клиент-серверном режиме:

Разыменование ссылочных полей через точку:
В первом запросе к табличной части документов используются конструкции: ТоварныйЧекТовары.Ссылка.Дата, Ссылка.Проведен, Ссылка.СкладОтправитель. Каждое подобное обращение через точку на уровне СУБД разворачивается в неявное соединение LEFT JOIN с таблицей шапки документа. Обращение ТоварныйЧекТовары.Номенклатура.Родитель.Наименование порождает целую цепочку соединений с таблицей справочника номенклатуры, причем это тяжелое поле дублируется в секции СГРУППИРОВАТЬ ПО.
Соединение с виртуальной таблицей:
Использование прямого соединения таблицы ВремТовЧек с виртуальной таблицей Обороты является серьезным нарушением стандартов разработки. Платформа сначала транслирует сложный подзапрос виртуальной таблицы, а затем выполняет его соединение. Для платформы 1С на PostgreSQL критически важно сначала помещать данные виртуальных таблиц в отдельную временную таблицу с явной индексацией полей связи (ИНДЕКСИРОВАТЬ ПО), и лишь затем выполнять соединение.
Периодичность «Регистратор» в таблице оборотов:
Указание периодичности Регистратор отключает использование предрассчитанных итогов регистра бухгалтерии. Запрос фактически обращается к реальной физической таблице движений (проводок). В сочетании с условиями по субконто внутри виртуальной таблицы это генерирует тяжелые конструкции условий с CASE WHEN.

Итоговый оптимизированный вариант запроса

Перепишем запрос с учетом лучших практик разработки под высоконагруженные СУБД:


// Шаг 1. Отбираем только нужные документы по шапке, избегая неявных JOIN
ВЫБРАТЬ
    ДокументШапка.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВремДокументы
ИЗ
    Документ.ТоварныйЧек КАК ДокументШапка
ГДЕ
    ДокументШапка.Дата МЕЖДУ &НачДата И &КонДата
    И ДокументШапка.Проведен = ИСТИНА
    И ДокументШапка.СкладОтправитель = &Склад

ИНДЕКСИРОВАТЬ ПО
    Ссылка
;

// Шаг 2. Выбираем строки только отобранных документов и получаем родителя номенклатуры
ВЫБРАТЬ
    Товары.Ссылка КАК Документ,
    Товары.Номенклатура КАК Номенклатура,
    СУММА(Товары.Количество) КАК КоличествоПоДокументу,
    СУММА(Товары.Сумма) КАК СуммаПоДокументу,
    Товары.Номенклатура.Родитель КАК ГруппаНоменклатуры
ПОМЕСТИТЬ ВремТовЧек
ИЗ
    ВремДокументы КАК ВремДокументы
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ТоварныйЧек.Товары КАК Товары
        ПО ВремДокументы.Ссылка = Товары.Ссылка
СГРУППИРОВАТЬ ПО
    Товары.Ссылка,
    Товары.Номенклатура,
    Товары.Номенклатура.Родитель

ИНДЕКСИРОВАТЬ ПО
    Документ,
    Номенклатура
;

// Шаг 3. Изолируем выборку проводок во временную таблицу
ВЫБРАТЬ
    ХозрасчетныйОбороты.Регистратор КАК Регистратор,
    ХозрасчетныйОбороты.Субконто1 КАК Номенклатура,
    СУММА(ХозрасчетныйОбороты.СуммаОборот) КАК СуммаОборот,
    СУММА(ХозрасчетныйОбороты.КоличествоОборот) КАК КоличествоОборот
ПОМЕСТИТЬ ВремОборотыРегистра
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Обороты(
        &НачДата,
        &КонДата,
        Регистратор,
        Счет В (&МассивСчетов),
        &ВидыСубконто,
        Организация = &Организация И Субконто2 = &Склад,
        КорСчет В (&МассивСчетов),
        &ВидыСубконто
    ) КАК ХозрасчетныйОбороты
ГДЕ
    ХозрасчетныйОбороты.Регистратор В (ВЫБРАТЬ ВремДокументы.Ссылка ИЗ ВремДокументы)
СГРУППИРОВАТЬ ПО
    ХозрасчетныйОбороты.Регистратор,
    ХозрасчетныйОбороты.Субконто1

ИНДЕКСИРОВАТЬ ПО
    Регистратор,
    Номенклатура
;

// Шаг 4. Финальное соединение подготовленных временных таблиц
ВЫБРАТЬ
    ВремТовЧек.Документ КАК Документ,
    ВремТовЧек.Номенклатура КАК Номенклатура,
    ВремТовЧек.КоличествоПоДокументу КАК КоличествоПоДокументу,
    ВремТовЧек.СуммаПоДокументу КАК СуммаПоДокументу,
    ВремТовЧек.ГруппаНоменклатуры.Наименование КАК ГруппаНоменклатуры,
    -ЕСТЬNULL(ВремОборотыРегистра.СуммаОборот, 0) КАК СтоимостьПартии,
    -ЕСТЬNULL(ВремОборотыРегистра.КоличествоОборот, 0) КАК КоличествоПоРегистру
ИЗ
    ВремТовЧек КАК ВремТовЧек
        ЛЕВОЕ СОЕДИНЕНИЕ ВремОборотыРегистра КАК ВремОборотыРегистра
        ПО ВремТовЧек.Документ = ВремОборотыРегистра.Регистратор
            И ВремТовЧек.Номенклатура = ВремОборотыРегистра.Номенклатура

Такой подход гарантирует линейную масштабируемость: планировщик PostgreSQL соединяет небольшие, заранее отфильтрованные и проиндексированные временные таблицы, что исключает деградацию производительности даже при большом объеме записей в регистре бухгалтерии.