为什么 postgresql 更新查询有时这么慢，即使有索引
我有一个简单的更新查询（foo 列类型为 BOOLEAN （默认为 false））：
update tablename set foo = true where id = 234;
其中“id”设置为（主）键，如果我运行“解释分析”我得到：
Index Cond: (id = 234)
Total runtime: 0.358 ms
但是，我仍然有很多无法解释的查询在慢日志（pgfouine）中，花费了 200 多秒（？！）：
Times executed: 99, Av. duration (s): 70
任何人都可以解释一下，原因是什么？ （表中 1.5 mio 行，postgresql 8.4）
i have a simple update query (foo column type is BOOLEAN (default false)):
update tablename set foo = true where id = 234;
which "id" is set to (primary) key, and if i run "explain analyze" i got:
Index Cond: (id = 234)
Total runtime: 0.358 ms
but still, i have plenty of unexplained queries at slow log (pgfouine), which took more than 200s (?!):
Times executed: 99, Av. duration (s): 70
can anyone please explain, whats the reason for that? (1.5 mio rows in table, postgresql 8.4)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我的第一个猜测是您有一些其他查询锁定整个表或正在更新的行。您的简单更新是被迫等待其他操作完成。
My first guess would be that you have some other query locking the whole table or the rows that are being updated. Your simple update is the forced to wait for that other operation to complete.
检查更新的列上是否没有任何索引或约束。如果是这样，那么数据库可能正在执行索引重新计算或约束评估。这些附加任务不包含在 EXPLAIN ANALYZE 结果中。
另一种可能性是由于 I/O 操作而导致速度缓慢。查看有关 Postgres 中 UPDATE 性能的线程。
Check, that you don't have any index or constraint on the updated column. If so, then the database might be doing index recalculation or constraint evaluation. These additional tasks are not included in the EXPLAIN ANALYZE result.
Other possibility is that it is slow due to I/O operations. Check this thread about UPDATE performance in Postgres.