优化plpgsql函数

Question

PostgreSQL版本为9.0。

我必须优化plpgsql函数。这个想法只是运行所有文档并测试表webdte.doc_tip_cifra中的相关行902,903,905,907是否已经存在。如果它们不存在，请插入空行以满足以后的验证。即使我只使用了4个条件中的一个，并且使用了它必须运行的行数的一半，它现在的速度也慢得离谱。谁有提升性能的点子？

CREATE OR REPLACE FUNCTION webdte.addtagobligatoriosventa(idlibro bigint)
  RETURNS character AS
$BODY$
DECLARE                 
    id_documento bigint;
    validador integer;
    validador1 integer;
    validador2 integer;
    validador3 integer;
    validador4 integer;

    tipo_cifra integer;
    --counts integer[];
BEGIN
    SELECT INTO validador1, validador2, validador3, validador4
             max(CASE id_tipo_cifra WHEN 901 THEN 1 ELSE 0 END)
            ,max(CASE id_tipo_cifra WHEN 902 THEN 1 ELSE 0 END)
            ,max(CASE id_tipo_cifra WHEN 905 THEN 1 ELSE 0 END)
            ,max(CASE id_tipo_cifra WHEN 907 THEN 1 ELSE 0 END)
    FROM   webdte.doc_tip_cifra
    WHERE  id_doc = id_documento;

    if (validador1 = 0) then
        insert into webdte.doc_tip_cifra (id_doc, id_tipo_cifra, tasa_imp, val_imp)
        values (id_documento, 901, 0, 0);

    end if;
        if (validador2 = 0) then
        insert into webdte.doc_tip_cifra (id_doc, id_tipo_cifra, tasa_imp, val_imp)
        values (id_documento, 902, 0, 0);

    end if;
        if (validador3 = 0) then
        insert into webdte.doc_tip_cifra (id_doc, id_tipo_cifra, tasa_imp, val_imp)
        values (id_documento, 905, 0, 0);

    end if;
        if (validador4 = 0) then
        insert into webdte.doc_tip_cifra (id_doc, id_tipo_cifra, tasa_imp, val_imp)
        values (id_documento, 907, 0, 0);

    end if;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

也许决定使用insert触发器更好，它在每个文档插入时在doc_tip_cifra上插入4个空行，以避免在所有文档上执行这种愚蠢而昂贵的循环，并为每个文档测试4次？你认为如何？

Answer 1

事实证明，你实际上并不需要计数。你的preceding question已经传达了这种印象。然而，在我的解决方案中，仅仅用max替换sum并不能让您走得更远。

是的，它是有效的，但它的效率低得离谱。在找到匹配的行之后，您不必遍历表的其余部分。这就是 的作用。我提出了这个完全不同的方法：

INSERT INTO webdte.doc_tip_cifra (id_doc, id_tipo_cifra, tasa_imp, val_imp)
SELECT id_documento, 901, 0, 0
WHERE  NOT EXISTS (
    SELECT 1
    FROM   webdte.doc_tip_cifra
    WHERE  id_doc = id_documento
    AND    id_tipo_cifra = 901
    );

INSERT INTO webdte.doc_tip_cifra (id_doc, id_tipo_cifra, tasa_imp, val_imp)
SELECT id_documento, 902, 0, 0
WHERE  NOT EXISTS (
    SELECT 1
    FROM   webdte.doc_tip_cifra
    WHERE  id_doc = id_documento
    AND    id_tipo_cifra = 902
    );

INSERT INTO webdte.doc_tip_cifra (id_doc, id_tipo_cifra, tasa_imp, val_imp)
SELECT id_documento, 905, 0, 0
WHERE  NOT EXISTS (
    SELECT 1
    FROM   webdte.doc_tip_cifra
    WHERE  id_doc = id_documento
    AND    id_tipo_cifra = 905
    );

INSERT INTO webdte.doc_tip_cifra (id_doc, id_tipo_cifra, tasa_imp, val_imp)
SELECT id_documento, 907, 0, 0
WHERE  NOT EXISTS (
    SELECT 1
    FROM   webdte.doc_tip_cifra
    WHERE  id_doc = id_documento
    AND    id_tipo_cifra = 907
    );

您可以将其封装在plpgsql或SQL函数中，也可以将其作为纯sql运行。

除了前面的问题之外，这很可能会使用合适的索引。最好的应该是：

CREATE INDEX doc_tip_cifra_special_idx
ON webdte.doc_tip_cifra (id_doc, id_tipo_cifra);

应该让你的查询变得轻快。

此外，这种算法与concurrency有一个固有的问题。检查行是否已经存在和插入行之间的时间窗口应该尽可能小。在这一点上，将所有这些放在一个查询中是最好的。

尽管如此，它并不完美。如果您的数据库有大量的并发性，您可能会对这个excellent blog post by @depesz感兴趣，或者阅读更多under this related question。

是的，用触发器来解决这个问题听起来是个好主意。我会这么做的。