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://savepoint.blog.br

e-mail / gtalk / MSN: fabio.telles@gmail.com

Skype: fabio_telles

Timbira - The Brazilian Postgres Company

http://www.timbira.com.br