Thread

1. BUG #3500: Horrible performance when wrong type is set in prepared statement

   Flavio Botelho <fbotelho@stj.gov.br> — 2007-07-30T22:36:09Z

   The following bug has been logged online:
   
   Bug reference:      3500
   Logged by:          Flavio Botelho
   Email address:      fbotelho@stj.gov.br
   PostgreSQL version: 8.1.8
   Operating system:   Linux - Red Hat -- Linux somewhere 2.6.11-1.1369_FC4smp
   #1 SMP Thu Jun 2 23:08:39 EDT 2005 i686 i686 i386 GNU/Linux
   Description:        Horrible performance when wrong type is set in prepared
   statement
   Details: 
   
   We have id fields (set to integer types) that were being passed to our JDBC
   as string types and being set so in the prepared statements.
   
   This seems to produce the right result anyway (i didnt investigate, but i
   suspect so as if this was not the case someone would have seem such a big
   bug in the application).
   
   But performance gets absurdly bad, from a index scan to a table scan.
   
   I know the application should not be doing this. But i wonder if lots of the
   complaints about postgres performance couldnt be related to problems like
   this. I suggest that you change the behaviour of something like that from
   silently accepting the string value to throwing an error.
   
   Or if you really want to be nice for these users try to fit the right type
   before matching the possible indexes...
   
   Thanks for the hard work.
   
   

2. Re: BUG #3500: Horrible performance when wrong type is set in prepared statement

   Joseph Shraibman <jks@selectacast.net> — 2007-07-31T22:31:07Z

   Flavio Botelho wrote:
   
   > I know the application should not be doing this. But i wonder if lots of the
   > complaints about postgres performance couldnt be related to problems like
   > this. I suggest that you change the behaviour of something like that from
   > silently accepting the string value to throwing an error.
   > 
   Well that would be bad.  I still have a lot of code with numbers inside 
   quotes to work around the old postgres bug where index scans weren't 
   used unless the numbers were in quotes (because the table columns were 
   int2's and passed in numbers were assumed to be ints)
   
   Do you have EXPLAIN output showing the difference in your queries if the 
   values are in strings or not?