Re: very slow execution of stored procedures

Richard,

  I've tried all your advices and still nothing.
  It's been very hard to me understand why this is happening. The only
solution is to modify the stored procedure.

----------------------------------------------------------------------------
----------------------------------------------------------

bxs=# SET ENABLE_SEQSCAN = OFF;
SET VARIABLE
bxs=# SELECT finaliza_chamv2(1,65535,49644,'2001-04-17 11:50:55','2001-04-17
12:17:40', '0', 0, 0, 48, '', 0, 10, '2817107', 0, 261, 65535);
 finaliza_chamv2
-----------------
               0
(1 row)

Execution time = about 4s.

----------------------------------------------------------------------------
----------------------------------------------------------

This time I'm trying to remove timestamp conversions from where clause:

DROP FUNCTION finaliza_chamv2(INT4, INT4, INT4, CHAR(23), CHAR(23), CHAR(1),
                                INT4, INT4, INT4, CHAR(23), INT4, INT4,
CHAR(25),
                                INT4, INT4, INT4);

CREATE FUNCTION finaliza_chamv2(INT4, INT4, INT4, CHAR(23), CHAR(23),
CHAR(1),
                                INT4, INT4, INT4, CHAR(23), INT4, INT4,
CHAR(25),
                                INT4, INT4, INT4) RETURNS int4 AS
'
DECLARE
  pbxs           ALIAS FOR $1;
  pchave         ALIAS FOR $2;
  pidentificacao ALIAS FOR $3;
  pdtinicial     ALIAS FOR $4;
  pdtfinal       ALIAS FOR $5;
  pflgliber      ALIAS FOR $6;
  ptempototal    ALIAS FOR $7;
  pcodliber      ALIAS FOR $8;
  pddd           ALIAS FOR $9;
  pdtocup        ALIAS FOR $10;
  pindicadora    ALIAS FOR $11;
  pcategoria     ALIAS FOR $12;
  pidentidadea   ALIAS FOR $13;
  pfds           ALIAS FOR $14;
  presultcham    ALIAS FOR $15;
  pcifraorigem   ALIAS FOR $16;

BEGIN
  UPDATE cham_chamada
  SET dt_final        = TIMESTAMP(pdtfinal);
      flg_liberacao   = pflgliber,
      temp_total      = ptempototal,
      cod_liberjuntor = pcodliber,
      ddd             = pddd,
      indicadora      = pindicadora,
      cod_categoria   = pcategoria,
      identidadea     = pidentidadea,
      cod_fds         = pfds,
      cod_resultcham  = presultcham,
      cifra_origem    = pcifraorigem
  WHERE cod_bxs       = pbxs           AND
        chave         = pchave         AND
        identificacao = pidentificacao AND
        dt_inicial    = pdtinicial;

  IF pdtocup <> '''' THEN
    UPDATE cham_servico
    SET
      dt_ocupacao = TIMESTAMP(pdtocup)
    WHERE
      cod_bxs       = pbxs           AND
      chave         = pchave         AND
      identificacao = pidentificacao AND
      dt_inicial    = tempo          AND
      dt_finalizacao is null;
  END IF;

  RETURN 0;

END;
'

LANGUAGE 'plpgsql';


bxs=# SELECT finaliza_chamv2(1,65535,49644,'2001-04-17 11:50:55','2001-04-17
12:17:40', '0', 0, 0, 48, '', 0,
 10, '2817107', 0, 261, 65535);
Cancel request sent
ERROR:  Query was cancelled.

Execution time : ?? (I was forced to abort after more than 1 minute waiting
and a lot of cpu was sucked while executing)

Note that execution time go back to 4seconds if I put the original stored
procedure back.

----------------------------------------------------------------------------
----------------------------------------------------------

Here some info about my table and fast execution :

bxs=# EXPLAIN UPDATE cham_chamada
bxs-# SET dt_final = TIMESTAMP('2001-04-17 12:12:10'),
bxs-# flg_liberacao = '0',
bxs-# temp_total = 0,
bxs-# cod_liberjuntor = 0,
bxs-# ddd = 48,
bxs-# indicadora = 0,
bxs-# cod_categoria = 10,
bxs-# identidadea = '2817005',
bxs-# cod_fds = 0,
bxs-# cod_resultcham  = 6,
bxs-# cifra_origem = 65535
bxs-# WHERE cod_bxs = 1 AND
bxs-# chave = 65535 AND
bxs-# identificacao = 49644 AND
bxs-# dt_inicial = TIMESTAMP('2001-04-17 12:12:00');
NOTICE:  QUERY PLAN:

Index Scan using xpkcham_chamada on cham_chamada  (cost=0.00..4.23 rows=1
width=58)

EXPLAIN


bxs=# \d cham_chamada
           Table "cham_chamada"
    Attribute    |    Type     | Modifier
-----------------+-------------+----------
 cod_bxs         | integer     | not null
 chave           | integer     | not null
 identificacao   | integer     | not null
 dt_inicial      | timestamp   | not null
 indicadora      | integer     |
 cod_categoria   | integer     |
 identidadea     | varchar(20) |
 dt_final        | timestamp   |
 juntor          | integer     |
 indicadorb      | integer     |
 identidadeb     | varchar(20) |
 flg_chamada     | char(1)     |
 flg_liberacao   | char(1)     |
 temp_total      | integer     |
 ddd             | smallint    |
 cod_liberjuntor | integer     |
 cod_resultcham  | integer     |
 cod_fds         | integer     |
 cifra_origem    | integer     |
Indices: xie1cham_chamada,
         xie2cham_chamada,
         xpkcham_chamada


bxs=# SELECT COUNT(*) FROM cham_chamada;
 count
--------
 145978
(1 row)

----------------------------------------------------------------------------
----------------------------------------------------------
I found a solution that uses index scan. I redone some parameters, check out
:

CREATE FUNCTION finaliza_chamv2(INT4, INT4, INT4, TIMESTAMP, TIMESTAMP,
CHAR(1),
                                INT4, INT4, INT4, CHAR(23), INT4, INT4,
CHAR(25),
                                INT4, INT4, INT4) RETURNS int4 AS
'
DECLARE
  pbxs           ALIAS FOR $1;
  pchave         ALIAS FOR $2;
  pidentificacao ALIAS FOR $3;
  pdtinicial     ALIAS FOR $4;
  pdtfinal       ALIAS FOR $5;
  pflgliber      ALIAS FOR $6;
  ptempototal    ALIAS FOR $7;
  pcodliber      ALIAS FOR $8;
  pddd           ALIAS FOR $9;
  pdtocup        ALIAS FOR $10;
  pindicadora    ALIAS FOR $11;
  pcategoria     ALIAS FOR $12;
  pidentidadea   ALIAS FOR $13;
  pfds           ALIAS FOR $14;
  presultcham    ALIAS FOR $15;
  pcifraorigem   ALIAS FOR $16;

BEGIN

  UPDATE cham_chamada
  SET dt_final        = pdtfinal,
      flg_liberacao   = pflgliber,
      temp_total      = ptempototal,
      cod_liberjuntor = pcodliber,
      ddd             = pddd,
      indicadora      = pindicadora,
      cod_categoria   = pcategoria,
      identidadea     = pidentidadea,
      cod_fds         = pfds,
      cod_resultcham  = presultcham,
      cifra_origem    = pcifraorigem
  WHERE cod_bxs       = pbxs           AND
        chave         = pchave         AND
        identificacao = pidentificacao AND
        dt_inicial    = pdtinicial;

  IF pdtocup <> '''' THEN
    UPDATE cham_servico
    SET
      dt_ocupacao = pdtocup
    WHERE
      cod_bxs       = pbxs           AND
      chave         = pchave         AND
      identificacao = pidentificacao AND
      dt_inicial    = pdtinicial     AND
      dt_finalizacao is null;
  END IF;

Now execution time is <1s. Ok, but I really would like to know what's
happening to the older version.

Best Regards,
José Vilson de Mello de Farias
Dígitro Tecnologia Ltda. - Brazil


----- Original Message -----
From: Richard Huxton <dev@archonet.com>
To: Vilson farias <vilson.farias@digitro.com.br>
Cc: <pgsql-general@postgresql.org>
Sent: Sexta-feira, 20 de Abril de 2001 06:26
Subject: Re: [GENERAL] very slow execution of stored procedures


: From: "Vilson farias" <vilson.farias@digitro.com.br>
:
: > Greetings,
: >
: >     I found something very weird related with stored procedures
execution.
: I
: > have this stored procedure to finalize a phone call, writing tha time of
: > call finalization and some other values to a calls table, called
: > cham_chamada. Please check this out (very simple) :
:
: Nobody else seems to have answered this yet, so I'll have a stab.
:
: > ------------------------------------------------------------------
: >
: > CREATE FUNCTION finaliza_chamv2(INT4, INT4, INT4, CHAR(23), CHAR(23),
: > CHAR(1),
: >                                 INT4, INT4, INT4, CHAR(23), INT4, INT4,
: > CHAR(25),
: >                                 INT4, INT4, INT4) RETURNS int4 AS
:
: [snipped simple update function]
:
: > If I change all variables to the parameters value inside the stored
: > procedure and then execute the frist script, then it is very fast, check
: out
: >
: > execution time : <1ms
:
: > now its time to do the same thing using the stored procedure :
: > execution time : about 5s
: >
: > Is it supose to execute with different speed? What can I do to fix it?
: >
: > I'm using postgres RPM 7.0.3-2 in RedHat 6.2.
:
: Well - there are some differences - if I understand correctly, the parser
is
: smarter about things when constants are explicitly specified (like in the
: quick example).
:
: I'm assuming your table is large and what is happening is that the
function
: is not using indexes. The most likely reason I can see is the timestamp()
: calls in the code.
:
: If you do:
:
: select proname,proiscachable from pg_proc where proname='timestamp';
:
: You'll see that the conversion functions are marked not cachable, so that
: would probably discourage the use of the index on the timestamp fields.
:
: Use another variable to hold the converted timestamp value and see if that
: helps. If you still don't get an improvement try passing in the values as
: timestamps rather than text.
:
: If that still doesn't help try:
:
: SET ENABLE_SEQSCAN = OFF;
:
: before calling the function and see what that does.
:
: If you are still having problems, can you supply the output of EXPLAIN for
: the fast version.
:
: > ps: There are some specific procedures I needed to execute before I got
: > pl/pgsql working :
: >
: > CREATE FUNCTION plpgsql_call_handler ()
: > RETURNS opaque AS '/usr/lib/pgsql/plpgsql.so'
: > LANGUAGE 'C';
: >
: > CREATE PROCEDURAL LANGUAGE 'plpgsql'
: > HANDLER plpgsql_call_handler
: > LANCOMPILER 'PL/PgSql internal';
:
: A simpler method is to do:
:
: createlang plpgsql mydatabase
:
: from the command-line.
:
: HTH
:
: - Richard Huxton
: