sql - Postgres 8.4.4(Win7 x64 上的 x32)在小 table 上更新非常慢

Question

我有一个非常简单的更新语句:

UPDATE W SET state='thing'
WHERE state NOT IN ('this','that') AND losttime < CURRENT_TIMESTAMP;

表 W 只有 90 行，但每行的丢失时间和状态列大约每 10 秒更新一次。有关于状态和损失时间的索引(以及主索引)。

我注意到大型数据库(即其他表有很多条目，而不是表 W)在一段时间内，查询变得越来越慢。运行了48小时，我在PqAdminIII的查询窗口中运行计时，执行了17分钟!

我在另一个显示相同问题的表上有一个类似的查询:

UPDATE H SET release='1' 
WHERE a NOT IN (SELECT id from A WHERE state!='done') AND release!='1';

H 没有任何索引，但我已尝试在 H(release) 上添加和删除索引，但行为没有任何变化。在数据库运行 48 小时并且表 H 有约 100k 行之后，此查询耗时 27 分钟。 Postgres 服务器将有一个线程在查询期间完全固定(100% CPU 使用率)，因此看起来没有网络、磁盘等的任何争用。

总的来说，我看到的行为是我的数据库按预期运行了大约 5 分钟，然后随着与基本维护相关的 UPDATE 命令开始运行的时间越来越长，一切都逐渐停止。到第二天，做一个简单的维护周期(少量更新)需要一个小时，一开始运行时间约为 100 毫秒。我似乎很清楚，性能下降与数据库中的信息量呈超线性关系——可能是 N^2 或类似的数量。

Autovacuum 使用默认设置。我(再次)通读了手册，没有看到任何让我印象深刻的内容。

我在这里挠头。我没有在 9.0.1 和 9.0.2 发行说明中看到任何似乎相关的错误修复。任何人都可以帮助我了解正在发生的事情吗？谢谢，M

-x-x-x-x-

好吧，我这里可能有两个问题。

第一个更新现在运行得很快。不确定发生了什么，所以我将假设我需要更频繁地运行 VACUUM/ANALYZE 或某些组合——比如每分钟左右。我真的很想知道为什么 autovacuum 不为我做这件事。

第二次更新继续缓慢运行。查询计划表明索引没有得到有效使用，并且出现了 80k*30k 交叉，这可能是我观察到的超线性运行时间的原因。 (大家认同这个方案的解读吗？)

我可以将 UPDATE 转换为 SELECT:

SELECT * from H
where a not in (SELECT id from A where state='done') AND release!='1';

具有相似的运行时间(27 分钟)。

如果我不信任 postgres 优化器并执行此操作:

WITH r as (select id from A where state='done')
SELECT a from H 
JOIN on H.a=r.id 
WHERE H.released='0';

然后查询将在 ~500 毫秒内运行。

我如何将这些知识转化为以可接受的速度运行的更新？我的尝试:

UPDATE H SET release='1'
FROM A
where A.state!='done' AND release!='1' AND A.id=H.a;

运行大约 140 秒，速度更快，但仍然非常非常慢。

我可以从这里去哪里？

-x-x-x-x-

VACUUM ANALYZE 已添加为“例行维护”的一部分，其中应用程序将大约每分钟运行一次，独立于任何正在运行的 autovacuum。

此外，重写第二个查询以消除已知的缓慢 NOT IN 子句，将其替换为“Left Anti-Semi Join”(嗯？)

UPDATE H SET release='1' 
WHERE release='0' AND NOT EXISTS (SELECT * FROM A WHERE id=H.a AND state!='done');

Answer 1

PostgreSQL 实现了 MVCC。

这意味着每次您进行更新时，都会创建一个新的行副本，并将旧的行标记为已删除(但实际上并未删除)。

这会减慢查询速度。

您应该及时运行 VACUUM。

PostgreSQL 8.4.4 运行 autovacuum 守护进程来执行此操作，但它可能会在您的安装上出现一些问题。

当您手动运行 VACUUM 时情况是否有所改善？