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 <[EMAIL PROTECTED]> To: Vilson farias <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Sexta-feira, 20 de Abril de 2001 06:26 Subject: Re: [GENERAL] very slow execution of stored procedures : From: "Vilson farias" <[EMAIL PROTECTED]> : : > 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 : ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster