От экспертов «1С-Рарус»: Два кейса оптимизации работы с 1С через управление индексами в MS SQL и PostgreSQL

Введение

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

Кейс #1. Низкая скорость группового перепроведения складских и производственных документов и оптимизация перепроведения

Крупное кейтеринговое предприятие производит свою продукцию на 300 точках. Каждая производственная точка представляет собой отдельную структурную единицу с возможностью передачи продукции между точками. Для автоматизации своей деятельности используется типовая конфигурация «1С:УНФ 8. Управление предприятием общепита».

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

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

Инфраструктура исследуемой системы

Программный продукт: 1С:УНФ 8. Управление предприятием общепита.
База данных: Microsoft SQL Server 2012.
Платформа 1С:Предприятие 8: 8.3.20.1710 64х.
Операционная система: Windows Server 2012 R2.
Процессор: Intel Xeon Silver 4210 CPU @ 2.2GHz.
Оперативная память: 256 GB.
SSD: 3 TB.

Статистика по информационной базе

Количество организаций: 3.
Количество структурных единиц: 300.
Количество документов в месяц: 50000.
Количество строк в документах: от 5 до 150.

Сбор технологического журнала

При старте экспертных работ нередко появляется ощущение, что ты только что разбирал один в один похожий кейс. Ощущение дежавю произошло и с нами после прочтения данного кейса, т. к. оно нам очень напомнило случай из статьи. Тем более, когда после анализа статистики оказалось, что за месяц перепроводятся 50 000 документов и количество строк в них разнится от 5 до 150 строк.

Потому в первую очередь мы посмотрели, действительно ли это мог быть такой же случай. Для этого запустили сбор логов технологического журнала по события CALL, SDBL, DBMSSQL и запустили групповое перепроведение 3 дней.

Полный текст файла logcfg.xml:

<?xml  version="1.0" encoding="UTF-8"?>

<config  xmlns="http://v8.1c.ru/v8/tech-log">

       <log   history="24" location="d:\logs">

             <property  name="all"/>

             <event>

                    <eq  property="name" value="CALL"/>

                    <eq  property="p:processName" value="base_name"/>

             </event>

             <event>

                    <eq  property="name" value="SDBL"/>

                    <eq  property="p:processName" value="base_name"/>

             </event>

             <event>

                    <eq  property="name" value="DBMSSQL"/>

                    <eq  property="p:processName" value="base_name"/>

             </event>

       </log>

       <dump   location="d:\dumps\" 

                    create="1" 

                    type="0" 

                    externaldump="1"/>

</config> 

Проанализировать логи можно с помощью различных инструментов. Одним из них является конфигурация «1С-Рарус: Сценарный обработчик конфигураций» с модулем для анализа технологического журнала, про которую более подробно мы поговорим в следующих публикациях.

Перед запуском сценария анализа ТЖ необходимо настроить параметры окружения. В частности, нас будут интересовать путь к логам технологического журнала, а также параметры подключения к ClickHouse:

После чего запустить выполнение сценария:

Во время его выполнения, полученные из 1С логи технологического журнала преобразовываются в csv файл. Далее выполняется нормализация текстов запросов и поиск временных лагов, мы рассказывали об этом в статье https://rarus.ru/publications/20210127-ot-ekspertov-lagi-v-tekhnologicheskom-loge-1c-458537/.

Полученный результат загружается в базу ClickHouse.

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

Для получения длительных операций выполним следующий запрос:

Select top 100 

        _duration/1000000 as duration_sec, 

        _event, 

       _time, 

        sessionid, 

        sql, 

        context, 

        --descr, 

        * 

    from default.grimut_table 

    where 

        _event == 'DBMSSQL' 
    order by (_duration/1000000) DESC

В результате обнаруживаем длительные запросы. В первую очередь это запросы выполнения контроля остатков регистров «Запасы и затраты» и «Запасы на складах»:

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

|						ДвиженияЗапасыИзменение КАК ДвиженияЗапасыИзменение)) КАК ЗапасыОстатки
|		ПО ДвиженияЗапасыИзменение.Организация = ЗапасыОстатки.Организация
|			И ДвиженияЗапасыИзменение.СтруктурнаяЕдиница = ЗапасыОстатки.СтруктурнаяЕдиница
|			И ДвиженияЗапасыИзменение.СчетУчета = ЗапасыОстатки.СчетУчета
|			И ДвиженияЗапасыИзменение.Номенклатура = ЗапасыОстатки.Номенклатура
|			И ДвиженияЗапасыИзменение.Характеристика = ЗапасыОстатки.Характеристика
|			И ДвиженияЗапасыИзменение.Партия = ЗапасыОстатки.Партия
|			И ДвиженияЗапасыИзменение.ЗаказПокупателя = ЗапасыОстатки.ЗаказПокупателя
|ГДЕ
|	ЕСТЬNULL(ЗапасыОстатки.КоличествоОстаток, 0) < 0
|
|УПОРЯДОЧИТЬ ПО
|	НомерСтроки
|;
// ... По аналогии с данным пакетом реализован контроль по другим регистрам.
|";

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

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

Чтобы убедиться в этом мы воспользовались наработкой, которую мы применяли в статье https://rarus.ru/publications/20220831-ot-ekspertov-zavisanie-provedeniya-dokumenta-1c-parameter-sniffing-mssql-postgresql-547514/, которая бы модифицировала текст запроса с помощью функции ниже и план формировался бы каждый раз новый:

// Процедура - Модифицировать текст запроса для отключения параметра сниффинга.
//
// Параметры:
// Запрос - Запрос - Тело запроса.
// ПоляЗамены - Структура - Список полей для замены, где Ключ - строка поиска, Значение - Числовое поле из таблицы.
//
Процедура ОтключитьПараметрСниффингаВЗапросе(Запрос, ДанныеЗамены) Экспорт	
	ГСЧ = Новый ГенераторСлучайныхЧисел(ТекущаяУниверсальнаяДатаВМиллисекундах());
	СлучайноеЧисло = Формат(ГСЧ.СлучайноеЧисло(0, 429496729) + ГСЧ.СлучайноеЧисло(0, 4294967295), "ЧН=0; ЧГ=");	
	Для каждого ТекСтрока Из ДанныеЗамены Цикл		
		СтрокаПоиска = ТекСтрока.Ключ;
		СтрокаЗамены = СтрШаблон("(%1 * 0) * %2 КАК Поле%2,
		|%3", ТекСтрока.Значение, СлучайноеЧисло, СтрокаПоиска);		
		Запрос.Текст = СтрЗаменить(Запрос.Текст, СтрокаПоиска, СтрокаЗамены);	
	КонецЦикла;
КонецПроцедуры 

Однако это не ускорило выполнение, и наше ощущение схожести случаев оказалось лишь ощущением.

Потому без анализа плана этих длительных запросов не обойтись. Вы можете спросить почему мы сразу не приступили к их анализу? Причины две: во-первых, нам казалось, что кейс один в один и должно помочь; а во-вторых, так было быстрее.

Поиск долгих запросов

Чтобы получить план проблемного запроса нужно было найти документ, который приводил бы к такой деградации. Для этого мы сопоставили время выполнения запроса из технологического журнала со временем в журнале регистрации. Затем запустили базу в режиме 1С:Предприятие с отладкой и с помощью функции библиотеки стандартных подсистем «ОбщегоНазначения.ЗапросВСтрокуXML» — получили преобразованное значение запроса с параметрами для воспроизведения:

Для того, чтобы использовать данное значение необходимо в консоли запросов, предлагаемой фирмой 1С (its.1c.ru/db/metod8dev/content/4500/hdoc), воспользоваться командой «Заполнить по XML»:

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

Однако для удобства представления давайте воспользуемся графическим планом Microsoft SQL Server. Перейдем в Management Studio и настроим сбор расширенных событий query_post_execution_showplan, rpc_completed и sql_batch_completed.

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

CREATE EVENT SESSION [rarus_plan] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlserver.sql_text)
    WHERE ([package0].[equal_uint64]([sqlserver].[database_id],(ИДБазы)))),
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[equal_uint64]([sqlserver].[database_id],(ИДБазы)) AND [package0].[greater_than_equal_uint64]([duration],(1000000)))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.session_id,sqlserver.sql_text)
    WHERE ([package0].[equal_uint64]([sqlserver].[database_id],(ИДБазы)) AND [package0].[greater_than_equal_uint64]([duration],(1000000))))
ADD TARGET package0.event_file(SET filename=N'ПутьКФайлуЛогов\rarus_locks.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO 

Где:

ПутьКФайлуЛогов — путь к файлу, где будет размещаться результат сбора событий.
ИДБазы — ИД информационной базы. Для его получения можно выполнить команду SELECT DB_ID() AS [Database ID];

Или создать с помощью оснастки:

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

После завершения останавливаем сбор и находим необходимый план запроса:

Устройство виртуальной таблицы остатков

Перед анализом полученного плана запроса давайте вспомним как хранятся остатки и как выполняется запрос к таблице остатков. Для получения данных из виртуальной таблицы остатков регистра накопления платформа «1С:Предприятие 8» использует таблицы итогов и движений (its.1c.ru/db/metod8dev/content/2726/hdoc). В таблице итогов хранятся остатки в разрезе всех измерений с периодичностью месяц на начало месяца. В таблице движений же хранятся все записи регистра:

Для анализа принципа выполнения давайте выполним запрос к остаткам по номенклатуре на 22.09.2022:

ВЫБРАТЬ
	ЗапасыОстатки.Организация КАК Организация,
	ЗапасыОстатки.Номенклатура КАК Номенклатура,
	ЗапасыОстатки.КоличествоОстаток КАК КоличествоОстаток
ИЗ
	РегистрНакопления.Запасы.Остатки(&НаДату, Номенклатура = &Номенклатура) КАК ЗапасыОстатки

В момент выполнения запроса система определяет включены ли текущие итоги по данному регистру. Если включены, то из таблицы итогов получаются текущие остатки, а из них вычитаются рассчитанные данные по таблице движений от момента времени получения остатков:

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

Стоит отметить, что в некоторых релизах платформы алгоритм получения может немного отличаться, но архитектура хранения данных сохраняется.

Анализ плана запроса в MS SQL и предположение о нехватке индекса

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

Неужели по данным регистрам отсутствуют индексы раз MS SQL Server решил не использовать их, а применить только минимальные отборы?

Рассмотрим структуру данных регистра «Запасы и затраты». Выделенные измерения являются индексируемыми:

Структура регистра «Запасы и затраты»

С помощью встроенной функции ПолучитьСтруктуруХраненияБазыДанных получаем информацию о полях в индексах.

При этом _AccumRg31553_1 и _AccumRg31553_2 стандартные индексы по периоду и регистратору, которые платформа формирует для всех регистров накопления, а _AccumRg31553_3 — _AccumRg31553_5 — созданы на основе индексируемых полей, выбранных в конфигураторе.

Можно предположить, что, передав в отборы таблицу целиком, MS SQL Server не нашел индекса, который покрывал бы все или хотя бы некоторые поля или их селективность не подходила для применения. Потому был выполнен отбор по физической таблице движений без применения индексов, что и спровоцировало длительное выполнение запроса.

Чтобы проверить данную гипотезу выполним серию запросов к остаткам.

В первом запросе убедимся, что при отборе по массиву элементов по индексированному полю сработает индекс. Для этого запустим сбор расширенных событий и выполним следующий запрос:

ВЫБРАТЬ
	ЗапасыОстатки.Организация КАК Организация,
	ЗапасыОстатки.СтруктурнаяЕдиница КАК СтруктурнаяЕдиница,
	ЗапасыОстатки.Номенклатура КАК Номенклатура,
	ЗапасыОстатки.Характеристика КАК Характеристика,
	ЗапасыОстатки.Партия КАК Партия,
	ЗапасыОстатки.ЗаказПокупателя КАК ЗаказПокупателя,
	ЗапасыОстатки.СчетУчета КАК СчетУчета,
	ЗапасыОстатки.ЗаказНаПроизводство КАК ЗаказНаПроизводство,
	ЗапасыОстатки.КоличествоОстаток КАК КоличествоОстаток,
	ЗапасыОстатки.СуммаОстаток КАК СуммаОстаток
ИЗ
	РегистрНакопления.Запасы.Остатки(&МоментВремени, Номенклатура В (&МассивНоменклатуры)) КАК ЗапасыОстатки

Получим следующий план запроса:

Получается отбор точечно по полю все-таки заставил MS SQL Server воспользоваться индексом.

Если попробовать передать временную таблицу с этим полем, то картина сразу портится:

Таким образом, при выполнении отбора по временной таблице даже по имеющим свои индексы MS SQL Server не использовал таблицу индексов. Что же делать?

Первым вариантом, который мы реализовали являлась эмуляция платформенного запроса к остаткам. Т. е. мы получаем остатки на начало месяца. Далее формируем запрос к таблице движений с отбором по индексированным полям с помощью конструкции В, а полученную выборку уже отбираем по исходной временной таблице. Использование данного подхода заставляет систему управления базами данных пользоваться индексами. Что позволило добиться оптимизации некоторых запросов до 25%, но результаты скорости перепроведения все еще были недостаточны.

Пример запроса эмуляции платформенных остатков:

ВЫБРАТЬ
	ТаблицаВсехДвижений.Организация КАК Организация,
	ТаблицаВсехДвижений.СтруктурнаяЕдиница КАК СтруктурнаяЕдиница,
	ТаблицаВсехДвижений.Номенклатура КАК Номенклатура,
	ТаблицаВсехДвижений.Характеристика КАК Характеристика,
	ТаблицаВсехДвижений.Партия КАК Партия,
	ТаблицаВсехДвижений.ЗаказПокупателя КАК ЗаказПокупателя,
	ТаблицаВсехДвижений.СчетУчета КАК СчетУчета,
	ТаблицаВсехДвижений.ЗаказНаПроизводство КАК ЗаказНаПроизводство,
	ТаблицаВсехДвижений.Количество * ВЫБОР
			КОГДА ТаблицаВсехДвижений.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Приход)
				ТОГДА 1
			ИНАЧЕ -1
		КОНЕЦ КАК Количество,
	ТаблицаВсехДвижений.Сумма * ВЫБОР
			КОГДА ТаблицаВсехДвижений.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Приход)
				ТОГДА 1
			ИНАЧЕ -1
		КОНЕЦ КАК Сумма
ПОМЕСТИТЬ РегистрНакопленияЗапасыОстаткиДвижения
ИЗ
	РегистрНакопления.Запасы КАК ТаблицаВсехДвижений
