Как запрос 1С замедляет PostgreSQL в 22 раза — и как мы это починили

16.06.26

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

+2 –
Если вы работаете с 1С на PostgreSQL и жалуетесь на тормоза — скорее всего, дело в join predicate pushdown, которого в стандартном PostgreSQL нет. В MS SQL Server этот механизм работает «из коробки», и при миграции именно запросы к виртуальным таблицам 1С бьют по производительности сильнее всего. В этой статье — реальный кейс с разбором плана выполнения, ручным экспериментом и доработкой планировщика СУБД, которая ускорила запросы от 22 до 54 000 раз.

Разработчики и администраторы баз данных, работающие с платформой «1С:Предприятие», не понаслышке знают, какие сложные и ветвистые запросы она умеет генерировать. Огромные конструкции с вложенными подзапросами, временными таблицами и многочисленными соединениями — обычное дело для крупных ERP-систем.

Главная сложность здесь в том, что переписать такой запрос вручную получается далеко не всегда: код генерируется самой платформой. В таких ситуациях эффективным решением становится доработка планировщика самой СУБД, чтобы он научился самостоятельно оптимизировать подобные конструкции.

В этой статье мы разберем практический кейс оптимизации «тяжёлых» запросов 1С, связанных с агрегацией данных, и расскажем, как доработка механизма переноса предикатов внутрь подзапросов помогла ускорить выполнение некоторых операций в десятки раз.

 

С чего всё началось

К нам обратился клиент с проблемой: один из типовых запросов, сформированных платформой 1С, выполнялся медленно. Ниже приведён упрощённый текст похожего запроса, воспроизведённого на наших внутренних стендах:

explain (analyze, buffers)
SELECT T1._Fld144701_RTRef,
       T1._Fld144701_RRRef,
       T2.Fld37857Turnover_
  FROM _Reference139051 T1
  JOIN (SELECT date_trunc('month', T3._Period) AS Period_,
               T3._Fld156208RRef AS Fld156208RRef,
               SUM(CASE WHEN T3._RecordKind = CAST(0 AS NUMERIC) THEN T3._Fld37857
                        ELSE -T3._Fld37857
                   END) AS Fld37857Turnover_
          FROM _AccumRg37853 T3
         WHERE T3._Fld1551 = CAST(0 AS NUMERIC)
           AND T3._Period >= '2018-02-01 00:00:00'::timestamp
           AND T3._Period <= '2018-02-28 23:59:59'::timestamp
           AND T3._Active = TRUE
         GROUP BY date_trunc('month',T3._Period), T3._Fld156208RRef
        HAVING (SUM(CASE WHEN T3._RecordKind = CAST(0 AS NUMERIC) THEN T3._Fld37857
                         ELSE -T3._Fld37857
                     END)
               ) <> CAST(0 AS NUMERIC)
       ) T2
   ON T2.Fld156208RRef = T1._IDRRef
WHERE T1._Fld1551 = CAST(0 AS NUMERIC)
  AND T1._Fld144699RRef = '\\274$Tx$\\201\\265\\025\\021\\355\\226>?G\\351K'::bytea;

И его план выполнения:

Hash Join  (cost=741083.35..760573.04 rows=10455 width=54) (actual time=7750.730..8277.197 rows=5008.00 loops=1)
   Hash Cond: (t3._fld156208rref = t1._idrref)
   Buffers: shared hit=74357 read=490315
   ->  HashAggregate  (cost=722726.86..734069.06 rows=645346 width=57) (actual time=7700.034..8161.507 rows=313442.00 loops=1)
         Group Key: date_trunc('month'::text, t3._period), t3._fld156208rref
         Filter: (sum(CASE WHEN (t3._recordkind = '0'::numeric) THEN t3._fld37857 ELSE (- t3._fld37857) END) <> '0'::numeric)
         Batches: 1  Memory Usage: 245777kB
         Rows Removed by Filter: 328720
         Buffers: shared hit=40645 read=490315
         ->  Seq Scan on _accumrg37853 t3  (cost=0.00..657507.03 rows=5217587 width=31) (actual time=0.260..4410.574 rows=5219288.00 loops=1)
               Filter: (_active AND (_period >= '2018-02-01 00:00:00'::timestamp without time zone) AND 
                        (_period <= '2018-02-28 23:59:59'::timestamp without time zone) AND 
                        (_fld1551 = '0'::numeric))
               Rows Removed by Filter: 1267005
               Buffers: shared hit=40645 read=490315
   ->  Hash  (cost=17927.96..17927.96 rows=34282 width=39) (actual time=50.406..50.407 rows=34410.00 loops=1)
         Buckets: 65536  Batches: 1  Memory Usage: 2898kB
         Buffers: shared hit=33712
         ->  Index Scan using _reference139051_6 on _reference139051 t1  (cost=0.56..17927.96 rows=34282 width=39) (actual time=0.045..41.230 rows=34410.00 loops=1)
               Index Cond: ((_fld1551 = '0'::numeric) AND (_fld144699rref = '\\xbc2454782481b51511ed963e3f47e94b'::bytea))
               Index Searches: 1
               Buffers: shared hit=33712
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.532 ms
 Execution Time: 8306.325 ms

Из плана видно, что главной причиной медленной работы является полное сканирование данных подзапроса. Оно состоит из двух ресурсоемких этапов:

1. Последовательное сканирование таблицы _accumrg37853 с чтением более 5 миллионов строк:

->  Seq Scan on _accumrg37853 t3  (cost=0.00..657507.03 rows=5217587 width=31) (actual time=0.260..4410.574 rows=5219288.00 loops=1)

2. Построение агрегатов в памяти:

  ->  HashAggregate  (cost=722726.86..734069.06 rows=645346 width=57) (actual time=7700.034..8161.507 rows=313442.00 loops=1)

Общее время выполнения составило более 8 секунд, что критично для интерактивной работы пользователей.

 

Поиск причины и ручной эксперимент

Чтобы локализовать проблему и найти возможное решение, мы решили проверить, как поведёт себя планировщик при ручном ограничении выборки. Для этого сформируем данные основного запроса после применения условий WHERE во временную таблицу:

create temp table tt1 as
select T1._Fld144701_RTRef,
       T1._Fld144701_RRRef,
       T1._IDRRef
  from _Reference139051 T1
 where T1._Fld1551 = CAST(0 AS NUMERIC)
   and T1._Fld144699RRef = '\\274$Tx$\\201\\265\\025\\021\\355\\226>?G\\351K'::bytea;

analyze tt1;

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

explain (analyze, buffers)
SELECT date_trunc('month', T3._Period) AS Period_,
       T3._Fld156208RRef AS Fld156208RRef,
       SUM(CASE WHEN T3._RecordKind = CAST(0 AS NUMERIC) THEN T3._Fld37857
                ELSE -T3._Fld37857
           END) AS Fld37857Turnover_
  FROM _AccumRg37853 T3
 WHERE T3._Fld156208RRef IN (SELECT _IDRRef FROM tt1)
   AND T3._Fld1551 = CAST(0 AS NUMERIC)
   AND T3._Period >= '2018-02-01 00:00:00'::timestamp
   AND T3._Period <= '2018-02-28 23:59:59'::timestamp
   AND T3._Active = TRUE
 GROUP BY date_trunc('month',T3._Period), T3._Fld156208RRef
HAVING (SUM(CASE WHEN T3._RecordKind = CAST(0 AS NUMERIC) THEN T3._Fld37857
                 ELSE -T3._Fld37857
            END)
       ) <> CAST(0 AS NUMERIC);

Получили план выполнения:

HashAggregate  (cost=393880.63..405222.83 rows=645346 width=57) (actual time=356.887..365.645 rows=5008.00 loops=1)
   Group Key: date_trunc('month'::text, t3._period), t3._fld156208rref
   Filter: (sum(CASE WHEN (t3._recordkind = '0'::numeric) THEN t3._fld37857 ELSE (- t3._fld37857) END) <> '0'::numeric)
   Batches: 1  Memory Usage: 21521kB
   Rows Removed by Filter: 5343
   Buffers: shared hit=185578, local hit=320
   ->  Nested Loop  (cost=750.68..382089.95 rows=943254 width=31) (actual time=15.544..318.764 rows=81842.00 loops=1)
         Buffers: shared hit=185578, local hit=320
         ->  HashAggregate  (cost=750.12..1094.22 rows=34410 width=17) (actual time=15.486..24.562 rows=34410.00 loops=1)
               Group Key: tt1._idrref
               Batches: 1  Memory Usage: 3097kB
               Buffers: local hit=320
               ->  Seq Scan on tt1  (cost=0.00..664.10 rows=34410 width=17) (actual time=0.024..3.485 rows=34410.00 loops=1)
                     Buffers: local hit=320
         ->  Index Scan using _accumrg37853_3 on _accumrg37853 t3  (cost=0.56..10.73 rows=27 width=31) (actual time=0.006..0.008 rows=2.38 loops=34410)
               Index Cond: ((_fld1551 = '0'::numeric) AND (_fld156208rref = tt1._idrref) AND
                            (_period >= '2018-02-01 00:00:00'::timestamp without time zone) AND
                            (_period <= '2018-02-28 23:59:59'::timestamp without time zone))
               Filter: _active
               Index Searches: 34410
               Buffers: shared hit=185578
 Planning:
   Buffers: shared hit=13 dirtied=1
 Planning Time: 0.609 ms
 Execution Time: 370.688 ms

Как видим, вместо Hash Join стал использоваться Nested Loop, а для сканирования _accumrg37853 индекс  _accumrg3785_3, что привело к значительному ускорению запроса: время выполнения сократилось до 370 мс.

Значит, для оптимизации первоначального запроса без его переписывания требуется перенести условие соединения T2.Fld156208RRef = T1._IDRRef внутрь подзапроса. Но почему планировщик не сделал этого самостоятельно?

 

Ограничения стандартного механизма переноса условий

В СУБД PostgreSQL есть штатный механизм перемещения базовых условий фильтрации внутрь подзапроса, используемых для уменьшения объёма обрабатываемых данных ещё до их соединения с другими таблицами.

Например, в запросе ниже фильтры успешно помещаются в секцию WHERE трёх подзапросов, объединённых через UNION:

CREATE TABLE IF NOT EXISTS _t1 (
    a INT,
    b INT
);

EXPLAIN
SELECT T1.f1 FROM (
    SELECT T2.b AS f1 FROM _t1 AS T2
    UNION
    SELECT T3.b AS f2 FROM _t1 AS T3
    UNION
    SELECT T4.b AS f3 FROM _t1 AS T4
    ) AS T1
WHERE (CASE WHEN T1.f1 IS NULL THEN 12 ELSE 11 END <> 11);

                               QUERY PLAN                                
-------------------------------------------------------------------------
 HashAggregate  (cost=0.02..0.05 rows=3 width=4)
   Group Key: t2.b
   ->  Append  (cost=0.00..0.01 rows=3 width=4)
         ->  Seq Scan on _t1 t2  (cost=0.00..0.00 rows=1 width=4)
               Filter: (CASE WHEN (b IS NULL) THEN 12 ELSE 11 END <> 11)
         ->  Seq Scan on _t1 t3  (cost=0.00..0.00 rows=1 width=4)
               Filter: (CASE WHEN (b IS NULL) THEN 12 ELSE 11 END <> 11)
         ->  Seq Scan on _t1 t4  (cost=0.00..0.00 rows=1 width=4)
               Filter: (CASE WHEN (b IS NULL) THEN 12 ELSE 11 END <> 11)
(9 rows)

Также базовое условие может быть перемещено в секцию HAVING, как в следующем примере:

EXPLAIN (COSTS OFF)
SELECT s1.*
  FROM (SELECT T2.b AS f1
             , COUNT(1) AS cnt
          FROM _t1 AS T2
         GROUP BY T2.b
       ) s1
 WHERE s1.cnt > 1;

        QUERY PLAN        
--------------------------
 HashAggregate
   Group Key: t2.b
   Filter: (count(1) > 1)
   ->  Seq Scan on _t1 t2
(4 rows)

Однако на условия соединения этот механизм автоматического перемещения ограничений ранее не распространялся. Из-за этого планировщик не мог самостоятельно оптимизировать исходный запрос 1С. Чтобы решить эту проблему, требовалось доработать СУБД, научив оптимизатор перемещать условия соединения внутрь подзапросов с агрегацией.

Стоит отметить, что в MS SQL Server подобный механизм присутствует и называется join predicate pushdown.

 

План первоначального запроса после доработки СУБД

После реализации улучшенного механизма переноса предикатов исходный запрос стал выполняться примерно в 22 раза быстрее благодаря выбору Nested Loop и сканирования таблицы _accumrg37853 по индексу  _accumrg3785_3:

Nested Loop  (cost=17.96..649402.40 rows=10455 width=54) (actual time=30.568..374.274 rows=5008.00 loops=1)
  Buffers: shared hit=219290
  ->  Index Scan using _reference139051_6 on _reference139051 t1  (cost=0.56..17927.96 rows=34282 width=39) (actual time=0.035..45.458 rows=34410.00 loops=1)
        Index Cond: ((_fld1551 = '0'::numeric) AND (_fld144699rref = '\\xbc2454782481b51511ed963e3f47e94b'::bytea))
        Index Searches: 1
        Buffers: shared hit=33712
  ->  HashAggregate  (cost=17.41..17.88 rows=27 width=57) (actual time=0.009..0.009 rows=0.15 loops=34410)
        Group Key: date_trunc('month'::text, t3._period)
        Filter: (sum(CASE WHEN (t3._recordkind = '0'::numeric) THEN t3._fld37857 ELSE (-t3._fld37857) END) <> '0'::numeric)
        Batches: 1  Memory Usage: 32kB
        Rows Removed by Filter: 0
        Buffers: shared hit=185578
        ->  Index Scan using _accumrg37853_3 on _accumrg37853 t3  (cost=0.56..17.14 rows=27 width=31) (actual time=0.006..0.008 rows=2.38 loops=34410)
              Index Cond: ((_fld1551 = '0'::numeric) AND (_fld156208rref = t1._idrref) AND
                           (_period >= '2018-02-01 00:00:00'::timestamp without time zone) AND
                           (_period <= '2018-02-28 23:59:59'::timestamp without time zone))
              Filter: _active
              Index Searches: 34410
              Buffers: shared hit=185578
Planning:
  Buffers: shared hit=368
Planning Time: 2.243 ms
Execution Time: 374.893 ms

Ниже приведены замеры времён планирования и выполнения без и с использованием join predicate pushdown, время выполнения сократилось примерно в 20 раз.

 

Режим Время планирования запроса (мс) Время выполнения запроса (мс)
#1 #2 #3 #4 #5 #6 #7 #8 #9 #1 #2 #3 #4 #5 #6 #7 #8 #9
Без join predicate pushdown 0.439 0.437 0.468 0.454 0.446 0.469 0.460 0.434 0.432 7 374.632 7 302.677 7 315.513 7 303.238 7 151.690 7 126.639 6 920.008 6 913.543 6 892.886
С join predicate pushdown 0.487 0.488 0.487 0.476 0.488 0.496 0.489 0.488 0.489 369.600 354.066 331.134 337.584 335.701 332.566 326.931 363.975 334.942

 

 

Другие примеры: вложенные GROUP BY, UNION ALL + GROUP BY + JOIN и LIMIT

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

Пример 1: запрос с вложенными GROUP BY

Запрос содержит два подзапроса, в каждом из которых используются агрегатные функции. Ниже приведён план выполнения запроса без использования join_predicate_pushdown:

set enable_mergejoin to off;

EXPLAIN (ANALYZE, BUFFERS)
SELECT t1.fld37862rref,
       t1.fld37863rref,
       t1.Fld37857FinalBalance_,
       t1.Fld37858FinalBalance_
  FROM (SELECT T2.fld37862rref AS fld37862rref,
               T2.fld37863rref AS fld37863rref,
               SUM(T2.Fld37857Balance_ + T2.Fld37857Receipt_ - T2.Fld37857Expense_) AS Fld37857FinalBalance_,
               SUM(T2.Fld37858Balance_ + T2.Fld37858Receipt_ - T2.Fld37858Expense_) AS Fld37858FinalBalance_
          FROM (SELECT T3._fld37862rref AS fld37862rref,
                       T3._fld37863rref AS fld37863rref,
                       SUM(CAST(0 AS NUMERIC)) AS Fld37857Balance_,
                       SUM(CASE WHEN T3._RecordKind = CAST(0 AS NUMERIC) THEN T3._fld37857 ELSE CAST(0 AS NUMERIC) END) AS Fld37857Receipt_,
                       SUM(CASE WHEN T3._RecordKind = CAST(0 AS NUMERIC) THEN CAST(0 AS NUMERIC) ELSE T3._fld37857 END) AS Fld37857Expense_,
                       SUM(CAST(0 AS NUMERIC)) AS Fld37858Balance_,
                       SUM(CASE WHEN T3._RecordKind = CAST(0 AS NUMERIC) THEN T3._fld37858 ELSE CAST(0 AS NUMERIC) END) AS Fld37858Receipt_,
                       SUM(CASE WHEN T3._RecordKind = CAST(0 AS NUMERIC) THEN CAST(0 AS NUMERIC) ELSE T3._fld37858 END) AS Fld37858Expense_
                  FROM _accumrg37853 T3
                 WHERE T3._Fld1551 = CAST(0 AS NUMERIC)
                   AND T3._Period <= '2018-01-23 23:59:59'::timestamp
                   AND T3._Active = TRUE
                 GROUP BY T3._fld37862rref, T3._fld37863rref
                HAVING SUM(CAST(0 AS NUMERIC)) != CAST(0 AS NUMERIC) OR
                       SUM(CASE WHEN T3._RecordKind = CAST(0 AS NUMERIC) THEN T3._fld37857 ELSE CAST(0 AS NUMERIC) END) != CAST(0 AS NUMERIC) OR
                       SUM(CASE WHEN T3._RecordKind = CAST(0 AS NUMERIC) THEN CAST(0 AS NUMERIC) ELSE T3._fld37857 END) != CAST(0 AS NUMERIC) OR
                       SUM(CAST(0 AS NUMERIC)) != CAST(0 AS NUMERIC) OR
                       SUM(CASE WHEN T3._RecordKind = CAST(0 AS NUMERIC) THEN T3._fld37858 ELSE CAST(0 AS NUMERIC) END) != CAST(0 AS NUMERIC) OR
                       SUM(CASE WHEN T3._RecordKind = CAST(0 AS NUMERIC) THEN CAST(0 AS NUMERIC) ELSE T3._fld37858 END) != CAST(0 AS NUMERIC)
                ) T2
         GROUP BY T2.fld37862rref, T2.fld37863rref
        HAVING (SUM(T2.Fld37857Balance_ + T2.Fld37857Receipt_ - T2.Fld37857Expense_)) <> CAST(0 AS NUMERIC) OR
               (SUM(T2.Fld37858Balance_ + T2.Fld37858Receipt_ - T2.Fld37858Expense_)) <> CAST(0 AS NUMERIC)
       ) T1
  JOIN pg_temp.tt1 T4
    ON T1.fld37862rref = T4._Q_000_F_000RRef
   AND T1.fld37863rref = T4._Q_000_F_001RRef;

