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

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

28.12.2022
140 мин
39000

Оглавление

  1. Введение
  2. Кейс #1. Низкая скорость группового перепроведения складских и производственных документов и оптимизация перепроведения
    1. Инфраструктура исследуемой системы
    2. Статистика по информационной базе
    3. Сбор технологического журнала
    4. Поиск долгих запросов
    5. Устройство виртуальной таблицы остатков
    6. Анализ плана запроса в MS SQL и предположение о нехватке индекса
    7. Попытка решения средствами 1С
    8. Альтернативные способы управления индексами
    9. Проверяем, что дало создание индексов
    10. Какие результаты дала оптимизация индексов для ускорения перепроведения документов
  3. Кейс #2. Долгое построение отчета о прибылях и убытках
    1. Инфраструктура исследуемой системы
    2. Проводим замеры производительности при построении отчета о прибылях и убытках
    3. Получение плана запроса в PostgreSQL (Explain Analyze)
    4. Немного о прочтении плана запроса и инструментах
    5. Поиск решения средствами 1С
    6. Повторный поиск решения, анализ недостающих индексов
    7. Добавление индекса вручную и проверка результата
  4. Поддержка добавленных индексов
    1. Результаты анализа сброса индекса в таблице
    2. Восстановление индекса
    3. Регламент восстановления индекса
  5. Заключение

Введение

Данная статья содержит два кейса с расследованием снижения производительности учетных систем на базе 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С-Рарус: Сценарный обработчик конфигураций» с модулем для анализа технологического журнала, про которую более подробно мы поговорим в следующих публикациях.

Внимание! Указанное описание продукта и его настройки оставлено исключительно для сохранения истории развития наших продуктов.

Модуль анализа технологического журнала для решения 1С-Рарус:СОК на текущий момент не используется, для парсинга логов ТЖ и их визуализации применяется наш актуальный сервис «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) КАК СуммаОстатокЗапасы
|ИЗ
|	ДвиженияЗапасыИзменение КАК ДвиженияЗапасыИзменение
|		ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Запасы.Остатки(
|				&amp;МоментКонтроля,
|				(Организация, СтруктурнаяЕдиница, СчетУчета, Номенклатура, Характеристика, Партия, ЗаказПокупателя) В
|					(ВЫБРАТЬ
|						ДвиженияЗапасыИзменение.Организация КАК Организация,
|						ДвиженияЗапасыИзменение.СтруктурнаяЕдиница КАК СтруктурнаяЕдиница,
|						ДвиженияЗапасыИзменение.СчетУчета КАК СчетУчета,
|						ДвиженияЗапасыИзменение.Номенклатура КАК Номенклатура,
|						ДвиженияЗапасыИзменение.Характеристика КАК Характеристика,
|						ДвиженияЗапасыИзменение.Партия КАК Партия,
|						ДвиженияЗапасыИзменение.ЗаказПокупателя КАК ЗаказПокупателя
|					ИЗ

|						ДвиженияЗапасыИзменение КАК ДвиженияЗапасыИзменение)) КАК ЗапасыОстатки
|		ПО ДвиженияЗапасыИзменение.Организация = ЗапасыОстатки.Организация
|			И ДвиженияЗапасыИзменение.СтруктурнаяЕдиница = ЗапасыОстатки.СтруктурнаяЕдиница
|			И ДвиженияЗапасыИзменение.СчетУчета = ЗапасыОстатки.СчетУчета
|			И ДвиженияЗапасыИзменение.Номенклатура = ЗапасыОстатки.Номенклатура
|			И ДвиженияЗапасыИзменение.Характеристика = ЗапасыОстатки.Характеристика
|			И ДвиженияЗапасыИзменение.Партия = ЗапасыОстатки.Партия
|			И ДвиженияЗапасыИзменение.ЗаказПокупателя = ЗапасыОстатки.ЗаказПокупателя
|ГДЕ
|	ЕСТЬ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 и предположение о нехватке индекса

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

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

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

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

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