ГДЕ
	ТаблицаВсехДвижений.Период >= &УОП_НачалоИтоговПериодаКонтроля2
	И ТаблицаВсехДвижений.Период <= &УОП_ПериодКонтроля2
	И НЕ (ТаблицаВсехДвижений.Период = &УОП_ПериодКонтроля2
	И ТаблицаВсехДвижений.Регистратор > &УОП_РегистраторКонтроля2)
	И ТаблицаВсехДвижений.Организация В (&ДвиженияЗапасыИзменение_Организация) 
	И ТаблицаВсехДвижений.СтруктурнаяЕдиница В (&ДвиженияЗапасыИзменение_СтруктурнаяЕдиница) 
	И ТаблицаВсехДвижений.Номенклатура В (&ДвиженияЗапасыИзменение_Номенклатура)
	И ТаблицаВсехДвижений.Активность
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ТаблицаИтогов.Организация КАК Организация,
	ТаблицаИтогов.СтруктурнаяЕдиница КАК СтруктурнаяЕдиница,
	ТаблицаИтогов.СчетУчета КАК СчетУчета,
	ТаблицаИтогов.Номенклатура КАК Номенклатура,
	ТаблицаИтогов.Характеристика КАК Характеристика,
	ТаблицаИтогов.Партия КАК Партия,
	ТаблицаИтогов.ЗаказПокупателя КАК ЗаказПокупателя,
	ТаблицаИтогов.КоличествоОстаток КАК КоличествоОстаток,
	ТаблицаИтогов.СуммаОстаток КАК СуммаОстаток
ПОМЕСТИТЬ РегистрНакопленияЗапасыОстаткиДоГруппировки
ИЗ
	РегистрНакопления.Запасы.Остатки(&УОП_НачалоИтоговПериодаКонтроля2, (Организация, СтруктурнаяЕдиница,
	Номенклатура, Характеристика, Партия,
	ЗаказПокупателя,СчетУчета) В
	(ВЫБРАТЬ
		ДвиженияЗапасыИзменение.Организация КАК Организация,
		ДвиженияЗапасыИзменение.СтруктурнаяЕдиница КАК СтруктурнаяЕдиница,
		ДвиженияЗапасыИзменение.Номенклатура КАК Номенклатура,
		ДвиженияЗапасыИзменение.Характеристика КАК Характеристика,
		ДвиженияЗапасыИзменение.Партия КАК Партия,
		ДвиженияЗапасыИзменение.ЗаказПокупателя КАК ЗаказПокупателя,
		ДвиженияЗапасыИзменение.СчетУчета КАК СчетУчета
	ИЗ
		ДвиженияЗапасыИзменение КАК ДвиженияЗапасыИзменение)) КАК ТаблицаИтогов
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
	ТаблицаДвижений.Организация,
	ТаблицаДвижений.СтруктурнаяЕдиница,
	ТаблицаДвижений.СчетУчета,
	ТаблицаДвижений.Номенклатура,
	ТаблицаДвижений.Характеристика,
	ТаблицаДвижений.Партия,
	ТаблицаДвижений.ЗаказПокупателя,
	СУММА(ТаблицаДвижений.Количество),
	СУММА(ТаблицаДвижений.Сумма)
ИЗ
	РегистрНакопленияЗапасыОстаткиДвижения КАК ТаблицаДвижений
ГДЕ
	(ТаблицаДвижений.Организация, ТаблицаДвижений.СтруктурнаяЕдиница,
	ТаблицаДвижений.Номенклатура, ТаблицаДвижений.Характеристика, ТаблицаДвижений.Партия,
	ТаблицаДвижений.ЗаказПокупателя,ТаблицаДвижений.СчетУчета) В
	(ВЫБРАТЬ
		ДвиженияЗапасыИзменение.Организация КАК Организация,
		ДвиженияЗапасыИзменение.СтруктурнаяЕдиница КАК СтруктурнаяЕдиница,
		ДвиженияЗапасыИзменение.Номенклатура КАК Номенклатура,
		ДвиженияЗапасыИзменение.Характеристика КАК Характеристика,
		ДвиженияЗапасыИзменение.Партия КАК Партия,
		ДвиженияЗапасыИзменение.ЗаказПокупателя КАК ЗаказПокупателя,
		ДвиженияЗапасыИзменение.СчетУчета КАК СчетУчета
	ИЗ
		ДвиженияЗапасыИзменение КАК ДвиженияЗапасыИзменение)
СГРУППИРОВАТЬ ПО
	ТаблицаДвижений.Организация,
	ТаблицаДвижений.СтруктурнаяЕдиница,
	ТаблицаДвижений.СчетУчета,
	ТаблицаДвижений.Номенклатура,
	ТаблицаДвижений.Характеристика,
	ТаблицаДвижений.Партия,
	ТаблицаДвижений.ЗаказПокупателя
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ТаблицаФинальный.Организация КАК Организация,
	ТаблицаФинальный.СтруктурнаяЕдиница КАК СтруктурнаяЕдиница,
	ТаблицаФинальный.СчетУчета КАК СчетУчета,
	ТаблицаФинальный.Номенклатура КАК Номенклатура,
	ТаблицаФинальный.Характеристика КАК Характеристика,
	ТаблицаФинальный.Партия КАК Партия,
	ТаблицаФинальный.ЗаказПокупателя КАК ЗаказПокупателя,
	СУММА(ТаблицаФинальный.КоличествоОстаток) КАК КоличествоОстаток,
	СУММА(ТаблицаФинальный.СуммаОстаток) КАК СуммаОстаток
ПОМЕСТИТЬ РегистрНакопленияЗапасыОстатки
ИЗ
	РегистрНакопленияЗапасыОстаткиДоГруппировки КАК ТаблицаФинальный
СГРУППИРОВАТЬ ПО
	ТаблицаФинальный.Организация,
	ТаблицаФинальный.СтруктурнаяЕдиница,
	ТаблицаФинальный.СчетУчета,
	ТаблицаФинальный.Номенклатура,
	ТаблицаФинальный.Характеристика,
	ТаблицаФинальный.Партия,
	ТаблицаФинальный.ЗаказПокупателя
ИМЕЮЩИЕ
	НЕ СУММА(ТаблицаФинальный.КоличествоОстаток) = 0
	И НЕ СУММА(ТаблицаФинальный.СуммаОстаток) = 0
;
////////////////////////////////////////////////////////////////////////////////
УНИЧТОЖИТЬ РегистрНакопленияЗапасыОстаткиДвижения
;
////////////////////////////////////////////////////////////////////////////////
УНИЧТОЖИТЬ РегистрНакопленияЗапасыОстаткиДоГруппировки 

Второй вариант: добавить такой индекс, который бы покрывал и оптимизировал выполнение ряда запросов. К счастью, MS SQL Server анализирует выполняемые запросы и с помощью динамических административных функций позволяет получить информацию о недостающих индексах. В частности, за информацию об отсутствующих индексах отвечают функции: dm_db_missing_index_group_stats, dm_db_missing_index_details и dm_db_missing_index_groups.

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

dm_db_missing_index_details — содержит информацию о колонках необходимых к включению в индекс, а также колонках соответствующих предикатам «равенства» и «неравенства». Предикат «равенства» означает отбор по оператору сравнения «=», любой другой считается — «неравенства».

dm_db_missing_index_groups — связывает между собой предыдущие две функции.

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

Текст запроса для анализа 10 наиболее рекомендованных индексов:

SELECT TOP 10 

      DB_NAME(database_id) AS ИмяБазы, 

      mid.statement AS РекомендацииДляТаблицы, 

      mid.equality_columns AS ОтборПоРавенству, 

      mid.inequality_columns AS ОтборПоНеРавенству, 

      mid.included_columns AS НеобходимыеКолонки, 

      migs.avg_total_user_cost *  migs.avg_user_impact * (migs.user_seeks +  migs.user_scans) AS КоэффициентПользы, 

      migs.avg_user_impact AS СреднийПроцентВыигрыша, 

      mid.*, 

      migs.* 

FROM 

      sys.dm_db_missing_index_group_stats AS  migs

      INNER JOIN sys.dm_db_missing_index_groups AS  mig

            ON (migs.group_handle =  mig.index_group_handle) 

      INNER JOIN sys.dm_db_missing_index_details AS  mid

      ON (mig.index_handle =  mid.index_handle) 

