PostgreSQL:更新3m条记录的有效方法是什么？

Question

在我的工作中(几年前)，作为某种粗略的猜测优化，我将body列从我们的comments表移动到了一个辅助comment_extensions表。每次我们想要显示注释时都进行连接似乎是不明智的，所以我将尝试将该列移回我们的comments表并运行一些基准测试。

我的问题是这个更新是爬行的。我让它运行了一个小时，然后关闭了它，担心它会花上一整晚的时间。

UPDATE comments SET body = comment_extensions.body 
                FROM comment_extensions 
                WHERE comments.id = comment_extensions.comment_id;

它是一个PostgreSQL 8.1数据库，并且对comment_extensions.comment_id进行了索引。

有什么建议可以让它运行得更快吗？

Answer 1

这个怎么样？

http://www.postgresql.org/docs/8.1/interactive/sql-createtableas.html

CREATE TABLE joined_comments
    AS SELECT c.id, c.author, c.blablabla, ce.body
    FROM comments c LEFT JOIN comment_extensions ce
    ON c.id = ce.comment_id;

这将创建一个新的joined_comments表。这可能已经足够了(您仍然需要重新创建索引等等)，但我记得Postgres 8.1在创建串行列的方式上有一个bug (对不起，找不到链接)。

所以我建议您在拥有这个新连接表之后，从joined_comments表复制到一个二进制文件，从一开始就创建一个新的comments表，声明id是一个序列，然后从那个二进制文件复制到新的comments表。然后，重新创建索引。

Answer 2

那么，对于一个学术问题，为什么这是不明智的？需要知道评论信息的查找占多大比例？

我的建议是:小批量更新(一次10,000行？)。这可能需要一整晚的时间。根据系统的性质，您可能还必须实现转换逻辑，以防止系统在此迁移过程中更新扩展表或从扩展表中提取数据。

大型数据库会受到这样的影响;)

Answer 3

在执行此操作时，禁用日志可能会给您带来一些好处。如果是在非生产表中进行测试，则可能不需要日志文件为您提供的保护。

如果comments.body上有索引或键，那么在更新之前删除它，然后在更新之后重新创建它。

comments.body字段是固定宽度字符(N)还是可变字符？在过去，Varchar比char()慢，我怀疑它现在仍然慢。所以使用char而不是varchar。

如果执行select操作将数据合并到数据文件(比如引用的csv)，并编写脚本将其转换为插入，那么清空comments表并使用可能比查询更快的插入加载它，尽管comments.id上的索引有助于提高速度。

无论如何，3e6记录都需要一些时间。