Решение проблемы Values в Postgres

12.11.24

База данных - HighLoad оптимизация

Мы исследуем проблему долгого выполнения запросов PostgreSQL при использовании конструкции VALUES: когда она возникает, как на нее можно повлиять, а главное, почему ее продуманная отработка важна для более быстрого функционирования решений на базе 1С

В компании "Тантор Лабс" занимаются разработкой СУБД на базе postgres для 1С - Tantor Special Edition 1C.

При тестировании новой фичи или выпуске нового релиза мы проводим нагрузочные тесты на конфигурациях ЕРП, Документооборот и ЗУП, чтобы убедиться, что ни одна ключевая операция не стала выполняться медленнее и ускорились определенные ключевые операции (о самом процессе тестирования сборки расскажем в следующем году), если дорабатываем, например, планировщик.

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

 

 

Проблема

Нагрузочный тест показал, что проведение документа "Реализация товаров и услуг" длится в среднем 18 секунд.

Возьмем в одном сеансе проведем 20 документов и будем собирать все запросы длительностью более 1 секунды с помощью модуля "Расширенная аналитика" платформы Tantor.

Видим, что 20 раз выполнялся запрос со средней длительностью 3,5 секунды:

 

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

 

С точки зрения SQL, это обычный отбор по регистратору:

 

SELECT
    ...
FROM
    _InfoRg37391X1 T1
    ...
WHERE
    ... AND (T1._RecorderTRef = '\\000\\000\\004\\272'::bytea AND T1._RecorderRRef IN ( VALUES('\\2041\\030f\\332\\261R\\333\\021\\356\\224%dU\\311('::bytea), ('\\2041\\030f\\332\\261R\\333\\021\\356\\224%dU\\311('::bytea)))

 

Казалось бы, селективнейший отбор по регистратору, но почему его postgres не применит сразу, чтобы сократить выборку?

Дело в том, что с точки зрения postgres конструкция VALUES это такой же узел плана запроса, как и скан таблицы, например:

 

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

В нашем запросе участвует более 10 таблиц согласно модели плана запроса:

 

Join
  ->  Scan Table public._document925
  ->  Scan Table public._inforg37391x1
  ->  Scan Table public._inforg43257
  ->  Scan Table public._reference157
  ->  Scan Table public._reference157
  ->  Scan Table public._reference189
  ->  Scan Table public._reference195
  ->  Scan Table public._reference302
  ->  Scan Table public._reference315
  ->  Scan Table public._reference315
  ->  Scan Table public._reference315
  ->  Scan Table public._reference315
  ->  Scan Table public._reference395
  ->  Scan Table public._reference395
  ->  Scan Values "*VALUES*"

 

И в данном случае решить проблему можно, увеличив параметры join_collapse_limit и from_collapse_limit с 10 до 20. Сделаем это и получим следующий план:

 

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

Но изменение данных параметров не панацея, т.к. соединений в запросах 1С может быть и более 20.. Но не только в количестве соединений дело. Вот пример запроса, в котором всего 3 соединения таблиц, и планировщик все равно не может подобрать оптимальный план:

 

После применения отбора из VALUES количество выбираемых строк сокращается в 15 раз.

С точки зрения 1С конструкция VALUES это использование отбора в массиве на языке запросов 1С:

ВЫБРАТЬ
    ...
ИЗ
    РегистрСведений.Регистр1
ГДЕ
    Регистратор в (&МассивРегистраторов)                                                                                               

 

В 1С такие конструкции используются повсеместно, т.к. использовать в качестве параметра запроса массив из нескольких значений является нормальной практикой.

Совсем недавно эта проблема на связке 1С+postgres была решена.

 

Tantor Special Edition 1C 16.4.0 (15.8.0)

Начиная с указанных версий в нашей редакции СУБД, предназначенной для работы с 1С, мы решили данную проблему следующим образом: при выполнении запроса планировщик postgres на стадии парсинга текста запроса подменяет конструкцию VALUES на ANY ARRAY.

