sql - 优化plpgsql函数

PostgreSQL版本是9.0。
我必须优化plpgsql函数。这个想法只是运行所有的文件和测试，如果相关的行902903905907在表webdte.doc_tip_cifra已经存在。如果它们尚未存在，则插入空行以满足随后的验证。即使我只使用这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触发器，即在每个文档insert上插入4个空行，以避免在所有文档上执行这个愚蠢而昂贵的循环，并为每个文档测试4次？
你怎么认为？

最佳答案

事实证明，你并不需要数数。你的preceding question传达了这种印象。但是，在我的解决方案中，用sum替换max并不是很容易。
是的，这很有效，但效率太低了。找到匹配的行后，不必遍历表的其余部分。这就是EXISTS semi-joins的作用。我建议采用完全不同的方法：

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运行。
除了前面的问题之外，这很可能使用合适的索引。最佳值是amulti-column index类似：

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

会让你的问题变得轻松。
而且，这种算法有一个固有的并发问题。检查一行是否已经存在并插入它的时间窗口应该尽可能的小。在这方面，将其全部放在一个查询中是最好的。
不过，这并不完美。如果您的数据库看到大量并发，您可能会对这个excellent blog post by @depesz感兴趣，或者阅读更多under this related question。
是的，用扳机解决这个问题听起来是个好主意。我会的。

关于sql - 优化plpgsql函数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11623059/