Почему падает СУБД PostgreSQL при выполнении запросов в 1С и как правильно заменить ПОЛНОЕ СОЕДИНЕНИЕ на ОБЪЕДИНИТЬ ВСЕ?

← На главную

Каждый разработчик 1С рано или поздно сталкивается с ситуацией, когда "лёгкий" тестовый запрос в консоли неожиданно вешает всю систему, делает базу недоступной для всех пользователей и отправляет СУБД в глубокаут. Особенно часто это происходит при работе с СУБД PostgreSQL, которая крайне чувствительна к неоптимальным запросам, содержащим каскадные полные соединения (ПОЛНОЕ СОЕДИНЕНИЕ или FULL OUTER JOIN).

В этой статье мы подробно разберем, почему один некорректный запрос способен "уронить" базу данных размером в десятки гигабайт, что происходит с СУБД PostgreSQL в момент аварии, почему она уходит в "режим восстановления" (Crash Recovery), и как правильно переписать такой запрос с использованием конструкции ОБЪЕДИНИТЬ ВСЕ, чтобы он выполнялся за доли секунды без риска для стабильности сервера.

Анатомия катастрофы: почему PostgreSQL уходит в "режим восстановления"

Когда при попытке войти в 1С вы видите пугающие сообщения: "Сервер баз данных не обнаружен" и "ВАЖНО: система баз данных в режиме восстановления", это означает, что главный процесс PostgreSQL экстренно перезапустился после критического сбоя. Давайте проанализируем по шагам, как именно неоптимальный запрос приводит к такому результату:

Нехватка оперативной памяти и запуск OOM-Killer. Тяжелый запрос, связывающий несколько таблиц "в бок" через полные соединения, требует колоссальных ресурсов для построения планов выполнения и хэш-таблиц в памяти. Если сервер работает под управлением ОС Linux, при исчерпании физической оперативной памяти (RAM) срабатывает системный защитный механизм Out-Of-Memory Killer (OOM-Killer). Он анализирует запущенные процессы, выбирает тот, который потребляет больше всего памяти (им оказывается рабочий процесс СУБД postgres, выполняющий наш запрос), и принудительно завершает его системным сигналом SIGKILL (Signal 9).
Паника процесса Postmaster. Главный управляющий процесс PostgreSQL (postmaster) обнаруживает, что один из его дочерних процессов, обслуживающий клиентское соединение, внезапно и некорректно прекратил работу. Поскольку этот процесс имел прямой доступ к общей разделяемой памяти СУБД (shared_buffers), postmaster не может гарантировать, что данные в памяти не были повреждены в момент его убийства. Чтобы предотвратить порчу базы данных, СУБД мгновенно сбрасывает абсолютно все активные пользовательские сессии (база "падет у всех") и инициирует процедуру перезапуска.
Процедура Crash Recovery. После перезапуска PostgreSQL переходит в режим аварийного восстановления. Он последовательно считывает логи предзаписи (WAL-файлы) от последней успешной контрольной точки (checkpoint) до момента падения. СУБД накатывает подтвержденные транзакции и откатывает те, которые не успели завершиться. Пока этот процесс идет, база данных выдает ошибку "система в режиме восстановления" и не принимает новые подключения. Время восстановления напрямую зависит от объема транзакционной активности перед сбоем и размера WAL-логов.

Проблема временных файлов (Temp Files)

Даже если на сервере настроены лимиты и OOM-Killer не убивает процесс СУБД, выполнение тяжелого запроса может упереться в дисковую подсистему. Для выполнения сортировок и соединений PostgreSQL выделяет под каждый процесс объем памяти, ограниченный параметром work_mem в файле конфигурации postgresql.conf. Если промежуточные данные запроса не помещаются в этот лимит, СУБД начинает сбрасывать их на диск во временные файлы.

При неоптимальном каскадном ПОЛНОЕ СОЕДИНЕНИЕ на базе объемом в 19 ГБ размер временных файлов может мгновенно разрастись до сотен гигабайт. Если на диске, где расположены временные файлы СУБД, закончится свободное место, это приведет к аварийной остановке операционной системы или аварии самой СУБД.

Почему трансляция ПОЛНОЕ СОЕДИНЕНИЕ в PostgreSQL неэффективна

Давайте выясним причину, почему именно ПОЛНОЕ СОЕДИНЕНИЕ так сильно нагружает PostgreSQL. Платформа "1С:Предприятие" является кроссплатформенной и должна транслировать запросы 1С в SQL-синтаксис любой поддерживаемой СУБД. Нативная поддержка FULL OUTER JOIN в PostgreSQL имеет ряд архитектурных ограничений. Чтобы обойти их и гарантировать идентичное поведение запроса на любой СУБД, платформа 1С часто принудительно переписывает (трансформирует) одно ПОЛНОЕ СОЕДИНЕНИЕ в сложную конструкцию из LEFT OUTER JOIN, RIGHT OUTER JOIN и объединений UNION.

Когда в одном запросе каскадно связываются четыре таблицы через полные соединения, а внутри них еще присутствуют внутренние соединения (ВНУТРЕННЕЕ СОЕДИНЕНИЕ), итоговый SQL-текст, генерируемый платформой 1С, превращается в гигантского "монстра". Планировщик PostgreSQL тратит огромное количество ресурсов только на то, чтобы построить план его выполнения, после чего начинает лавинообразно плодить декартовы произведения строк в памяти сервера.

Разбор неоптимального запроса

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


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

Проанализируем этот код. Здесь мы видим каскадное связывание таблиц "вбок". Проблема усугубляется тем, что к результатам полных соединений многократно подмешиваются внутренние соединения со справочником Справочник.СтрокиОтчетов по владельцу. Если в одной из цепочек связь нарушается (например, нет данных за какой-то квартал), то из-за жестких внутренних соединений СУБД вынуждена выполнять сложнейшие вычисления, чтобы сопоставить пустые значения (NULL). Планировщик СУБД просто сходит с ума, пытаясь оптимизировать этот хаос, что и приводит к мгновенной утилизации всей доступной памяти и диска.

Правильный паттерн оптимизации: переход на ОБЪЕДИНИТЬ ВСЕ

В стандартах разработки "1С:Предприятие" использование конструкции ПОЛНОЕ СОЕДИНЕНИЕ крайне не рекомендуется. Практически любую задачу сопоставления таблиц можно и нужно решать через объединение данных "вниз" с последующей группировкой.

Давайте разберем по шагам, как правильно переписать этот запрос, чтобы он работал молниеносно и безопасно.

Шаг 1. Собираем все данные в одну временную таблицу по вертикали. Вместо того чтобы сопоставлять таблицы кварталов бок о бок, мы объединяем их строки в одну общую временную таблицу с помощью быстрой конструкции ОБЪЕДИНИТЬ ВСЕ. Чтобы не потерять информацию о том, к какому кварталу относится значение, мы добавим числовой маркер (поле НомерКвартала) или просто сложим все суммы в одно типизированное поле.

Шаг 2. Группируем полученные данные. После того как все данные лежат в одной временной таблице, мы выполняем группировку по ключевым полям связи (ВидОтчета, Аналитика1, Аналитика2, Показатель) и суммируем показатели.

Шаг 3. Соединяем со справочником один раз. Нам больше не нужно дублировать внутренние соединения со справочником СтрокиОтчетов для каждого квартала отдельно. Мы соединяем сгруппированный результат со справочником всего один раз в самом конце.

Посмотрим на пример правильной структуры запроса:


// Шаг 1. Объединяем показатели всех кварталов по вертикали во временную таблицу
ВЫБРАТЬ
    ПервоеЗначение.ВидОтчета КАК ВидОтчета,
    ПервоеЗначение.Аналитика1 КАК Аналитика1,
    ПервоеЗначение.Аналитика2 КАК Аналитика2,
    ПервоеЗначение.Показатель КАК Показатель,
    ПервоеЗначение.ПЗ КАК Значение
ПОМЕСТИТЬ ВТ_ВсеПоказатели
ИЗ
    ПервоеЗначение КАК ПервоеЗначение

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    ВтороеЗначение.ВидОтчета,
    ВтороеЗначение.Аналитика1,
    ВтороеЗначение.Аналитика2,
    ВтороеЗначение.Показатель,
    ВтороеЗначение.ВЗ
ИЗ
    ВтороеЗначение КАК ВтороеЗначение

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    ТретьеЗначение.ВидОтчета,
    ТретьеЗначение.Аналитика1,
    ТретьеЗначение.Аналитика2,
    ТретьеЗначение.Показатель,
    ТретьеЗначение.ТЗ
ИЗ
    ТретьеЗначение КАК ТретьеЗначение

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    ЧетвертоеЗначение.ВидОтчета,
    ЧетвертоеЗначение.Аналитика1,
    ЧетвертоеЗначение.Аналитика2,
    ЧетвертоеЗначение.Показатель,
    ЕСТЬNULL(ЧетвертоеЗначение.ЧЗ, 0)
ИЗ
    ЧетвертоеЗначение КАК ЧетвертоеЗначение
;

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

ГРУППИРОВАТЬ ПО
    ВТ_Показатели.ВидОтчета,
    ВТ_Показатели.Аналитика1,
    ВТ_Показатели.Аналитика2,
    ВТ_Показатели.Показатель,
    СтрокиОтчетов.Ссылка

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