为什么有时候即使使用索引，postgresql的更新查询也很慢？

Question

我有一个简单的更新查询（foo列类型为BOOLEAN（默认为false））：

update tablename set foo = true where id = 234;

哪个“id”被设置为（主）键，如果我运行“explain analyze”，则会得到：

Index Cond: (id = 234)
Total runtime: 0.358 ms

但是，在我的慢查询日志（pgfouine）中，我仍然有很多未解决的问题，其中一些查询需要超过200秒才能完成（？！）：

Times executed: 99, Av. duration (s): 70

请问有人能解释一下，这是什么原因呢？（表中有150万行，使用的是PostgreSQL 8.4）

- matija

---

如果id是主键（因此最多只有一行被更新），这应该是几乎瞬间完成的。要么您遇到了某些问题（运行vacuum analyze verbose），要么其他查询正在锁定整个表...您不能在postgresql日志中识别那些200s查询吗？您是否在配置中设置了“log_duration”参数？ - leonbloy

是的，我将 "log_min_duration_statement" 设置为1000，所以我在慢日志文件中得到了这些查询。从日志文件中确定了那些200秒的查询，仅仅是一些简单的更新操作。 如果 PostgreSQL 锁定整个表，是在什么情况下呢？这不是由行级锁定管理吗？ 有没有办法识别是哪个进程进行了锁定？这个慢查询是否可能是由新创建的行引起的？只是猜测，我没有其他想法。 - matija

在PostgreSQL中，更新操作根本不会锁定读取者，因此锁定可能根本不是问题所在。您是否调整了像shared_buffers这样的参数？那些意外变慢的查询是否在特定时间发生？它们一起发生还是只有一个偶尔发生？ - araqnid

当程序运行缓慢时，尝试使用“explain analyze”进行分析。 - rogerdpack

Answer 1

我的第一个猜测是你有其他查询锁定了整个表或正在更新的行。因此，你的简单更新操作被迫等待其他操作完成。

- Eric Petroelje

Answer 2

请检查您更新的列上是否有任何索引或约束。如果是这样，那么数据库可能正在进行索引重新计算或约束评估。这些额外的任务不包含在EXPLAIN ANALYZE结果中。

另一个可能性是由于I/O操作而变慢。请参阅Postgres中有关UPDATE性能的主题以了解更多信息。

- Pavel Horal

---

关于更新缓慢，这个链接也许会有帮助：https://dev59.com/TXA75IYBdhLWcg3wUHSQ#jZ6cEYcBWogLw_1bzgAH - Matthew Cornell