Postgres优化更新

Question

我必须做一些复杂的数据导入。我需要做一些更新，目前在一个查询中更新超过300万行。此查询每个应用大约30-45秒(其中一些甚至是4-5分钟)。我的问题是，我是否可以加快速度。我可以在哪里读到关于它的信息，什么类型的索引，以及我可以设置哪些列来改进这些更新。我不需要确切的答案，所以我不显示表格。我正在寻找一些东西来了解它。

Answer 1

两件事：

1)发布UPDATE查询的EXPLAIN ANALYZE。

2)如果您的UPDATE不需要是原子的，那么您可能需要考虑拆分受UPDATE影响的行数。要最大限度地减少由于超过Free Space Map而导致的“丢失行数”，请考虑使用以下方法：

1. BEGIN
2. UPDATE ... LIMIT N;或一些限制行数的谓词(例如，WHERE username ilike 'a%';).
3. COMMIT
4. VACUUM table_being_updated
5. Repeat步骤1-4，直到所有行都为updated.
6. ANALYZE table_being_updated

我怀疑您正在更新表中的每一行，并且不需要在单个事务结束时使用新值使所有行都可见，因此上述将UPDATE拆分为较小事务的方法将是一种很好的方法。

是的，在UPDATE的谓词中指定的相关列上的INDEX将提供极大的帮助。同样，如果您需要进一步的帮助，请发布EXPLAIN ANALYZE。

Answer 2

如果您所说的多个更新是指对每个更新的行执行一个更新命令，那么问题是所有目标表的索引都将被更新，并且所有的约束都将在每个更新的行上被检查。如果是这种情况，那么尝试使用一次更新来更新所有行：

update t
set a = t2.b 
from t2
where t.id = t2.id

如果导入的数据在文本文件中，则首先将其插入到临时表中，然后从那里进行更新。查看我的答案here