PostgreSQL带索引的更新速度缓慢

Question

3

简单地更新包含大约5百万行的表中一个列的重置方法如下：

UPDATE t_Daily 
SET Price= NULL

这个表中的任何索引都与价格无关。

没有使用索引运行需要45秒。

使用一个或多个索引至少需要20分钟（我不断地不得不停止它）。

我完全理解维护索引会影响插入和更新语句的性能，但是这个更新操作对表索引没有任何更改，为什么会对性能有如此可怕的影响呢？

非常感谢任何想法。

- Giles

您能否提供一个演示此问题的 [MCVE] 呢？ - sticky bit

2

抱歉，我不明白，您是想让我提供500万行数据吗？ - Giles

2个回答

2

这是正常且预期的行为：更新索引可能比更新表本身要耗费十倍左右。表没有排序！

如果price没有建立索引，您可以使用避免更新索引的HOT（Heap Only Tuple）更新。为了利用它，表必须定义一个小于100的fillfactor，使得更新后的行能够在与原始行相同的块中找到空间。

注：HOT更新只能应用于堆表，即没有索引或所有索引都是被覆盖的。

- Laurenz Albe

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Giles · Accepted Answer

发现了一些进一步的信息（感谢Laurenz-Albe提供的热门提示）。

由于MVCC，在Postgres中进行更新操作包括查找要更新的行，并将新版本的行插入回数据库。这样做的主要缺点是需要将该行重新添加到每个索引中。

因此，即使只更新了一个不在索引中的列，它也会重新编写索引。