1С и Postgres: первый серьезный опыт борьбы и противостояния, постигаем open source

27.02.23

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

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

Скачать файл

ВНИМАНИЕ: Файлы из Базы знаний - это исходный код разработки. Это примеры решения задач, шаблоны, заготовки для того, чтобы самостоятельно ее доработать для вашей базы данных. Файлы ориентированы на специалистов 1С, которые могут разобраться в коде и оптимизировать программу для запуска в вашей базе данных. Гарантии работоспособности нет. Возврата нет. Технической поддержки нет.

Наименование SM По подписке Купить один файл
auto_explain.so
.so 100,35Kb
17
17
0 SM
Скачать
plan_optimizer_1c.so
.so 66,23Kb
16
16
0 SM
Скачать

Проблема

В разгар перехода на PostgreSQL (далее PG) к нам обратились коллеги из отдела развития информационных систем собственных производств за решением распространенной проблемы: в ERP «Закрытие месяца» на MSSQL идёт 2 часа, а на PG — 11 часов. Требовалось сократить этот срок хотя бы до 3 часов. К слову, все зависало на конкретном пункте: «Распределение затрат и расчет себестоимости». 

Конфигурация 1С:ERP Агропромышленный комплекс 2 (2.5.8), полностью на поддержке, расширения хоть и были, но закрытие месяца не затрагивали. Сервер PG 14.5.3 x64 скачан с сайта 1С, установлен на linux сервер под CentOS 7 (CPU 8 ядер, ОЗУ 64ГБ). Версия сервера 1С 8.3.21.1393 x64. Размер базы около 90ГБ. 

Обновление до последнего релиза не исправило ситуацию, поэтому приступили непосредственно к анализу и разбору. 

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

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

Техжурнал показал интересную картину. Обнаружили 3 запроса, которые выполнялись по 2.5 - 3 часа.

 

 

2 запроса — дубль одного и того же.  

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

  • РасчетСебестоимостиКорректировкаСтоимости.ТекстСуммыПрочихРасходов 
  • РасчетСебестоимостиРешениеСЛУ.ТекстВтТаблицаСвязейПостатейныеРасходы 

Первым делом под подозрение попал PG и linux сервер, на котором он установлен. Linux и сам PG были настроены по всем известным рекомендациям. Всё перепроверили, ошибок не нашли. Далее обратились за помощью в корпподдержку 1С — рекомендовали поиграться с default_statistics_target (количество записей, просматриваемых при сборе статистики по таблицам). Увеличив дефолтный параметр со 100 до 1000, не получили положительных изменений. И если при значении 100, первый проблемный запрос появлялся где-то через 20 минут от начала расчета себестоимости, причем всегда и при любых тестах, то со значением 1000 это время возросло до 40 минут. Явно сказывается расчет статистики на временных таблицах. Увеличивать его смысла уже не было, даже 1000 точно не будем ставить при таких просадках. 

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

Это был первый серьезный опыт решения подобных проблем, так как процесс перехода на PG был только в начальной стадии и пока не приходилось ни анализировать долгие запросы PG, ни снимать планы PG, разве что только в теории) 

 

Auto_explain

Для снятия плана воспользовались стандартной библиотекой auto_explain. Почему-то считали, что это будет так же просто и легко, как в MSSQL, но нас поджидал сюрприз.  

Нет никаких отборов, кроме длительности запросов. Даже по имени базы. Это, мягко говоря, немного шокировало. Отбор по базе казался, ну как бы, настолько очевидным, что когда его тут не обнаружилось, то решили, что, наверное, не та библиотека, ну или делаем что-то не то. Но, увы, все так. А если это общий сервер для тестирования и там много баз? По ним всем собирать все запросы, серьезно? 

И как следствие, это получение планов уже оптимизированных запросов, которые могут выполняться достаточно быстро, и отбор по длительности придется отключить для получения всего и вся со всех баз. При этом включение и отключение отбора делается настройками (ALTER SYSTEM SET…), и тут главное — не забыть все вернуть обратно. 

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

Но была одна особенность, которую мы не брали в расчет, да и не сразу к этому пришли: все это был open source. А значит можно не только заглянуть под капот, но внести изменения, дописать, переписать и т. д. 

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

Естественно, из любопытства пошли смотреть на код auto_explain. Он входит в состав официального дистрибутива, который можно скачать с сайта PG (https://www.postgresql.org). 

Весь код был в одном файле-модуле auto_explain.c, открыв который, увидели всего около 500 строк кода, где большая часть — это инициализация и подготовка, а основная логика — 70 строк. И это все? Ну как бы привыкли к многотысячным модулям в 1С, но тут все казалось уж слишком простым. Модуль написан на языке СИ, опыта работы с ним, увы, не было, но все казалось очень знакомым: переменные, структуры, условия, циклы и т. д. Где и что поправить, нашли сразу, и конечно, появилась гениальная идея: собрать свой auto_explain с блэкджеком и отборами. 

В модуле обнаружили сам текст запроса, поэтому не стали ограничиваться только отбором по имени базы — посчитали очень полезным искать конкретный запрос по вхождению некоторого ключа в текст. В данном случае не будем рассматривать возможные просадки при поиске текста в тексте (есть подозрения, что они могут быть, так как запросы у 1С имеют немыслимые размеры) — при использовании такого типа отбора важно получить сам план, остальное вторично. 

Включение и отключение новых отборов (ALTER SYSTEM SET…) повесили на новые параметры, которые также прописываются в этом модуле. 

 
 Код модуля auto_explain.c

С доработками разобрались, но как теперь получить библиотеку? 

Интернет, конечно, все показал и рассказал — вот небольшая инструкция, но для CentOS7 (также могут отличаться предустановленные компоненты). Все делаем через терминал на linux сервере, где стоит PG.

  1. Чтобы было меньше проблем с зависимыми пакетами, подключаем дополнительный репозиторий, иначе в основных просто не будет каких-либо пакетов 
    yum install centos-release-scl
  2. Устанавливаем подходящий под версию PG пакет devel. У нас была версия от 1С, поэтому пакет скачивали с сайта 1С. Он находится в дополнительных модулях (Дистрибутив СУБД PostgreSQL для Linux x86 (64-bit) (дополнительные модули) одним архивом (RPM)) 
    yum install postgresql14-1c-devel-14.5-3.el7.x86_64.rpm
  3. Ставим ещё несколько пакетов для компиляции: 
    yum install gcc 
    yum install openssl-devel
  4. Копируем на сервер, в какую-нибудь папку, наш доработанный модуль и make файл (лежит там же, где и модуль, переделывать его не надо)

  5. Устанавливаем переменную окружения: export PATH=/usr/pgsql-14/bin/:$PATH 
    Компилируем модуль: make USE_PGXS=1 
    Тут важно отменить, что компиляция идет за счет уже установленного PG — за это как раз и отвечает USE_PGXS=1 (почему пошли именно таким путем, история умалчивает) 
    При компиляции не должно быть никаких error, warning и т. д.

В результате мы должны увидеть нашу библиотеку:

 

 

Копируем ее в каталог lib и заменяем существующую:

 

 

На этом подготовительные работы завершены, включаем (pgadmin или psql): 

ALTER SYSTEM SET auto_explain.base_name = 'zl_tst_pg_2' 

ALTER SYSTEM SET auto_explain.query_text_like = 'KEY-111' 

ALTER SYSTEM SET auto_explain.log_min_duration = 0

Наслаждаемся планами только по запросам с конкретным ключом: 

 

 

 

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

 

А что там с закрытием месяца?

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

 

 

Loop join ожидает, что будет 1 строка, а получает 39 тысяч строк, отчего надолго «уходит в себя», так как на временной таблице нет индекса и приходится идти в скан (Seq Scan), а там около 200 тысяч строк. 

Чтобы не править запрос, ради эксперимента выключили loop (enable_nestloop = off), по одной из рекомендаций на ИТС (https://its.1c.ru/db/metod8dev/content/4692/hdoc). Вдруг всё пройдёт как надо на одних hash join, и тогда при закрытии месяца достаточно выключить loop, а после включить его обратно.  

Запустили закрытие месяца, которое выполнилось за 4.5 часа. По распределению времени получили такой расклад:

  • Распределение затрат и расчет себестоимости – 1 час. Все проблемы с длительными запросами ушли. 

  • Отражение документов в регламентированном учете — 3.5 часа. 

Ожидалось, что время на весь процесс будет приближено к 2 часам, как на MSSQL. Так как по-прежнему не выполнялось требование в 3 часа, продолжили искать решение проблемы. 

Оптимизировали проблемные запросы через расширение и «в лоб»: все неугодное вынесли во временные таблицы и оптимизатор перестал ошибаться при построении плана запроса. 

Закрытие, уже с включенным loop и оптимизированными запросами, распределилось так: 

  • Распределение затрат и расчет себестоимости – 1 час 

  • Отражение документов в регламентированном учете – 1 час 

Картинка стала интереснее. Получается, что без loop отражение в рег.учете просаживается в 3.5 раза. Конечно, не ожидали фантастического результата, но чтоб так! Заодно экспериментальным путем доказали, что loop лучше не выключать. 

План оптимизированных запросов снимать не стали: запрос уже будет выглядеть по-другому, да и результат понятен. 

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

Подведем небольшой итог по возможным решениям проблемы:

  1. Править проблемные запросы через расширение. Слезно просить 1С в очередном обновлении все исправить. А до тех пор поддерживать работоспособность расширения из релиза в релиз. Также надо учесть, что это все были проблемы конкретного случая — сколько такого вылезет в момент эксплуатации, представить тяжело.
  2. Точечное отключение loop на проблемных запросах. Отключать предполагалось через триггер PG, а давать команду все так же через расширение и записью в таблицу, на которую навешен триггер. В этом случае не придется разбирать сам запрос: если тормозит, просто отключаем loop.  Этот способ больше для экстренных случаев, когда решение нужно здесь и сейчас.
  3. Отключение loop со сканом.  А что, если не просто отключать loop, а только там, где он идет в скан? Все-таки loop хорош, когда использует индекс, особенно для больших таблиц, как в нашем случае.

Реализовать 3 вариант уже не казалось фантастикой (спасибо auto_explain), и идея звучала хоть и радикально, но что-то в этом было. Любопытство и сильный интерес взяли верх. 

 

Пишем свою библиотеку plan_optimizer_1c для отключения loop join

Но как понять, как его отключать и откуда начинать копать? Хорошо, что есть зацепка — это параметр enable_nestloop. 

Он используется лишь в одном модуле costsize.c. Вот, кстати, как именно происходит то самое отключение: 

if (!enable_nestloop)
	startup_cost += disable_cost;

Другими словами, стоимость просто задирается в космос. И если не будет вообще никаких вариантов кроме loop, то возьмется loop, хоть он и отключен. 

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

Покопав еще немного, нашли подходящий для себя хук (это специальные места, в которых мы можем ворваться в код — разработчики великодушно все предусмотрели). Список доступных хуков можно посмотреть тут: https://github.com/taminomara/psql-hooks/blob/master/Detailed.md 

Но была проблема — хук был уже после проработки всех вариантов соединения. Т. е. PG сначала прорабатывает merge, loop, hash, а уже потом вызывается хук, в котором можно было работать со списком проработанных вариантов. Но PG в такой список не добавлял все возможные варианты, а вытеснял, те, что были дорогими. В общем, если PG посчитает что loop дешевле hash, то hash он даже не будет добавлять. 

Решение было таковым: найти в этом списке loop со сканом, сильно увеличить стоимость оценки и еще раз проработать вариант с hash. 

Тут тоже не обошлось без подводных камней. Тот метод, что прорабатывает hash, не объявлен в заголовочных файлах (которые с расширением .h), а значит компилятор его не найдет. Приняли решение перетянуть сам метод в свою библиотеку. Естественно, он попросил еще несколько, и в совокупности получили около 4 методов. Все они находятся в модуле joinpath.c. Думаю, ничего страшного в этом нет, все равно компилируем под конкретную версию PG. 

В самом алгоритме отключения решили все-таки отталкиваться от индексов: отключаем loop, где он не идет в индекс, при этом учитываем, что в индекс может пойти через Memoize и Materialize. Получается, что все остальное считается для нас сканом. Возможно, не все случаи и операторы заложили — посчитали, что на данном этапе этого достаточно. 

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

 
 Код модуля plan_optimizer_1c.c (за основу был взят естественно auto_explain)
 
 Makefile

Ну и компилируем уже известным способом. 

Полученную библиотеку закидываем в lib и прописываем в shared_preload_libraries или  session_preload_libraries, как пример вместе с auto_explain: 

ALTER SYSTEM SET session_preload_libraries = auto_explain,plan_optimizer_1c

Включаем: 

ALTER SYSTEM SET plan_optimizer_1c.nestloop_on_scan_to_hash = on

При этом enable_nestloop должен быть включен. 

Настало время закрыть месяц еще раз, но уже без изменений на стороне 1С. Результаты были такие: 

  • Распределение затрат и расчет себестоимости – 1 час 

  • Отражение документов в регламентированном учете – 1 час 

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

Решили посмотреть на план, сделали расширение и через него закинули в запрос ключ KEY-111 для auto_explain. Ждем 20 мин, не боясь забить логами сервер и получаем результат: 

 

 

Как и ожидалось, вместо loop красуется hash, а время выполнения всего запроса составило 1.4 сек. Но одного теста показалось маловато, да и время закрытия месяца получали приблизительно. Как раз была одна база, для нагрузки сервера, на основе ERP. Обработкой, в несколько потоков, создается цепочка документов Заказ клиента – Заказ поставщика – Приобретение – Перемещение – Реализация – Оплата. Все документы создаются со смещением, чтобы на конец месяца оставались остатки. Количество строк в табличных частях немного, около 5. Для теста отказались от многопоточности, чтобы исключить ожидания на блокировках. Для начала заполнили пустую базу, создали где-то 15000 цепочек, и запустили тест на создание 30 цепочек. Решили проверить сразу 3 варианта: с включенным loop, с нашей библиотекой plan_optimizer_1c и вообще без loop, только на hash. Все тесты делались многократно. Результат:

 

 

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

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

 

Итоги 

Open source открыл новые возможности для решения проблем, о которых раньше и не задумывались. 

Все оказалось не так страшно и сложно, как могло быть. Многие вещи вообще делались впервые, и не исключаем того факта, что некоторые моменты были восприняты и поняты не так. Надо быть осторожным с доработками того или иного механизма, так как он мог быть изменен в вашей версии PG. Скажем, 1С свою PG патчит и не слабо, даже тот же auto_explain попадает под раздачу (это обнаружилось уже после всего). 

Что касается того, что было сделано.  

Доработанный auto_explain уже в полную используется, новые отборы очень даже кстати. Конечно было бы неплохо, если бы 1С включила это в свои патчи. 

plan_optimizer_1c, как минимум, подойдет для закрытия месяца, когда можно вот так отключить loop со сканом, а после закрытия уже вернуть все обратно. Остается вопрос: можно ли оставить так на постоянной основе? Хоть тесты и показывают хороший результат, но все индивидуально и зависит от разных факторов и условий. Надо проводить реальные тесты, приближенные к основным процессам в работе пользователей. Поэтому нам еще предстоит неоднократно проверить на прочность эту библиотеку, чтобы окончательно определиться с ее использованием, а в идеале получить решение от 1С.

PG postgres postgresql 1C

См. также

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

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    4092    spyke    28    

47

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

Оказывается, в типовых конфигурациях 1С есть, что улучшить!

13.03.2024    6266    vasilev2015    19    

39

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

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

1 стартмани

15.02.2024    9647    195    ZAOSTG    74    

105

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

Принимать, хранить и анализировать показания счетчиков (метрики) в базе 1С? Почему бы нет? Но это решение быстро привело к проблемам с производительностью при попытках построить какую-то более-менее сложную аналитику. Переход на PostgresSQL только временно решил проблему, т.к. количество записей уже исчислялось десятками миллионов и что-то сложное вычислить на таких объемах за разумное время становилось все сложнее. Кое-что уже практически невозможно. А что будет с производительностью через пару лет - представить страшно. Надо что-то предпринимать! В этой статье поделюсь своим первым опытом применения СУБД Clickhouse от Яндекс. Как работает, что может, как на нее планирую (если планирую) переходить, сравнение скорости работы, оценка производительности через пару лет, пример работы из 1С. Все это приправлено текстами запросов, кодом, алгоритмами выполненных действий и преподнесено вам для ознакомления в этой статье.

1 стартмани

24.01.2024    4168    glassman    17    

39

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

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

09.01.2024    8459    doom2good    49    

67

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

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

20.11.2023    10763    ivanov660    6    

78

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

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

15.11.2023    5887    a.doroshkevich    20    

73
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. ansverrr 27.02.23 15:21 Сейчас в теме
Ничего не понятно, но очень интересно! По общему впечатлению - отличный материал для 1С:Эксплуататоров. Авторы статьи - герои. Которых мы не заслужили, но в которых нуждались. :)
adeich; maksa2005; aidar_safin; Stref75; sapervodichka; +5 Ответить
2. sapervodichka 6846 27.02.23 15:30 Сейчас в теме
(1) одни подводные камни - значит пришло время пересаживаться с лодки на подлодку )
3. user1915184 27.02.23 16:18 Сейчас в теме
Взял из open source - верни в open source. Золотое правило.
Не поленитесь еще теперь по своим доработкам прислать пулл реквест в репу https://github.com/postgres/postgres/pulls чтобы поддержать комьюнити. Наверняка его сразу не примут и потребуется немного постараться, но оно того стоит!

