Postgres函数优化

Question

我正在运行下面的函数。用一个小得多的表来测试它，就像预期的那样(18行-~400 as )。然而，当指向我的实际数据(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)常量相比要便宜一些，而不是将表达式trunc(random() * 6)的double precision结果转换为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来排除并发写入的争用条件。手册中的细节。

替代方案

如果列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;

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

• 如何(或可以)在多列上选择DISTINCT？
• 只有最后一个变体需要id上的索引才能快速。应该由主键约束覆盖。

Answer 2

尝试一个简单的更新：

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

我想它至少快50~200倍。