ORDER BY 

      avg_total_user_cost  * avg_user_impact * (user_seeks  + user_scans) DESC 

Выполнив данный запрос получаем следующую картину:

Если перевести результат запроса в представления 1С, то можно заметить, что 7 из 10 советов предлагают добавить индексы регистру накопления «Запасы и затраты». При этом предполагаемый выигрыш получится более 90%:

Важно отметить, что результат данного запроса является лишь информацией на подумать, а не готовым решением с гарантией в 100%. Помимо этого, при использовании платформы с добавлением индексов не все так просто.

Попытка решения средствами 1С

Технологическая платформа «1С:Предприятие 8» поддерживает явное и неявное управление индексами. Под явным управлением подразумевается изменения свойства «индексировать» у измерений, ресурсов и реквизитов:

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

При этом даже явная установка индексов имеет свои ограничения. Например, отсутствует возможность создания своих составных индексов, кроме использования значения «индексировать с дополнительным упорядочиванием». Однако применение данного значения не является прямым аналогом данной функциональности и требует осознания последствий его использования, т. к. созданные индексы могут быть избыточны и тогда приведут к деградации системы и, конечно, данное значение есть далеко не всегда. Регистры накопления одни из них.

Потому для решения данной задачи средствами 1С можно создать индексируемое измерение, содержащие ссылку на справочник с реквизитами по аналитикам других измерений. Тогда отобрав заранее по аналитике нужные элементы, система сможет пользоваться индексом по единственному полю. Такой подход можно встретить в системах класса ERP.

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

Альтернативные способы управления индексами

На текущий момент платформа не позволяет создавать свои индексы. Однако давайте немного пофантазируем на тему: «как система будет работать, если такая функциональность появится в платформе». Чтобы сильно не улететь в фантазиях — рассмотрим создание индекса для текущего кейса:

Сейчас у регистра «Запасы и затраты» существует 3 индекса, которые добавили разработчики, в том числе, индексы по измерениям «Организации и «Номенклатура». Давайте рассмотрим совместный индекс, который бы позволил СУБД более точечно находить записи без лишней фильтрации.

В Management Studio информацию по индексам можно найти в соответствующей группе Indexes:

Чтобы при создании индекса не заполнять обязательные параметры и поля — воспользуемся скриптом создания на основании существующего индекса по «Организации», который предлагает MS SQL:

Модифицируем его установив свободное наименование индекса и добавив дополнительное поле по номенклатуре:

USE [ИмяБазыДанных]
GO
 
SET ANSI_PADDING ON
GO
CREATE UNIQUE NONCLUSTERED INDEX [НаименованиеИндекса] ON [dbo].[_AccumRg31553]
(
  [_Fld1608] ASC,
  [_Fld31554RRef] ASC, /* Организация */
  [_Fld31556RRef] ASC, /* Номенклатура */
  [_Period] ASC,
  [_RecorderTRef] ASC,
  [_RecorderRRef] ASC,
  [_LineNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
GO

Стоит отметить, что после выполнения данного запроса СУБД отобразит данный индекс, а функция ПолучитьСтруктуруХраненияБазыДанных — нет.

Проверяем, что дало создание индексов

После создания индекса и реиндексации выполним очистку кэша планов запроса с помощью:
DBCC FREEPROCCACHE.

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

Какие результаты дала оптимизация индексов для ускорения перепроведения документов

Изначальное обращение было с жалобой на длительность группового перепроведения. Потому в качестве финального тестирования выполним групповое перепроведение за одинаковый период без учета и с учетом новых индексов и сравним показатели.

Перепроводить будем рабочую неделю с наибольшим количеством крупных документов с отключенной математикой параллельного выполнения:

В результате перепровелось порядка 15 тысяч документов: без индекса — почти 6 часов, с индексом — 3 часа:

Результаты без добавления индекса

Результаты после добавления индекса

Таким образом, добавление индекса позволило ускорить групповое перепроведение месяца в 2 раза. А благодаря включению механизма параллельного перепроведения по оценкам получилось ускорить еще 2 раза.

Кейс #2. Долгое построение отчета о прибылях и убытках

Другой случай экспертного расследования произошел в дружественной организации. В базе финансового учета некоторые пользователи жаловались на долгое формирование одного из ключевых структурированных отчетов — «Отчет о прибылях и убытках» (ОПУ):

При отборе отчета за год, время выполнения составляло порядка 1–2 минут, что, конечно, не укладывается в допустимые рамки. При увеличении отбора по периоду время формирования отчета росло нелинейно. Такая ситуация наблюдалась у пользователей с ограниченными правами, под полными правами отчет выполнялся относительно быстро.

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

Инфраструктура исследуемой системы

Программный продукт: Конфигурация на основе БСП.
База данных: PostgreSQL 11.8.
Платформа 1С:Предприятие 8: 8.3.17.1549 64х (в режиме совместимости с 8.3.12).
Операционная система: Ubuntu 18.04.6.
Процессор: Intel Xeon CPU E5-2637 v2@ 3.5GHz.
Оперативная память: 96 GB.
SSD: 1 TB.

Проводим замеры производительности при построении отчета о прибылях и убытках

Итак, по условиям тормозит отчет под пользователем с ограниченными правами. Влияние RLS на производительность — это известная проблематика, но попробуем разобраться, сможем ли мы повлиять на это.

Посмотрим на запрос основной СКД исследуемого отчета — «ОПУ». Входными данными для него являются движения оборотного регистра накопления «Расходы затраты»:

Количество записей в регистре за год достигает ~3 млн., в разрезе ~50-ти ЦФО (организаций).

Запрос в СКД состоит из 9-ти пакетов, а они суммарно из 28-ми подпакетов объединения с выборками как из оборотов, так и из физической таблиц регистра «Расходы затраты»:

В качестве условий в пакетах используется обязательный отбор по периоду и списку счетов. Со стороны пользователя возможен настраиваемый отбор по ЦФО, подразделениям, статьям. И, как правило, в отчете установлен отбор хотя бы по ЦФО. Пример подзапроса:

Согласно настройкам доступа, в конфигурации ограничения накладываются на ЦФО и подразделения. Следовательно, механизм RLS неявно должен накладывать отборы на соответствующие поля:

Для первичной оценки ситуации сняли замер производительности средствами конфигуратора и получили такую картину:

Львиную долю выполнения отчета составляла строчка выборки следующей порции вывода в процессоре компоновки данных.

Поначалу это сильно удивило, мы даже выяснили несложным методом, на каком именно элементе происходит замедление:

Данный момент может быть любопытен тем, кто интересуется вопросом «а как оно устроено?» внутри. Однако, на самом деле в этой строчке просто выполнялся запрос СКД, что мы и выясним далее.

Получение плана запроса в PostgreSQL (Explain Analyze)

Увы, любой отладчик никогда не даст ответа на вопрос «Что происходит в БД?».

Как обычно, чтобы вникнуть глубже в причины замедления нужно снять лог технологического журнала 1С. Для этого мы воспользовались такой настройкой, с отбором по событиям CALL, SCALL, SDBL, DBPOSTGRS:

<?xml  version="1.0" encoding="UTF-8"?>

<config  xmlns="http://v8.1c.ru/v8/tech-log">

<log history="4" location="/srv/HDD/CFR/techlogs/">

     <event>

          <eq value="CALL" property="Name"/>

          <eq value="base_name" property="p:processName"/>

     </event>

     <event>

          <eq value="SCALL" property="Name"/>

          <eq value="base_name" property="p:processName"/>

     </event>

     <event>

          <eq value="SDBL" property="Name"/>

          <eq value="base_name" property="p:processName"/>

     </event>

     <event>

          <eq value="DBPOSTGRS" property="Name"/>

          <eq value="base_name" property="p:processName"/>

     </event>

     <property name="all"/>

</log>

</config> 

С помощью следующего баш-скрипта получили топ запросов по времени выполнения, сгруппированный по тексту запросов:

Одним из самых продолжительных запросов был с тегом «РАСХОДЫ», который выполнялся 28,7 сек. Его и взяли в разработку:

Нашли пакет с таким же тегом в нашем СКД:

Пакет состоит из объединения двух запросов к оборотной таблице регистра и двух к физической таблице. Основные условия, которые накладываются на запросы:

1. К таблице оборотов:
1. Период.
2. Сценарий — комментарий «%СценарийЗакрытия%» служебный, перед формированием отчета программно заменяется на условие.
3. СчетДебета/СчетКредита.
4. ЦФОДебета/ЦФОКредита.
5. ПодразделениеДебета/ПодразделениеКредита.
2. К физической таблице — все те же условия, плюс дополнительное условие на поле «ПериодВозникновения» (реквизит регистра).

Тут стоит отметить что абсолютно все измерения регистра и реквизит «ПериодВозникновения» проиндексированы.

Чтобы разобраться в проблеме мы сняли ТЖ с планами запросов при выполнении отчета, добавив в файл logcfg специальный тег «<plansql/>»:

<?xml version="1.0" encoding="UTF-8"?>

<config xmlns="http://v8.1c.ru/v8/tech-log">

<plansql/>

<log  history="4" location="/srv/HDD/CFR/techlogs/">

     <event>

          <eq value="DBPOSTGRS" property="Name"/>

          <eq value="base_name" property="p:processName"/>

     </event>

     <property name="all"/>

</log>

</config> 

При достаточно малом количестве событий в логе иногда не обязательно для анализа использовать специальные оснастки или баш-скрипты, достаточно, например, программы Notepad++, в которой используя поиск по текущему документу по имени события, можно получить лаконичный список событий со временем выполнения, где долгие события визуально выделяются:

Так и сделаем, находим строки лога, которые занимают много времени, и идентифицируем среди них искомую с тегом «Расходы» (~30 секунд):

Далее в выбранном событии находим поле «planSQLText», оно-то и содержит план запроса:

Немного о прочтении плана запроса и инструментах

Анализировать план запроса можно различными способами. Конечно можно читать его в текстовом виде, но намного проще с помощью визуализации. Мы не нашли в postgres встроенного визуализатора планов, аналогичного имеющемуся в Extended Events MS SQL. И в момент расследования воспользовались бесплатными инструментами, общедоступными в сети.

Так выглядит блок-схема плана запроса «Расходы»:

При визуальной оценке мы видим блок операторов, явно связанный с RLS:

Также по индикации замечаем два самых «тяжелых» пункта с операторами Index Scan:

Посмотрим подробнее на выделенные пункты.

Первый с номером #9 выполняется чуть более 15 секунд:

Для определения метаданных воспользуемся обработкой «Просмотр метаданных».

В текущем операторе идет обращение к таблице _accumrgtn623_1. Числом после нижнего подчеркивания «_1» как правило обозначается индекс, поэтому находим нужные метаданные поиском по такой строке «accumrgtn623» — это таблица оборотов регистра накопления «Расходы затраты», а «_accumrgtn623_1» её индекс:

Соответствие полей таблиц/индексов полям метаданным можно определить, найдя в обработке основную таблицу:

Таким образом понимаем, что _accumrgtn623_1 — это основной индекс таблицы итогов по периоду и всем измерениям регистра:

Тем же способом определим соответствие полей регистра в метаданных для полей условий указанных в операторе index scan:

• Блок Index Cond :
  • _period — Период,
  • _fld610rref — СчетДебета,
  • _fld616rref — СтатьяДебета.
• Блок Filter:
  • _fld612rref — ЦФОДебета,
  • _fld614rref — ПодразделениеДебета,
  • _fld618rref — Сумма.

Второй «тяжелый» оператор с номером #70 выполняется также около 15 секунд:

Обращение здесь идет к тому же индексу. Аналогично определяем условия:

• Блок Index Cond:
  • _period — Период,
  • _fld611rref — СчетКредита,
  • _fld617rref — СтатьяКредита.
• Блоке Filter:
  • _fld613rref — ЦФОКредита,
  • _fld615rref — ПодразделениеКредита,
  • _fld618rref — Сумма.

Тут можно заметить одну особенность postgres, в индексе порядок первых нескольких полей следующий:

• _Period — Период,
• _Fld669RRef — Сценарий,
• _Fld610RRef — СчетДебета,
• _Fld611RRef — СчетКредита,
• _Fld612RRef — ЦФОДебета.

А в одном из пакетов в условиях Index Cond участвуют _Period (Период) и _Fld611RRef (СчетКредита) без полей _Fld669RRef (Сценарий), _Fld610RRef (СчетДебета), которые идут до _Fld611RRef.

В документации PostgreSQL можно найти информацию о том, что при поиске в индексе могут использоваться условия с пробелами в последовательности полей, хотя об эффективности такой выборки мы судить не беремся. В MS SQL, например, подобная ситуация, как правило, приводит к оператору вида «Index Seek ... Where ...» и по сути к «замаскированному» скану индекса.

Также мы сняли аналогичный план запроса только для полных прав:

Как и ожидалось, в этом плане отсутствует блок операторов, связанный с RLS, а самые «тяжелые» операторы занимают порядка 50 мс.

Для примера посмотрим на один из блоков. В нём используется оператор «Bitmap Heap Scan» на основании данных «Bitmap Index Scan», который использует индекс «_accumrgtn623_8». Согласно документации, операторы «Bitmap Heap Scan» и «Bitmap Index Scan», как правило, «ходят парой». Сначала дочерний оператор просматривает индекс и находит в нем искомые адреса строк, а затем родительский узел выбирает эти строки из таблиц:

Индекс _accumrgtn623_8 построен по полям:

• _Fld616RRef — СтатьяДебета,
• _period — период.

И, похоже, при наличии условия по статьям является достаточно высокоселективным.

Поиск решения средствами 1С

При анализе запроса СКД мы понимали, что условия в пакетах разнородны по полям, а при разборе плана было понятно, что он не оптимален. Поэтому мы искали различные пути воздействия для ускорения формирования отчета и поставили несколько экспериментов в попытке улучшить ситуацию.

Смещение измерений

По применяемым отборам в системе и распределению данных в таблице мы предположили, что измерение «Сценарий» слишком низкоселективное, да и планировщик его не использует для поиска индекса, поэтому было решено сместить измерение вниз:

Также в качестве другого эксперимента, в надежде улучшить ситуацию хотя бы по пакетам с условиями по дебетовой части, было решено сместить выше измерение ЦФОДебета, исходя из предпосылки что на Счета и ЦФО всегда есть условия:

Единый запрос к физической таблице

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

Поэтому мы решили убрать разнородность в подзапросах, при которой данные выбирались и из таблицы оборотов, и из физической таблицы. Запрос был переписан с центральной выборкой в единую временную таблицу всех нужных данных из физической таблицы с последующей раздельной выборкой по пакетам из этой временной таблицы:

Отборы по ЦФО и подразделениям

Ещё одна гипотеза была о том, что проблемы производительности может вызывать ситуация пересечения условий по полям ЦФО и подразделение, указанных в отборе отчета и накладываемых из-за RLS. Поэтому было решено заменить возможное условие СКД (в фигурных скобках) к таблице регистра, на выборку значений заранее во временную таблицу:

И условие в запросе вида «Поле В (Выборка из ВТ)»:

Результаты изысканий

По итогу поставленных экспериментов проведен ряд тестов по самому нагруженному варианту. Тесты показали позитивную динамику. Для пользователя с ограниченными правами время формирования отчета сократилось в ~2–4 раза.

В таблице представлены замеры времени событий ТЖ, поэтому её не стоит напрямую сравнивать с изначальными замерами, но по ней можно увидеть тренд изменения скорости:

По новым планам запросов видно, что планировщик стал сочетать вместе два индекса:

• по полю СчетДебета — _accumrg609_3;
• и полю СчетКредита — _accumrg609_4.

Объединяя их оператором BitmapOr:

Индексы по счетам — стандартные индексы таблицы регистра, которые платформа создала на основании флага «Индексировать» у измерений СчетДебета и СчетКредита:

Однако, более широкие тесты с постановкой различных условий в отчете показали, что всё не столь радужно. Улучшив производительность в одном месте, мы ухудшили её во всех остальных замерах:

Повторный поиск решения, анализ недостающих индексов

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

Из предыдущих результатов было понятно, что планировщик запросов выбирает не оптимальный план. Часто такое может происходить по причине неполной статистики или из-за чрезвычайной сложности запроса. Но со статистикой в нашем случае всё было в порядке и по нашей оценке запрос не столь сложен.

А что если у планировщика нет выбора? Банально не существует такого индекса, который подходил бы в этих «нагруженных» ситуациях?

Поэтому решили зайти с другой стороны, и попробовать найти — а каких именно индексов не хватает?

Как ранее было описано в статье — для MS SQL существует ряд динамических представлений, которые могут «давать советы» по недостающим индексам. К сожалению, мы не нашли в PostgreSQL их полных аналогов. Но в наших поисках мы рассмотрели инструменты pg_stat_statements и pg_stat_all_tables/pg_stat_user_tables, с помощью которых можно получить кое-какую информацию.

Представление pg_stat_statements

Модуль предоставляет статистику выполнения запросов по различным параметрам — времени выполнения, нагрузке на процессор, эффективности работы с кэшем и т. д.

Для включения сбора статистики нужно добавить строку «pg_stat_statements» в раздел «shared_preload_libraries» файла postgresql.conf, и перезагрузить сервер PostgreSQL.

Далее, чтобы статистика стала доступна в определенной базе данных, нужно выполнить команду CREATE EXTENSION pg_stat_statements.

Пример запроса простого получения топ-10 запросов по времени выполнения:

Select 

     total_time, --Общее время, потраченное на оператор, в миллисекундах

     calls,      --Число выполнений

     rows,       --Общее число строк, полученных или затронутых оператором

     query       --Текст, представляющий оператор

from pg_stat_statements

order by total_time desc

limit 10

Подробнее о работе этого модуля и его возможностях можно почитать в документации
postgrespro.ru/docs/postgresql/11/pgstatstatements.

Представления pg_stat_all_tables / pg_stat_user_tables

Модуль pg_stat_all_tables предоставляет статистику обращения к таблицам, храня, например, такие параметры как:

• seq_scan — количество последовательных чтений, запущенных по этой таблице;
• idx_scan — количество сканирований по индексу, запущенных по этой таблице.

Представление pg_stat_user_tables по сути содержит ту же информацию, что и pg_stat_all_tables, но отфильтрованную только по пользовательским таблицам.

Пример запроса простого получения топ-10 таблиц с предполагаемыми проблемами недостатка индексов:

SELECT 

     relname AS TableName,

     seq_scan,

     to_char(seq_scan,  '999,999,999,999') AS TotalSeqScan,

     to_char(idx_scan,  '999,999,999,999') AS TotalIndexScan,

     to_char(n_live_tup,  '999,999,999,999') AS TableRows,

     pg_size_pretty(pg_relation_size(relname  :: regclass)) AS TableSize

FROM pg_stat_all_tables

WHERE schemaname = 'public'

     AND 50*seq_scan > idx_scan     --Ищем таблицы,  по которым сканов таблицы больше 2% от всех

     AND n_live_tup > 10000    --в которых  более 10000 строк

     AND pg_relation_size(relname :: regclass) > 5000000 --и объемом более ~5МБ 

ORDER BY relname ASC
limit 10

Подробнее о работе этого модуля и его возможностях можно почитать в документации
postgrespro.ru/docs/postgresql/11/monitoring-stats.

Добавление индекса вручную и проверка результата

Рассмотренные инструменты статистики PostgreSQL безусловно полезны и в ситуации расследования узких мест производительности дали бы много важной информации. Но в нашем конкретном случае мы уже знали с каким запросом и с какой таблицей проблемы и модули pg_stat_statements и pg_stat_all_tables не привнесли нового в наше расследование.

Поэтому мы решили попробовать найти нужные индексы на основе собственных наблюдений и анализа планов запросов.

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

И сняли планы запросов пакета «Расходы» для самых нагруженных случаев с отбором за год. Понятно, что это не единственный «тяжелый» пакет запроса, но по оценке это было самое узкое место.

Рассмотрим план запроса, выполненный под пользователем с ограниченными правами и настроенными ограничениями, под которым отчет выполняется за год ~76 секунд:

Как и в прошлых замерах планировщик выбирал индекс _accumrgtn623_1, который, напомним, является основным индексом таблицы итогов регистра накопления «Расходы затраты», с последовательностью полей:

• _Period,
• _Fld669RRef,
• _Fld610RRef,
• _Fld611RRef,
• _Fld612RRef,
• _Fld613RRef,
• _Fld614RRef,
• _Fld615RRef,
• _Fld616RRef,
• _Fld617RRef,
• _Fld1289RRef,
• _Fld1290RRef,
• _Splitter.

При этом отборы накладываются на поля _Period (Период), _Fld611RRef (СчетКредита), _Fld615RRef (ПодразделениеКредита).

А что если добавить индексы именно на эти наборы полей?

• _Period(Период),
• _Fld611RRef (СчетКредита),
• _Fld615RRef (ПодразделениеКредита).

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

• _Period(Период),
• _Fld610RRef (СчетДебета),
• _Fld614RRef (ПодразделениеДебета).

Для добавления индекса в postgres вручную можно воспользоваться оснасткой PgAdmin. В дереве баз находим нужную таблицу и в ветке Indexes через контекстное меню переходим в пункт Create → Index:

Указываем произвольное имя и необходимые поля. У нас получилось два индекса с «говорящими» именами:

• _accumrgtn623_610_614,
• _accumrgtn623_611_615.

Ждем создания индекса, снимаем замеры и... «первый блин комом». Хотя планировщик и стал использовать новый индекс, но время пакета «Расходы» и время выполнения отчета практически не изменилось:

Далее мы выдвинули гипотезу о низкой селективности отбора по периоду, при условии наличия отборов по счетам, ЦФО и подразделениям. И было решено добавить аналогичные индексы, в которых поле период было бы последним. Также помня, что в запросе помимо выборки из таблицы оборотов есть ещё и выборка из физической таблицы с отбором по реквизиту «ПериодВозникновения», решили добавить похожие индексы и для неё.

Добавлены индексы:

1. Для таблицы _accumrg609 (физ. таблица РН Расходы затраты):
   1. _accumrg609_610_614_period по полям:
      1. _Fld610RRef — СчетДебета,
      2. _Fld614RRef — ПодразделениеДебета,
      3. _Period — Период,
      4. _Fld3252 — ПериодВозникновения.
   2. _accumrg609_611_615_period по полям:
      1. _Fld611RRef — СчетКредита,
      2. _Fld615RRef — ПодразделениеКредита,
      3. _Period — Период,
      4. _Fld3252 — ПериодВозникновения.
2. Для таблицы _accumrgtn623 (таблица оборотов РН Расходы затраты):
   1. _accumrg623_610_614_period по полям:
      1. _Fld610RRef — СчетДебета,
      2. _Fld614RRef — ПодразделениеДебета,
      3. _Period — Период.
   2. _accumrg623_611_615_period по полям:
      1. _Fld611RRef — СчетКредита,
      2. _Fld615RRef — ПодразделениеКредита,
      3. _Period — Период.

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

Контрольный вариант выполнения отчета пользователя с настроенными ограничениями с периодом за год показал время выполнения ~1–2 секунды (!), вместо 76 секунд до добавления индексов.

Конечно, наученные прошлым горьким опытом, мы замеряли сразу несколько точек, и, оказалось, что под другим пользователем, имеющим в правах RLS, но без настроенных ограничений — время отчета сократилось не столь значительно, только в полтора раза — с 58 до 40 секунд. Поэтому расследование продолжилось.

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

• _accumrg609_610_614_period_3252,
• _accumrg609_611_613_period_3252,
• _accumrg609_611_615_period_3252,
• _accumrgtn623_610_612_614_616_period,
• _accumrgtn623_611_613_615_617_period,
• _accumrgtn623_616_610_612_period.

Ускорились самые узкие места, а это означает, что мы смогли снять «боль» пользователей, создав индексы, которые в нашем случае практически невозможно создать инструментами платформы.

Поддержка добавленных индексов

Технологическая платформа «1С:Предприятие 8» является сложной и многофункциональной системой. Подобно человеческому организму, который рассчитывает только на свои силы в борьбе с заразой и вирусами, платформа рассчитывает, что вмешательства на уровне СУБД не было. Потому не всегда учитывает их. Это же объясняет почему функция получения структуры хранения не отображает индексы, созданные вручную.

Однако, одно дело не отобразить индекс, а другое удалить его — и такая ситуация тоже возможна.

Давайте выделим операции, которые работают с индексами и посмотрим, как платформа на них реагирует и приведет к сбросу.

Анализируемые операции:

• восстановление из выгрузки информационной базы (dt);
• восстановление из бэкапа СУБД;
• реиндексация таблиц;
• реструктуризация индекса;
• пересчет итогов;
• изменение структуры и обновление конфигурации;
• обновление релиза платформы.

Для чистоты экспериментов подготовим простую конфигурацию, содержащую пару справочников, документ и регистр накопления:

В регистре накопления создадим новый индекс, содержащий «Организацию» и «Номенклатуру» с помощью следующего запроса в Management Studio:

SET ANSI_PADDING ON
GO
CREATE UNIQUE NONCLUSTERED INDEX [_AccumRg45_rarus] ON [dbo].[_AccumRg45]
(
[_Fld46RRef] ASC, 
[_Fld47RRef] ASC, 
[_Period] ASC, 
[_RecorderRRef] ASC, 
[_LineNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
GO
; 

В первую очередь начнем с выгрузки и загрузки информационной базы.

Данные команды позволяют сформировать бэкап средствами 1С. Главное преимущество данного способа — уменьшенный размер выгрузки, т. к. иногда базы даже за сотни гигабайт уменьшались в несколько раз. Однако эксперимент показал, что в результате загрузки из конфигурации платформа не восстановит ручные индексы:

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

Как и ожидалось, все индексы, включая вручную созданный — восстановились:

Следующие операции «Пересчет итогов» и «Реиндексация таблиц информационной базы» аналогично не привели к сбросу индекса:

А вот выполнение «Реструктуризации таблиц информационной базы» — привело к сбрасыванию:

Для анализа влияния изменения структуры и обновления конфигурации на добавленный индекс выполним ряд действий.

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

Аналогично система повела себя при добавлении нового реквизита регистра:

При этом добавление новой команды, изменение модуля набора записей и написание справки — никак не повлияло на добавленный индекс. Как и добавление нового справочника, документа, регистра накопления и сведений. Даже несмотря на статус, говорящий о реструктуризации конфигурации базы данных:

Так как обновление платформы подразумевает переход на новый релиз продукта, то сброс индекса зависит от двух факторов: установлен ли режим совместимости конфигурации от старой версии платформы и были ли доработки со стороны платформы, которые потребовали бы выполнение реструктуризации таблиц.

Например, у нас была конфигурация с режимом совместимости 8.3.12 и нам потребовалась функциональность доступная в релизе 8.3.17:

После изменения на 8.3.17 и выполнения обновления конфигурации платформа выдала сообщение с большим перечнем изменений. Ряд сообщений касались изменения структуры таблиц базы данных и необходимости в их реструктуризации.

Подтвердив изменения система на несколько часов ушла в реструктуризацию таблиц. База была развернута на PostgreSQL, и для выяснения причины зависания мы обратились к таблице активности процессов SELECT × FROM pg_stat_activity. Там был обнаружен длительный запрос, который изменял регистр бухгалтерии.

UPDATE  _AccRg68NG 

SET _ValueDt1_TYPE = T4._Value_TYPE,

 _ValueDt1_RTRef =  T4._Value_RTRef,

 _ValueDt1_RRRef =  T4._Value_RRRef,

 _KindDt1RRef = T4._KindRRef,

 

 _ValueCt1_TYPE = T6._Value_TYPE,

 _ValueCt1_RTRef =  T6._Value_RTRef,

 _ValueCt1_RRRef =  T6._Value_RRRef,

 _KindCt1RRef = T6._KindRRef,

 

 _ValueDt2_TYPE = T8._Value_TYPE,

 _ValueDt2_RTRef = T8._Value_RTRef,

 _ValueDt2_RRRef =  T8._Value_RRRef,

 _KindDt2RRef = T8._KindRRef,

 

 _ValueCt2_TYPE = T10._Value_TYPE,

 _ValueCt2_RTRef =  T10._Value_RTRef,

 _ValueCt2_RRRef =  T10._Value_RRRef,

 _KindCt2RRef = T10._KindRRef,

 

 _ValueDt3_TYPE = T12._Value_TYPE,

 _ValueDt3_RTRef =  T12._Value_RTRef,

 _ValueDt3_RRRef =  T12._Value_RRRef,

 _KindDt3RRef = T12._KindRRef,

 

 _ValueCt3_TYPE = T14._Value_TYPE,

 _ValueCt3_RTRef =  T14._Value_RTRef,

 _ValueCt3_RRRef =  T14._Value_RRRef,

 _KindCt3RRef = T14._KindRRef

FROM _AccRg68NG T2

LEFT OUTER JOIN _Acc8_ExtDim63 T3

ON T3._Acc8_IDRRef = T2._AccountDtRRef AND T3._LineNo = CAST(1 AS  NUMERIC)

LEFT OUTER JOIN _AccRgED96NG T4

ON T4._Period = T2._Period AND T4._Rec..

В данном запросе видно заполнение ряда колонок по временным таблицам. При детальном анализе списка изменений платформы выяснилось, что в релизе 8.3.15 были внесены доработки в части оптимизации регистра бухгалтерии, которые при сохранении конфигурации и пытались выполниться:

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

Результаты анализа сброса индекса в таблице

Восстановление индекса

В случае если индекс все-таки пропал, то достаточно повторить математику создания индекса.

Пока создание индекса не является штатной функциональностью платформы можно воспользоваться следующей функцией.

Данная функция формирует текст запроса создания индекса регистра накопления для Microsoft SQL Server и PostgreSQL, но при желании можно легко адаптировать и для других объектов:

// Формирование текста скрипта СУБД для создания индексов регистров.
//
// Параметры:
// ТипСУБД - Строка - Значения: "MSSQL", "PostgreSQL".
// ПараметрыНовыхИндексов - Соответствие - Параметры добавления индексов:
// Ключ - Метаданные регистра.
// Значение - Структура, где ключ Наименование измерения".
// Например: 
// ПараметрыНовыхИндексов = Новый Соответствие;
// ПараметрыНовыхИндексов[Метаданные.РегистрыНакопления.Запасы] = 
// Новый Структура("Организация,Номенклатура");
// ДополнятьСтандартнымиПолями - Булево	- Необходимость добавления платформенных полей.
// 
// Возвращаемое значение:
// Строка - Скрипт для СУБД.
//
&НаСервереБезКонтекста 
Функция ПолучитьТекстСкриптаФормированияИндексов(ТипСУБД, ПараметрыНовыхИндексов, ДополнятьСтандартнымиПолями = Истина)
	Результат = "";
	Если ТипСУБД = "MSSQL" Тогда
		ТекстШаблонаШапки = "SET ANSI_PADDING ON
		|GO
		|IF ((SELECT INDEXPROPERTY(object_id('[dbo].[%1]') , '[%1_rarus]', 'IndexID')) IS NULL)
		|CREATE UNIQUE NONCLUSTERED INDEX [%1_rarus] ON [dbo].[%1]
		|(";
		ТекстШаблонаПоля = "[%1] ASC";
		ТекстШаблонаПодвала = ")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
		|GO
		|;
		|";
	ИначеЕсли ТипСУБД = "PostgreSQL" Тогда
		ТекстШаблонаШапки = "CREATE UNIQUE INDEX IF NOT EXISTS %1_rarus
		|ON public.%1 USING btree
		|(";
		ТекстШаблонаПоля = "%1 ASC NULLS LAST";
		ТекстШаблонаПодвала = ")
		|TABLESPACE pg_default;
		|";
	Иначе
		Возврат Результат;
	КонецЕсли;
	СоответствиеМетаданных = Новый Соответствие;
	ОтборПоМетаданным = Новый Массив;
	Для Каждого ТекМетаданные Из ПараметрыНовыхИндексов Цикл
		ПолноеИмяРегистра = ТекМетаданные.Ключ.ПолноеИмя();
		ОтборПоМетаданным.Добавить(ТекМетаданные.Ключ);
		Если НЕ ДополнятьСтандартнымиПолями Тогда
			СоответствиеМетаданных[ПолноеИмяРегистра] = ТекМетаданные.Значение;
			Продолжить;
		КонецЕсли;
		СтруктураИндекса = Новый Структура;
		СтруктураИндекса.Вставить("ОбластьДанныхОсновныеДанные");
		Для Каждого ТекПоле Из ТекМетаданные.Значение Цикл
			СтруктураИндекса.Вставить(ТекПоле.Ключ);
		КонецЦикла;
		// Список стандартных полей. Лучше заполнять по аналогии с платформенными.
		СтруктураИндекса.Вставить("Период");
		СтруктураИндекса.Вставить("Регистратор");
		СтруктураИндекса.Вставить("НомерСтроки");
		СоответствиеМетаданных[ПолноеИмяРегистра] = СтруктураИндекса;
	КонецЦикла;
	МассивСтрокСкрипта = Новый Массив;
	СтруктураПоиска = Новый Структура("ИмяПоля");
	// Заполнения наименования полей в терминологии СУБД.	
	ТаблицаСтруктуры = ПолучитьСтруктуруХраненияБазыДанных(ОтборПоМетаданным,Истина);
	Для Каждого ТекСтрока Из ТаблицаСтруктуры Цикл
		СтруктураИндекса = СоответствиеМетаданных[ТекСтрока.ИмяТаблицы];
		Если СтруктураИндекса = Неопределено Тогда
			Продолжить;
		КонецЕсли;
		МассивПолейИндекса = Новый Массив;
		Для Каждого ТекСвойство Из СтруктураИндекса Цикл
			СтруктураПоиска.ИмяПоля = ТекСвойство.Ключ;
			МассивПолей = ТекСтрока.Поля.НайтиСтроки(СтруктураПоиска);
			Для Каждого ТекПоле Из МассивПолей Цикл
				МассивПолейИндекса.Добавить(СтрШаблон(ТекстШаблонаПоля, ТекПоле.ИмяПоляХранения));
			КонецЦикла;
		КонецЦикла;
		Если НЕ ЗначениеЗаполнено(МассивПолейИндекса) Тогда
			Продолжить;
		КонецЕсли;
		МассивСтрокСкрипта.Добавить(СтрШаблон(ТекстШаблонаШапки, ТекСтрока.ИмяТаблицыХранения));
		МассивСтрокСкрипта.Добавить(СтрСоединить(МассивПолейИндекса, ", 
		|"));
		МассивСтрокСкрипта.Добавить(ТекстШаблонаПодвала);
	КонецЦикла;
	Результат = СтрСоединить(МассивСтрокСкрипта, Символы.ПС);
	Возврат Результат;
КонецФункции

Регламент восстановления индекса

Для поддержки базы данных в стабильном состоянии необходимо выполнять такие операции как обновление статистики, реорганизации индексов, перестроения индексов и т. п. Чтобы не запускать их самостоятельно в MS SQL Server существуют механизм планов обслуживания (learn.microsoft.com/ru-ru/sql/relational-databases/maintenance-plans/maintenance-plans?view=sql-server-ver15).

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

Давайте рассмотрим пример создания нового плана обслуживания для такого мини-сценария. Для этого в Management Studio необходимо в группе Management — Maintenance Plans folder вызвать контекстное меню и выбрать New Maintenance Plan:

После чего можно выбрать вложенный план обслуживания, переименовать его и установить расписание запуска:

Завершив редактирование основных реквизитов вложенного плана можно перейти к созданию задачи. Для этого нужно раскрыть Toolbox (Панель элементов) и выбрать Execute T-SQL Statement Task (Задача: Выполнение инструкции T-SQL):

По итогу система создаст графический элемент данной задачи. Выбираем данный элемент и в поле ввода вставляем скрипт создания индекса. Если используется несколько баз данных, то в начало скрипта можно указать имя базы для которой выполнять данный скрипт:

USE <ИмяБазыДанных>; 

GO

Останется сохранить полученный план обслуживания:

Если речь идет о PostgreSQL под Linux, то можно организовать регламент восстановления индекса через планировщик cron.

Для этого нужно создать sh-скрипт, который будет вызывать запрос создания индекса:

#!/bin/bash 

  echo "Создаем  индекс"

  psql -U <pg_user> -d <db_name> -c "CREATE INDEX IF NOT EXISTS <index_name>

     ON public.<table_name> USING btree

     (<field_name1> ASC NULLS LAST, <field_name2> ASC NULLS LAST)

     TABLESPACE <tablespace_name>;" 

Сохраним скрипт в файл, например, под именем DB_index_recovery.sh, далее его можно добавить в планировщик cron. Для этого нужно вызвать редактор файла настроек командой «crontab -e» и в конец файла добавить строку с полным путем к скрипту.
Например, настройка запуска для нашего скрипта с выполнением раз в 20 минут будет выглядеть так:

Заключение

Давайте попробуем сделать выводы.

• Существуют случаи, когда свойство «Индексировать» не решает задачу скорости.
• Таких случаев достаточно много.
• Для того, чтобы понять полезен ли или нет будет индекс необходимо уметь работать с планами запроса.
• Если по плану вы видите, что индекс не работает, не всегда нужно бежать и делать другой. Возможно, что причина в ином, например, в необновленной статистике.
• В редких случаях бывают ситуации, когда механизмов платформы по работе с индексами вам может не хватить.
• Индексы в MS SQL и PostgreSQL работают по-разному.