Re: Performance problem in PLPgSQL

> It just inserts nb records in a loop in 4 different maneers:
> - Directly in an int field
> - Then in a numeric field (that's where we're having problems)
> - Then in the same numeric field, but trying a cast (it doesn't change a
> thing)
> - Then tries with an intermediary temp variable of numeric type (which
> solves the problem).
>
>
> Here are the runtimes (tables were truncated beforehand):
>
> 9.1.9:
> select test_insert(1000000);
> NOTICE: time for int:00:00:09.526009
> NOTICE: time for numeric:00:00:10.557126
> NOTICE: time for numeric, casted:00:00:10.821369
> NOTICE: time for numeric with tmp variable:00:00:10.850847
>
>
> 9.2.4:
> select test_insert(1000000);
> NOTICE: time for int:00:00:09.477044
> NOTICE: time for numeric:00:00:24.757032 <----
> NOTICE: time for numeric, casted:00:00:24.791016 <----
> NOTICE: time for numeric with tmp variable:00:00:10.89332
>
>
> I really don't know exactly where the problem comes from… but it's been
> hurting a function very badly (there are several of these static queries
> with types mismatch). And of course, the problem is not limited to
> numeric… text has the exact same problem.
>
> Regards,
>
> Marc
>
>
I got the same problem today. Unfortunately, we need to rollback to 9.1 in
our production site. Of course the team needed to make better tests before
go to production. Of course they really need to write better functions in
PL/pgSQL, but this problem was a really "no go" for us.

Just don't let this gotcha gone in our to do.

--
Regards,
Fábio Telles Rodriguez
blog: http:// <http://www.midstorm.org/~telles/>s<http://tellesr.wordpress.com/>
avepoint.blog.br
e-mail / gtalk / MSN: fabio(dot)telles(at)gmail(dot)com
Skype: fabio_telles

Timbira - The Brazilian Postgres Company
http://www.timbira.com.br