Устройство регистра сведений, итоги, детальный разбор

02.08.2024

• Состав тестового стенда
• Индексы регистра сведений
• Регистр сведений без включения итогов, чтение
• Регистр сведений с включенной таблицей итогов 'СрезПоследних', чтение
• Регистр сведений без итогов, запись методом:
• добавления
• замещения
• слияния
• удаления
• Регистр сведений с итогами, запись методом добавления
• Заключение

Состав тестового стенда

• ОС: Linux на ядре 6.10
• Платформа 1С 8.3.25.1336 (x64), клиент-сервер Как установить? Нет серверной лицензии 1С?
• PostgesPro 16, настройки по рекомендациям с сайта ИТС 1С Как установить?

Индексы регистра сведений

Полная информация обо всех индексах в наилучшем виде содержится в статье на ИТС.

Регистр сведений без включения таблицы итогов

В новой пустой базе создадим регистр сведений РС_1:

Скриншоты

Итоги не включены:

Скриншоты

Физическая таблица создалась всего одна: Описание таблиц

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

Г = Новый ГенераторСлучайныхЧисел();
НачДата = Дата("20240101");   
Для Ч = 1 По 100 Цикл
  НаборЗаписей1 = РегистрыСведений.РС_2.СоздатьНаборЗаписей();
  Для Ч1 = 1 По 100000 Цикл
    НоваяЗапись = НаборЗаписей1.Добавить();
    НоваяЗапись.Период = НачДата;
    НоваяЗапись.Измерение1 = Г.СлучайноеЧисло(0, 9); 
    НоваяЗапись.Ресурс1 = Г.СлучайноеЧисло(0, 999999);
    НачДата = НачДата + 3;
  КонецЦикла;	
  НаборЗаписей1.Записать(РежимЗамещения.Добавление); // 8.3.25 (!)
КонецЦикла;

Чтобы избежать нехватки оперативной памяти запись делаем порциями по 100 тыс. строк. Обратите внимание на новую фишку платформы 8.3.25: РежимЗамещения.Добавление. Кроме старых двух режимов замещения разработчики платформы добавили ещё два новых: слияние и удаление. Все эти режимы будут рассмотрены в данной статье.

Измерение1 заполнено так, что запрос по виртуальной таблице среза последних должен вернуть всего 10 строк, убеждаемся с помощью консоли запросов что это так:

ВЫБРАТЬ
  РС_1СрезПоследних.Период КАК Период,
  РС_1СрезПоследних.Измерение1 КАК Измерение1,
  РС_1СрезПоследних.Ресурс1 КАК Ресурс1
ИЗ
  РегистрСведений.РС_1.СрезПоследних(&Период, ) КАК РС_1СрезПоследних  

Если всё верно, снимаем с помощью технологического журнала текст реального запроса по событию DBPOSTGRS: Скачать настройку ТЖ

SELECT
  T1.Period_, T1.Fld48_, T1.Fld49_
FROM  
  (SELECT
    T4._Period AS Period_, T4._Fld48 AS Fld48_, T4._Fld49 AS Fld49_
  
  FROM 
    (SELECT
      T3._Fld48 AS Fld48_,
      MAX(T3._Period) AS MAXPERIOD_
      FROM _InfoRg47 T3
    WHERE T3._Period <= '2024-08-01 00:00:00'::timestamp
    GROUP BY T3._Fld48) T2
  
  INNER JOIN _InfoRg47 T4
    ON T2.Fld48_ = T4._Fld48 AND T2.MAXPERIOD_ = T4._Period) T1

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

Запрос выполнялся 2.1 секунды. Смотрим план выполнения запроса:

План выполнения запроса

Как можно прокомментировать этот план:

• Планировщик использовал Seq Scan (прочитал и обошёл всю физическую таблицу данных регистра сведений) при выполнении отбора по периоду в конструкции WHERE, что заняло 460 ms. Здесь мог бы использоваться Index Only Scan, так как у нас индекс покрывающий. Причин игнора покрывающего индекса может быть несколько: а) у нашего регистра физический размер таблицы индексов примерно такой же как таблица данных, поэтому ускорения скорее всего не было бы; б) статистика показала что таблица заполнена "белым шумом"; в) сброшен Visibility Map (пояснения см. ниже)
• Для выполнения операции GROUP BY применился обработчик HashAggregate, что тоже заняло 718 ms на большом количестве строк.

