From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | CS DBA <cs_dba(at)consistentstate(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: function slower than the same code in an sql file |
Date: | 2011-10-28 05:10:18 |
Message-ID: | CAFj8pRALvyPzsTrAaC4cZQCOQVJvEpjWeczqgo=DZmrYvbQxBw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Davis | 2011-10-28 05:54:51 | Re: Usage of pg_stat_database |
Previous Message | Tom Lane | 2011-10-28 05:10:08 | Re: function slower than the same code in an sql file |