Спасибо за очередную статью, подтверждающую что не "PG плохой, MSSQL хороший", а всего лишь надо заниматься оптимизацией запросов (как и в любом другом фреймворке).
Berckk; whirlpool; Shmell; check2; bilex; q_i; rintik; pavlov_dv; Dach; siliconvalet; leongl; ixijixi; +12 Ответить
24. ivanov660 4447 28.02.23 20:20 Сейчас в теме
(3) Текущая ссылка на зеркало, через него они не принимают PR. Как отправить изменения читаем тут: https://wiki.postgresql.org/wiki/Submitting_a_Patch.
4. ptr128 27.02.23 16:18 Сейчас в теме
А не проще ли создавать индексы на временных таблицах. Или даже изначально использовать постоянные нежурналируемые таблицы с нужными индексами, вместо временных?
8. shakun_dg 121 28.02.23 08:11 Сейчас в теме
(4) Можно по-разному решить проблему, но так или иначе придется лезть в типовое решение и в дальнейшем поддерживать и обслуживать все свои доработки. А лезть в закрытие месяца не очень то и хочется)
11. ptr128 28.02.23 10:55 Сейчас в теме
(8) Вот более развернутый пример, для понимания того, как можно не трогая типовое решение индексировать временные таблицы:
Код на SQL


P.S. Не знаю, почему форум пробелы вставляет куда ни попадя. Победить не удалось (
5. denacid 95 27.02.23 16:29 Сейчас в теме
(1) Герои не носят плащи, герои пишут на СИ)
16. starik-2005 3052 28.02.23 15:33 Сейчас в теме
(5)
герои пишут на СИ
Все приличные студенты ИТ-ники ныне возведены в ранг героя. Я ожидал чего-то подобного, но не так скоро...
6. aShumakoff 147 27.02.23 17:25 Сейчас в теме
Если правильно понял, свое расширение для постгрес было написано для полусения отборов по планам. А если включить техрурнал с отбором по базе и тегом plansql?
7. shakun_dg 121 28.02.23 08:01 Сейчас в теме
(6) ИМХО удобней использовать и проще контролировать прямые и родные для СУБД инструменты. Техжурнал тот еще посредник.
9. anosin 29 28.02.23 08:49 Сейчас в теме
(6) техжурнал тебе не посоветует индекса по собранной статистике.
10. ptr128 28.02.23 10:15 Сейчас в теме
(8) Не надо лезть в типовое решение. Подобная заплатка реализуема средствами СУБД через event trigger на создание временной таблицы
12. ivanov660 4447 28.02.23 12:11 Сейчас в теме
Не согласен с выводом
Loop join ожидает, что будет 1 строка, а получает 39 тысяч строк, отчего надолго «уходит в себя», так как на временной таблице нет индекса и приходится идти в скан (Seq Scan), а там около 200 тысяч строк.

