使用JOIN更新FROM语句（大表性能）Postgresql？

Question

我正在努力使以下查询在合理的性能下执行：

UPDATE order_item_imprint SET item_new_id = oi.item_new_id
      FROM order_item oi
      INNER JOIN order_item_imprint oii ON oi.item_number = oii.item_id          

目前，它在8天内无法完成，所以我们将其终止。查询解释如下：

Merge Join  (cost=59038021.60..33137238641.84 rows=1432184234121 width=1392)
Merge Cond: ((oi.item_number)::text = (oii.item_id)::text)
  ->  Nested Loop  (cost=0.00..10995925524.15 rows=309949417305 width=1398)
        ->  Index Scan using unique_order_item_item_number on order_item oi  (cost=0.00..608773.05 rows=258995 width=14)
        ->  Seq Scan on order_item_imprint  (cost=0.00..30486.39 rows=1196739 width=1384)
  ->  Materialize  (cost=184026.24..198985.48 rows=1196739 width=6)
        ->  Sort  (cost=184026.24..187018.09 rows=1196739 width=6)
              Sort Key: oii.item_id
              ->  Seq Scan on order_item_imprint oii  (cost=0.00..30486.39 rows=1196739 width=6)

我在两个表上建立了索引，并确保比较字段具有相同的类型和大小。我现在试图更改postgresql服务器配置，以希望有所帮助，但我不确定它是否有效。

order_item_imprint表的大小约为110万，并且磁盘占用空间为145MB，而order_item表的大小约为其三分之一。

主要目标是我需要能够在几小时的维护窗口期间运行此查询以及其他几个查询。

在执行计划之前已运行自动清理和分析。

- Martin Dale Lyness

---

你在那个表上有哪些索引？在哪些列上？连接的列是什么数据类型？这是什么类型的连接（1:1，1:N）？ - Janick Bernet

它们是两列上的B树索引，并且正如您可以从最后一个连接中看到的1.xxx万亿行的1:N连接一样。 - Martin Dale Lyness

Answer 1

我找到了一种替代的写法，让pgsql优化器能够更有效地构建查询。实际上，你所做的是删除了order_item_inprint上的无约束自连接。如果你看第一行，你会看到以下行估计：rows=1432184234121。这是它试图执行的14亿次更新。当你在join中给order_item_inprint取别名时，它被视为更新目标的一个单独的表。

- Richard Huxton

Answer 2

我发现了一种替代的查询方式，可以让pgsql优化器更高效地构建查询：

UPDATE order_item_imprint SET item_new_id = oi.item_new_id 
FROM order_item oi where oi.item_number = order_item_imprint.item_id

显然，该联接是多余的，可以使用以下执行计划：

Hash Join  (cost=1.38..5.73 rows=48 width=1407)
  Hash Cond: ((order_item_imprint.item_id)::text = (oi.item_number)::text)
  ->  Seq Scan on order_item_imprint  (cost=0.00..3.63 rows=63 width=1399)
  ->  Hash  (cost=1.17..1.17 rows=17 width=23)
        ->  Seq Scan on order_item oi  (cost=0.00..1.17 rows=17 width=23)

- Martin Dale Lyness