在大型PostgreSQL表上更新查询太慢

Question

我正在尝试提高简单的更新查询速度，但是对于一行数据而言，耗时在0.7-1.5秒之间，速度太慢了。

UPDATE users SET firstname = 'test' WHERE userid=2120;

以下是解释：

Update on users  (cost=0.43..8.45 rows=1 width=331) (actual time=0.068..0.068 rows=0 loops=1)
->  Index Scan using users_pkey on users  (cost=0.43..8.45 rows=1 width=331) (actual time=0.039..0.040 rows=1 loops=1)
    Index Cond: (userid = 2120)
Trigger updatemv: time=727.372 calls=1
Total runtime: 727.487 ms

数据库总大小为20GB，大约有60个表格。我在表格“用户”（users）上遇到了问题，它有136万行。表格“用户”具有36个列（4个bigint，5个整数，10个变长字符（从32到255），其他是布尔字段），其中许多行的一半为空。此外，“用户”表上有6个索引。该数据库托管在Amazon RDS db.m4.2xlarge上，配备8个vCPU、32 GB RAM和100 GB SSD。PostgresSQL版本为9.3。

我尝试使用VACUUM ANALYZE来优化表格，但仍然过慢。

我了解了升级RAM/CPU、在postgresql.conf中调整数据库、创建大型表格的分离表空间等方面。但我不确定如何最好地处理有百万行的大型表格。

根据当前趋势，我的表格将在未来12个月内增长到2000万行，因此我需要持久的解决方案。

如果有任何建议可以提高对大型表格上更新查询的速度，将会很有帮助。

- Zoran Đukić

---

我感觉少了点什么……1.36百万行的数据需要20GB，那么每一行就是15KB。而且这些数据仅分布在36列之间？这些列也太大了吧，尤其是如果其中大多数是‘NULL’的话。 - Gordon Linoff

触发器更新 updatemv：时间=727.372 调用=1。您在此表上有触发器，或者其他引用它的外键表上有触发器吗？ - joop

@GordonLinoff 数据库大约有60个表，总大小为20GB。我遇到了最大的表“users”的问题，该表有1.36百万行。我认为没有大列。它包括4个bigint、3个整数、15个字符变量（从32到255）、5个时间戳和布尔字段。 - Zoran Đukić

检查显而易见的问题：是否存在被其他修改事务锁定的锁？ - Laurenz Albe

您似乎有一个物化视图，当表更改时会进行更新。视图的更新似乎是由名为updatemv的触发器触发的。 - Peter Henell

非常感谢大家，我有一个物化视图刷新触发器，当我将其删除后，更新只需要0.123毫秒，而不是727.487毫秒，快了6000倍 :) 我会尝试以不同的方式组织物化视图。 - Zoran Đukić

Answer 1

感谢@joop的帮助，我解决了我的问题。原来是因为我有一个触发器在刷新物化视图，当我把它移除后，更新查询只需要0.123毫秒，而不是727.487毫秒，快了6000倍。

我重新组织了物化视图。

- Zoran Đukić

Answer 2

调整postgresql.conf中的参数可以产生巨大的影响，而且是免费的，所以我会从那里开始。默认值太低了。

- Curt Evans