28.02.2023 09:04
ptr128 14 2600 Источник

Предисловие

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

Решение

Тогда же было ясно, что править типовую конфигурацию совсем не хочется. Ладно еще индексы постоянных таблиц, но для добавления индексов во временные таблицы средствами 1С потребуется править код. Поэтому решено было не править конфигурацию вообще, а индексировать нужные временные таблицы на лету средствами самого PostgreSQL. Для этой цели в нем уже давно имеется такая интересная команда, как CREATE EVENT TRIGGER. В нашем случае, интересен вызов событийного триггера сразу же после создания таблицы, то есть по событию ddl_command_end.

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

  • CREATE TEMP TABLE...

  • CREATE TEMP TABLE AS...

  • SELECT... INTO TEMP TABLE...

Учитывая это создаем такую функцию для создания индекса:

CREATE OR REPLACE FUNCTION build_index_on_tmp_tmp()
RETURNS event_trigger LANGUAGE plpgsql AS $$
BEGIN
  IF EXISTS (
      SELECT 1
      FROM pg_event_trigger_ddl_commands() E
      WHERE E.object_identity='pg_temp.tmp_tmp'
        AND tg_tag IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')) THEN
    CREATE UNIQUE INDEX IF NOT EXISTS tmp_tmp_idx ON tmp_tmp(Id);
  END IF;
END;
$$;

А вызываться эта функция будет уже из событийного триггера:

CREATE EVENT TRIGGER build_index_on_tmp_tmp_tr ON ddl_command_end
  EXECUTE FUNCTION build_index_on_tmp_tmp();

Теперь при создании временной таблицы tmp_tmp любым из трех перечисленных путей, таблица окажется сразу же индексирована:

DROP TABLE IF EXISTS tmp_tmp;
CREATE TEMP TABLE tmp_tmp (Id int, some_text text NULL);
INSERT INTO tmp_tmp(Id)
SELECT generate_series(1,1000000);

DROP TABLE IF EXISTS tmp_tmp;
CREATE TEMP TABLE tmp_tmp AS
SELECT generate_series(1,1000000) AS Id, NULL::text AS some_text;

DROP TABLE IF EXISTS tmp_tmp;
SELECT generate_series(1,1000000) AS Id, NULL::text AS some_text
INTO TEMP TABLE tmp_tmp;

Для сравнения посмотрим на разницу без триггера и с триггером на простейшем запросе

SELECT * FROM tmp_tmp WHERE Id=500000;

С индексом получаем

Index Scan using tmp_tmp_idx on tmp_tmp  (cost=0.42..2.64 rows=1 width=36) (actual time=0.030..0.031 rows=1 loops=1)
  Index Cond: (id = 500000)
Planning Time: 0.169 ms
Execution Time: 0.043 ms

Без него

Seq Scan on tmp_tmp  (cost=0.00..11449.69 rows=2810 width=36) (actual time=39.425..78.025 rows=1 loops=1)
  Filter: (id = 500000)
  Rows Removed by Filter: 999999
Planning Time: 0.112 ms
Execution Time: 78.045 ms

Итоги

Данный лайф‑хак применим далеко не только к 1C, но и к любой другой системе, в код которой нет желания или возможности залезть. Небольшим довеском непосредственно в PostgreSQL можно решить проблемы производительности многих запросов, вынудив планировщик отправиться по индексу. Иногда это еще потребует управления статистиками, но этот вопрос уже выходит за рамки текущей статьи.

Комментарии (14)


  1. gibson_dev
    00.00.0000 00:00
    #25276760

    Вещь полезная, спасибо. Но хотелось бы так же разобраться а какие именно таблицы создаются? Глубоко в 1с не лез.


    1. ptr128 Автор
      00.00.0000 00:00
      #25276790

      Так в данном случае это не имеет значения. Каким бы образом через МенеджерВременныхТаблиц или в коде через ПОМЕСТИТЬ не создавалась бы временная таблица в 1С. Все три возможных способа создания таблиц в функции уже учтены.


      1. Lazhu
        00.00.0000 00:00
        #25276876

        Лайфхак полезный. Но что-то сомневаюсь, что все временные таблицы, создаваемые 1с, называются tmp_tmp и имеют поле Id


        1. ptr128 Автор
          00.00.0000 00:00
          #25276914

          Да кто же его знает, какие именно временные таблицы используются именно в Вашей конфигурации? Одних типовых конфигураций несколько десятков. Берете в зубы auto_explain, отыскиваете запросы, выполняющиеся, например, свыше 10 минут и анализируете источник проблемы. По моему опыту, проблемы почти всегда решаются индексацией таблиц. С индексацией постоянных таблиц проблем нет, а что делать с временными - я описал в статье на примере абстрактной временной таблицы tmp_tmp.


          1. LordDarklight
            00.00.0000 00:00
            #25287990
            +1

            И всё-таки не понятно с идентификацией того что индексируется и по чему.

            Абстракции это хорошо, но статье ну очень не хватает более глубокого анализа сути проблемы на конкретных примерах

            Ну а по методологии конфигурирования в среде 1С Предприятие 8 есть прямая рекомендация от компании 1С - всегда индексировать поля временных таблиц, если по ним далее ведутся отборы и соединения. Хотя эта рекомендация, на самом деле, далека от истиной во всех инстанциях, и такая индексация реально в меньшем числе случаев даёт заметный выигрыш - в большинстве же случаев его не будет или даже будет лишнее проседание производительности (обычно не сильно большое на запрос - если только он не крутится в большом цикле), то в целом рекомендация к индексации во временных таблицах это скорее благо - но не панацея, и ручной тюнинг всегда желателен. Да и в одной ситуации (и статистики одной и той же БД) может быть один результат, то в других условиях (даже в той же базе) может быть уже противоположный результат - и реальную оценку должен обеспечивать только постоянный сбор статистических данных!

            Другая проблема самой платформы 1С Предприятие 8 - это невозможность создать для временной таблице несколько разных индексов - а это порой очень важно, когда таблица используется далее в нескольких местах - приходится довольствоваться неполным, наиболее общим составом индекса, что обычно не очень оптимально. Или дублировать таблицу в отдельные временные таблицы с разными индексами (чаще всего в типовых конфигурациях так и делают). Вот тут создание доп. индекса на лету именно средствами СУБД было бы очень полезно!


            1. ptr128 Автор
              00.00.0000 00:00
              #25288162

              И всё-таки не понятно с идентификацией того что индексируется и по чему.

              Это просто выходит за рамки статьи. Я даже не уверен, что это можно уместить в одной статье.

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

              CREATE OR REPLACE FUNCTION catch_tmp_tables_info()
RETURNS event_trigger LANGUAGE plpgsql AS $func$
DECLARE
  obj record;
  metadata text;
BEGIN
    FOR obj IN
      SELECT *
      FROM 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,$$;$$)
      FROM pg_attribute
      WHERE attrelid=obj.objid AND attnum>0
      INTO metadata;
      
      RAISE NOTICE '% ddl commands: % "%"', tg_tag, obj.object_identity, metadata;
    END LOOP;
END;
$func$;

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

              всегда индексировать поля временных таблиц

              Вот к этому я точно не призывал. Сначала выявляем проблему, и только если она есть - решаем.

              Абстракции это хорошо, но статье ну очень не хватает более глубокого анализа сути проблемы на конкретных примерах

              Я Вас услышал. Возможно я не прав. Но, к сожалению, я уже два года с 1С вообще не общаюсь, поэтому конкретный пример брать было просто не откуда. Если такая возможность появится, сделаю уже другую статью, уже не среднего, а сложного уровня, где разберу уже конкретику.


              1. LordDarklight
                00.00.0000 00:00
                #25288772

                Это просто выходит за рамки статьи. Я даже не уверен, что это можно уместить в одной статье.

                Вопрос был не про анализ того почему запрос тормозит и как его оптимизировать. А о технической части реализации проводимой оптимизации начиная со стадии: вот запрос, в нём такие-то временные таблицы, вот эти таблицы нуждаются в индексации по таким-то полям - чтобы это сделать, не меняя исходный запрос нужно сделать вот конкретно то-то и то-то. И так привести в едином кейсе несколько конкретных примеров (ключевое описание статьи начинается со слов "нужно сделать" - всё что до него - это дано).

                Более реальный пример я думал добавить, но побоялся, что тогда статья станет уже совсем про 1С, а не любую систему. 

                Да пишите примеры про любую прикладную СУБД (это же примеры, они всегда к чему-то приложены)

                Проблема 1С ещё в том, что имена временных таблиц там формируются динамически. Имена полей - тоже мало о чем говорят

                Именно по этой причине и написал свой вопросы Выше. Вы же в статье упоминаете проведение оптимизации для 1С - это сразу и заинтересовало, т.к. знал указанную проблему с идентификацией, но в статье именно её Вы не решали. От того именно для меня ценность статьи сократилась на порядок (может и читать бы не стал). Нет, ну конечно - техника мощная и статья интересная для определённых читателей. Но раз уж упоминаете 1С, то надо до конца разбирать особенности приложения данной технике непосредственно к данной прикладной системе, с учётом имеющихся в ней нюансов, ключевым образом влияющих на возможность применения описываемой техники (хотя я бы просто внёс правки в код запроса внутри конфигурации 1С - и не парился бы - но да - тут уже проблема обновлений и прочее прочее прочее - но оно полюбэ будет надёжнее - а надёжность для меня важнее! Как и эффективность дальнейшего сопровождения; вот только второй индекс во временную таблицу в 1С Предприятие 8 средствами платформы пока не добавить.... хотя вроде бы слышал, что обещали сделать, но может что-то путаю; индексы само собой изменил бы не в тексте исходного запроса, а обработал его программно, перед выполнением).

                 Поэтому идентифицировать временную таблицу приходится уже строкой ее метаданных, которые я собираю подобной функцией

                Вот это уже ближе к сути. Пояснений бы побольше - ну или писать новую статью и делать отсюда на неё отсылку! Хотя по мне - так это всё одна тема и одна статья должны быть! И дело не только в 1С - а вообще о всём разделе идентификации временной таблицы!

                Вот к этому я точно не призывал. Сначала выявляем проблему, и только если она есть - решаем.

                Я не о том; я как раз ниже всё пояснил. Я о том, что если в стороннем запросе явно не хватает индексов во временной - то это (согласно данной официальной рекомендации) хороший повод написать вендору в суппорт заявку на добавление данных индексов! В идеале вопрос индексирования таблиц (которые гипотетически могут быть нетривиального размера или взаимодействовать с таковыми другими таблицами) хорошо делать настраиваем - т.е. добавлять индексы по некоей опции оптимизации - но до такого мало какие прикладные учётные системы доросли, не то что 1С Предприятие 8. Ну а если индексы уже есть - то если нужно их расширить - это всегда должно легко решаться заявками вендору решения


  1. IDDQDesnik
    00.00.0000 00:00
    #25276820

    Спасибо, как раз думаем о тестировании 1С на PostgreSQL. Какую сборку постгреса используете? Сравнивали ли https://releases.1c.ru/project/AddCompPostgre и https://1c.postgres.ru/ ?


    1. ptr128 Автор
      00.00.0000 00:00
      #25276870

      Я на стороне интегратора работаю. Разные варианты были. Последние два года вообще с 1С не общаюсь, больше погрузившись в ML. А два года - не малый срок. Поэтому таких рекомендаций давать не возьмусь. Могу только утверждать, что PostgreSQL существенно лучше ведет себя под Linux, чем под Windows. На статью сподвигла уже не первая просьба о помощи в вышеописанной проблеме от коллег и знакомых.


  1. mixsture
    00.00.0000 00:00
    #25288204

    Теперь при создании временной таблицы tmp_tmp любым из трех перечисленных путей, таблица окажется сразу же индексирована

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


    1. ptr128 Автор
      00.00.0000 00:00
      #25288452

      убирать индексы на массовую загрузку, а затем докидывать их обратно

      Я согласен с этим утверждением, но есть два "но".

      Во-первых, событийный триггер в PostgreSQL позволяет перехватить только момент создания таблицы, но никак не момент завершения заливки в нее данных. Поэтому, как бы не хотелось индексировать временную таблицу после заливки в нее данных, как это реализовать только средствами БД я не представляю.

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


  1. mixsture
    00.00.0000 00:00
    #25288680
    +1

    Еще мне в этом методе не нравится неявность, когда он перестал работать.
    Я для себя сравниваю с патчем в расширении внутри 1с.

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

    В тригере СУБД мы вообще не узнаем. Нужны явные проверки рабочести нашей вставки индекса после каждого обновления. Либо в этот метод добавлять какой-то сбор статистики и следить уже за ней, либо всегда держать логирование долгих запросов и ловить тут. Все методы на основе статистики уже внесут задержку — мы узнаем после начала работы пользователей.


    1. ptr128 Автор
      00.00.0000 00:00
      #25295074

      мы узнаем после начала работы пользователей

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


  1. frrrost
    00.00.0000 00:00
    #25297440
    +1

    При использовании такого метода надо учитывать, что 1С (по крайней мере при работе с MS SQL Server, в PG не изучал) переиспользует временные таблицы - со всей накопившейся структурой метаданных

    С SQL Server у меня как-то был интересный случай. При помощи черной магии добавили создание колоночного (columnstore) индекса по временной таблице, участвовавшей в расчёте себестоимости. Первая итерация отрабатывала успешно, но потом начинались проблемы - построчное заполнение таблицы с columnstore-индексом начинает чудовищно тормозить, съедается весь эффект от оптимизации. Более того, из-за того, что таблица была очень простая (одна колонка со ссылками), она переиспользовалась и в других похожих запросах из той же сессии (например, при передаче массива в качестве параметра). Очень быстро все такие таблицы оказывались с columnstore-индексами, что практически парализовывало работу сессии. Вылечилось явным удалением columnstore-индекса после выполнения нужного запроса.

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