Решение проблемы 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 оптимизация Технологический журнал Системный администратор Программист Бесплатно (free)

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

24.06.2024    5481    ivanov660    12    

56

Администрирование СУБД Платформа 1С v8.3 Конфигурации 1cv8 Россия Бесплатно (free)

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

23.05.2024    10402    human_new    18    

56

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

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

2 стартмани

15.02.2024    12758    257    ZAOSTG    84    

115

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

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

09.01.2024    15164    doom2good    49    

71

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

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

20.11.2023    13938    ivanov660    7    

83

HighLoad оптимизация Бесплатно (free)

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

15.11.2023    7388    a.doroshkevich    22    

75

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

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

13.11.2023    18456    ivanov660    32    

77
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. ivanov660 4583 12.11.24 16:44 Сейчас в теме
1. Хорошо бы к планам запроса, еще приводить пример 1С запроса. В противном случае остаётся только покивать головой
2. Ничего не слышал про изменение алгоритма работы 1С, но слышал про оптимизацию оператора IN СУБД Postgres (кажется это произошло в 14 версии) .
2. starik-2005 3092 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 98 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 98 13.11.24 10:35 Сейчас в теме
(5) Я тестировал на 8.3.25.1445
10. Jimbo 10 13.11.24 15:27 Сейчас в теме
(6) глюков много, знакомые откатились на 24-ю, не уточнил которую именно
8. starik-2005 3092 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 4583 13.11.24 16:41 Сейчас в теме
(8) А, точно, давно было дело. На сколько я помню разработчики postgresдоработали этот механизм, так как страдали все, а не только 1С.
Относительно второй фразы дальнейшего - мы сравнивали с работой MS SQL. В некоторых случаях postgres еще не дотягивает до мелкомягких.
И вишенка на торте - основная проблема для планировщика СУБД возникает в случаях, когда в условиях используется составной тип. На это влияет достаточно сильно механизм формирования статистики по умолчанию - она одно колоночная. В некоторых случаях если создать многоколоночную по всем полям составного типа - работать начинает лучше.
12. starik-2005 3092 13.11.24 16:49 Сейчас в теме
(11)
когда в условиях используется составной тип
Честно говоря, это и в MS SQL не всегда работает хорошо.

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

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

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