performance - Postgres函数优化

Question

我正在运行以下功能。用一个小得多的表测试它按预期工作(18 行 - ~400 毫秒)。但是，当指向我的真实数据(315000 行)时，它运行了 48 小时并且仍在运行。这比我在线性外推法下的预期要长得多。

1. 有更好的方法吗？
2. 有没有办法测试它在运行时是否正在做它应该做的事情？

有什么办法可以优化下面的功能吗？

DO 
$do$ 
DECLARE r public.tablex%rowtype; 
BEGIN 
FOR r IN SELECT id FROM public.tablex 
LOOP 
IF (select cast((select trunc(random() * 6 + 1)) as integer) = 5) THEN 
UPDATE public.tablex SET test='variable1' WHERE id = r.id; 
ELSIF (select cast((select trunc(random() * 6 + 1)) as integer) = 6) THEN 
UPDATE public.tablex SET test='variable2' WHERE id = r.id; 
END IF; 
END LOOP; 
RETURN; 
END 
$do$;

Answer 1

@kordirko明确表示您的 DO 语句不必要地昂贵。但还有更多。

概率

更新中的概率分布不均匀:

• 1/6 或所有行更新为 'variable1'。
• 但只有 5/36 ((1/6) * (5/6)) 更新为 'variable2'。

从您的其余代码来看，我假设这是一个意外错误，并且您希望每个代码的 1/6 份额。

清理

你可以简化为:

UPDATE tablex
SET    test = CASE trunc(random() * 6)
                 WHEN  float '4' THEN 'variable1'
                 WHEN  float '5' THEN 'variable2'
                 ELSE  test
              END;

• 结果加1没有意义。而是比较 4 和 5 而不是 5 和 6 (或 3 和1 - 这里没有区别)。

• 与 double precision (= float) 常量比较而不是转换 double precision 结果要便宜一些将每一行的表达式 trunc(random() * 6) 转换为 integer，就像它会在您的原始代码中发生一样。

更好，但仍然非常低效。

高级

UPDATE tablex
SET    test = CASE WHEN random() >= float '0.5' THEN 'variable1'
                                                ELSE 'variable2' END
WHERE  random() >= float '0.6666667';

100 % 等效(除非您有触发器 ON UPDATE 或一些奇特的设置)但快得多，因为只有实际接收更新的行会被触及。三分之二的行根本没有被触及。

• 请注意，这两个 random() 调用是完全独立的。

• 如果您想尽可能精确地了解三分之二的概率，请使用 2/float '3.0' 而不是 float '0.6666667'。但这确实是学术上的差异。

• 您可能希望在运行此操作之前(在同一事务中)LOCK tablex IN ROW EXCLUSIVE MODE 以排除并发写入的竞争条件。 Details in the manual.

备选

如果 test 列已经可以包含目标值“variable1”或“variable2”之一(在很多情况下)，这会更便宜，但:

UPDATE tablex t
SET    test = upd.val
FROM  (
   SELECT id, CASE WHEN random() >= float '0.5' THEN 'variable1'
                                                ELSE 'variable2' END AS val
   FROM   tablex
   WHERE  random() >= float '0.6666667'
   -- ORDER  BY id   -- the last two lines only to defend against ...
   -- FOR    UPDATE  -- ... concurrent writes and possible deadlocks
   ) upd
WHERE  t.id = upd.id
AND    t.test IS DISTINCT FROM upd.val;

避免更多的空更新。
如果 test 被定义为 NOT NULL 你可以简化为:

 AND    t.test <> upd.val;

比较这个相关答案的最后一章:

• How do I (or can I) SELECT DISTINCT on multiple columns?

• 只有最后一个变体需要 id 上的索引才能更快。应该被主键约束覆盖。