Наименование индекса Поля, входящие в индекс
_AccumRg31553_1 ОбластьДанныхОсновныеДанные (_Fld1608), Период (_Period), Тип регистратора (_RecorderTRef), Регистратор (_RecorderRRef), Номер строки (_LineNo)
_AccumRg31553_2 Область данных основные данные (_Fld1608), Тип регистратора (_RecorderTRef), Регистратор (_RecorderRRef), Номер строки (_LineNo)
_AccumRg31553_3 Область данных основные данные (_Fld1608), Организация (_Fld31554RRef), Период (_Period), Тип регистратора (_RecorderTRef), Регистратор (_RecorderRRef), Номер строки (_LineNo)
_AccumRg31553_4 Область данных основные данные (_Fld1608), Номенклатура(_Fld31556RRef), Период (_Period), Тип регистратора (_RecorderTRef), Регистратор (_RecorderRRef), Номер строки (_LineNo)
_AccumRg31553_5 Область данных основные данные (_Fld1608), Заказ покупателя (_Fld31559RRef), Период (_Period), Тип регистратора (_RecorderTRef), Регистратор (_RecorderRRef), Номер строки (_LineNo)

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

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

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

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

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

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

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

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

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

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

Таким образом, при выполнении отбора по временной таблице даже по имеющим свои индексы 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Потому для решения данной задачи средствами 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. Долгое построение отчета о прибылях и убытках

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

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

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

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

Кейс #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>

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

cat *.log|sed -r '/p_[0-9]+: /d'|sed -e 's/\xef\xbb\xbf//g; s/Usr=.+,Sql=/ ,Sql=/; s/^[0-9][0-9]\:[0-9][0-9]\.[0-9]*-[0-9]*,Context,.,Context=/ ,Context=/;s/,DBPOSTGRS,.*,Sql=/DBPOSTGRS,Sql=/g;s/#tt[0-9]*/#tt/g;s/^[0-9][0-9]\:[0-9][0-9]\.[0-9]*-/<LineBreake>/'|awk -F'DBPOSTGRS' -vRS='<LineBreake>' '{if ($2 !~/^$/) {Dur[$2]=Dur[$2]+$1; Execs[$2]=Execs[$2]+1}} END {for (i in Dur) print "NewLine" Dur[i] "=Duration," Execs[i] "=ExecsN" i}'|sed -r 's/\s/ /'|sed -r '/^$/d'|tr '\n' '@'|sed -r 's/NewLine/\n/g'|sort -rnb|head -n50|sed -r 's/@/\n/g'>DBPOSTGRS_top.txt

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

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

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

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

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

  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++, в которой используя поиск по текущему документу по имени события, можно получить лаконичный список событий со временем выполнения, где долгие события визуально выделяются:

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

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

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

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

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

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

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

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

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

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

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

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

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

Приведем небольшую справку по операторам postgres:

  • Seq Scan — последовательное чтение всей таблицы, аналог в MS SQL — Table Scan.
  • Index Scan — оператор чтения из индекса, при котором получаются необходимые адреса строк и далее считываются из основной таблицы. При этом условие в разделе Index Cond используется для поиска по индексу, а блок Filter для фильтрации строк после поиска. Таким образом аналоги оператора в MS SQL — это Index Scan и Index Seek, всё зависит от блоков Index Cond и Filter.
  • Index Only Scan — по сути тоже самое что 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С

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как ранее было описано в статье — для 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 были внесены доработки в части оптимизации регистра бухгалтерии, которые при сохранении конфигурации и пытались выполниться:

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

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

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

Операция Результат
Восстановление из выгрузки информационной базы (dt) Индекс сбрасывается
Восстановление из бэкапа СУБД Индекс сохраняется
Реструктуризация индекса Индекс сбрасывается
Пересчет итогов Индекс сохраняется
Изменение структуры и обновление конфигурации Зависит от изменений
Обновление релиза платформы Зависит от платформы

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

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

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

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

Данная функция формирует текст запроса создания индекса регистра накопления для 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>;"

Пояснения:

  • pg_user — имя пользователя PostgreSQL, у которого есть права на редактирование баз данных.
  • db_name — имя базы данных.
  • index_name — произвольное имя, которое вы хотите присвоить индексу.
  • table_name — имя таблицы индекса.
  • field_name1, field_name2... — имена полей таблицы, которые войдут в индекс.
  • tablespace_name — табличное пространство postgres.

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

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

Заключение

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

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

Есть вопросы по статье? Задайте их нам!

Рассылка «Новости компании»: узнавайте о новых продуктах, услугах и спецпредложениях

Посмотреть все рассылки «1С‑Рарус»

Поле не должно быть пустым
Электронная почта указывается только латиницей, обязательно должен присутствовать знак @, доменное имя не может быть короче двух символов

Посмотреть все рассылки «1С-Рарус»

Иконка «Предупреждение» Отправляя эту форму, Вы соглашаетесь с Политикой конфидециальности и даете согласие на обработку персональных данных компанией «1С-Рарус»

Заинтересованы в сотрудничестве?
Нужна консультация?
Свяжитесь с нами!