Thread: pl/pgsql slowness

Is it normal that a query that takes <1 sec when executed from psql
prompt 
takes >15 sek when executed from a function (and takes >95% of cpu for
all that time ?

example (on 7.0.2)

>UPDATE item SET id_path = '';

returns immediately (on 2000 item table)

then I create a function
CREATE FUNCTION "regenerate_id_paths" ( ) RETURNS int4 AS '   BEGIN       UPDATE item SET id_path = '''';       RETURN
-1;  END;
 
' LANGUAGE 'plpgsql';

and then

>select regenerate_id_paths( );

takes more than 15 sec and uses as much cpu as it can get while running;




BTW, where can I learn more about pl/pgsql süntax ?

The postgre docs suggest that "For more complex examples the programmer
might look at the regression
test for PL/pgSQL." but there is only one example using a for loop and
none using while. 

I suspect that it may be missing more.



-----------
Hannu

Hannu Krosing <hannu@tm.ee> writes:
> Is it normal that a query that takes <1 sec when executed from psql
> prompt takes > 15 sek when executed from a function

No.  I can't reproduce the quoted misbehavior under either 7.0.2 or
current sources; your example takes ~1 sec either way for me.
        regards, tom lane

Tom Lane wrote:
> 
> Hannu Krosing <hannu@tm.ee> writes:
> > Is it normal that a query that takes <1 sec when executed from psql
> > prompt takes > 15 sek when executed from a function
> 
> No.  I can't reproduce the quoted misbehavior under either 7.0.2 or
> current sources; your example takes ~1 sec either way for me.

Sorry, my fault. 

I ran the queries over two similar tables, but the slow one had several
huge indexes on it.
They had grown huge over time and vacuum did nothing to reduce them. 

So it seems that it had nithing to do with plpgsql.

-----------
Hannu