ptr128 Feb 28 2023 at 12:04

1C (и не только) c PostgreSQL

Medium

3 min

Case

Предисловие

Уже несколько лет назад я столкнулся с проблемой производительности 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 можно решить проблемы производительности многих запросов, вынудив планировщик отправиться по индексу. Иногда это еще потребует управления статистиками, но этот вопрос уже выходит за рамки текущей статьи.

Tags:

Hubs: