Блог

Postgres использует алгоритм соединения с вложенным циклом, когда я использую неравномерное условие соединения в своем запросе на обновление. Я понимаю, что соединение с вложенным циклом может быть очень дорогостоящим, поскольку правое отношение сканируется один раз для каждой строки, найденной в левом отношении, согласно [https://www.postgresql.org/docs/8.3/planner-optimizer.html ]

Запрос на обновление и план выполнения приведены ниже.

Запрос

 explain analyze
UPDATE target_tbl tgt
set descr     = stage.descr,
    prod_name = stage.prod_name,
    item_name = stage.item_name,
    url       = stage.url,
    col1_name = stage.col1_name,
    col2_name = stage.col2_name,
    col3_name = stage.col3_name,
    col4_name = stage.col4_name,
    col5_name = stage.col5_name,
    col6_name = stage.col6_name,
    col7_name = stage.col7_name,
    col8_name = stage.col8_name,
    flag      = stage.flag
from tbl1 stage
where tgt.col1 = stage.col1
  and tgt.col2 = stage.col2
  and coalesce(tgt.col3, 'col3'::text) = coalesce(stage.col3, 'col3'::text)
  and coalesce(tgt.col4, 'col4'::text) = coalesce(stage.col4, 'col4'::text)
  and stage.row_number::int >= 1::int
  and stage.row_number::int < 50001::int;
  

План выполнения

 Update on target_tbl tgt  (cost=0.56..3557.91 rows=1 width=813) (actual time=346153.460..346153.460 rows=0 loops=1)
  ->  Nested Loop  (cost=0.56..3557.91 rows=1 width=813) (actual time=4.326..163876.029 rows=50000 loops=1)
        ->  Seq Scan on tbl1 stage  (cost=0.00..2680.96 rows=102 width=759) (actual time=3.060..2588.745 rows=50000 loops=1)
              Filter: (((row_number)::integer >= 1) AND ((row_number)::integer < 50001))
        ->  Index Scan using tbl_idx on target_tbl tgt  (cost=0.56..8.59 rows=1 width=134) (actual time=3.152..3.212 rows=1 loops=50000)
              Index Cond: ((col1 = stage.col1) AND (col2 = stage.col2) AND (COALESCE(col3, 'col3'::text) = COALESCE(stage.col3, 'col3'::text)) AND (COALESCE(col4, 'col4'::text) = COALESCE(stage.col4, 'col4'::text)))
Planning time: 17.700 ms
Execution time: 346157.168 ms
  

1. Есть ли какой-либо способ избежать соединения с вложенным циклом во время выполнения вышеупомянутого запроса?

2. Или есть способ, который может помочь мне снизить стоимость сканирования вложенного цикла, в настоящее время для обновления всего 50000 записей требуется 6-7 минут?

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

Исправьте эту проблему, и все станет лучше:

 ANALYZE tbl1;
  

Если этого недостаточно, соберите более подробную статистику:

 ALTER TABLE tbl1 ALTER row_number SET STATISTICS 1000;
ANALYZE tbl1;
  

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

 CREATE INDEX ON tbl1 ((row_number::integer));
ANALYZE tbl1;
  

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

Но «правильное отношение» здесь — это сканирование индекса, а не сканирование всей таблицы.

Вы можете заставить его прекратить использование индекса, изменив начальный столбец условия соединения на что-то вроде where tgt.col1 0 = stage.col1 ... . После этого он, вероятно, изменится на хэш-соединение или объединение слиянием, но вам придется попробовать и посмотреть, работает ли это. Кроме того, новый план на самом деле может быть не быстрее. (И исправление проблемы оценки было бы предпочтительнее, если это сработает)

Или есть способ, который может помочь мне снизить стоимость сканирования вложенного цикла, в настоящее время для обновления всего 50000 записей требуется 6-7 минут?

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