Выполняется проблемный запрос менее чем 1 мс, план

 

А вот каким становится план запроса, который выше, независимо от from_collapse_limit и join_collapse_limit выполнялся плохо:

 

Как мы и ожидали, количество выбираемых строк сократилось в 15 раз, и запрос ускорился почти в 4 раза.

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

 

Платформа 1С 8.3.24

Начиная с этой версии, платформа больше не использует конструкцию VALUES. Вместо нее используется просто конструкция IN, которая не приводит к формированию отдельного узла плана запроса, а отбор накладывается сразу. Текст запроса SQL теперь будет следующим:

SELECT
    ...
FROM
    _InfoRg37391X1 T1
    ...
WHERE
    ... AND (T1._RecorderTRef = '\\000\\000\\004\\272'::bytea AND T1._RecorderRRef IN ('\\2041\\030f\\332\\261R\\333\\021\\356\\224%dU\\311('::bytea, '\\2041\\030f\\332\\261R\\333\\021\\356\\224%dU\\311('::bytea)))

 

План запроса: 

 

В чем разница между использованием ANY ARRAY и IN?

С точки зрения планировщика обе конструкции в плане запроса преобразуются в оператор ANY, вы можете это увидеть по планам выше. И, следовательно, работают одинаково.

А с точки зрения разработки, то IN может принимать в качестве параметров как массивы, так и подзапросы. Однако, если мы точно знаем, что в качестве параметра будет массив (а в нашем случае именно так), то имеет смысл использовать именно ANY ARRAY, чтобы сделать код более ясным и понятным.

 

Заключение

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

Если вы по каким-то причинам не можете перейти на платформу 8.3.24, то здесь вам поможет наша СУБД Tantor Special Edition 1C. Мы постоянно работаем над улучшением работы планировщика под специфику запросов 1С и далее планируем знакомить вас с внедряемыми оптимизациями.

Postgres Tantor special edition VALUES платформа 8.3.24 новые возможности платформы оптимизация планировщик план запроса платформа Tantor расширенная аналитика advanced analytics

См. также

HighLoad оптимизация Программист 1С v8.3 1C:ERP Бесплатно (free)

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

18.02.2025    5215    ivanov660    39    

58

HighLoad оптимизация Технологический журнал Системный администратор Программист Бесплатно (free)

Обсудим поиск и разбор причин длительных серверных вызовов CALL, SCALL.

24.06.2024    7857    ivanov660    13    

60

Администрирование СУБД 1С v8.3 1C:Бухгалтерия Россия Бесплатно (free)

При хранении файлов в томах на диске они иногда исчезают. Разбираемся, почему.

23.05.2024    14519    human_new    19    

58

HighLoad оптимизация Инструменты администратора БД Системный администратор Программист 1С v8.3 1C:Бухгалтерия Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих запросов на sql, ожиданий, конвертация запроса в 1С и рекомендации, где может тормозить.

5 стартмани

15.02.2024    15893    310    ZAOSTG    100    

122

Администрирование СУБД Системный администратор Программист Бесплатно (free)

Казалось бы, базовое знание: «индексы надо обслуживать, чтобы запросы выполнялись быстро». Но обслуживание индексов выполняется долго и может мешать работе пользователей. Кроме того, в последнее время популярны разговоры о том, что индексы можно вообще не обслуживать – насколько это оправданно? Рассмотрим: на что влияет обслуживание индексов, когда надо и когда не надо его выполнять, и если надо – как это сделать так, чтобы никому не помешать?

16.01.2024    20526    Филин    17    

54

HighLoad оптимизация Программист 1С v8.3 1C:Бухгалтерия Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    25632    doom2good    49    

72

HighLoad оптимизация Системный администратор Программист Бесплатно (free)

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    17709    ivanov660    7    

