我该如何优化这个更新每一行的 PostgreSQL 查询？

Question

我写了一个更新整个表的查询。如何改进这个查询以减少时间：

update page_densities set density = round(density - 0.001, 2)

查询成功返回：628391行受影响，执行时间为1754179毫秒（29分钟）。

编辑：通过设置工作内存...

set work_mem = '500MB';
update page_densities set density = round(density - 0.001, 2)

查询成功返回：628391行受影响，执行时间为731711毫秒（12分钟）。

- Abdul Baig

---

有任何涉及“密度”的限制可以暂时关闭吗？您尝试分几步执行更新了吗？ - Trinimon

@Trinimon：使用多个更新语句会比使用单个更新语句慢很多。 - user330315

@G.B：在29分钟内更新628391条记录太慢了。我猜想你的硬盘速度太慢了-也许你的数据库位于网络驱动器上？或者是USB驱动器？我有一个运行在虚拟机中的相对较慢的服务器，它需要大约8秒钟才能更新500万行。 - user330315

我们能得到一个输出为"\d页密度"吗？ - Gary - Stand with Ukraine

不确定是否适用于PostgreSQL，但在Oracle中，如果在UPDATE语句之前发出LOCK TABLE page_densities IN EXCLUSIVE MODE命令，这个查询将运行得更快。您还可以考虑以下几点：a）禁用表上的所有触发器（如果有的话）；b）在更新后删除所有索引并重新构建它们。 - Erich Kitzmueller

Answer 1

假设密度不是一个索引，您可以通过不同的填充因子来提高性能。请参阅此问题/答案或PostgreSQL文档以获取更多信息：

http://www.postgresql.org/docs/9.4/static/sql-createtable.html

PostgreSQL数据库中针对300万行的慢速简单更新查询

虽然您无法修改表的填充因子，但可以创建一个具有不同填充因子的新表并将数据复制过去。以下是一些示例代码。

--create a new table with a different fill factor
CREATE TABLE page_densities_new
(
 ...some fields here
)
WITH (
  FILLFACTOR=70
);

--copy all of the records into the new table
insert into page_densities_new select * from page_densities;

--rename the original/old table
ALTER TABLE page_densities RENAME TO page_densities_old;

--rename the new table
ALTER TABLE page_densities_new RENAME TO page_densities;

在此之后，您将拥有一个与原始表格名称和数据相同但填充因子不同的表格。我将其设置为70，但它可以是10到100的任何值。（100是默认值）

- Tom Gerken