pgplsql存储过程的性能？

We have the following stored procedure that is recently performing very slow on larger amount of date in postgres db. Questions:

我们有以下存储过程,最近在postgres db中的较大日期执行速度非常慢。问题:

We parse a string in the nature of (the first number is the id of the row, the second is the state)

我们解析一个字符串的性质(第一个数字是行的id,第二个是状态)

||2|0||3|1||4|0||

Is it better to parse, split the string and loop in a higher language like java? Can the loop be more efficient in Postgres? How are transactions handled in stored procedures? The whole function is one transaction? Probably we are doing to much write and delete operations on the db. The deletes also take too long. Can that be handled more efficient?

是否更好地解析,拆分字符串并循环使用像java这样的高级语言? Postgres中的循环可以更有效吗?如何在存储过程中处理事务?整个功能是一个交易?可能我们在db上做了很多写操作和删除操作。删除也需要很长时间。这可以更有效地处理吗?

CREATE OR REPLACE FUNCTION verificaemitidos(entrada text, largo_mensaje integer)
  RETURNS character AS
$BODY$
    DECLARE                 
    texto_procesado text;       
    identificador bigint;
    estado_mensaje int;
    i int;
    existe_documento int;
    estado_documento text;
    rut numeric;
    tipo int;
    folio_doc numeric;
    otros_estados int;
    BEGIN
        --estado 1 insertado
        --estado 0 no insertado
        --mensaje id_documento|estado||id_documento|estado||


        i := 1;
        while (i <= largo_mensaje)
        loop            
            --Proceso el mensaje
            texto_procesado := split_part(entrada,'||', i) ;
            identificador := split_part(texto_procesado, '|', 1);   
            estado_mensaje := split_part(texto_procesado, '|', 2);              
            -- Se comienza a hacer la comparacion
            existe_documento := (select count (id) from uris_emitidos where id = identificador);
            select estado, emp_rut, tipo_doc, folio into estado_documento, rut, tipo, folio_doc from uris_emitidos where id = identificador;

            --si existe el documento            
            if (existe_documento > 0) then              
                --si el documento que se ingreso esta insertado
                if (estado_mensaje = 1) then
                    --si esta aceptado se eliminan todos los documentos con ese rut, tipo, folio
                    if (estado_documento = 'A') then
                        delete from uris_emitidos where folio = folio_doc and emp_rut = rut and tipo_doc = tipo;
                    end if;
                    --si esta aceptado con reparo se eliminan todos los documentos con ese rut, tipo, folio
                    if (estado_documento = 'B') then
                        delete from uris_emitidos where folio = folio_doc and emp_rut = rut and tipo_doc = tipo;
                    end if;
                    --si esta rechazado se elimina el rechazado y el publicado
                    if (estado_documento = 'R') then
                        delete from uris_emitidos where folio = folio_doc and emp_rut = rut and tipo_doc = tipo and estado in ('R', 'P');
                    end if;
                    --si esta publicado se elimina
                    if (estado_documento = 'P') then
                        delete from uris_emitidos where id = identificador;
                    end if;
                --si el documento que se ingreso no esta insertado              
                else
                    --si esta aceptado se actualiza para que el proceso lo re-encole
                    if (estado_documento = 'A') then 
                        update uris_emitidos set estado_envio = 0, cont = (cont + 1) where id = identificador;                      
                    end if;
                    --si esta aceptado con reparo se actualiza para que el proceso lo re-encole
                    if (estado_documento = 'B') then
                        update uris_emitidos set estado_envio = 0, cont = (cont + 1) where id = identificador;                      
                    end if;
                    --si esta rechazado se verifica que no existe un registro aceptado que se haya encolado o este en espera de encolar
                    if (estado_documento = 'R') then
                        otros_estados = (select count(id) from uris_emitidos ue where ue.folio = folio_doc and ue.emp_rut = rut and ue.tipo_doc = tipo and ue.estado in ('A', 'B'));
                        --si otros estados = 0 significa que el estado rechazado es el mejor estado que hay, por lo tanto se debe re-encolar
                        if (otros_estados = 0) then
                            update uris_emitidos set estado_envio = 0, cont = (cont + 1) where id = identificador;
                        end if;
                    end if;
                    --si esta rechazado se verifica que no existe un registro aceptado o rechazado que se haya encolado o este en espera de encolar
                    if (estado_documento = 'P') then
                        otros_estados = (select count(id) from uris_emitidos where folio = folio_doc and emp_rut = rut and tipo_doc = tipo and estado in ('A', 'B', 'R'));
                        --si otros estados = 0 significa que el estado rechazado es el mejor estado que hay, por lo tanto se debe re-encolar
                        if (otros_estados = 0) then
                            update uris_emitidos set estado_envio = 0, cont = (cont + 1) where id = identificador;
                        end if;
                    end if;

                end if;

            end if;

            i := i+1;
        end loop;
        return 'ok';


    END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

4 个解决方案

#1

Can the loop be more efficient in pgsql?

循环可以在pgsql中更有效吗?

As @wildplasser mentioned, it is generally much faster to run SQL statements that manipulate sets of rows than to manipulate each row individually. Loops are only possible in plpgsql (or other procedural language functions or, in a limited fashion, in recursive CTEs), not in plain SQL. They do their job just fine, but are not the strong suit of PostgreSQL.

正如@wildplasser所提到的,运行操作行集的SQL语句通常比单独操作每一行要快得多。循环只能在plpgsql(或其他过程语言函数中,或以有限的方式,在递归CTE中),而不是在纯SQL中。他们的工作做得很好,但不是PostgreSQL的强项。

How are transactions handled in stored procedures? The whole function is one transaction?

如何在存储过程中处理事务?整个功能是一个交易?

Yes, the whole function runs as one transaction. It can be part of a bigger transaction, but it cannot be split up.

是的,整个函数作为一个事务运行。它可以是更大交易的一部分,但不能拆分。

Read more about how plpgsql functions work in this related answer on dba.SE.

在dba.SE上阅读有关plpgsql函数如何在这个相关答案中工作的更多信息。

Is it better to parse, split the string and loop in a higher language like java?

是否更好地解析,拆分字符串并循环使用像java这样的高级语言?

If the string isn't huge (many thousand elements), it really doesn't matter, as long as your logic is sound. It's not the string parsing that slows you down. It's the "row at a time" manipulation of rows in the table.

如果字符串不是很大(数千个元素),只要你的逻辑是合理的,它就没关系。这不是字符串解析会降低你的速度。它是表中行的“一次一行”操作。

The much faster alternative would be do it all in one or few SQL statements. I would use data modifying CTEs for this (introduced with PostgreSQL 9.1): Parse the string once, and run DML statements on this internal working table.

更快的替代方法是在一个或几个SQL语句中完成所有操作。我将使用数据修改CTE(与PostgreSQL 9.1一起引入):解析字符串一次,并在此内部工作表上运行DML语句。

Consider the following demo (untested):

考虑以下演示(未经测试):

WITH a(t) AS (  -- split string into rows
    SELECT unnest(string_to_array(trim('||2|0||3|1||4|0||'::text, '|'), '||'))
    )
    , b AS (    -- split record into columns per row
    SELECT split_part(t, '|', 1) AS identificador 
          ,split_part(t, '|', 2) AS estado_mensaje 
    FROM   a
    )
    , c AS (    -- implements complete IF branch of your base loop
    DELETE FROM uris_emitidos u
    USING  b
    WHERE  u.id = b.identificador
    AND    u.estado IN ('A','B','R','P')
    AND    b.estado_mensaje = 1
    )
--  , d AS (    -- implements ELSE branch of your loop
--  DELETE ...
--  )
SELECT 'ok':

Adding to the principal design flaws, the logic in your loops is redundant and inconsistent. I consolidated the whole IF branch into the first DELETE statement above.

添加到主要设计缺陷中,循环中的逻辑是冗余且不一致的。我将整个IF分支合并到上面的第一个DELETE语句中。

More information about the function used in the manual here.

有关此手册中使用的功能的更多信息,请参见此处

#2

When you look at it, the arguments (except largo_mensaje) to the dreaded function could be viewed as the fields of a worklist:

当你看到它时,可怕的函数的参数(largo_mensaje除外)可以被视为工作清单的字段:

CREATE TABLE worklist
    ( texto_procesado text
    , identificador bigint
    , estado_mensaje int
    );

And the corresponding work could be performed like ( I borrowed this from Erwin's answer):

并且可以执行相应的工作(我从Erwin的答案中借用了这个):

DELETE FROM uris_emitidos u
 USING  worklist wl
 WHERE  u.id = wl.identificador
   AND    u.estado IN ('A','B','R','P')
   AND    wl.estado_mensaje = 1
   AND    wl.texto_procesado IN ('action1' , 'action2', ...)
    ;

, and afterwards the worklist has to been cleaned up ( AND NOT EXISTS (SELECT * FROM uris_emitidos WHERE ));

,之后必须清理工作清单(而不是EXISTS(SELECT * FROM uris_emitidos WHERE));

#3

The solution I come up with in the moment is the following. Definitely faster than looping though and reducing the number of writes and reads on the db. Thanks

我现在提出的解决方案如下。绝对比循环更快,并减少db上的写入和读取次数。谢谢

CREATE OR REPLACE FUNCTION verificaemitidos2(entrada text, largo_mensaje integer)
  RETURNS character AS
$BODY$
    DECLARE
    texto_procesado text;
    identificador bigint;
    estado_mensaje int;
    i int;
    existe_documento int;
    estado_documento text;
    rut numeric;
    tipo int;
    folio_doc numeric;
    otros_estados int;
    BEGIN
        --estado 1 insertado
        --estado 0 no insertado
        --mensaje id_documento|estado||id_documento|estado||

        --DROP TABLE worklist;
        CREATE TEMP TABLE worklist
            ( identificador bigint,
              estado_mensaje int,
              rut_emisor numeric,
              tipo_doc numeric,
              folio numeric,
              estado text
            );

        INSERT INTO worklist (identificador, estado_mensaje, rut_emisor, tipo_doc, folio, estado)
            SELECT split_part(t, '|', 1)::bigint ,
              split_part(t, '|', 2)::integer ,
              uri.emp_rut,
              uri.tipo_doc,
              uri.folio,
              uri.estado
              from (SELECT unnest(string_to_array(trim(entrada::text, '|'), '||'))) as a(t),
              uris_emitidos uri
              WHERE uri.id = split_part(t, '|', 1)::bigint;

        -- ESTADO 1
        -- ACEPTADOS PRIMEROS DOS CASOS

        DELETE FROM uris_emitidos u
         USING  worklist wl
         WHERE  wl.estado_mensaje = 1
           AND  wl.estado IN ('A', 'B')
           AND  u.folio = wl.folio
           AND  u.emp_rut = wl.rut_emisor
           AND  u.tipo_doc =  wl.tipo_doc;

        -- ESTADO 1
        -- CASO 3

        --delete from uris_emitidos where folio = folio_doc and emp_rut = rut and tipo_doc = tipo and estado in ('R', 'P');

        DELETE FROM uris_emitidos u
         USING  worklist wl
         WHERE  wl.estado_mensaje = 1
           AND  wl.estado IN ('R')
           AND  u.estado IN ('R', 'P')
           AND  u.folio = wl.folio
           AND  u.emp_rut = wl.rut_emisor
           AND  u.tipo_doc =  wl.tipo_doc;

        -- ESTADO 1
        -- CASO 4

         DELETE FROM uris_emitidos u
         USING  worklist wl
         WHERE  u.id = wl.identificador
           AND  wl.estado_mensaje = 1
           AND  wl.estado = 'P';

        -- ESTADO 0
        -- CASOS 1+2

        UPDATE uris_emitidos u
        SET estado_envio = 0, cont =  (u.cont + 1)
        FROM worklist wl
        WHERE  u.id = wl.identificador
        AND  wl.estado_mensaje = 0
        AND  wl.estado IN ('A' , 'B');

         -- update uris_emitidos set estado_envio = 0, cont = (cont + 1) where id = identificador;

        -- ESTADO 0
        -- CASO 3

        UPDATE uris_emitidos u
        SET estado_envio = 0, cont =  (u.cont + 1)
        FROM worklist wl
        WHERE  u.id = wl.identificador
        AND  wl.estado_mensaje = 0
        AND  wl.estado IN ('R')
        AND NOT EXISTS (
        SELECT 1 FROM uris_emitidos ue
        WHERE ue.folio = wl.folio
        AND ue.emp_rut = wl.rut_emisor
        AND ue.tipo_doc = wl.tipo_doc
        AND ue.estado IN ('A', 'B'));

        -- ESTADO 0
        -- CASO 4

        UPDATE uris_emitidos u
        SET estado_envio = 0, cont =  (u.cont + 1)
        FROM worklist wl
        WHERE  u.id = wl.identificador
        AND  wl.estado_mensaje = 0
        AND  wl.estado IN ('P')
        AND NOT EXISTS (
        SELECT 1 FROM uris_emitidos ue
        WHERE ue.folio = wl.folio
        AND ue.emp_rut = wl.rut_emisor
        AND ue.tipo_doc = wl.tipo_doc
        AND ue.estado IN ('A', 'B', 'R'));

        DROP TABLE worklist;

        RETURN 'ok';
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

#4

Your posted answer can be improved and simplified:

您发布的答案可以改进和简化:

CREATE OR REPLACE FUNCTION x.verificaemitidos3(_entrada text)
  RETURNS text AS
$BODY$
BEGIN
   --estado 1 insertado
   --estado 0 no insertado

   CREATE TEMP TABLE worklist ON COMMIT DROP AS
   SELECT split_part(t, '|', 1)::bigint AS identificador
         ,split_part(t, '|', 2)::integer AS estado_mensaje
         ,uri.emp_rut AS rut_emisor
         ,uri.tipo_doc
         ,uri.folio
         ,uri.estado
   FROM  (SELECT unnest(string_to_array(trim(_entrada::text, '|'), '||'))) a(t)
   JOIN   uris_emitidos uri ON uri.id = split_part(t, '|', 1)::bigint;

   -- ESTADO 1

   DELETE FROM uris_emitidos u
   USING  worklist w
   WHERE  w.estado_mensaje = 1
   AND   (
         (w.estado IN ('A', 'B')   -- CASOS 1+2
      OR  w.estado =   'R'         -- CASO 3
      AND u.estado IN ('R', 'P')
      )
      AND u.folio = w.folio
      AND u.emp_rut = w.rut_emisor
      AND u.tipo_doc =  w.tipo_doc

      OR (w.estado = 'P'           -- CASO 4
      AND w.identificador = u.id
      )
      );

   -- ESTADO 0

   UPDATE uris_emitidos u
   SET    estado_envio = 0
         ,cont = cont + 1
   FROM   worklist w
   WHERE  w.estado_mensaje = 0
   AND    w.identificador = u.id
   AND   (w.estado IN ('A', 'B')   -- CASOS 1+2

      OR  w.estado = 'R'           -- CASO 3
      AND NOT EXISTS (
         SELECT 1
         FROM   uris_emitidos ue
         WHERE  ue.folio = w.folio
         AND    ue.emp_rut = w.rut_emisor
         AND    ue.tipo_doc = w.tipo_doc
         AND    ue.estado IN ('A', 'B')
         )

      OR  w.estado = 'P'         -- CASO 4
      AND NOT EXISTS (
         SELECT 1
         FROM   uris_emitidos ue
         WHERE  ue.folio = w.folio
         AND    ue.emp_rut = w.rut_emisor
         AND    ue.tipo_doc = w.tipo_doc
         AND    ue.estado IN ('A', 'B', 'R')
         )
      );

   RETURN 'ok';
END;
$BODY$  LANGUAGE plpgsql VOLATILE;

Major points

• Half as long and twice as fast.

一半长,两倍快。

• Remove 2nd function parameter. You don't need it any more.

删除第二个功能参数。你不再需要它了。

• Remove all variables. None of them are used any more.

删除所有变量。它们都不再使用了。

• Use ON COMMIT DROP to avoid conflict with existing temp table on repeated calls in one session. In Postgres 9.1 you could use CREATE TABLE IF EXISTS

使用ON COMMIT DROP可避免在一个会话中重复调用时与现有临时表冲突。在Postgres 9.1中,您可以使用CREATE TABLE IF EXISTS

• Create temp table directly from results of SELECT with CREATE TABLE AS.

使用CREATE TABLE AS直接从SELECT结果创建临时表。

• Combine all DELETEs.

结合所有DELETE。

• Combine all UPDATEs.

结合所有UPDATE。

• Trim some noise.

修剪一些噪音。

• Use return type text instead of character.

使用返回类型文本而不是字符。