Тут скорее речь идет о статистике и о том что postgres через несколько соединений плохо справляется с предсказанием, поэтому считая что будет мало строк выбирает Nested Loop.
(Для пересчета статистики по временным таблицам есть плагин online_analyze (online_analyze.table_type = 'temporary' включать только так), но не уверен что поможет.)
Удобнее было бы реализовать что-то вроде AQO или взять его, т.е. исправлять похожие "кривые" планы. Я бы не рекомендовал использовать глобальное отключение использования nested loop.
Gilev.Vyacheslav; +1 Ответить
15. shakun_dg 121 28.02.23 13:17 Сейчас в теме
(12) online_analyze.table_type = 'temporary' так и настроено и PG прекрасно видит количество строк на временных таблицах, но после нескольких отборов оптимизатор говорит что будет 1 строка, и как следствие выбирает loop для соединения, который, к сожалению, получает 39 тысяч строк.
17. ptr128 28.02.23 16:18 Сейчас в теме
(12) Тут проблема у PostgreSQL. Статистику для временной таблицы построит или обновит VACUUM, но когда это он сделает - неизвестно. Может через секунду, а может и через час. Когда доберется до нее и как загрузка сервера позволит. Поэтому и рекомендуется после заполнения временной таблицы явно из кода запускать на нее ANALYZE.
С другой стороны, и на MS SQL я встречал изрядные тормоза, когда забывали проиндексировать временную таблицу. Поэтому индексировать временную таблицу стоит всегда, за исключением тех случаев, когда все равно по ней нужно только полное сканирование. Например, когда она затем будет использована только в FR OM без WH ERE и все остальные таблицы в JOIN имеют подходящие индексы.
С рекомендацией не использовать глобальное отключение NESTED LOOP - полностью согласен.
А вот решение, которое я применял, считаю предпочтительным. А именно, как уже писал выше, индексировать временные таблицы при их создании средствами событийного триггера, не трогая конфигурацию 1С.
18. ivanov660 4447 28.02.23 16:32 Сейчас в теме
(17)Читаем документацию модуля online_analyze https://postgrespro.ru/docs/postgrespro/15/online-analyze

Модуль online_analyze предоставляет набор функций, которые немедленно обновляют статистику после операций INSERT, UPDATE, DELETE или SELECT INTO в целевых таблицах.


т.е. отвечая на ваш вопрос - сделает сразу (если включите, конечно), модуль встроен ребятами из Postgres Pro.
Gilev.Vyacheslav; +1 Ответить
19. ptr128 28.02.23 16:48 Сейчас в теме
(18)
(18)
online_analyze

А я про этот костыль и не упоминал. У него есть целый ряд других проблем, из-за чего online_analyze и не принят в mainstreem. Навскидку:
1. Каждый DML запрос, обновляющий таблицу, ожидает завершение обновления ее статистик.
2. У автора (Сигаева) даже в планах нет использовать background worker.
3. При откате транзакции статистика не откатывается, что в последствии может привести к непредсказуемому поведению планировщика запросов.
siliconvalet; +1 1 Ответить
20. ivanov660 4447 28.02.23 16:56 Сейчас в теме
(19)
Костыль один, костыль другой - это да проблема. Но пока я не видел альтернативных удобных плагинов потсгре.
Кстати пишут, что
начиная с версии 8.3.13, платформа 1С самостоятельно включает использование analyze явным образом после вставки во временную таблицу
https://it-expertise.ru/blog/records/parametr-online-analyze-postgresql-vs-1c-predpriyatie-8/
21. ptr128 28.02.23 17:10 Сейчас в теме
(20)
Ну так про явный вызов ANALYZE я и писал выше. Но радикальное решение проблемы ТС, повторюсь, в индексации временной таблицы при ее создание событийным триггером. И эффект от этого будет существенно выше, чем от статистик по неиндексированной таблице, заставляющих, по сути, СУБД строить хеш индекс таблицы в памяти при каждом запросе, обращающемся к ней.
И это еще если JOIN по равенству полей. А если нет? HASH JOIN тогда и не поможет. А правильный индекс - еще как поможет.
22. shakun_dg 121 28.02.23 18:23 Сейчас в теме
(21) 1С рандомно генерирует имя временной таблицы при выполнении запроса, имена полей тоже не исключение, хоть и зависят от порядка в тексте запроса 1С. Про составные поля и говорить не стоит. Такое отлавливать триггером в PG так себе занятие, проще уже индекс средствами 1С накинуть раз уж на то пошло.
Gilev.Vyacheslav; +1 Ответить
23. ptr128 28.02.23 19:39 Сейчас в теме
(22)
Если через ПОМЕСТИТЬ, то по счетчику. Но нам или важна структура таблицы, или только одно поле, с именем заканчивающимся на RRef. А уж проверить структуру таблицы в триггере, если уж очень хочется - не проблема. Мне до структуры доходить редко приходилось. Чаще, триггером индексирую первое поле, имя которого заканчивается на RRef, чего уже хватает.

Про проще, не знаю. В случае ПОМЕСТИТЬ, это уж точно код править. Лично мне проще на plpgsql один раз функцию написать, чем после каждого обновления патчить типовую конфигурацию.
25. shakun_dg 121 28.02.23 21:06 Сейчас в теме
(23)
Чаще, триггером индексирую первое поле, имя которого заканчивается на RRef

Как определяете какую именно временную таблицу индексировать?
26. ptr128 28.02.23 22:08 Сейчас в теме
(25) А я и точно и не определял. Если создается временная таблица с нужным мне количеством полей и первым полем, заканчивающимся на RRef - я по нему и индексировал. С одной стороны, на индексации каких-то лишних таблиц я терял секунды. Но с другой стороны, при закрытии выигрывал часы.
27. shakun_dg 121 01.03.23 07:49 Сейчас в теме
(26) Если то самое первое RRef никак не участвует ни в отборах ни в соединениях, и по факту надо индексировать какое-нибудь 3 или 4 по счету RRef, то такую ситуацию как то дополнительно обрабатывали? или просто не было нужды?
Количество полей временной таблицы тоже вещь не постоянная, легко может поменяться запрос в новом релизе, соответственно придется править триггер. Ну или поле было не составным, а стало составным (или на оборот), а с виду запрос 1С и не менялся, то тоже надо править триггер. Получается что все равно надо следить и контролировать.
28. ptr128 01.03.23 13:51 Сейчас в теме
(27)
Редко, но бывают ситуации, когда приходится анализировать не только количество, но и типы, длины и порядок всех полей таблицы. Благо метаданные таблицы доступны.
Саму информацию о временных таблицах и их метаданных собираю такой функцией на событийном триггере:
CREATE OR REPLACE FUNCTION catch_tmp_tables_info()
RETURNS event_trigger LANGUAGE plpgsql AS $func$
DECLARE
  obj record;
  metadata text;
BEGIN
    FOR obj IN
      SEL ECT *
      FR OM pg_event_trigger_ddl_commands()
      WHERE object_type='table' AND schema_name='pg_temp'
    LOOP
      SELECT STRING_AGG(attname||$$,$$||attnum::varchar||$$,$$||atttypid::varchar
          ||$$($$||attlen::varchar||$$,$$||atttypmod::varchar||$$) $$||attnotnull::varchar,$$;$$)
      FR OM pg_attribute
      WH ERE attrelid=obj.objid AND attnum>0
      INTO metadata;
      
      RAISE NOTICE '% ddl commands: % "%"', tg_tag, obj.object_identity, metadata;
    END LOOP;
END;
$func$;
Показать

А то, что после нового релиза может как появится новая проблема, так и решиться имеющаяся - это уже жизнь. От этого мы никуда не денемся.
13. mixsture 28.02.23 12:30 Сейчас в теме
Даже по имени базы.
...
А если это общий сервер для тестирования и там много баз?


Имхо, тут постгрес идеологически по-другому подходит. С позиции 1 инстанс = 1 база. Обратное, хоть теоретически и возможно, но убивает полностью процесс бекапов/восстановления в двоичном виде.
Shmell; ptr128; +2 Ответить
14. ValeraEm 139 28.02.23 13:04 Сейчас в теме
Зачем отбор по имени базы? Это не актуально. Для базы приложения отдельный сервер в отдельном контейнере и вся заточка под конкретный кейс.
Shmell; ptr128; +2 Ответить
29. check2 367 13.04.23 12:12 Сейчас в теме
Коллеги, я же правильно понял, что вы использовали Postgres из дистрибутивов 1С? А PostgresPro Std/Ent не пробовали?
Было бы полезно знать есть ли такие болячки в pgpro.
30. shakun_dg 121 13.04.23 14:41 Сейчас в теме
(29) Использовали только от 1С, другие дистрибутивы не юзали
31. Поручик 4683 28.10.23 00:58 Сейчас в теме
Импортозамещение называется. Наших заказчиков, госконторы, усиленно толкают на замену серверов и баз данных.
Оставьте свое сообщение