Здесь стоить заметить, что если на СУБД AUTOVACUUM настроен недостаточно агрессивно (а в конфигах по умолчанию применительно к 1С это так), то он не будет успевать вовремя устанавливать Visibility Map после каждого изменения данных в регистре, что приведет к резкому росту числа фетчей при выполнении Index Only Scan или отказу планировщика от использования составного индекса. Подробности тут и тут

Давайте теперь в консоли запросов заменим текст запроса к срезу последних на его эквивалент:

ВЫБРАТЬ
  T4.Период КАК Период,
  T4.Измерение1 КАК Измерение1,
  T4.Ресурс1 КАК Ресурс1
ИЗ
  (ВЫБРАТЬ
    T3.Измерение1 КАК Измерение1,
    МАКСИМУМ(T3.Период) КАК МаксПериод
  ИЗ
    РегистрСведений.РС_1 КАК T3
  ГДЕ
    T3.Период <= &Период
   
  СГРУППИРОВАТЬ ПО
    T3.Измерение1) КАК T2
  
  ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.РС_1 КАК T4
  ПО T2.Измерение1 = T4.Измерение1
    И T2.МаксПериод = T4.Период  

Переписав запрос мы заодно избавились от лишнего SELECT, но тем не менее в плане запроса ничего существенно не изменилось:

План выполнения запроса

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

План выполнения запроса

Напоследок, вспомним требования курса "Специалист по платформе" и перепишем запрос с помощью временных таблиц, ведь мы 1С-ники и любим это делать:

ВЫБРАТЬ
  РС_1.Измерение1 КАК Измерение1,
  МАКСИМУМ(РС_1.Период) КАК МаксПериод
ПОМЕСТИТЬ ВТ_Максимумы
ИЗ
  РегистрСведений.РС_1 КАК РС_1
ГДЕ
  РС_1.Период <= &Период

СГРУППИРОВАТЬ ПО
  РС_1.Измерение1
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
  ВТ_Максимумы.Измерение1 КАК Измерение1,
  ВТ_Максимумы.МаксПериод КАК Период,
  РС_1.Ресурс1 КАК Ресурс1
ИЗ
  ВТ_Максимумы КАК ВТ_Максимумы
  ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.РС_1 КАК РС_1
  ПО ВТ_Максимумы.Измерение1 = РС_1.Измерение1
    И ВТ_Максимумы.МаксПериод = РС_1.Период

Поздравляю, у нас отрицательный рост! 😇

План выполнения запроса

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

Регистр сведений с включенной таблицей итогов 'СрезПоследних'

Включим в нашем регистре итоги:

Скриншоты

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

Физических таблиц прибавилось:

Выполним запрос к виртуальной таблице 'СрезПоследних' с параметром &Период:

ВЫБРАТЬ
  РС_1СрезПоследних.Период КАК Период,
  РС_1СрезПоследних.Измерение1 КАК Измерение1,
  РС_1СрезПоследних.Ресурс1 КАК Ресурс1
ИЗ
  РегистрСведений.РС_1.СрезПоследних(&Период, ) КАК РС_1СрезПоследних  

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

А теперь выполним этот же запрос, но без параметра:

ВЫБРАТЬ
  РС_1СрезПоследних.Период КАК Период,
  РС_1СрезПоследних.Измерение1 КАК Измерение1,
  РС_1СрезПоследних.Ресурс1 КАК Ресурс1
ИЗ
  РегистрСведений.РС_1.СрезПоследних() КАК РС_1СрезПоследних  

План выполнения запроса

Скорость выполнения мгновенная, меньше 1 ms! Секрет в том, что платформа 1С теперь перенаправила запрос на новую физическую таблицу со срезом последних, в котором всего 10 строк:

SELECT
  T1.Period_, T1.Fld48_, T1.Fld49_
FROM (SELECT
  T2._Period AS Period_, 
  T2._Fld48 AS Fld48_,
  T2._Fld49 AS Fld49_
FROM _InfoRgSL59 T2) T1  

SELECT * FROM public."_inforgsl59";
  _period                |_fld48|_fld49|
  -----------------------+------+------+
  2024-12-13 05:19:57.000|     8| 68058|
  2024-12-13 05:19:48.000|     6|957820|
  2024-12-13 05:19:54.000|     5|204052|
  2024-12-13 05:18:15.000|     9|594649|
  2024-12-13 05:19:51.000|     2|386668|
  2024-12-13 05:19:33.000|     3|916543|
  2024-12-13 05:19:39.000|     0| 40560|
  2024-12-13 05:18:39.000|     7|665524|
  2024-12-13 05:19:30.000|     1|215676|
  2024-12-13 05:18:57.000|     4|871707|

