如何从sql查询加速postgresql更新？目前需要几天才能完成运行

yeotifhr 于 2021-08-09 发布在 Java

关注(0)|答案(3)|浏览(434)

如何从下面的sql查询加速postgresql更新？目前需要几天才能完成运行。

UPDATE import_parts ip
SET part_part_id = pp.id
FROM parts.part_parts pp
WHERE pp.upc = ip.upc
AND (ip.status is null or ip.status != '6');

为什么一开始就要花上几天的时间？
大多数情况下，我手动终止查询，因为运行时间太长，超过24小时。上一次它成功地完成运行，花了将近38个小时。 import_parts 表有 971971 排 parts.part_parts 表有 2196357 排 parts.part_parts 表上有索引 upc 以及 id 是表的主键。
我已经试过跑步了 VACUUM ANALYZE 在 import_parts 表和 parts.part_parts 表，但查询仍然需要很长时间才能运行，所以我在30分钟后手动终止了它。我希望能在30分钟内完成查询。
下面是运行完查询后运行查询时的解释结果 VACUUM ANALYZE 在 import_parts 表和 parts.part_parts 表格：

更新1：
我也试过设置 enable_nestloop 关闭： SET enable_nestloop TO off 但是查询仍然需要很长时间才能运行，所以我手动终止了它。这是实验的结果 EXPLAIN 禁用“启用循环”时：

更新2：
以下是使用abelisto在回答这篇文章时提出的问题时解释的结果：

但是，当我实际运行查询时，遇到了以下错误： ERROR: more than one row returned by a subquery used as an expression 我还在想办法纠正这个错误。

sql postgresql sqlperformance postgresql-performance

来源：https://stackoverflow.com/questions/62493528/how-can-i-speed-up-this-postgresql-update-from-sql-query-it-currently-takes-day

3条答案

按热度按时间

dohp0rv51#

首先，试着像这样重写你的查询

UPDATE import_parts ip
SET part_part_id = (
  SELECT pp.id
  FROM parts.part_parts pp
  WHERE pp.upc = ip.upc)
WHERE status is null or status != '6';

很明显，这引起了一些类似于

ERROR:  more than one row returned by a subquery used as an expression

使用附加条件修复它（子查询应该为目标表中的每一行正好返回一行或零行）

赞(0）回复(0）举报 2021-08-09

xjreopfe2#

从你所说的看来 upc 不是唯一的 parts_parts . 尝试运行以下命令：

select upc, count(*)
from parts.parts_parts pp
group by upc
having count(*) > 1;

这些重复可能会导致性能问题。您可以通过任意选择一个值来绕过此问题，例如：

UPDATE import_parts ip
  SET part_part_id = pp.id
  FROM (SELECT pp.upc, MIN(pp.id) as id
        FROM parts.part_parts pp
        GROUP BY pp.upc
       ) pp
  WHERE pp.upc = ip.upc AND (ip.status is null or ip.status <> '6');

赞(0）回复(0）举报 2021-08-09

qv7cva1a3#

在import\u parts中创建一个索引，其列为：upc，status。
我建议你分成两句话：
我不知道你的状态，但我想你的状态是：空，1，2，3，4，5，6，7

UPDATE import_parts ip
SET part_part_id = pp.id
FROM parts.part_parts pp
WHERE pp.upc = ip.upc
AND ip.status is null
;

UPDATE import_parts ip
SET part_part_id = pp.id
FROM parts.part_parts pp
WHERE pp.upc = ip.upc
AND ip.status IN(1, 2, 3, 4, 5, 7)
;

当然，您需要为您的值更改1、2、3、4、5、7（与6不同）
我也喜欢@gordon linoff的答案，但这取决于upc有多少行

赞(0）回复(0）举报 2021-08-09

我来回答

如何从sql查询加速postgresql更新？目前需要几天才能完成运行

3条答案

相关问题

热门标签

最新问答