А как у вас получилось, что запросы могли использовать merge join? Платформа 1С при выполнении каждого запроса явно запрещает его использование среди прочего:
SET client_min_messages=error;
SET lc_messages to 'en_US.UTF-8';
SET enable_mergejoin=off;
SET escape_string_warning=off;
SET standard_conforming_strings=off;
SET cpu_operator_cost=0.001;
SET client_encoding='UTF8';
SET lock_timeout=20000;
Создание статистики не является автоматическим шагом. Такой запрос нужно найти, понять какую статистику для него создать и потом создать в обход платформы 1С. Доработка СУБД же избавляет нас от таких действий. Но, действительно, здесь можно еще улучшить качество планирования, если научить планировщик по другому использовать статистику для оценки таких параметризированных соединений. Но об этом в отдельной статье, где мы разберем проблему со стороны 1С. Суть этой статьи это именно научить планировщик оценивать COALESCE - это первый шаг нашего системного подхода к улучшению качества планирования таких сложных 1Сных запросов.
Это таблица регистра бухгалтерии. У некоторых счетов может быть 1, 2 или вообще не быть субконто. Из-за этого поля "СубконтоN" могут принимать значения NULL. Тоже самое с полями Валюта, Подразделение и НаправлениеДеятельности - они не являются балансовыми, и поэтому могут иметь значение NULL, поэтому в тексте запроса ORM использует для таких полей COALESCE
Не понял, что вы хотите сказать. COALESCE(t2._value1_rrref, ‘\xff’::bytea)) - так отправляет запрос приложение. В индекс не попадают те условия, которых просто нет в индексе. Nested loop тут неэффективен, потому что поля из секции Filter фильтруют большое количество строк. Конечно, тут можно было создать покрывающий индекс, но это совсем другая история. Тут описывается проблема функции COALESCE
Косты в 500 раз увеличиваются, и проблему это не решит. Вариант с COALESCE:
"Update on _accrgat251202 (cost=0.22..2200460.34 rows=0 width=0)"
" -> Nested Loop (cost=0.22..2200460.34 rows=187163 width=492)"
" -> Seq Scan on tt11 t2 (cost=0.00..51107.10 rows=584843 width=240)"
" Filter: ((_edcount = '2'::numeric) AND (_fld3181 = '0'::numeric))"
" -> Index Scan using _accrgat251202_1o_new on _accrgat251202 (cost=0.22..3.60 rows=6 width=225)"
" Index Cond: ((_fld3181 = '0'::numeric) AND (_accountrref = t2._accountrref) AND (_period = t2._period) AND (_fld51168rref = t2._fld51168rref) AND (COALESCE(_value1_type, '\xff'::bytea) = COALESCE(t2._value1_type, '\xff'::bytea)) AND (COALESCE(_value1_rtref, '\xff'::bytea) = COALESCE(t2._value1_rtref, '\xff'::bytea)) AND (COALESCE(_value1_rrref, '\xff'::bytea) = COALESCE(t2._value1_rrref, '\xff'::bytea)) AND (COALESCE(_value2_type, '\xff'::bytea) = COALESCE(t2._value2_type, '\xff'::bytea)) AND (COALESCE(_value2_rtref, '\xff'::bytea) = COALESCE(t2._value2_rtref, '\xff'::bytea)) AND (COALESCE(_value2_rrref, '\xff'::bytea) = COALESCE(t2._value2_rrref, '\xff'::bytea)) AND (_splitter = '0'::numeric))"
" Filter: ((COALESCE(t2._fld51171rref, '\xff'::bytea) = COALESCE(_fld51171rref, '\xff'::bytea)) AND (COALESCE(t2._fld51170rref, '\xff'::bytea) = COALESCE(_fld51170rref, '\xff'::bytea)) AND (COALESCE(t2._fld51169rref, '\xff'::bytea) = COALESCE(_fld51169rref, '\xff'::bytea)))"
" Estimated Fetched Rows: 31"
Вариант с IS NOT DISTINCT FROM:
"Update on _accrgat251202 (cost=0.22..587839220.54 rows=0 width=0)"
" -> Nested Loop (cost=0.22..587839220.54 rows=1 width=492)"
" -> Seq Scan on tt11 t2 (cost=0.00..51107.10 rows=584843 width=240)"
" Filter: ((_edcount = '2'::numeric) AND (_fld3181 = '0'::numeric))"
" -> Index Scan using _accrgat251202_1o_new on _accrgat251202 (cost=0.22..1005.03 rows=1 width=225)"
" Index Cond: ((_fld3181 = '0'::numeric) AND (_accountrref = t2._accountrref) AND (_period = t2._period) AND (_fld51168rref = t2._fld51168rref) AND (_splitter = '0'::numeric))"
" Filter: ((NOT (t2._fld51169rref IS DISTINCT FROM _fld51169rref)) AND (NOT (t2._fld51171rref IS DISTINCT FROM _fld51171rref)) AND (NOT (t2._value2_rtref IS DISTINCT FROM _value2_rtref)) AND (NOT (t2._value2_rrref IS DISTINCT FROM _value2_rrref)) AND (NOT (t2._value1_rrref IS DISTINCT FROM _value1_rrref)) AND (NOT (t2._fld51170rref IS DISTINCT FROM _fld51170rref)) AND (NOT (t2._value1_rtref IS DISTINCT FROM _value1_rtref)) AND (NOT (t2._value2_type IS DISTINCT FROM _value2_type)) AND (NOT (t2._value1_type IS DISTINCT FROM _value1_type)))"
" Estimated Fetched Rows: 11394"
Построение такого индекса по выражению проблему не решает. Hash Join все равно очень дорог. Вот как устроены индексы для рассматриваемого кейса:
У каждого индекса таблиц итогов по субконто есть индекс "напарник". Например, в таблице ИтогиПоСчетамССубконто1 помимо индекса AccRgAT1817851 есть индекс AccRgAT1817851ong. Состав полей у этих индексов одинаков. Отличие в том, что по полям, у которых разрешено значение NULL, в составе индекса используется конструкция COALESCE.
Например, в индексе AccRgAT1817851 поле Подразделение задано просто как поле Fld81745RRef, а в индексе AccRgAT181785_1ong оно задано функцией COALESCE(_Fld81745RRef, '\xff'::bytea).
Интерактивный SELECT пользователя через pgAdmin или другие инструменты в обход платформы по-прежнему остается для меня нарушением Лицензионного соглашения, и ссылок на ИТС, разрешающих это, я пока не видел
Коллега, судя по объёму дискуссии, у вас уже накопился материал на отдельную статью о лицензировании «1С». Предлагаю не пытаться уместить её в комментарии к статье про pg_anon: напишите полноценный разбор в своём блоге, приложите официальные трактовки «1С», а здесь просто дайте ссылку. Так всем будет значительно удобнее.
Но, я хочу заметить, что вы выше писали, что чтение данных из таблиц 1С в обход платформы является нарушением, вплоть до SELECT через pgAdmin. Я привёл статью самой фирмы 1С, где прямое чтение из СУБД и выборочная выгрузка таблиц через pg_dump описаны как рекомендуемая практика. Теперь в вашем запросе речь уже не о чтении. Там прямо сказано, что ИТС допускает и рекомендует pg_dump -t для передачи таблиц разработчикам, а вопрос задаётся только о маскировании данных при выгрузке. То есть исходный тезис «любое прямое взаимодействие с таблицами — нарушение» вы фактически сняли, но позицию сменили без признания этого. Поэтому не очень понятно, что именно вы хотите доказать.
В данной статье https://its.1c.ru/db/metod8dev#content:6011:hdoc от фирмы 1С есть: 1. Чтение данных на СУБД напрямую в обход сервера приложений 2. Создание "легкой" копии базы данных средствами СУБД с частичным переносом данных
А создание бекапа средствами СУБД является нарушением или нет? Если да, то бекапы можно делать только через средства платформы 1С и сервера приложений?
Да, мы же не "портим" данные, которые влияют на расчет, например, коэффициенты различные, надбавки. Ссылочная целостность у нас не затрагивается при обезличивании.
Зависит от задачи для чего предназначается копия. Например, на такой копии даже можно запустить расчет себестоимости, чтобы понять причины почему он долго выполняется, не говоря уже о выполнении ежедневных небольших задач разработчиков 1С.
Так и pg_anon просто так из коробки без настройки мало поможет.
Поэтому дается универсальный шаблон, с которым можно обезличить практически любую базу 1С. В типовой обработке вам нужно помнить где у вас могут быть чувствительные данные. В pg_anon просто задаете закономерности поиска этих данных и он сам все находит.
Типовой обработкой такой же сценарий не воспроизводил, потому что ей просто сложно его воспроизвести 1 в 1. Но по данным из pg_anon могу сказать, что потребовалось бы перезаписать 11 млн элементов справочников, 4 млн документов и 25 млн записей регистров сведений и прочих объектов.
После INSERT сервер 1С ведь, судя по RowsAffected, уже знает, что вставлено 0 строк. Если таблица перед этим гарантированно была очищена, логичнее вообще не посылать FASTTRUNCATE, чем отправлять его в СУБД, чтобы та там еще раз определяла, пустая таблица или нет.
Платформа 1С с версии 8.4 как раз так и будет делать: перед вызовом FASTTRUNCATE проверять является ли временная таблица пустой и если ИСТИНА, то не вызывать FASTTRUNCATE
И еще небольшая придирка к описанию AccessExclusiveLock. Фраза «снимаем блокировку, теперь она может быть использована другими сеансами» для TEMP-таблицы несколько сбивает с толку: PostgreSQL TEMP-таблица принадлежит конкретной сессии, и другие сессии ее в принципе использовать не могут. То есть эта блокировка здесь нужна не для конкуренции разных пользовательских сеансов за tt3.
Спасибо, действительно формулировка неверная получается. Код данной функции можно еще упростить, если мы знаем, что с ней будет работать только платформа 1С, но на момент написания статьи основная проблема была именно в heap_truncate
А вот проблема со статистикой после ДОБАВИТЬ, на мой взгляд, существенно интереснее FASTTRUNCATE. Получается довольно неприятная абстракция: на уровне языка 1С мы просто добавили строки в существующую таблицу, а оптимизатор PostgreSQL после этого может принимать решения по статистике, которая описывает уже совсем другое содержимое. И решать это глобальным online_analyze тоже выглядит несколько тяжеловесно. Кажется, логичнее было бы самой платформе делать ANALYZE по некоторому порогу изменения размера ВТ. Именно так и делается в открытом и бесплатном lsFusion. Странно, что в 1С не доабвили такой возможности.
В смысле 1C использует UNLOGGED таблицы ? Если речь идет о пуле временных таблиц для соединений, то в lsFusion это было сделано давно (до этой статьи)
Нет, 1С не использует UNLOGGED таблицы, просто вы в 3м варианте написали про переиспользование временных таблиц. Я имел ввиду, что это есть в 1С. Большое количество временных таблиц в IsFusion генерируют разработчики приложений или на уровне вашей платформы в определенных сценариях зашито использование временных таблиц, что и приводит к их большому количеству?
Конкретно последняя оптимизация была, чтобы вообще заменить их на unlogged и делить между разными подключениями
Интересное решение, но непонятно какой эффект это дало и как оценить профит из фразы "На стенде четыре сохранения потребовали ноль CREATE вместо сорока"? Не добавило ли проблем то, что разные сеансы стали конкурировать за эти UNLOGGED таблицы?
Но показанного текста недостаточно, чтобы сказать, что ожидание LockManager возникло непосредственно на invalidation queue
Да, возможно тест не показателен, ниже в статье был рассказ и об очереди инвалидационных сообщений. Разработчики собирали perf и делали анализ его результатов. Я потом на основе их результатов и писал статью. Что-то мог конечно не раскрыть так, как это мог бы раскрыть разработчик, который делал анализ результатов perf и работал с исходным кодом PostgreSQL.
А как у вас получилось, что запросы могли использовать merge join? Платформа 1С при выполнении каждого запроса явно запрещает его использование среди прочего:
Создание статистики не является автоматическим шагом. Такой запрос нужно найти, понять какую статистику для него создать и потом создать в обход платформы 1С.
Доработка СУБД же избавляет нас от таких действий. Но, действительно, здесь можно еще улучшить качество планирования, если научить планировщик по другому использовать статистику для оценки таких параметризированных соединений. Но об этом в отдельной статье, где мы разберем проблему со стороны 1С. Суть этой статьи это именно научить планировщик оценивать COALESCE - это первый шаг нашего системного подхода к улучшению качества планирования таких сложных 1Сных запросов.
Тут дело в бизнес-логике данной таблицы:
Это таблица регистра бухгалтерии. У некоторых счетов может быть 1, 2 или вообще не быть субконто. Из-за этого поля "СубконтоN" могут принимать значения NULL.
Тоже самое с полями Валюта, Подразделение и НаправлениеДеятельности - они не являются балансовыми, и поэтому могут иметь значение NULL, поэтому в тексте запроса ORM использует для таких полей COALESCE
Не понял, что вы хотите сказать.
COALESCE(t2._value1_rrref, ‘\xff’::bytea)) - так отправляет запрос приложение. В индекс не попадают те условия, которых просто нет в индексе. Nested loop тут неэффективен, потому что поля из секции Filter фильтруют большое количество строк. Конечно, тут можно было создать покрывающий индекс, но это совсем другая история. Тут описывается проблема функции COALESCE
Косты в 500 раз увеличиваются, и проблему это не решит.
Вариант с COALESCE:
Вариант с IS NOT DISTINCT FROM:
Построение такого индекса по выражению проблему не решает. Hash Join все равно очень дорог.
Вот как устроены индексы для рассматриваемого кейса:
Значит в вопросе совсем не разбираетесь..
Ваша цитата:
В указанной статье https://its.1c.ru/db/metod8dev#content:6011:hdoc идет чтение из таблицы базы данных напрямую, минуя сервер приложений:
Коллега, судя по объёму дискуссии, у вас уже накопился материал на отдельную статью о лицензировании «1С». Предлагаю не пытаться уместить её в комментарии к статье про pg_anon: напишите полноценный разбор в своём блоге, приложите официальные трактовки «1С», а здесь просто дайте ссылку. Так всем будет значительно удобнее.
Но, я хочу заметить, что вы выше писали, что чтение данных из таблиц 1С в обход платформы является нарушением, вплоть до SELECT через pgAdmin. Я привёл статью самой фирмы 1С, где прямое чтение из СУБД и выборочная выгрузка таблиц через pg_dump описаны как рекомендуемая практика.
Теперь в вашем запросе речь уже не о чтении. Там прямо сказано, что ИТС допускает и рекомендует pg_dump -t для передачи таблиц разработчикам, а вопрос задаётся только о маскировании данных при выгрузке. То есть исходный тезис «любое прямое взаимодействие с таблицами — нарушение» вы фактически сняли, но позицию сменили без признания этого.
Поэтому не очень понятно, что именно вы хотите доказать.
В данной статье https://its.1c.ru/db/metod8dev#content:6011:hdoc от фирмы 1С есть:
1. Чтение данных на СУБД напрямую в обход сервера приложений
2. Создание "легкой" копии базы данных средствами СУБД с частичным переносом данных
А создание бекапа средствами СУБД является нарушением или нет? Если да, то бекапы можно делать только через средства платформы 1С и сервера приложений?
Да, мы же не "портим" данные, которые влияют на расчет, например, коэффициенты различные, надбавки. Ссылочная целостность у нас не затрагивается при обезличивании.
Зависит от задачи для чего предназначается копия. Например, на такой копии даже можно запустить расчет себестоимости, чтобы понять причины почему он долго выполняется, не говоря уже о выполнении ежедневных небольших задач разработчиков 1С.
Подключиться к СУБД через PgAdmin и выбрать данные из таблицы нарушает лицензионное соглашение 1С? Что-то новое)
Не понял смысл фразы. Можете пояснить?
Поэтому дается универсальный шаблон, с которым можно обезличить практически любую базу 1С.
В типовой обработке вам нужно помнить где у вас могут быть чувствительные данные. В pg_anon просто задаете закономерности поиска этих данных и он сам все находит.
Типовой обработкой такой же сценарий не воспроизводил, потому что ей просто сложно его воспроизвести 1 в 1. Но по данным из pg_anon могу сказать, что потребовалось бы перезаписать 11 млн элементов справочников, 4 млн документов и 25 млн записей регистров сведений и прочих объектов.
Платформа 1С с версии 8.4 как раз так и будет делать: перед вызовом FASTTRUNCATE проверять является ли временная таблица пустой и если ИСТИНА, то не вызывать FASTTRUNCATE
Спасибо, действительно формулировка неверная получается. Код данной функции можно еще упростить, если мы знаем, что с ней будет работать только платформа 1С, но на момент написания статьи основная проблема была именно в
heap_truncateСогласен на 100%.
Нет, 1С не использует UNLOGGED таблицы, просто вы в 3м варианте написали про переиспользование временных таблиц. Я имел ввиду, что это есть в 1С.
Большое количество временных таблиц в IsFusion генерируют разработчики приложений или на уровне вашей платформы в определенных сценариях зашито использование временных таблиц, что и приводит к их большому количеству?
Интересное решение, но непонятно какой эффект это дало и как оценить профит из фразы "На стенде четыре сохранения потребовали ноль
CREATEвместо сорока"? Не добавило ли проблем то, что разные сеансы стали конкурировать за эти UNLOGGED таблицы?Да, возможно тест не показателен, ниже в статье был рассказ и об очереди инвалидационных сообщений. Разработчики собирали perf и делали анализ его результатов. Я потом на основе их результатов и писал статью. Что-то мог конечно не раскрыть так, как это мог бы раскрыть разработчик, который делал анализ результатов perf и работал с исходным кодом PostgreSQL.
Тут будет плюс от unlogged в том, что у вас появляется параллелизм, если конечно результат такого запроса вы не помещаете во временную.
А вообще ваш выбранный 3 вариант это то, как делает платформа 1С. Вот тут в начале статьи объяснение как сервер приложений 1C работает с временными таблицами и какую информацию хранит - https://infostart.ru/1c/articles/2221687/
Также мы сталкивались с подобной проблемой как в вашей текущей статье, вот ее описание - https://infostart.ru/1c/articles/2535895/#Первые результаты и проблемы
А вот ее решение на уровне доработки PostgreSQL - https://habr.com/ru/companies/tantor/articles/924978/#2-2
Не совсем понял. В ходе статьи вы пишите про временные таблицы, а затем
То есть вы заменили использование временных таблиц на unlogged-таблицы?
pg_anon: как обезличить базу 1С без промежуточной копии - https://infostart.ru/1c/articles/2747170/