Заплатили мы за скорость ценой замедления записи, но какой ценой! Разберемся с этой бедой в следующих главах.

Регистр сведений без итогов, запись методом добавления

Для добавления новых записей, не затирая старых, в режиме совместимости 8.3.25 и выше указывается параметр РежимЗамещения.Добавление:

Процедура ЗаписатьДобавлением(ДатаНачала)
    
  Г = Новый ГенераторСлучайныхЧисел();
  НачДата = ДатаНачала;
    
  НаборЗаписей1 = РегистрыСведений.РС_1.СоздатьНаборЗаписей();
  Для Ч1 = 1 По 100 Цикл
    НоваяЗапись = НаборЗаписей1.Добавить();
    НоваяЗапись.Период = НачДата;
    НоваяЗапись.Измерение1 = Г.СлучайноеЧисло(0, 9); 
    НоваяЗапись.Ресурс1 = Г.СлучайноеЧисло(0, 999999);
    НачДата = НачДата + 3;
  КонецЦикла;	
  НаборЗаписей1.Записать(РежимЗамещения.Добавление);
    
КонецПроцедуры

Блок-схема процесса записи:

Все запросы СУБД выполняются эффективно и по оптимальным планам с использованием индексов. Наиболее значительные моменты:

1. Транзакция: весь процесс проиходит в единой транзакции, за исключением очистки временных таблиц, для которых применяется внетранзакционный небезопасный вызов SELECT FASTTRUNCATE (Добавлено в PostgreSQL патчем специально для 1С).

2. Блокировка записей накладывается исключительная на основную таблицу регистра, по всем измерениям из набора записей. В случае таймаута вызываются события TTIMEOUT и EXCP.

3. Контроль уникальности измерений выполняется путём выполнения запроса, при этом весьма эффективно используется покрывающий индекс:

SELECT T1._Period, T1._Fld48, T1._Fld49
FROM pg_temp.tt4 T1
INNER JOIN _InfoRg47 T2
  ON T1._Period = T2._Period AND T1._Fld48 = T2._Fld48

План выполнения запроса

4. Копирование из временной в основную выполняется быстрой инструкцией INSERT:

INSERT INTO _InfoRg47 (_Period, _Fld48, _Fld49)
SELECT T1._Period, T1._Fld48, T1._Fld49
FROM pg_temp.tt4 T1

Регистр сведений без итогов, запись методом замещения

Чтобы затереть полностью регистр сведений и записать новый набор, в режиме совместимости 8.3.25 и выше, при записи указывается параметр РежимЗамещения.Замещение:

Процедура ЗаписатьЗамещением(ДатаНачала)
    
  Г = Новый ГенераторСлучайныхЧисел();
  НачДата = ДатаНачала;
    
  НаборЗаписей1 = РегистрыСведений.РС_1.СоздатьНаборЗаписей();
  Для Ч1 = 1 По 100 Цикл
    НоваяЗапись = НаборЗаписей1.Добавить();
    НоваяЗапись.Период = НачДата;
    НоваяЗапись.Измерение1 = Г.СлучайноеЧисло(0, 9); 
    НоваяЗапись.Ресурс1 = Г.СлучайноеЧисло(0, 999999);
    НачДата = НачДата + 3;
  КонецЦикла;	
  НаборЗаписей1.Записать(РежимЗамещения.Замещение);
    
КонецПроцедуры

Блок-схема процесса записи:

Наиболее значительные моменты:

1. Транзакция: весь процесс проиходит в единой транзакции.

2. Блокировка записей накладывается исключительная и полностью на всю таблицу регистра. В случае таймаута вызываются события TTIMEOUT и EXCP. Обратите внимание, что это единственный режим записи, при котором пользовательская процедура ПриЗаписи() вызывается не до наложения блокировки, а после.

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

DELETE FROM _InfoRg47

План выполнения запроса

3. Вставка строк:

COPY _InfoRg47 FROM STDIN BINARY

Регистр сведений без итогов, запись методом слияния

Это новый режим записи, добавленный в платформе 1С 8.3.25. В этом режиме набор записываемых данных может заменить старые записи и одновременно добавить новые. Указывается параметр РежимЗамещения.Слияние:

Процедура ЗаписатьСлиянием(ДатаНачала)
    
  Г = Новый ГенераторСлучайныхЧисел();
  НачДата = ДатаНачала;
    
  НаборЗаписей1 = РегистрыСведений.РС_1.СоздатьНаборЗаписей();
  Для Ч1 = 1 По 100 Цикл
    НоваяЗапись = НаборЗаписей1.Добавить();
    НоваяЗапись.Период = НачДата;
    НоваяЗапись.Измерение1 = Г.СлучайноеЧисло(0, 9); 
    НоваяЗапись.Ресурс1 = Г.СлучайноеЧисло(0, 999999);
    НачДата = НачДата + 3;
  КонецЦикла;	
  НаборЗаписей1.Записать(РежимЗамещения.Слияние);
    
КонецПроцедуры

Блок-схема процесса записи:

Все запросы СУБД выполняются эффективно и по оптимальным планам с использованием индексов. Наиболее значительные моменты:

1. Транзакция: весь процесс проиходит в единой транзакции, за исключением очистки временных таблиц, для которых применяется внетранзакционный небезопасный вызов SELECT FASTTRUNCATE (Добавлено в PostgreSQL патчем специально для 1С).

2. Блокировка записей накладывается исключительная на основную таблицу регистра, по всем измерениям из набора записей. В случае таймаута вызываются события TTIMEOUT и EXCP.

3. Обновление существующих записей основной таблицы регистра выполняется всего одним запросом:

UPDATE _InfoRg47 SET _Fld49 = T2._Fld49
FROM pg_temp.tt4 T2
WHERE T2._Period = _InfoRg47._Period AND T2._Fld48 = _InfoRg47._Fld48

План выполнения запроса

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

INSERT INTO _InfoRg47 (_Period, _Fld48, _Fld49) SELECT
  T1._Period, T1._Fld48, T1._Fld49
  FROM pg_temp.tt4 T1
  WHERE NOT (EXISTS(SELECT
    CAST(1 AS NUMERIC)
    FROM _InfoRg47 T2
    WHERE T1._Period = T2._Period AND T1._Fld48 = T2._Fld48))

План выполнения запроса

Регистр сведений без итогов, запись методом удаления

Это новый режим записи, добавленный в платформе 1С 8.3.25. В этом режиме удаляются только те записи, которые содержатся по измерениям в наборе. Это очень эффективное нововведение, которое может дать ускорение в сотни и тысячи раз для выборочного удаления записей из регистра сведений! Указывается параметр РежимЗамещения.Удаление:

Процедура ЗаписатьУдалением(ДатаНачала, ДатаОкончания)

  Запрос = Новый Запрос;
  Запрос.Текст = "ВЫБРАТЬ
    |    РС_1.Период КАК Период,
    |    РС_1.Измерение1 КАК Измерение1,
    |    РС_1.Ресурс1 КАК Ресурс1
    |ИЗ
    |    РегистрСведений.РС_1 КАК РС_1
    |ГДЕ
    |    РС_1.Период МЕЖДУ &ДатаНачала И &ДатаОкончания";
  Запрос.УстановитьПараметр("ДатаНачала", ДатаНачала);
  Запрос.УстановитьПараметр("ДатаОкончания", ДатаОкончания);
    
  НаборЗаписей1 = РегистрыСведений.РС_1.СоздатьНаборЗаписей();
  НаборЗаписей1.Загрузить(Запрос.Выполнить().Выгрузить());
  НаборЗаписей1.Записать(РежимЗамещения.Удаление);

КонецПроцедуры

Блок-схема процесса записи:

Все запросы СУБД выполняются эффективно и по оптимальным планам с использованием индексов. Наиболее значительные моменты:

1. Транзакция: весь процесс проиходит в единой транзакции, за исключением очистки временных таблиц, для которых применяется внетранзакционный небезопасный вызов SELECT FASTTRUNCATE (Добавлено в PostgreSQL патчем специально для 1С).

2. Блокировка записей накладывается исключительная на основную таблицу регистра, по всем измерениям из набора записей. В случае таймаута вызываются события TTIMEOUT и EXCP.

3. Выборочное удаление производится эффективным запросом с использованием индекса основной таблицы (в регистре было 10 миллионов строк):

DELETE FROM _InfoRg47 T1
WHERE EXISTS(SELECT
  1
  FROM pg_temp.tt4 T2
  WHERE (T1._Period = T2._Period) AND (T1._Fld48 = T2._Fld48))

План выполнения запроса

Регистр сведений с итогами, запись методом добавления

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

Процедура ЗаписатьДобавлением(ДатаНачала)
    
  Г = Новый ГенераторСлучайныхЧисел();
  НачДата = ДатаНачала;
    
  НаборЗаписей1 = РегистрыСведений.РС_1.СоздатьНаборЗаписей();
  Для Ч1 = 1 По 100 Цикл
    НоваяЗапись = НаборЗаписей1.Добавить();
    НоваяЗапись.Период = НачДата;
    НоваяЗапись.Измерение1 = Г.СлучайноеЧисло(0, 9); 
    НоваяЗапись.Ресурс1 = Г.СлучайноеЧисло(0, 999999);
    НачДата = НачДата + 3;
  КонецЦикла;	
  НаборЗаписей1.Записать(РежимЗамещения.Добавление);
    
КонецПроцедуры

Блок-схема процесса записи в целом совпадает со схемой записи без итогов, в него теперь добавились 2 новых блока:

Изучим внимательнее изучим их:

1. Обновление таблицы итогов: здесь обнаруживается адский ад:

UPDATE _InfoRgSL59 SET _Period = T2.Period_, _Fld49 = T2.Fld49_
FROM 
  (SELECT
    T6._Period AS Period_,
    T6._Fld48 AS Fld48_,
    T6._Fld49 AS Fld49_
  FROM 
    (SELECT
      T4._Fld48 AS Fld48_,
      MAX(T4._Period) AS MINMAX_PERIOD_
    FROM _InfoRg47 T4
    INNER JOIN pg_temp.tt2 T5
      ON T4._Fld48 = T5._Fld48
    GROUP BY T4._Fld48) T3
  INNER JOIN _InfoRg47 T6
    ON T6._Fld48 = T3.Fld48_ AND T6._Period = T3.MINMAX_PERIOD_) T2
  WHERE (_InfoRgSL59._Fld48 = T2.Fld48_) AND (_InfoRgSL59._Period <= T2.Period_)

План выполнения запроса

При выполнении задачи платформа из благих побуждений решила сократить в подзапросе количество строк для выполнения основной агрегатной функции, поэтому вставила лишний INNER JOIN с временной таблицей (см. INNER JOIN pg_temp.tt2 T5 ON T4._Fld48 = T5._Fld48). Postgres для выполнения использовал соединение методом HashJoin, причём при нашем заполнении данными это ещё оказалось и совершенно лишней операцией с затратой лишних 17 секунд.

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

2. Добавление в таблицу итогов:

INSERT INTO _InfoRgSL59 (_Period, _Fld48, _Fld49)
SELECT
  T5._Period,
  T5._Fld48,
  T5._Fld49
FROM 
  (SELECT
    T2._Fld48 AS Fld48_,
    MAX(T2._Period) AS MINMAX_PERIOD_
  FROM _InfoRg47 T2
  INNER JOIN pg_temp.tt2 T3
    ON T2._Fld48 = T3._Fld48
  WHERE NOT (EXISTS
    (SELECT
      CAST(1 AS NUMERIC)
    FROM _InfoRgSL59 T4
    WHERE T3._Fld48 = T4._Fld48))
  GROUP BY T2._Fld48) T1
INNER JOIN _InfoRg47 T5
  ON T5._Fld48 = T1.Fld48_ AND T5._Period = T1.MINMAX_PERIOD_

План выполнения запроса

Почему такие сложности? Ведь можно было просто дропнуть физическую таблицу итогов и записать его целиком в один заход потратив 1 секунду, как было при запросах на чтение? Ответ заключается в том, что 1С - это многопользовательская система и в момент сразу после дропа таблицы итогов кто-то другой смог бы прочитать его пустым. При записи исключительная блокировка накладывается только на основную физическую таблицу!

Заключение

Платформа 1С 8.3.25 с периодическими регистрами сведений на миллионы строк без включения итогов на СУБД PostgreSQL работает вполне оптимально. При включении итогов уже не очень - фирме 1С есть что здесь улучшать. Я бы не стал включать итоги для решения большинства задач, а использовал бы для ускорения запросов отдельный регистр.