我有一个非常简单的更新语句:
UPDATE W SET state='thing'
WHERE state NOT IN ('this','that') AND losttime < CURRENT_TIMESTAMP;
表 W 只有 90 行,每行的丢失时间和状态列大约每 10s 秒更新一次。有状态和损失时间的索引(以及主索引)。
我注意到大型数据库(即其他表有很多条目,而不是表 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 有约 10 万行之后,此查询需要 27 分钟。 Postgres 服务器在查询期间将有一个完全固定的线程(100% CPU 利用率),因此看起来不会出现任何网络、磁盘等争用。
因此,从广义上讲,我看到的行为是我的数据库按预期运行了大约 5 分钟,然后随着与基本维护相关的 UPDATE 命令开始运行越来越长的时间,一切逐渐停止。到第二天,需要一个小时来完成一个简单的维护周期(少量更新),该周期一开始运行约 100 毫秒。在我看来,很明显,性能下降与数据库中的信息量呈超线性关系——可能是 N^2 或类似的信息量。
Autovacuum 使用默认值。我(再次)通读了手册,但没有看到任何让我惊讶的内容。
我在这里摸不着头脑。我在 9.0.1 和 9.0.2 发行说明中没有看到任何似乎相关的错误修复。谁能帮助我理解发生了什么?谢谢,米
-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 子句,将其替换为“左反半连接”(嗯?)
UPDATE H SET release='1'
WHERE release='0' AND NOT EXISTS (SELECT * FROM A WHERE id=H.a AND state!='done');