Postgres/Perl performance help needed

[cbbrowne-HInyCGIudOg at public.gmane.org]

> On Tue, 2004-05-25 at 16:16, Madison Kelly wrote:
> > Hi all,
> > 
> >    The intrepid "Asker of questions" is back (so sorry!).
> > 
> >    I am hoping I can pick the brains of those of you here who play with 
> > postgresql a lot. Maybe you can help me configure the server to be more 
> > efficient or suggest a different way to approach my code.
> > 
> 
> <snip>
> 
> One thing which should help is to prepare your select/update/insert
> statements before even entering the function, then reusing them with
> different bound values.  (See the DBI documentation for more on bound
> values.) E.G.

No, it won't.  PostgreSQL does not yet support any sort of 'precompiled'
version of this.  The problem with _not_ re-evaluating the query each
time is that changing the parameters may significantly change the query
plan.

> This should not only help speed up your code (so that the statements
> aren't continually re-parsed) it should also reduce the risk of
> malformed SQL statements since bound parameters are automatically
> appropriately quoted and escaped.

The re-evaluation part I don't buy.  The quoting part is worth
something, though.

> Another thing to look at is if unnecessary string conversions are
> happening.  E.G. if the field file_sr_uuid is an int and your
> statements end up as "WHERE file_src_uuid='n'" there *may* be an
> unneeded string conversion happening and you should instead do "WHERE
> file_src_uuid=n".

That won't be an unnecessary string conversion.

There is a _necessary_ string conversion in transforming the string into
an integer.  

And up until version 7.5 (which is still very much in beta, now), there
is an advantage to keeping it as a string until the query parser gets to
it because there is the unfortunate possibility that the integer you
pass in might be of a type that does not match what is actually in the
field, and in that case, pre-7.5, it is possible for that to lead to the
optimizer not noticing a relevant index.
--
output = reverse("gro.mca" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/internet.html
"So you don't  want  to hear about my  ideas  for Cliche  Programming?
(Basically, the  unit of abstraction   is a Stereotype;  instances  of
Stereotypes interact by exchanging Hype.)" -- Peter Lewerin
--
The Toronto Linux Users Group.      Meetings: http://tlug.ss.org
TLUG requests: Linux topics, No HTML, wrap text below 80 columns
How to UNSUBSCRIBE: http://tlug.ss.org/subscribe.shtml