Re: Performance problem in PLPgSQL

2013/8/23 Fábio Telles Rodriguez <fabio(dot)telles(at)gmail(dot)com>

>
> 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.
>

please, can you send a self explained test

this issue should be fixed, and we need a examples.

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