Re: 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