Этой проблеме уже не менее 15 лет.

Исходные данные – база на PostgreSQL, большая. Вполне себе типовые отчеты с не менее типовыми запросами 1C, содержащие обращение к виртуальной таблице СрезПоследних какого-нибудь регистра сведений с большим количеством строк, выполняются неприлично длительное время. Вплоть до нескольких часов.

Причина – оптимизатор строит неверный план запроса. Причем тот же запрос на MS SQL выполняется быстро и оптимизатор не ошибается. Прям обидно.

Сейчас будем разбираться в чем ошибается оптимизатор и какие пути решения тут возможны.

Введение

Сразу оговорюсь, проблема касается только таблиц с большим количеством строк. Чисто эмпирически я бы эту границу поставил в районе примерно 1 млн. строк. Ну просто по нашим замерам и опыту задержка тут будет измеряться несколькими секундами (до 10 сек.), и она условно комфортная для пользователей. Поэтому, допускаю, что многие из читателей могли и не сталкиваться с проблемой.

Дальнейшие примеры будут касаться таблиц регистров с гораздо большим количеством строк, и длительность таких запросов может измеряться уже не секундами, а десятками минут и даже часами.

Напомню, СрезПоследних – это встроенный в платформу 1С механизм, который позволяет быстро и эффективно получить именно ту запись регистра сведений, которая является самой последней (самой "свежей") на заданную дату для каждой уникальной комбинации измерений.

С точки зрения СУБД, СрезПоследних – это запрос с вложенными запросами и группировками таблицы периодического регистра сведений и фильтром по требуемым измерениям. Звучит просто. Но вот оптимизатор запроса на PostgreSQL делает план запроса неоптимальным, из-за чего он выполняется долго.

Обратимся к вендору платформы 1С. Еще в 2010 году на ИТС опубликована статья по озвученной проблеме:

О ней не писал только ленивый, и мы не будем исключением и вставим свои пять копеек. Разбор пойдет со стороны СУБД PostgreSQL. Версия PG не важна – за 15 лет тут изменений нет, всё стабильно. А также сравним ситуацию с MS SQL, где такой проблемы нет.

Анализ проблемы СрезПоследних

В качестве исходных данных мы взяли довольно большую базу данных MS SQL (20+ Тб), которую прям вот только что перевели на PostgreSQL, поэтому есть возможность проанализировать поведение практически одного и того же запроса в двух СУБД.

В качестве инструментария используем систему мониторинга Perfexpert для поиска в трассах длительных запросов, связанных с методом СрезПоследних, и плана их выполнения. А также используем сервис для анализа и графической визуализации планов запросов Explain PostgreSQL от компании Тензор.

Берем один из таких запросов, находим ему аналог в трассе MS и сравниваем планы. Поехали.

Исходные данные

• Регистр сведений: _InfoRg44326

• Кол-во строк: 250+ млн.;

• Размер таблицы с индексами: 63 Гб.

• Текст запроса (и для PG, и для MS)

• Собранный план выполнения запроса (и для PG, и для MS)

В принципе, сами данные не так важны. Мы на пальцах покажем проблему и как ее можно было бы решить, не прибегая к переписыванию кода.

Пример sql-запроса с таблицей на 200+ млн строк в PG и в MS

Надо отметить, что 200 млн. строк для среза последних в PostgreSQL – это довольно тяжело. Но программист этого может и не знать (хотя что-то такое, наверняка, подозревает, и где-то что-то слышал) и напишет стандартное обращение к виртуальной таблице типа этого:

ВЫБРАТЬ Номенклатура, Цена
ИЗ РегистрСведений.ЦеныКомпании.СрезПоследних(&ВыбДата, ТипЦен=&ВыбТипЦен, Номенклатура=&Номенклатура);

Это не фрагмент кода из реальной системы, чьи sql-запросы будут анализироваться дальше, а просто для понимания – на стороне кода 1С программист обращается через точку к срезу последних регистра и получает выборку данных. Иногда быстро, иногда долго, иногда очень долго. Причем возвращаемое количество строк не важно. А вот на стороне СУБД происходит целое действо. Рассмотрим реальный запрос и план его выполнения. Сначала в PostgreSQL, затем в MSSQL.

insert into
	pg_temp.tt115 (_Q_000_F_000_TYPE,
	_Q_000_F_000_RTRef,
	_Q_000_F_000_RRRef)
select
	T1.Fld44327_TYPE,
	T1.Fld44327_RTRef,
	T1.Fld44327_RRRef
from
	(
	select
		T6._Fld44327_TYPE as Fld44327_TYPE,
		T6._Fld44327_RTRef as Fld44327_RTRef,
		T6._Fld44327_RRRef as Fld44327_RRRef,
		T6._Fld44329 as Fld44329_
	from
		(
		select
			T3.Fld44327_TYPE as Fld44327_TYPE,
			T3.Fld44327_RTRef as Fld44327_RTRef,
			T3.Fld44327_RRRef as Fld44327_RRRef,
			T3.Fld44328RRef as Fld44328RRef,
			T3.Fld44329_ as Fld44329_,
			T3.Fld44330_ as Fld44330_,
			T3.Fld44331RRef as Fld44331RRef,
			T3.Fld44332RRef as Fld44332RRef,
			T3.MAXPERIOD_ as MAXPERIOD_,
			SUBSTR(MAX(T5._RecorderTRef || T5._RecorderRRef), 1, 4) as MAXRECORDERTRef,
			SUBSTR(MAX(T5._RecorderTRef || T5._RecorderRRef), 5, 16) as MAXRECORDERRRef
		from
			(
			select
				T4._Fld44327_TYPE as Fld44327_TYPE,
				T4._Fld44327_RTRef as Fld44327_RTRef,
				T4._Fld44327_RRRef as Fld44327_RRRef,
				T4._Fld44328RRef as Fld44328RRef,
				T4._Fld44329 as Fld44329_,
				T4._Fld44330 as Fld44330_,
				T4._Fld44331RRef as Fld44331RRef,
				T4._Fld44332RRef as Fld44332RRef,
				MAX(T4._Period) as MAXPERIOD_
			from
				_InfoRg44326 T4
			where
				((T4._Fld2507 = cast(0 as numeric)))
				and (T4._Active = true)
			group by
				T4._Fld44327_TYPE,
				T4._Fld44327_RTRef,
				T4._Fld44327_RRRef,
				T4._Fld44328RRef,
				T4._Fld44329,
				T4._Fld44330,
				T4._Fld44331RRef,
				T4._Fld44332RRef) T3
		inner join _InfoRg44326 T5 on
			T3.Fld44327_TYPE = T5._Fld44327_TYPE
			and T3.Fld44327_RTRef = T5._Fld44327_RTRef
			and T3.Fld44327_RRRef = T5._Fld44327_RRRef
			and T3.Fld44328RRef = T5._Fld44328RRef
			and T3.Fld44329_ = T5._Fld44329
			and T3.Fld44330_ = T5._Fld44330
			and T3.Fld44331RRef = T5._Fld44331RRef
			and T3.Fld44332RRef = T5._Fld44332RRef
			and T3.MAXPERIOD_ = T5._Period
		where
			((T5._Fld2507 = cast(0 as numeric)))
			and (T5._Active = true)
		group by
			T3.Fld44327_TYPE,
			T3.Fld44327_RTRef,
			T3.Fld44327_RRRef,
			T3.Fld44328RRef,
			T3.Fld44329_,
			T3.Fld44330_,
			T3.Fld44331RRef,
			T3.Fld44332RRef,
			T3.MAXPERIOD_) T2
	inner join _InfoRg44326 T6 on
		T2.Fld44327_TYPE = T6._Fld44327_TYPE
		and T2.Fld44327_RTRef = T6._Fld44327_RTRef
		and T2.Fld44327_RRRef = T6._Fld44327_RRRef
		and T2.Fld44328RRef = T6._Fld44328RRef
		and T2.Fld44329_ = T6._Fld44329
		and T2.Fld44330_ = T6._Fld44330
		and T2.Fld44331RRef = T6._Fld44331RRef
		and T2.Fld44332RRef = T6._Fld44332RRef
		and T2.MAXPERIOD_ = T6._Period
		and T2.MAXRECORDERTRef = T6._RecorderTRef
		and T2.MAXRECORDERRRef = T6._RecorderRRef
	where
		(T6._Fld2507 = cast(0 as numeric))) T1
where
	(T1.Fld44327_TYPE = '\\\\010'::bytea
		and T1.Fld44327_RTRef = '\\\\000\\\\000\\\\004\\\\313'::bytea)
	and exists(select 1 from pg_temp.tt135 T7 where (T1.Fld44327_TYPE = T7._Q_001_F_000_TYPE and T1.Fld44327_RTRef = T7._Q_001_F_000_RTRef and T1.Fld44327_RRRef = T7._Q_001_F_000_RRRef))
group by
	T1.Fld44327_TYPE,
	T1.Fld44327_RTRef,
	T1.Fld44327_RRRef

План запроса PG (в конце статьи будут все планы и запросы, чтобы при желании вы могли их сами поковырять) выглядит так:

План реальный, собранный мониторингом Perfexpert из боевой системы.

Фактически мы видим несколько вложенных подзапросов, внутри каждого есть группировки и объединения таблиц друг с другом (T4, T5, T6), которые по факту являются одной и той же физической таблицей _InfoRg44326. Но главное условие с отборами, что указано в самом конце запроса (сравнение с другой временной таблицей с псевдонимом T7), работает только с самым «внешним» в дереве SELECT, где используется таблица T6. Я обвел на плане этот момент синим. А самый внутренний подзапрос с таблицей T4 остается без этого важного фильтра, и по итогам он выгребает из таблицы все 200+ млн. строк, группирует, тратит на это 267 430 мс (~4,5 мин.). Потом следующим шагом он делает INNER JOIN сам с собой (таблица T5), потратив при этом 1 584 102 мс (26 мин.) на поиск плюс 101 414 мс (1,7 мин.) на соединение и 947 127 мс (15,7 мин) на ещё одну группировку. Это основные временные затраты.

В сумме запрос выполнился за 2 968 714 мс (49 минут) и вернул аж 32 строки, перелопатив несколько раз весь регистр.

Теперь рассмотрим аналогичный запрос в MS SQL.

(@P1 numeric(10),@P2 numeric(10),@P3 numeric(10),@P4 datetime2(3))
INSERT INTO #tt66 WITH(TABLOCK) (_Q_001_F_000_TYPE, _Q_001_F_000_RTRef, _Q_001_F_000_RRRef) 
SELECT
T1.Fld44327_TYPE,
T1.Fld44327_RTRef,
T1.Fld44327_RRRef
FROM (SELECT
T6._Fld44327_TYPE AS Fld44327_TYPE,
T6._Fld44327_RTRef AS Fld44327_RTRef,
T6._Fld44327_RRRef AS Fld44327_RRRef,
T6._Fld44329 AS Fld44329_
FROM (SELECT
T3.Fld44327_TYPE AS Fld44327_TYPE,
T3.Fld44327_RTRef AS Fld44327_RTRef,
T3.Fld44327_RRRef AS Fld44327_RRRef,
T3.Fld44328RRef AS Fld44328RRef,
T3.Fld44329_ AS Fld44329_,
T3.Fld44330_ AS Fld44330_,
T3.Fld44331RRef AS Fld44331RRef,
T3.Fld44332RRef AS Fld44332RRef,
T3.MAXPERIOD_ AS MAXPERIOD_,
SUBSTRING(MAX(T5._RecorderTRef + T5._RecorderRRef),1,4) AS MAXRECORDERTRef,
SUBSTRING(MAX(T5._RecorderTRef + T5._RecorderRRef),5,16) AS MAXRECORDERRRef
FROM (SELECT
T4._Fld44327_TYPE AS Fld44327_TYPE,
T4._Fld44327_RTRef AS Fld44327_RTRef,
T4._Fld44327_RRRef AS Fld44327_RRRef,
T4._Fld44328RRef AS Fld44328RRef,
T4._Fld44329 AS Fld44329_,
T4._Fld44330 AS Fld44330_,
T4._Fld44331RRef AS Fld44331RRef,
T4._Fld44332RRef AS Fld44332RRef,
MAX(T4._Period) AS MAXPERIOD_
FROM dbo._InfoRg44326 T4
WHERE ((T4._Fld2507 = @P1)) AND (T4._Active = 0x01)
GROUP BY T4._Fld44327_TYPE,
T4._Fld44327_RTRef,
T4._Fld44327_RRRef,
T4._Fld44328RRef,
T4._Fld44329,
T4._Fld44330,
T4._Fld44331RRef,
T4._Fld44332RRef) T3
INNER JOIN dbo._InfoRg44326 T5
ON T3.Fld44327_TYPE = T5._Fld44327_TYPE AND T3.Fld44327_RTRef = T5._Fld44327_RTRef AND T3.Fld44327_RRRef = T5._Fld44327_RRRef AND T3.Fld44328RRef = T5._Fld44328RRef AND T3.Fld44329_ = T5._Fld44329 AND T3.Fld44330_ = T5._Fld44330 AND T3.Fld44331RRef = T5._Fld44331RRef AND T3.Fld44332RRef = T5._Fld44332RRef AND T3.MAXPERIOD_ = T5._Period
WHERE ((T5._Fld2507 = @P2)) AND (T5._Active = 0x01)
GROUP BY T3.Fld44327_TYPE,
T3.Fld44327_RTRef,
T3.Fld44327_RRRef,
T3.Fld44328RRef,
T3.Fld44329_,
T3.Fld44330_,
T3.Fld44331RRef,
T3.Fld44332RRef,
T3.MAXPERIOD_) T2
INNER JOIN dbo._InfoRg44326 T6
ON T2.Fld44327_TYPE = T6._Fld44327_TYPE AND T2.Fld44327_RTRef = T6._Fld44327_RTRef AND T2.Fld44327_RRRef = T6._Fld44327_RRRef AND T2.Fld44328RRef = T6._Fld44328RRef AND T2.Fld44329_ = T6._Fld44329 AND T2.Fld44330_ = T6._Fld44330 AND T2.Fld44331RRef = T6._Fld44331RRef AND T2.Fld44332RRef = T6._Fld44332RRef AND T2.MAXPERIOD_ = T6._Period AND T2.MAXRECORDERTRef = T6._RecorderTRef AND T2.MAXRECORDERRRef = T6._RecorderRRef
WHERE (T6._Fld2507 = @P3)) T1
WHERE (T1.Fld44327_TYPE = 0x08 AND T1.Fld44327_RTRef = 0x000004CB) AND EXISTS(SELECT
1
FROM #tt62 T7 WITH(NOLOCK)
WHERE (T1.Fld44327_TYPE = T7._Q_001_F_000_TYPE AND T1.Fld44327_RTRef = T7._Q_001_F_000_RTRef AND T1.Fld44327_RRRef = T7._Q_001_F_000_RRRef)) AND (T1.Fld44329_ <= @P4)
GROUP BY T1.Fld44327_TYPE,
T1.Fld44327_RTRef,
T1.Fld44327_RRRef

Как видите, запрос практически идентичный. Регистр тот же, строк столько же (разве чуть-чуть поменьше, т.к. замеры выполнены до миграции на PostgreSQL), но длительность запроса на порядки быстрее.

Сейчас покажем почему. Смотрим план запроса. Он оценочный, взятый из системы мониторинга Perfexpert, поэтому длительность каждой операции мы не увидим, но это не важно, т.к. длительность всего запроса – 1,7 сек.

Ответ на вопрос «почему» на самом деле достаточно очевиден и Америку мы тут не открываем. На форумах это уже обсуждалось и не раз.

Если посмотреть на заполнение всё тех же таблиц T4 и T5, то увидим, что оптимизатор прокинул внутрь этих вложенных подзапросов внешнее условие и указал его в качестве предикатов, чего никак не хотел делать PG. Выделил зеленым на рисунке выше. И теперь эти участки запроса выполняются практически мгновенно, т.к. необходимое условие фильтрации прокинуто сразу на все подзапросы, и они не выполняют тяжелые операции поиска, соединений и группировок с двумястами миллионами строк.

У читателей может возникнуть сомнение, что мы, мол, выбрали какие-то отдельные запросы, которые выполняются вот тут медленно, а вот тут быстро. Но звучит как-то неубедительно. Поэтому всем желающим предлагаем провести самостоятельное моделирование проблемы и увидеть причины. Скрипты приложены в конце статьи, а описание моделирования ниже.

Синтетический тест sql-запроса для СрезПоследних

Описание модели.

1. Создаем и заполняем таблицу test_gr 100 миллионами строк (аналог _InfoRg). В таблице три колонки. Первая Col1 – как бы измерение, вторая Col2– как бы ресурс и третья Per – как бы дата. Все колонки целые числа, заполненные случайным образом. После заполнения размер таблицы стал 5,3 Гб.

2. Поскольку все значения случайные, то в одной строке вручную добавлено значение «11111» в первую колонку, по которому и будет проводиться отбор. На всякий случай.

3. Добавляем индекс по первой и третьей колонке.

4. Создаем таблицу pg_temp.Cols (аналог временной таблицы pg_temp.tt135), в которой одна колонка и одна строка со значением «11111», по которому будут соединяться таблицы.

5. Создаем упрощенный текст запроса, относящийся к срезу последних, и выполняем его:
   а) сначала с условием фильтрации, оставленным только снаружи, т.е. как есть;
   б) далее с дополнительными условиями фильтрации, прокинутыми во внутренние SELECT (на картинке ниже они закомментированы).

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

Сравним планы. Взял 1, 2 и 4 варианты.

Что видим? PostgreSQL не умеет прокидывать основное условие во внутренние подзапросы, хотя там используется одна и та же таблица и ее, конечно, имеет смысл фильтровать сразу, а не после нескольких группировок. Как только мы указываем дополнительные условия, поиск в таблицах (T4 или T5) происходит практически мгновенно.

Для чистоты эксперимента проделаем это в MSSQL

Даже с закомментированным условием (where exists(select 1 from….) во внутреннем SELECT запрос выполняется практически мгновенно, за доли секунды. И в плане хорошо видно, что оптимизатор прокинул условие условие во все вложенные SELECT’ы.

Итого

Какие выводы напрашиваются из полученных результатов?

Проблема понятна и даже решаема. На разных уровнях. Вендор 1С рекомендует в случае долгого выполнения СрезПоследних переписывать такие проблемные запросы, вынося запрос к виртуальной таблице во временную, и далее обращаться за результатом уже к ней. Вариант рабочий, но, конечно, усложняет код запроса по сравнению с простым соединением со срезом последних. А хотелось бы как в MS SQL.

Мы решили не дожидаться решения проблемы от вендоров и сделали свое. Точнее, решение уже было, осталось его настроить под новую задачу – QProcessing. Инструмент очень гибкий, мы уже описывали его применение ни в одной статье для совершенно разных задач. Вот и СрезПоследних дождался своей очереди.

Буквально пару предложений о QProcessing, широкими мазками, чтобы вы не переключались и не бегали по ссылкам. QProsessing — это программный прокси-сервер, связывающий сервер приложений 1C:Предприятие и сервер баз данных. Можно разворачивать на отдельной машине, а можно и на сервере СУБД, если ресурсы позволяют.

Выполняет потоковое сканирование трафика sql-запросов от сервера приложений и выборочно меняет текст отдельных sql-запросов. Для задания логики используются система правил на базе регулярных выражений. Если пришедший запрос попал под правило, то он модифицируется определенным образом. Чаще всего – это хинты (подсказки) и методы соединений (Hash JOIN, Merge JOIN и т.п.). Но можно модифицировать и целые куски текста. Если запрос не попал под правило, то он проходит через QProcessing без изменений и выполняется на СУБД как есть.

Соответственно, видя проблему заказчика с запросами СрезПоследних, выполняющихся по 50-60 минут, мы подготовили несколько правил, которые принудительно прокинули внешнее условие в каждый из подзапросов – именно то, что вы видели в синтетическом тесте. После модификация длительность таких запросов стала в большинстве случаев около нулевой.

Надеюсь, вы дочитали до этого места, и статья оказалась полезной.

Как и обещал, вот перечь запросов, планов их выполнения, а также скриптов для самостоятельного анализа и моделирования:

1. Исходный тяжелый запрос в PG (49 мин.) + план выполнения.

2. Исходный тяжелый запрос в MS (1,7 сек) + план выполнения.

3. Скрипты для синтетического теста в PG и MS.

Ссылки на остальные части Записок оптимизатора 1С:

1. Записки оптимизатора 1С (ч.1). Странное поведение MS SQL Server 2019: длительные операции TRUNCATE

2. Записки оптимизатора 1С (ч.2). Полнотекстовый индекс или как быстро искать по подстроке

3. Записки оптимизатора 1С (ч.3). Распределенные взаимоблокировки в 1С системах

4. Записки оптимизатора 1С (ч.4). Параллелизм в 1С, настройки, ожидания CXPACKET

5. Записки оптимизатора 1С (ч.5). Ускорение RLS-запросов в 1С системах

6. Записки оптимизатора 1С (ч.6). Логические блокировки MS SQL Server в 1С: Предприятие

7. Записки оптимизатора 1С (ч.7). «Нелогичные» блокировки MS SQL для систем 1С предприятия

8. Записки оптимизатора 1С (ч.8). Нагрузка на диски сервера БД при работе с 1С. Пора ли делать апгрейд?

9. Записки оптимизатора 1С (ч.9). Влияние сетевых интерфейсов на производительность высоконагруженных ИТ-систем

10. Записки оптимизатора 1С (ч.10): Как понять, что процессор — основная боль на вашем сервере MS SQL Server?

11. Записки оптимизатора 1С (ч.11). Не всегда очевидные проблемы производительности на серверах 1С

12. Записки оптимизатора 1С (ч.12).  СрезПоследних в 1C:Предприятие на PostgreSQL. Почему же так долго?