Почему отчёт из трёх полей может строиться четыре минуты, если данных в нём меньше двадцати тысяч строк?
Вопрос не риторический: именно с ним к нам пришли после того, как отчёт по доступности товара стал выполняться 4 минуты 03 секунды. В нём не было ни сложной аналитики, ни расчёта себестоимости: номенклатура, склад и свободный остаток. Разработчик уже сократил выборку до 18 740 позиций и поставил отбор по организации. Результат не изменился. Технологический журнал показал один вызов СУБД длительностью 238 секунд и 401 826 чтений строк во внутреннем контуре плана.
Сначала подозревали виртуальную таблицу регистра накопления. Это привычная цель: остатки часто читают без периода и с отбором после виртуальной таблицы. Но параметры были заданы внутри вызова, граница периода совпадала с итогами регистра, а отдельный запрос к остаткам выполнялся за 780 мс. Замер разрушил первую гипотезу. Проблема начиналась, когда результат этой выборки становился правой стороной соединения.
Так проявляется неприятный дефект конструкции «соединение с подзапросом». Текст выглядит компактно, а оптимизатор СУБД получает промежуточный набор без надёжной оценки размера и распределения ключей. Он считает правую сторону маленькой, выбирает вложенные циклы и тысячи раз ищет строки там, где выгоднее один раз построить хеш-таблицу.
Почему подзапрос ломает оценку кардинальности
Платформа 1С переводит запрос во внутренний SQL, но окончательный способ соединения выбирает СУБД. Для физической таблицы у СУБД есть статистика: число строк, доля разных значений, частые ключи, гистограмма распределения. Для составного выражения из виртуальной таблицы, группировки и фильтра такой статистики нет. Оптимизатор вычисляет оценку по правилам селективности. Ошибка каждого шага умножается на следующую.
В исходном запросе левая часть содержала 18 740 товаров. Подзапрос остатков возвращал 6 912 строк, но PostgreSQL оценил его в 37 строк. Разница в 187 раз заставила планировщик выбрать Nested Loop. На каждой строке левой части выполнялся поиск по материализованному результату, причём условие по складу не вошло в эффективный индексный доступ. Фактически внутренний узел исполнился 18 740 раз.
Сам подзапрос не запрещён и не обязан быть медленным. Опасна граница, за которой оптимизатор не видит устойчивой структуры данных. Группировка, ВЫРАЗИТЬ() для составного типа, ЕСТЬNULL() в условии и обращение к виртуальной таблице увеличивают риск ошибки оценки. Несколько таких операций внутри правой стороны соединения дают план, который хорошо выглядит только на тестовой базе.
Есть ещё одна ловушка. Если подзапрос возвращает мало строк в копии базы, вложенные циклы действительно дешевле. После загрузки месяца продаж набор вырастает, но сохранённый план или прежние оценки продолжают вести оптимизатор по старому пути. Поэтому фраза «у разработчика выполняется за секунду» ничего не доказывает без одинакового объёма данных и фактического плана.
В консоли запросов 1С этого расхождения не видно. Она показывает время всего пакета и результат, но не сообщает, сколько раз СУБД выполнила внутренний узел. Нужен фактический план на стороне СУБД. В PostgreSQL смотрим поля rows и loops в каждой строке EXPLAIN ANALYZE; фактическую работу внутреннего узла оцениваем с учётом обоих значений. Один узел с actual rows=22 выглядит безобидно, пока рядом не обнаружится loops=18740.
Если на самом дорогом соединении план обещает несколько десятков строк, а после выполнения показывает несколько тысяч и тысячи повторных входов во внутренний узел, причина уже локализована достаточно точно для проверочного переписывания.
Мы не переписываем подзапрос только из-за его наличия. Сначала сравниваем оценочное и фактическое число строк на узлах соединения. Расхождение больше чем в 10 раз уже требует внимания; в этом кейсе оно достигло 187 раз.
Два плана: 400 тысяч строк против одного хеша
Исходная конструкция соединяла справочник номенклатуры с агрегированными остатками. В сокращённом виде запрос выглядел так:
ВЫБРАТЬ
Товары.Ссылка КАК Номенклатура,
Остатки.Склад,
ЕСТЬNULL(Остатки.КоличествоОстаток, 0) КАК Остаток
ИЗ
Справочник.Номенклатура КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ
Ост.Номенклатура,
Ост.Склад,
СУММА(Ост.КоличествоОстаток) КАК КоличествоОстаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(
&Момент,
Организация = &Организация) КАК Ост
СГРУППИРОВАТЬ ПО
Ост.Номенклатура,
Ост.Склад
) КАК Остатки
ПО Товары.Ссылка = Остатки.Номенклатура
ГДЕ
НЕ Товары.ПометкаУдаления
В реальном тексте было ещё два похожих подзапроса: резервы и товары к обеспечению. Здесь важно положение отбора Организация = &Организация. Он передан параметром виртуальной таблицы, а не наложен снаружи. Это сокращает чтение итогов регистра до формирования результата. Подробно этот эффект разобран в материале про параметры виртуальных таблиц 1С. Но даже правильный параметр не исправляет ошибочную оценку результата группировки.
Фрагмент фактического плана показал Nested Loop Left Join. На вход пришло 18 740 строк, внутренний Materialize выдал суммарно 401 826 строк, а узел соединения потратил 231,4 секунды. Оценочная стоимость не сигнализировала о беде, потому что план ожидал 37 строк справа. Буферные чтения оставались умеренными. Процессор тратился на повторную проверку ключей.
После материализации остатков во временную таблицу план изменился. СУБД один раз прочитала 6 912 строк, построила хеш по паре «Номенклатура, Склад» и выполнила Hash Left Join. Узел соединения занял 1,8 секунды. Полный вариант с резервами и обеспечением работал 11 секунд, поскольку две другие временные таблицы тоже требовали заполнения.
| Метрика | Подзапрос | Временная таблица |
|---|---|---|
| Строк справа, факт | 6 912 | 6 912 |
| Строк справа, оценка | 37 | 6 640 |
| Алгоритм | Nested Loop | Hash Left Join |
| Обработано на узле | 401 826 строк | 25 652 строки |
| Время узла | 231,4 с | 1,8 с |
Название алгоритма само по себе не диагноз. Nested Loop выигрывает, если слева десятки строк, а справа есть селективный индекс. Hash Join требует памяти и полного чтения одной стороны. Диагноз здесь составной: оценка промахнулась в 187 раз, внутренний узел повторился 18 740 раз, а фактическое время сосредоточилось именно в соединении.
Сравнивать планы нужно при одинаковых параметрах. Значение &Момент на конец закрытого месяца читает устойчивые итоги, а текущая секунда может затронуть движения после границы итогов. Склад с 40 товарами и центральный склад с 90 тысячами карточек также дают разную селективность. Мы фиксировали организацию, момент и список складов, очищали только результат клиентского кеша и не перезапускали PostgreSQL между сериями. Иначе тест измеряет прогрев дискового кеша, а не форму соединения.
Оба плана сохраняли целиком, потому что обрезанный снимок одного дорогого узла не показывает стоимость его входных наборов, фактическое число циклов и место, где впервые разошлись оценки.
Вынесите остатки во временную таблицу
Исправление начинается с пакетного запроса. Каждый тяжёлый набор формируется отдельным оператором, помещается во временную таблицу и индексируется по полям будущего соединения. После этого итоговая выборка работает с известными наборами, а не с тремя вложенными выражениями.
ВЫБРАТЬ
Ост.Номенклатура КАК Номенклатура,
Ост.Склад КАК Склад,
СУММА(Ост.КоличествоОстаток) КАК КоличествоОстаток
ПОМЕСТИТЬ ВТОстатки
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(
&Момент,
Организация = &Организация) КАК Ост
СГРУППИРОВАТЬ ПО
Ост.Номенклатура,
Ост.Склад
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
Склад
;
ВЫБРАТЬ
Товары.Ссылка КАК Номенклатура,
ВТОстатки.Склад,
ЕСТЬNULL(ВТОстатки.КоличествоОстаток, 0) КАК Остаток
ИЗ
Справочник.Номенклатура КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ ВТОстатки КАК ВТОстатки
ПО Товары.Ссылка = ВТОстатки.Номенклатура
ГДЕ
НЕ Товары.ПометкаУдаления
ИНДЕКСИРОВАТЬ ПО должен повторять полезный порядок ключей. Если итоговый запрос соединяет только по номенклатуре, начинать индекс со склада нельзя. Если соединение идёт по двум полям, индекс по одной номенклатуре оставит СУБД лишнюю фильтрацию. В нашем плане использовалась пара полей, поэтому индекс был составным.
Индекс не бесплатный. Для временной таблицы на 6 912 строк его построение заняло 34 мс. Для набора на 1,8 млн строк оно заняло бы секунды и добавило запись во временное пространство СУБД. Маленькую таблицу, которую читают один раз, иногда выгоднее оставить без индекса: хеш всё равно строится за один проход. Решение подтверждаем планом, а не шаблоном разработки.
Менеджер временных таблиц нужно передать запросу явно, если пакет собирается несколькими объектами Запрос. Иначе ВТОстатки существует только в контексте своего менеджера, а следующий запрос завершится сообщением Таблица не найдена "ВТОстатки". Рабочая схема выглядит так: создаём МенеджерВременныхТаблиц, присваиваем его свойству Запрос.МенеджерВременныхТаблиц у каждого участвующего запроса и закрываем после получения результата.
Материализация также отделяет этапы диагностики. Можно выполнить пакет до финальной выборки, проверить число строк во ВТОстатки и найти дубли ключа. Это полезно, когда левое соединение неожиданно размножает строки отчёта. Один товар и склад должны давать одну строку; если группировка включает характеристику, а соединение её игнорирует, ускорение запроса лишь быстрее вернёт неправильный результат.
Для контроля уникальности мы временно добавили проверочную выборку по ВТОстатки: группировка по номенклатуре и складу, затем условие КОЛИЧЕСТВО(*) > 1. Она вернула ноль строк. После этого проверочный оператор удалили из рабочего пакета. Оставлять его нельзя: полный проход по временной таблице на каждом формировании отчёта будет расходовать процессор ради уже проверенного инварианта.
Не все условия следует переносить в этап заполнения. Отбор по организации и моменту определяет состав остатков, поэтому находится в параметрах виртуальной таблицы. Отбор «остаток больше нуля» зависит от назначения отчёта. Если нулевые товары должны сохраниться из левой части, фильтр ВТОстатки.КоличествоОстаток > 0 в общем ГДЕ фактически превратит левое соединение во внутреннее. Такой подводный камень проявляется не падением, а исчезнувшими строками.
Перед переносом условия мы записываем, относится ли оно к источнику данных, ключу соединения или готовому результату, поскольку одинаковый на вид фильтр в этих местах меняет и план, и смысл выборки.
4 минуты превратились в 11 секунд
Кейс был на PostgreSQL 14, платформа 1С 8.3.23, база 460 ГБ, 286 активных пользователей в рабочий час. Отчёт строился по одной организации и 12 складам. Замеры выполняли на сервере после прогрева кеша, пять запусков каждого варианта. Медиана исходного запроса составила 243 секунды, лучшая попытка заняла 231 секунду.
В технологическом журнале включили событие DBPOSTGRS с фильтром Duration >= 1000000, где длительность задаётся в микросекундах. Это отсекло короткие обращения и оставило SQL отчёта. Для привязки использовали Context и время начала серверного вызова, затем проверили фактический план через EXPLAIN (ANALYZE, BUFFERS) на копии с обезличенными данными. На рабочей базе тяжёлый ANALYZE не запускали: он реально исполняет запрос.
Первой версией была настройка памяти PostgreSQL. Она не помогла. В плане не было сброса хеша на диск, work_mem хватало, а основное время уходило во вложенный цикл. Увеличение work_mem с 16 до 64 МБ изменило время лишь с 243 до 239 секунд. Это обычный пример, когда серверный параметр пытаются лечить без показаний плана.
Затем три подзапроса вынесли в ВТОстатки, ВТРезервы и ВТКОбеспечению. Индексы построили по фактическим условиям соединений. Заполнение заняло 3,2, 2,7 и 1,1 секунды соответственно, финальная выборка отработала за 3,6 секунды. Вместе с передачей результата на сервер 1С медиана составила 11,0 секунды. APDEX операции при целевом времени 20 секунд вырос с 0,41 до 0,96 за следующую рабочую неделю.
Мы отдельно проверили корректность: число строк, суммы по складам и 50 случайных карточек товара совпали с исходным вариантом. Это обязательная часть правки. Изменение порядка соединений и группировок легко скрывает дубль, особенно когда СКД потом сворачивает строки. Если медленный запрос спрятан в наборе данных схемы, последовательность поиска удобнее сверять с разбором, почему СКД-отчёт строится пять минут.
После публикации изменения оставили контроль на неделю. В ТЖ не появилось вызовов этого отчёта дольше 20 секунд, хотя число запусков выросло с 64 до 79 в день. Временные файлы PostgreSQL для сеансов отчёта не создавались, загрузка процессора не получила нового пика. Это сняло риск, что локальное ускорение одного пользователя оплачено деградацией всего кластера.
Контроль после выпуска нужен даже при убедительном стендовом результате, поскольку рабочая конкуренция сеансов, другой кеш и одновременные регламентные задания в часы пик способны изменить цену построения временных таблиц.
Когда подзапрос оставляем
Подзапрос допустим, если он возвращает небольшой предсказуемый набор, не содержит тяжёлой агрегации и фактический план оценивает его близко к реальности. Пример: выборка двух видов номенклатуры по уникальной ссылке или таблица из нескольких констант. Выносить её во временную таблицу ради формального правила значит добавить запись, индекс и более длинный код без выигрыша.
Пора материализовать набор, когда оценка и факт расходятся больше чем в 10 раз. Второй признак виден в плане: внутренний узел Nested Loop выполняется тысячи раз и забирает основное время. Ещё один сигнал даёт структура текста: справа находятся виртуальная таблица, группировка и преобразование составного типа одновременно. Эти признаки не заменяют измерение, но точно указывают, какой вариант проверить первым.
Начните с записи одного медленного вызова в ТЖ. Получите фактический план на копии, выпишите для каждого соединения estimated rows, actual rows, loops и actual time. Если оценки близки, временная таблица не исправит причину. Тогда проверяйте физические индексы, селективность условий и чтения; материал про индексы 1С через расширение поможет отделить нехватку индекса от ошибки формы запроса.
Есть граница применимости. Временная таблица не спасёт запрос, который сначала без отбора читает все движения регистра, а потом оставляет один склад. Она лишь материализует слишком большой набор. Не поможет она и при ожидании блокировки: в плане будет нормальное время выполнения, а пауза останется между операциями. В этих случаях возвращаемся к ТЖ и разделяем длительность обращения к СУБД, ожидание блокировки и серверный код.
Если после материализации оценки строк стали точными, но время осталось прежним, дальнейшее дробление запроса прекращаем и ищем дорогой физический доступ либо ожидание вне выполненного SQL в серверном вызове.
В нашем случае правило подтвердилось цифрами: три непрозрачных подзапроса давали 243 секунды, три временные таблицы с проверенными ключами дали 11 секунд. Но главный результат не сама конструкция. Мы перестали спорить о виртуальном регистре и памяти сервера после первого фактического плана. Следующая проверка для похожего отчёта занимает несколько минут: найти узел с максимальным временем, сравнить оценку с фактом и только потом переписывать запрос.