83
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. ivanov660 4746 12.11.24 16:44 Сейчас в теме
1. Хорошо бы к планам запроса, еще приводить пример 1С запроса. В противном случае остаётся только покивать головой
2. Ничего не слышал про изменение алгоритма работы 1С, но слышал про оптимизацию оператора IN СУБД Postgres (кажется это произошло в 14 версии) .
2. starik-2005 3180 12.11.24 17:52 Сейчас в теме
(1)
Ничего не слышал про изменение алгоритма работы 1С, но слышал про оптимизацию оператора IN СУБД Postgres
Я вот ничего не слышал про постгрес, но в абдейт-файле 1Са есть такие строчки
Работа с СУБД.
Оптимизировано использование оператора языка запросов В(<список значений>) при работе с использованием СУБД PostgreSQL. В частности, ускорено исполнение запросов ограничения доступа к данным.
Рекомендуется проанализировать условия в запросах и, в случае использования СУБД PostgreSQL, выполнить замену выражений вида Пер1=Пер2 ИЛИ Пер1=Пер3 на выражение Пер1 В (Пер2, Пер3).

Запросы, которые использовали оператор языка запросов В(<список значений>) при работе с использованием СУБД PostgreSQL, требовали больше времени для исполнения. Повышена производительность оператора языка запросов В(<список значений>) при работе с использованием СУБД PostgreSQL.

Источник: V8Update.htm#813e85bd-72de-11ec-aa69-0050569f678a
Для версии 8.3.22
3. Tantor 145 12.11.24 19:35 Сейчас в теме
(1)
1. По ссылке на сайте тензора на вкладке "Оригинал" есть текст запроса. Везде, кроме первого плана.
2. Это информация с партнерского форума 1С. Оптимизация внедрена именно с версии 8.3.24. Я проверял на 8.3.23 - там используется Values
5. nasonkin 42 13.11.24 08:02 Сейчас в теме
(3)
Оптимизация внедрена именно с версии 8.3.24


Только надо учитывать, что у 1С с первого раза редко, что сразу получается и поэтому до версии 8.3.24.1738 в платформе 8.3.24 ошибка 70059473 см. https://bugboard.v8.1c.ru/error/000144798


Описание:
При использовании СУБД PostgreSQL запрос с условием В (IN) может выполняться значительное время.

Способ обхода:
Изменить запрос, исключив использование оператора В (IN), например, изменить это условие на внутреннее соединение с временной таблицей.
triviumfan; +1 Ответить
6. Tantor 145 13.11.24 10:35 Сейчас в теме
(5) Я тестировал на 8.3.25.1445
10. Jimbo 13 13.11.24 15:27 Сейчас в теме
(6) глюков много, знакомые откатились на 24-ю, не уточнил которую именно
8. starik-2005 3180 13.11.24 12:05 Сейчас в теме
(1)
оптимизацию оператора IN СУБД Postgres
Нашел инфу в статье https://infostart.ru/1c/articles/1979106/
Вы, наверное, читали, что есть проблема с быстродействием оператора «В». (Хочу вас обрадовать в PostgreSQL начиная с 15-й версии механизм работы с оператором «В» был переработан и теперь все работает очень хорошо)
При том далее по тексту:
Чтобы вас не огорчать, скажу, что эти скриншоты я снимал на PostgreSQL 11 версии – на ней исходный запрос выполнялся порядка 9 секунд.

Когда мы перешли на 14-ю версию PostgreSQL, исходный запрос стал выполняться 500 миллисекунд. Т.е. они оптимизировали работу из коробки. Но все равно нормальное время выполнения – порядка одной миллисекунды, а по умолчанию получается в 500 раз хуже.
11. ivanov660 4746 13.11.24 16:41 Сейчас в теме
(8) А, точно, давно было дело. На сколько я помню разработчики postgresдоработали этот механизм, так как страдали все, а не только 1С.
Относительно второй фразы дальнейшего - мы сравнивали с работой MS SQL. В некоторых случаях postgres еще не дотягивает до мелкомягких.
И вишенка на торте - основная проблема для планировщика СУБД возникает в случаях, когда в условиях используется составной тип. На это влияет достаточно сильно механизм формирования статистики по умолчанию - она одно колоночная. В некоторых случаях если создать многоколоночную по всем полям составного типа - работать начинает лучше.
12. starik-2005 3180 13.11.24 16:49 Сейчас в теме
(11)
когда в условиях используется составной тип
Честно говоря, это и в MS SQL не всегда работает хорошо.

С другой стороны, постгрес умеет (а, б) В (селект х, у фром зет), MS SQL так не умел раньше, сейчас не знаю - особо не слежу.
14. Tantor 145 13.11.24 17:33 Сейчас в теме
(11) В нашей редакции СУБД многоколоночная статистика создается автоматически.
В MSSQL есть такой механизм как автоматическое создание статистики по итогам накопленных данных. Это позволяет MSSQL показывать результаты лучше в то время как постгрес в случае "WHERE a = 1 AND b = 2" просто перемножает оценки строк по a и b, если a и b не входят в состав одного индекса. Над этим мы тоже сейчас работаем.
15. ivanov660 4746 14.11.24 09:29 Сейчас в теме
(14) Вот это уже более интересное замечание. Есть описание как происходит создание многоколоночной статистики?
В ручную, конечно же не удобно искать и создавать. Для всех подряд полей делать конечно же не верно.
16. Tantor 145 14.11.24 13:49 Сейчас в теме
(15)
Есть описание как происходит создание многоколоночной статистики?

Оно происходит аналогично созданию одноколоночной статистики.
17. Tantor 145 14.11.24 13:50 Сейчас в теме
(15)
В ручную, конечно же не удобно искать и создавать

Может вы имеете ввиду расширенную статистику? Например,
CREATE STATISTICS _inforg8081_stat_1 (dependencies) ON _fld8098rref, _fld8085rref FROM _inforg8081;
18. ivanov660 4746 14.11.24 21:34 Сейчас в теме
(17)
1. Конечно, речь идет про расширенную статистику, я видимо предположил что это будет и так понятно:)
2. У MS SQL этот процесс автоматизирован, вот я предположил, что вы сделали аналог. Но видимо это не так.
19. Tantor 145 15.11.24 09:29 Сейчас в теме
(18) Автоматическое создание расширенной статистики в процессе реализации. По факту выхода релиза будет статья на инфостарте, где расскажем как этот механизм будет работать.
ivanov660; +1 Ответить
4. PerlAmutor 160 13.11.24 06:53 Сейчас в теме
А нельзя было исправить сразу работу оператора VALUES и закинуть исправление в ванильную ветку PostgreSQL, чтобы он работал нормально не только со специализированным дистрибутивом для 1С?
7. Tantor 145 13.11.24 10:51 Сейчас в теме
(4) Мы отправили предложение по включению доработки в hackers PostgreSQL - https://www.postgresql.org/message-id/0184212d-1248-4f1f-a42d-f5cb1c1976d2@tantorlabs.com
Пока это предложение не принято, находится в стадии проработки сообществом. Присоединяйтесь к обсуждению проблемы, чтобы сообщество поняло, что это нужная доработка планировщика.
9. Jimbo 13 13.11.24 15:26 Сейчас в теме
Краткие выводы, итоги ? Так какую версию платформы и СУБД юзать ? На последних столько глюков, что не спешу на 24-25 переходить , а типовые уже обязаловку требуют. С MS SQL нет сей проблемы ?
13. Tantor 145 13.11.24 17:23 Сейчас в теме
(9) На MSSQL данной проблемы нет.
Перед переходом на определенную версию платформы ее сначала нужно протестировать на своих информационных системах, а потом уже принимать решение о возможности перехода.
20. triviumfan 102 02.12.24 18:34 Сейчас в теме
(9) Очень зря, там ведь множество изменений и оптимизаций как для сервера 1с, так и разработчика.
Оставьте свое сообщение