Re: function slower than the same code in an sql file

Hello

plpgsql uses a cached prepared plans for queries - where optimizations
is based on expected values - not on real values. This feature can do
performance problems some times. When you have these problems, then
you have to use a dynamic SQL instead. This generate plans for only
one usage and then there optimization can be more exact (but it repeat
a plan generation)

http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Regards

Pavel Stehule

2011/10/28 CS DBA <cs_dba(at)consistentstate(dot)com>:
> Hi All ;
>
> I have code that drops a table, re-create's it (based on a long set of
> joins) and then re-creates the indexes.
>
> It runs via psql in about 10 seconds.  I took the code and simply wrapped it
> into a plpgsql function and the function version takes almost 60 seconds to
> run.
>
> I always thought that functions should run faster than psql... am I wrong?
>
> Thanks in advance
>
> --
> ---------------------------------------------
> Kevin Kempter       -       Constent State
> A PostgreSQL Professional Services Company
>          www.consistentstate.com
> ---------------------------------------------
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>