Hash Join  (cost=386172.32..386174.28 rows=1 width=98) (actual time=1508.636..1508.696 rows=3 loops=1)
   Hash Cond: ((t3._fld37862rref = t4._q_000_f_000rref) AND (t3._fld37863rref = t4._q_000_f_001rref))
   Buffers: shared hit=713041, local hit=1
   ->  GroupAggregate  (cost=386171.24..386172.90 rows=39 width=98) (actual time=1508.610..1508.661 rows=29 loops=1)
         Group Key: t3._fld37862rref, t3._fld37863rref
         Filter: (...)
         Rows Removed by Filter: 2
         Buffers: shared hit=713041
         ->  Sort  (cost=386171.24..386171.34 rows=39 width=226) (actual time=1508.601..1508.605 rows=31 loops=1)
               Sort Key: t3._fld37862rref, t3._fld37863rref
               Sort Method: quicksort  Memory: 27kB
               Buffers: shared hit=713041
               ->  HashAggregate  (cost=386168.23..386169.70 rows=39 width=226) (actual time=1508.538..1508.574 rows=31 loops=1)
                     Group Key: t3._fld37862rref, t3._fld37863rref
                     Filter: (...)
                     Batches: 1  Memory Usage: 80kB
                     Rows Removed by Filter: 3
                     Buffers: shared hit=713041
                     ->  Index Scan using _accumrg37853_1 on _accumrg37853 t3  (cost=0.56..351165.77 rows=1272817 width=43)
                                                                               (actual time=0.045..692.636 rows=1249051 loops=1)
                           Index Cond: ((_fld1551 = '0'::numeric) AND (_period <= '2018-01-23 23:59:59'::timestamp without time zone))
                           Filter: _active
                           Buffers: shared hit=713041
   ->  Hash  (cost=1.03..1.03 rows=3 width=34) (actual time=0.013..0.014 rows=3 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         Buffers: local hit=1
         ->  Seq Scan on tt1 t4  (cost=0.00..1.03 rows=3 width=34) (actual time=0.008..0.010 rows=3 loops=1)
               Buffers: local hit=1
Planning:
   Buffers: shared hit=1
 Planning Time: 0.516 ms
 Execution Time: 1508.787 ms

Причиной медленного выполнения является сканирование более одного миллиона записей таблицы _accumrg37853.

А вот план выполнения того же запроса с доработанным механизмом:

Nested Loop  (cost=0.56..55770.50 rows=1 width=98) (actual time=12.501..158.292 rows=3 loops=1)
   Buffers: shared hit=98055, local hit=1
   ->  Seq Scan on tt1 t4  (cost=0.00..1.03 rows=3 width=34) (actual time=0.007..0.011 rows=3 loops=1)
         Buffers: local hit=1
   ->  GroupAggregate  (cost=0.56..18589.81 rows=1 width=98) (actual time=52.757..52.757 rows=1 loops=3)
         Filter: (...)
         Buffers: shared hit=98055
         ->  GroupAggregate  (cost=0.56..18589.78 rows=1 width=226) (actual time=52.750..52.750 rows=1 loops=3)
               Filter: (...)
               Buffers: shared hit=98055
               ->  Index Scan using _accumrg37853_8 on _accumrg37853 t3  (cost=0.56..17855.43 rows=32636 width=43) (actual time=0.032..30.045 rows=52867 loops=3)
                     Index Cond: ((_fld1551 = '0'::numeric) AND (_fld37862rref = t4._q_000_f_000rref) AND
                                  (_fld37863rref = t4._q_000_f_001rref) AND
                                  (_period <= '2018-01-23 23:59:59'::timestamp without time zone))
                     Filter: _active
                     Buffers: shared hit=98055
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.631 ms
 Execution Time: 158.344 ms

Здесь два условия соединения были успешно перемещены во внутренний подзапрос, они обработаны индексом _accumrg37853_8, что позволило ускорить работу запроса почти в 9.4 раза (было 1480.392 мс, стало 156.961 мс).

Ниже приведены замеры времён планирования и выполнения без и с использованием join predicate pushdown.

 

Режим Время планирования запроса (мс) Время выполнения запроса (мс)
#1 #2 #3 #4 #5 #6 #7 #8 #9 #1 #2 #3 #4 #5 #6 #7 #8 #9
Без join predicate pushdown 0.481 0.492 0.488 0.492 0.483 0.494 0.481 0.500 0.490 1 480.392 1 508.199 1 508.702 1 536.084 1 527.228 1 476.463 1 529.106 1 477.393 1 494.576
С join predicate pushdown 0.642 0.647 0.632 0.649 0.635 0.646 0.640 0.645 0.653 156.961 158.514 156.852 158.970 159.951 161.500 160.958 158.555 160.793

 

 

Пример 2: запрос с LIMIT

В следующем примере запрос выполнялся медленно из-за того, что условие t1._idrref = t2.q_001_f_000rref не перемещалось внутрь подзапроса:

EXPLAIN (ANALYZE, BUFFERS)
SELECT T1._IDRRef,
       T2.Q_001_F_001_,
       T1._Number,
       T1._Date_Time,
       T1._Fld52
  FROM _Document49 T1
  LEFT JOIN (SELECT T3._Document49_IDRRef AS Q_001_F_000RRef,
                    MAX(T3._LineNo54) AS Q_001_F_001_
               FROM _Document49_VT53 T3
              WHERE T3._Fld57_TYPE = '\\x08'::bytea
                AND T3._Fld57_RTRef = '\\x000155DF'::bytea
              GROUP BY T3._Document49_IDRRef
            ) T2
    ON T1._IDRRef = T2.Q_001_F_000RRef
 ORDER BY T1._Fld52
 LIMIT 45;

Limit  (cost=79486.39..111210.93 rows=45 width=87) (actual time=1315.425..3858.427 rows=45 loops=1)
   Buffers: shared hit=529168
   ->  Nested Loop Left Join  (cost=79486.39..922043354.07 rows=1307769 width=87) (actual time=1315.424..3858.378 rows=45 loops=1)
         Join Filter: (t1._idrref = t2.q_001_f_000rref)
         Rows Removed by Join Filter: 19306436
         Buffers: shared hit=529168
         ->  Index Scan using _document49_3 on _document49 t1  (cost=0.43..45220.20 rows=1307769 width=55) (actual time=0.032..0.416 rows=45 loops=1)
               Buffers: shared hit=5
         ->  Materialize  (cost=79485.96..80660.89 rows=46997 width=52) (actual time=21.840..47.543 rows=429032 loops=45)
               Buffers: shared hit=529163
               ->  Subquery Scan on t2  (cost=79485.96..80425.90 rows=46997 width=52) (actual time=982.791..1160.152 rows=479586 loops=1)
                     Buffers: shared hit=529163
                     ->  HashAggregate  (cost=79485.96..79955.93 rows=46997 width=52) (actual time=982.790..1113.048 rows=479586 loops=1)
                           Group Key: t3._document49_idrref
                           Batches: 1  Memory Usage: 81937kB
                           Buffers: shared hit=529163
                           ->  Index Scan using _document49_vt53_1 on _document49_vt53 t3  (cost=0.56..75044.40 rows=888312 width=25)
                                                                                           (actual time=0.046..603.932 rows=933254 loops=1)
                                 Index Cond: ((_fld57_type = '\\x08'::bytea) AND (_fld57_rtref = '\\x000155df'::bytea))
                                 Buffers: shared hit=529163
 Planning Time: 0.234 ms
 Execution Time: 3864.758 ms

Перенос предиката соединения внутрь подзапроса и использование индекса _document49_vt53_sk позволило сократить время выполнения этого запроса до субмиллисекундных значений:

 Limit  (cost=0.98..2506.05 rows=45 width=87) (actual time=0.052..0.217 rows=45 loops=1)
   Buffers: shared hit=231
   ->  Nested Loop Left Join  (cost=0.98..72800911.28 rows=1307769 width=87) (actual time=0.051..0.211 rows=45 loops=1)
         Buffers: shared hit=231
         ->  Index Scan using _document49_3 on _document49 t1  (cost=0.43..45220.20 rows=1307769 width=55) (actual time=0.027..0.038 rows=45 loops=1)
               Buffers: shared hit=5
         ->  GroupAggregate  (cost=0.56..55.61 rows=1 width=52) (actual time=0.003..0.003 rows=0 loops=45)
               Buffers: shared hit=226
               ->  Index Scan using _document49_vt53_sk on _document49_vt53 t3  (cost=0.56..55.56 rows=19 width=25) (actual time=0.003..0.003 rows=0 loops=45)
                     Index Cond: (_document49_idrref = t1._idrref)
                     Filter: ((_fld57_type = '\\x08'::bytea) AND (_fld57_rtref = '\\x000155df'::bytea))
                     Rows Removed by Filter: 1
                     Buffers: shared hit=226
 Planning Time: 0.288 ms
 Execution Time: 0.249 ms

Ниже приведены замеры времён планирования и выполнения без и с использованием join_predicate_pushdown, ускорение в 15319 раз (было 3707.188 мс, стало 0.242 мс).

 

Режим Время планирования запроса (мс) Время выполнения запроса (мс)
#1 #2 #3 #4 #5 #6 #7 #8 #9 #1 #2 #3 #4 #5 #6 #7 #8 #9
Без join_predicate_pushdown 0.234 0.245 0.246 0.244 0.245 0.234 0.235 0.243 0.232 3 707.188 3 689.856 3 698.212 3 737.954 3 674.679 3 728.245 3 692.016 3 688.613 3 750.357
С join_predicate_pushdown 0.289 0.290 0.293 0.293 0.293 0.291 0.295 0.294 0.284 0.242 0.243 0.253 0.245 0.244 0.245 0.260 0.245 0.245

 

 

Пример 3: запрос с GROUP BY + UNION ALL + JOIN

Пример ниже не связан с 1С, но он хорошо демонстрирует возможности переноса условий соединения внутрь сложных конструкций, в том числе и в соединения таблиц, объединяемых с помощью UNION ALL.

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

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE stock (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE product (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    "group" INT
);

CREATE INDEX product_group ON product ("group");

CREATE TABLE receipt (
    id INT PRIMARY KEY,
    customer INT NOT NULL,
	"date" DATE NOT NULL,
    CONSTRAINT receipt_customer_fk
        FOREIGN KEY (customer) REFERENCES customer (id),
   	stock INT NOT NULL,
    CONSTRAINT receipt_stock_fk
        FOREIGN KEY (stock) REFERENCES stock (id),
    data VARCHAR(400)
);

CREATE INDEX receipt_customer_fk ON receipt (customer);
CREATE INDEX receipt_stock_fk ON receipt (stock);

CREATE TABLE shipment (
    id INT PRIMARY KEY,
    customer INT NOT NULL,
	"date" DATE NOT NULL,
    CONSTRAINT shipment_customer_fk
        FOREIGN KEY (customer) REFERENCES customer (id),
    stock INT NOT NULL,
    CONSTRAINT shipment_stock_fk
        FOREIGN KEY (stock) REFERENCES stock (id),
    data VARCHAR(400)
);

CREATE INDEX shipment_customer_fk ON shipment (customer);
CREATE INDEX shipment_stock_fk ON shipment (stock);

CREATE TABLE receiptdetail (
    id INT PRIMARY KEY,
    receipt INT NOT NULL,
	product INT NOT NULL,
	quantity NUMERIC(16,3),
    CONSTRAINT receiptdetail_receipt_fk
        FOREIGN KEY (receipt) REFERENCES receipt (id),
    CONSTRAINT receiptdetail_product_fk
    	FOREIGN KEY (product) REFERENCES product (id),
    data VARCHAR(400)
);

CREATE INDEX receiptdetail_receipt_fk ON receiptdetail (receipt);
CREATE INDEX receiptdetail_product_fk ON receiptdetail (product);

CREATE TABLE shipmentdetail (
    id INT PRIMARY KEY,
    shipment INT NOT NULL,
	product INT NOT NULL,
	quantity NUMERIC(16,3),
    CONSTRAINT shipmentdetail_shipment_fk
        FOREIGN KEY (shipment) REFERENCES shipment (id),
    CONSTRAINT shipmentdetail_product_fk
    	FOREIGN KEY (product) REFERENCES product (id),
    sd INT,
    data VARCHAR(400)
);

CREATE INDEX shipmentdetail_shipment_fk ON shipmentdetail (shipment);
CREATE INDEX shipmentdetail_product_fk ON shipmentdetail (product);
CREATE INDEX shipmentdetail_sd ON shipmentdetail (sd);
CREATE INDEX shipmentdetail_p_s ON shipmentdetail (product,shipment);

INSERT INTO stock (id, name)
WITH RECURSIVE
numbers ( n ) AS (
   SELECT 1 AS n
   UNION ALL
   SELECT n + 1 AS n
   FROM   numbers
   WHERE  n <= 10
)
SELECT n, 'Stock '||n FROM numbers;

INSERT INTO customer (id, name)
WITH RECURSIVE
numbers ( n ) AS (
   SELECT 1 AS n
   UNION ALL
   SELECT n + 1 AS n
   FROM   numbers
   WHERE  n <= 1000
)
SELECT n, 'Customer '||n FROM numbers;

INSERT INTO product (id, name, "group")
WITH RECURSIVE
numbers ( n ) AS (
   SELECT 1 AS n
   UNION ALL
   SELECT n + 1 AS n
   FROM   numbers
   WHERE  n <= 50000
)
SELECT n, 'Product '||n, floor(random() * 1000) + 1 FROM numbers;

INSERT INTO receipt (id, customer, stock, "date", data)
WITH RECURSIVE
numbers ( n ) AS (
   SELECT 1 AS n
   UNION ALL
   SELECT n + 1 AS n
   FROM   numbers
   WHERE  n <= 10000
)
SELECT n, floor(random() * 1000) + 1, floor(random() * 10) + 1, CURRENT_DATE-CAST(floor(random() * 300) AS int), 'data'||n FROM numbers;

INSERT INTO receiptdetail (id, receipt, product, quantity, data)
WITH RECURSIVE
numbers ( n ) AS (
   SELECT 1 AS n
   UNION ALL
   SELECT n + 1 AS n
   FROM   numbers
   WHERE  n <= 1000000
)
SELECT n, floor(random() * 10000) + 1, floor(random() * 50000) + 1, floor(random() * 100) + 1, 'data'||n FROM numbers;

INSERT INTO shipment (id, customer, stock, "date", data)
WITH RECURSIVE
numbers ( n ) AS (
   SELECT 1 AS n
   UNION ALL
   SELECT n + 1 AS n
   FROM   numbers
   WHERE  n <= 100000
)
SELECT n, floor(random() * 1000) + 1, floor(random() * 10) + 1, CURRENT_DATE-CAST(floor(random() * 300) AS int), 'data'||n FROM numbers;

INSERT INTO shipmentdetail (id, shipment, product, quantity, data, sd)
WITH RECURSIVE
numbers ( n ) AS (
   SELECT 1 AS n
   UNION ALL
   SELECT n + 1 AS n
   FROM   numbers
   WHERE  n <= 10000000
)
SELECT n, floor(random() * 100000) + 1, floor(random() * 50000) + 1, floor(random() * 10) + 1, 'data'||n, CASE WHEN n % 100000 = 0 THEN n+1 ELSE NULL END FROM numbers;

ANALYZE;

Ниже приведён проблемный запрос:

EXPLAIN (ANALYZE, BUFFERS)
SELECT balance.quantity
  FROM product
  JOIN (SELECT product, SUM(quantity) AS quantity
		  FROM (SELECT product, quantity
				  FROM receiptDetail 
				  JOIN receipt
				    ON receiptDetail.receipt = receipt.id
				 
				 UNION ALL
				
				SELECT product, -quantity
				  FROM shipmentDetail 
				  JOIN shipment
				    ON shipmentDetail.shipment = shipment.id
				) details
		  GROUP BY product
		) balance
	ON balance.product = product.id
 WHERE product.group = 54;

Посмотрим на план выполнения без использования join predicate pushdown:

Hash Join  (cost=358699.50..358704.52 rows=1 width=32) (actual time=9102.728..9122.174 rows=39 loops=1)
   Hash Cond: (receiptdetail.product = product.id)
   Buffers: shared hit=82336
   ->  HashAggregate  (cost=358648.72..358651.22 rows=200 width=36) (actual time=9101.870..9117.910 rows=50000 loops=1)
         Group Key: receiptdetail.product
         Batches: 1  Memory Usage: 26145kB
         Buffers: shared hit=82296
         ->  Append  (cost=299.02..303648.63 rows=11000018 width=34) (actual time=2.566..5511.195 rows=11000002 loops=1)
               Buffers: shared hit=82296
               ->  Hash Join  (cost=299.02..20333.15 rows=1000001 width=9) (actual time=2.565..323.227 rows=1000001 loops=1)
                     Hash Cond: (receiptdetail.receipt = receipt.id)
                     Buffers: shared hit=7482
                     ->  Seq Scan on receiptdetail  (cost=0.00..17408.01 rows=1000001 width=13) (actual time=0.016..89.806 rows=1000001 loops=1)
                           Buffers: shared hit=7408
                     ->  Hash  (cost=174.01..174.01 rows=10001 width=4) (actual time=2.414..2.415 rows=10001 loops=1)
                           Buckets: 16384  Batches: 1  Memory Usage: 480kB
                           Buffers: shared hit=74
                           ->  Seq Scan on receipt  (cost=0.00..174.01 rows=10001 width=4) (actual time=0.017..1.176 rows=10001 loops=1)
                                 Buffers: shared hit=74
               ->  Hash Join  (cost=2990.02..228315.39 rows=10000017 width=36) (actual time=30.946..4319.120 rows=10000001 loops=1)
                     Hash Cond: (shipmentdetail.shipment = shipment.id)
                     Buffers: shared hit=74814
                     ->  Seq Scan on shipmentdetail  (cost=0.00..174074.17 rows=10000017 width=13) (actual time=0.033..947.901 rows=10000001 loops=1)
                           Buffers: shared hit=74074
                     ->  Hash  (cost=1740.01..1740.01 rows=100001 width=4) (actual time=30.065..30.065 rows=100001 loops=1)
                           Buckets: 131072  Batches: 1  Memory Usage: 4540kB
                           Buffers: shared hit=740
                           ->  Seq Scan on shipment  (cost=0.00..1740.01 rows=100001 width=4) (actual time=0.017..10.502 rows=100001 loops=1)
                                 Buffers: shared hit=740
   ->  Hash  (cost=50.18..50.18 rows=48 width=4) (actual time=0.304..0.305 rows=39 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 10kB
         Buffers: shared hit=40
         ->  Bitmap Heap Scan on product  (cost=1.76..50.18 rows=48 width=4) (actual time=0.065..0.296 rows=39 loops=1)
               Recheck Cond: ("group" = 54)
               Heap Blocks: exact=38
               Buffers: shared hit=40
               ->  Bitmap Index Scan on product_group  (cost=0.00..1.75 rows=48 width=0) (actual time=0.041..0.041 rows=39 loops=1)
                     Index Cond: ("group" = 54)
                     Buffers: shared hit=2
Planning:
   Buffers: shared hit=59
 Planning Time: 0.628 ms
 Execution Time: 9123.310 ms

Запрос выполнялся долго из-за полных сканирований таблиц с выбором 10 миллионов строк с последующими соединениями по Hash Join. join predicate pushdown позволяет добавить условие product = product.id в каждый подзапрос запроса details, что приводит к индексным сканированиям и выполнению соединения данных с помощью Nested Loop.

Nested Loop  (cost=2.47..24165.19 rows=1 width=32) (actual time=1.123..28.776 rows=39 loops=1)
   Buffers: shared hit=25963
   ->  Bitmap Heap Scan on product  (cost=1.76..50.18 rows=48 width=4) (actual time=0.020..0.093 rows=39 loops=1)
         Recheck Cond: ("group" = 54)
         Heap Blocks: exact=38
         Buffers: shared hit=40
         ->  Bitmap Index Scan on product_group  (cost=0.00..1.75 rows=48 width=0) (actual time=0.010..0.011 rows=39 loops=1)
               Index Cond: ("group" = 54)
               Buffers: shared hit=2
   ->  GroupAggregate  (cost=0.71..502.38 rows=1 width=36) (actual time=0.735..0.735 rows=1 loops=39)
         Buffers: shared hit=25923
         ->  Append  (cost=0.71..501.81 rows=222 width=33) (actual time=0.008..0.706 rows=219 loops=39)
               Buffers: shared hit=25923
               ->  Nested Loop  (cost=0.71..48.95 rows=21 width=9) (actual time=0.008..0.064 rows=20 loops=39)
                     Buffers: shared hit=2446
                     ->  Index Scan using receiptdetail_product_fk on receiptdetail  (cost=0.42..24.99 rows=21 width=13) (actual time=0.005..0.030 rows=20 loops=39)
                           Index Cond: (product = product.id)
                           Buffers: shared hit=893
                     ->  Index Only Scan using receipt_pkey on receipt  (cost=0.29..1.14 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=776)
                           Index Cond: (id = receiptdetail.receipt)
                           Heap Fetches: 0
                           Buffers: shared hit=1553
               ->  Nested Loop  (cost=0.73..451.75 rows=201 width=36) (actual time=0.014..0.623 rows=200 loops=39)
                     Buffers: shared hit=23477
                     ->  Index Scan using shipmentdetail_p_s on shipmentdetail  (cost=0.43..226.14 rows=201 width=13) (actual time=0.011..0.286 rows=200 loops=39)
                           Index Cond: (product = product.id)
                           Buffers: shared hit=7910
                     ->  Index Only Scan using shipment_pkey on shipment  (cost=0.29..1.12 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=7783)
                           Index Cond: (id = shipmentdetail.shipment)
                           Heap Fetches: 0
                           Buffers: shared hit=15567
 Planning:
   Buffers: shared hit=56
 Planning Time: 0.685 ms
 Execution Time: 28.827 ms

Ниже приведены замеры времён планирования и выполнения без и с использованием join predicate pushdown, ускорение в 339 раз (было 10045.226 мс, стало 29.624 мс):

 

Режим Время планирования запроса (мс) Время выполнения запроса (мс)
#1 #2 #3 #4 #5 #6 #7 #8 #9 #1 #2 #3 #4 #5 #6 #7 #8 #9
Без join predicate pushdown 0.589 0.582 0.594 0.551 0.561 0.556 0.581 0.554 0.587 10 045.256 9 774.659 10 152.471 10 106.819 9 384.998 9 126.367 9 366.012 8 709.335 9 092.763
С join predicate pushdown 0.739 0.741 0.755 0.754 0.732 0.744 0.738 0.737 0.740 29.624 33.633 29.792 29.611 29.517 29.192 28.937 29.298 29.416

 

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

 

Пример 4: запрос с HAVING + GROUP BY + UNION ALL + JOIN

Запрос ниже является упрощённым вариантом запроса из мира 1С, но он также демонстрирует возможность передачи предикатов соединения в более сложные конструкции.

EXPLAIN (ANALYZE, BUFFERS)
SELECT T1.AccountRRef,
       T20._Q_001_F_000RRef
  FROM (SELECT T2.AccountRRef AS AccountRRef,
               T2.Value1_TYPE AS Value1_TYPE,
               T2.Value1_RTRef AS Value1_RTRef,
               T2.Value1_RRRef AS Value1_RRRef,
               T2.Value2_TYPE AS Value2_TYPE,
               T2.Value2_RTRef AS Value2_RTRef,
               T2.Value2_RRRef AS Value2_RRRef,
               MAX(T19._Kind) AS AccKind_
          FROM (SELECT T15._AccountDtRRef AS AccountRRef,

                       CASE WHEN T16._ED1 = TRUE THEN T15._ValueDt1_TYPE END AS Value1_TYPE,
                       CASE WHEN T16._ED1 = TRUE THEN T15._ValueDt1_RTRef END AS Value1_RTRef,
                       CASE WHEN T16._ED1 = TRUE THEN T15._ValueDt1_RRRef END AS Value1_RRRef,
                       CASE WHEN T16._ED2 = TRUE THEN T15._ValueDt2_TYPE END AS Value2_TYPE,
                       CASE WHEN T16._ED2 = TRUE THEN T15._ValueDt2_RTRef END AS Value2_RTRef,
                       CASE WHEN T16._ED2 = TRUE THEN T15._ValueDt2_RRRef END AS Value2_RRRef,

                       CAST(CAST(0 AS NUMERIC) AS NUMERIC(21, 2)) AS Fld802Balance_,

                       CASE
                         WHEN T16._BRF1 = TRUE THEN T15._Fld802 ELSE CAST(0 AS NUMERIC)
                       END AS Fld802FinalTurnover_
          
                  FROM _AccRg798 T15
                  JOIN pg_temp.tt3 T16
                    ON T16._IDRRef = T15._AccountDtRRef
                 WHERE T15._Fld786 = CAST(0 AS NUMERIC)
                   AND T15._Active = TRUE
                   AND T15._AccountDtRRef <> '\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea
                   AND T15._Period >= '2010-01-01 00:00:00'::timestamp
                   AND NOT (T15._Period = '2010-01-01 00:00:00'::timestamp AND T15._PeriodAdjustment < CAST(0 AS NUMERIC))
                   AND T15._Period <= '2015-07-01 00:00:00'::timestamp
                   AND NOT (T15._Period = '2015-07-01 00:00:00'::timestamp AND T15._PeriodAdjustment > CAST(0 AS NUMERIC))

                 UNION ALL

                SELECT T17._AccountCtRRef AS AccountRRef,
                       CASE WHEN T18._ED1 = TRUE THEN T17._ValueCt1_TYPE END AS Value1_TYPE,
                       CASE WHEN T18._ED1 = TRUE THEN T17._ValueCt1_RTRef END AS Value1_RTRef,
                       CASE WHEN T18._ED1 = TRUE THEN T17._ValueCt1_RRRef END AS Value1_RRRef,
                       CASE WHEN T18._ED2 = TRUE THEN T17._ValueCt2_TYPE END AS Value2_TYPE,
                       CASE WHEN T18._ED2 = TRUE THEN T17._ValueCt2_RTRef END AS Value2_RTRef,
                       CASE WHEN T18._ED2 = TRUE THEN T17._ValueCt2_RRRef END AS Value2_RRRef,

                       CAST(CAST(0 AS NUMERIC) AS NUMERIC(21, 2)) AS Fld802Balance_,

                       CASE WHEN T18._BRF1 = TRUE THEN -T17._Fld802 ELSE CAST(0 AS NUMERIC) END AS Fld802FinalTurnover_
                  FROM _AccRg798 T17
                  JOIN pg_temp.tt3 T18
                    ON T18._IDRRef = T17._AccountCtRRef
                 WHERE T17._Fld786 = CAST(0 AS NUMERIC)
                   AND T17._Active = TRUE
                   AND T17._AccountCtRRef <> '\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea
                   AND T17._Period >= '2010-01-01 00:00:00'::timestamp
                   AND NOT (T17._Period = '2010-01-01 00:00:00'::timestamp AND
                            T17._PeriodAdjustment < CAST(0 AS NUMERIC)
                           )
                   AND T17._Period <= '2015-07-01 00:00:00'::timestamp
                   AND NOT (T17._Period = '2015-07-01 00:00:00'::timestamp AND
                            T17._PeriodAdjustment > CAST(0 AS NUMERIC))
               ) T2
          JOIN _Acc26 T19
            ON T19._IDRRef = T2.AccountRRef
         WHERE T19._Fld786 = CAST(0 AS NUMERIC)
         GROUP BY T2.AccountRRef, T2.Value1_TYPE, T2.Value1_RTRef, T2.Value1_RRRef,
                  T2.Value2_TYPE, T2.Value2_RTRef, T2.Value2_RRRef
        HAVING CASE
                 WHEN SUM(T2.Fld802Balance_ + T2.Fld802FinalTurnover_) IS NULL
                   THEN CAST(0 AS NUMERIC)
                 WHEN MAX(T19._Kind) = CAST(0 AS NUMERIC) OR MAX(T19._Kind) = CAST(2 AS NUMERIC) AND
                      SUM(T2.Fld802Balance_ + T2.Fld802FinalTurnover_) > CAST(0 AS NUMERIC)
                   THEN SUM(T2.Fld802Balance_ + T2.Fld802FinalTurnover_)
                 ELSE CAST(0 AS NUMERIC)
               END <> CAST(0 AS NUMERIC) OR

               CASE
                 WHEN SUM(T2.Fld802Balance_ + T2.Fld802FinalTurnover_) IS NULL
                   THEN CAST(0 AS NUMERIC)
                 WHEN MAX(T19._Kind) = CAST(1 AS NUMERIC) OR MAX(T19._Kind) = CAST(2 AS NUMERIC) AND
                      SUM(T2.Fld802Balance_ + T2.Fld802FinalTurnover_) < CAST(0 AS NUMERIC)
                   THEN -SUM(T2.Fld802Balance_ + T2.Fld802FinalTurnover_)
                 ELSE CAST(0 AS NUMERIC)
              END <> CAST(0 AS NUMERIC)
      ) T1
 JOIN pg_temp.tt1 T20
   ON T1.AccountRRef = T20._Q_001_F_000RRef;

План выполнения показал, что медленное выполнение запроса связано с последовательным сканированием  таблицы _AccRg798, в результате было выбрано 73 миллиона строк, что привело к значительной нагрузке на СУБД в целом.

Hash Join  (cost=17155333.79..22634368.61 rows=35510 width=34) (actual time=147210.819..161761.636 rows=1 loops=1)
   Hash Cond: (t15._accountdtrref = t20._q_001_f_000rref)
   Buffers: shared hit=7169 read=4340880, local hit=9, temp read=192840 written=342120
   ->  HashAggregate  (cost=17155332.77..22536359.67 rows=7102023 width=241) (actual time=146729.008..161733.386 rows=253819 loops=1)
         Group Key: (...)
         Filter: (...)
         Planned Partitions: 8  Batches: 9  Memory Usage: 1048657kB  Disk Usage: 1545896kB
         Rows Removed by Filter: 5818559
         Buffers: shared hit=7169 read=4340880, local hit=8, temp read=192840 written=342120
         ->  Hash Join  (cost=41.27..8313647.51 rows=71022009 width=265) (actual time=0.444..92136.353 rows=73443504 loops=1)
               Hash Cond: (t15._accountdtrref = t19._idrref)
               Buffers: shared hit=7169 read=4340880, local hit=8
               ->  Append  (cost=16.15..8125560.49 rows=71022009 width=261) (actual time=0.182..74198.459 rows=73443504 loops=1)
                     Buffers: shared hit=7156 read=4340880, local hit=8
                     ->  Hash Join  (cost=16.15..3827028.77 rows=34962897 width=261) (actual time=0.181..33180.060 rows=36667641 loops=1)
                           Hash Cond: (t15._accountdtrref = t16._idrref)
                           Buffers: shared hit=3562 read=2170456, local hit=4
                           ->  Seq Scan on _accrg798 t15  (cost=0.00..3330839.36 rows=29308858 width=70) (actual time=0.021..20058.598 rows=29270508 loops=1)
                                 Filter: (...)
                                 Rows Removed by Filter: 9290204
                                 Buffers: shared hit=3562 read=2170456
                           ->  Hash  (cost=9.40..9.40 rows=540 width=20) (actual time=0.151..0.153 rows=540 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 36kB
                                 Buffers: local hit=4
                                 ->  Seq Scan on tt3 t16  (cost=0.00..9.40 rows=540 width=20) (actual time=0.005..0.069 rows=540 loops=1)
                                       Buffers: local hit=4
                     ->  Hash Join  (cost=16.15..3943421.68 rows=36059112 width=261) (actual time=0.349..35082.309 rows=36775863 loops=1)
                           Hash Cond: (t17._accountctrref = t18._idrref)
                           Buffers: shared hit=3594 read=2170424, local hit=4
                           ->  Seq Scan on _accrg798 t17  (cost=0.00..3330839.36 rows=32365454 width=70) (actual time=0.019..19664.609 rows=32361863 loops=1)
                                 Filter: (...)
                                 Rows Removed by Filter: 6198849
                                 Buffers: shared hit=3594 read=2170424
                           ->  Hash  (cost=9.40..9.40 rows=540 width=20) (actual time=0.295..0.295 rows=540 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 36kB
                                 Buffers: local hit=4
                                 ->  Seq Scan on tt3 t18  (cost=0.00..9.40 rows=540 width=20) (actual time=0.009..0.133 rows=540 loops=1)
                                       Buffers: local hit=4
               ->  Hash  (cost=19.06..19.06 rows=485 width=21) (actual time=0.253..0.254 rows=485 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 34kB
                     Buffers: shared hit=13
                     ->  Seq Scan on _acc26 t19  (cost=0.00..19.06 rows=485 width=21) (actual time=0.014..0.172 rows=485 loops=1)
                           Filter: (_fld786 = '0'::numeric)
                           Buffers: shared hit=13
   ->  Hash  (cost=1.01..1.01 rows=1 width=17) (actual time=0.032..0.033 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         Buffers: local hit=1
         ->  Seq Scan on tt1 t20  (cost=0.00..1.01 rows=1 width=17) (actual time=0.028..0.029 rows=1 loops=1)
               Buffers: local hit=1
 Planning Time: 1.695 ms
 Execution Time: 161972.942 ms

Временная таблица tt1 содержит всего одну строку, она соединяется с основным множеством по полю _accountdtrref, для первого подзапроса из UNION ALL речь идёт о поле T15._AccountDtRRef, а для второго аналогом является T17._AccountCtRRef.

Значит, можно рассмотреть возможность параметризованного индексного сканирования тяжёлой таблицы _accrg798. Также условие соединения T1.AccountRRef = T20._Q_001_F_000RRef можно добавить в сканирование таблицы _acc26.

Посмотрим на план выполнения запроса при использовании join predicate pushdown.

Nested Loop  (cost=468200.83..471868.05 rows=35510 width=34) (actual time=2.258..2.683 rows=1 loops=1)
   Buffers: shared hit=965, local hit=9
   ->  Seq Scan on tt1 t20  (cost=0.00..1.01 rows=1 width=17) (actual time=0.010..0.010 rows=1 loops=1)
         Buffers: local hit=1
   ->  HashAggregate  (cost=468200.83..470533.90 rows=66657 width=241) (actual time=2.245..2.669 rows=1 loops=1)
         Group Key: (...)
         Filter: (...)
         Batches: 1  Memory Usage: 3097kB
         Rows Removed by Filter: 2
         Buffers: shared hit=965, local hit=8
         ->  Nested Loop  (cost=0.96..453202.54 rows=666591 width=265) (actual time=0.071..1.759 rows=956 loops=1)
               Buffers: shared hit=965, local hit=8
               ->  Index Scan using _acc26_s_hpk on _acc26 t19  (cost=0.27..2.49 rows=1 width=21) (actual time=0.025..0.026 rows=1 loops=1)
                     Index Cond: ((_fld786 = '0'::numeric) AND (_idrref = t20._q_001_f_000rref))
                     Buffers: shared hit=3
               ->  Append  (cost=0.69..446534.13 rows=666591 width=261) (actual time=0.042..1.606 rows=956 loops=1)
                     Buffers: shared hit=962, local hit=8
                     ->  Nested Loop  (cost=0.69..222109.27 rows=318575 width=261) (actual time=0.042..0.770 rows=474 loops=1)
                           Buffers: shared hit=480, local hit=4
                           ->  Seq Scan on tt3 t16  (cost=0.00..10.75 rows=1 width=20) (actual time=0.005..0.042 rows=2 loops=1)
                                 Filter: (_idrref = t20._q_001_f_000rref)
                                 Rows Removed by Filter: 538
                                 Buffers: local hit=4
                           ->  Index Scan using _accrg798_3 on _accrg798 t15  (cost=0.69..218912.77 rows=318575 width=70) (actual time=0.028..0.283 rows=237 loops=2)
                                 Index Cond: ((_fld786 = '0'::numeric) AND (_accountdtrref = t20._q_001_f_000rref) AND ...)
                                 Filter: (...)
                                 Buffers: shared hit=480
                     ->  Nested Loop  (cost=0.69..221091.90 rows=348016 width=261) (actual time=0.032..0.754 rows=482 loops=1)
                           Buffers: shared hit=482, local hit=4
                           ->  Seq Scan on tt3 t18  (cost=0.00..10.75 rows=1 width=20) (actual time=0.003..0.039 rows=2 loops=1)
                                 Filter: (_idrref = t20._q_001_f_000rref)
                                 Rows Removed by Filter: 538
                                 Buffers: local hit=4
                           ->  Index Scan using _accrg798_4 on _accrg798 t17  (cost=0.69..216730.95 rows=348016 width=70) (actual time=0.021..0.264 rows=241 loops=2)
                                 Index Cond: ((_fld786 = '0'::numeric) AND (_accountctrref = t20._q_001_f_000rref) AND ...)
                                 Filter: (...)
                                 Buffers: shared hit=482
 Planning Time: 3.940 ms
 Execution Time: 3.019 ms

Ускорение достигнуто благодаря многоуровневому применению join predicate pushdown. Так последовательное сканирование таблицы _acc26 было заменено индексным из-за добавления условия _idrref = t20._q_001_f_000rref.

Также к фильтрации таблицы tt3 было добавлено условие _idrref = t20._q_001_f_000rref, что позволило сократить большинство её строк и заменить последовательное сканирование тяжёлой таблицы _accrf798 на индексное.

Ниже приведены времена планирования и выполнения запроса без и с использованием join predicate pushdown, ускорение в 54678 раз (было 165073.557 мс, стало 3.019 мс):

 

Режим Время планирования запроса (мс) Время выполнения запроса (мс)
#1 #2 #3 #4 #5 #6 #7 #8 #9 #1 #2 #3 #4 #5 #6 #7 #8 #9
Без join predicate pushdown 1.762 1.721 1.973 2.057 1.988 1.923 2.147 1.787 2.010 165 073.557 163 688.742 173 635.117 168 895.255 166 206.733 161 405.044 163 093.333 163 309.410 168 243.616
С join predicate pushdown 3.940 3.940 3.977 3.994 3.927 3.999 3.925 3.954 3.920 3.019 2.934 3.011 3.017 3.053 3.039 3.050 3.047 2.964


Здесь также увеличивается время планирования запроса, поскольку нужно рассмотреть возможность переноса условий соединения уже на двух уровнях: для подзапроса T1 и T2.

 

Выводы

Автоматически сгенерированные запросы в ERP-системах часто содержат сложные конструкции, оптимизация которых вручную затруднена или невозможна из-за архитектуры прикладного решения. В платформе «1С:Предприятие» подобные запросы встречаются очень часто — это практически любые обращения к виртуальным таблицам: срезам последних или первых, остаткам, оборотам и другим агрегированным сущностям.

В СУБД MS SQL Server механизм join predicate pushdown работает по умолчанию, сглаживая неоптимальность таких конструкций. При миграции на любой из форков PostgreSQL отсутствие аналогичного оптимизатора приводит к тому, что именно эти запросы становятся наиболее частыми причинами деградации производительности системы.

Доработка планировщика СУБД в части перемещения условий соединения внутрь подзапросов с агрегацией позволяет решать эту проблему на уровне платформы базы данных. Как показали практические тесты, реализация такого функционала в Postgres Pro Enterprise помогает сохранить скорость работы критически важных бизнес-операций при переходе с MS SQL Server и минимизировать риски для ИТ-ландшафта компании.

Вступайте в нашу телеграмм-группу Инфостарт

база данных ускорение запросов повышение производительности postgres postgresql join predicate pushdown

+2 –

Вы можете заказать платную адаптацию этой статьи под ваши задачи на «Бирже заказов».

  • 0% комиссии — оплата напрямую исполнителю;
  • Исполнители любого масштаба — от отдельных специалистов до команд под проект;
  • Прямой обмен контактами между заказчиком и исполнителем;
  • Безопасная сделка — при необходимости;
  • Рейтинги, кейсы и прозрачная система откликов.

См. также

Оптимизация запросов с оператором «В» для составных типов в 1С и СУБД Postgres

HighLoad оптимизация Программист 1С 8.3 1С:ERP Управление предприятием 2 Бесплатно (free)

Использование оператора «В» для полей или данных составного типа (например, Регистратор) может приводить к неочевидным проблемам.

10.11.2025    8972    ivanov660    48    

53
Проблемы производительности. Оператор различные в динамических списках

HighLoad оптимизация Программист 1С:Предприятие 8 1C:ERP Бесплатно (free)

Приведем примеры использования различных в динамических списках и посмотрим, почему это плохо.

18.02.2025    10568    ivanov660    39    

62
Проблемы производительности. Индексация с дополнительным упорядочиванием

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

Не всегда индексирование с дополнительным упорядочиванием полезно.

04.02.2025    9138    ivanov660    21    

57
Длительные вызовы - CALL, SCALL

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

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

24.06.2024    13254    ivanov660    13    

64
Разбираем недавний кейс. Не открываются файлы, которые были добавлены в базу

Администрирование СУБД 1С:Предприятие 8 1C:Бухгалтерия Россия Бесплатно (free)

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

23.05.2024    20310    human_new    22    

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

HighLoad оптимизация Программист 1С:Предприятие 8 1C:Бухгалтерия Бесплатно (free)

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

13.03.2024    9771    spyke    29    

54
Поинтегрируем: сервисы интеграции – новый стандарт или просто коннектор?

Перенос данных 1C Администрирование СУБД Механизмы платформы 1С Системный администратор Программист Стажер 1С:Предприятие 8 Бесплатно (free)

В платформе 8.3.17 появился замечательный механизм «Сервисы интеграции». Многие считают, что это просто коннектор 1С:Шины. Так ли это?

11.03.2024    37230    dsdred    78    

113
Анализ SQL сервера

HighLoad оптимизация Инструменты администратора БД Системный администратор Программист 1С 8.3 Абонемент ($m)

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

10 стартмани

15.02.2024    22961    392    ZAOSTG    112    

129
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. GeraltSnow 213 16.06.26 16:43 Сейчас в теме
В какой версии PostgresPro реализован такой механизм оптимизации? Он активен по умолчанию или нужно что-то в конфиге настроить?
+ – Ответить
Для отправки сообщения требуется регистрация/авторизация