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(at)archonet(dot)com>
To: Vilson farias <vilson(dot)farias(at)digitro(dot)com(dot)br>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Sexta-feira, 20 de Abril de 2001 06:26
Subject: Re: [GENERAL] very slow execution of stored procedures

: From: "Vilson farias" <vilson(dot)farias(at)digitro(dot)com(dot)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
: