From: Oliver Jowett <oliver@opencloud.com>
To: Guillaume Cottenceau <gc@mnc.ch>
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [PERFORM] Query much slower when run from postgres function
Date: Tue, 10 Mar 2009 21:39:44 +1300
Message-ID: <49B62750.6090103@opencloud.com> (raw)
In-Reply-To: <87bps9ls4f.fsf@meuh.mnc.lan>
References: <49B51B60.6030600@megafon.hr>
<27649.1236616294@sss.pgh.pa.us>
<87fxhmmygj.fsf@meuh.mnc.lan>
<1d4e0c10903091004m758d7ea2s91e33631be81215a@mail.gmail.com>
<5165.1236618994@sss.pgh.pa.us>
<491f66a50903091256n7fe4a3f6i93267d0d53b983cb@mail.gmail.com>
<49B58ABE.7070105@opencloud.com>
<87bps9ls4f.fsf@meuh.mnc.lan>
Guillaume Cottenceau wrote:
> Oliver Jowett <oliver 'at' opencloud.com> writes:
>
>> The idea behind the threshold is that if a PreparedStatement object is
>> reused, that's a fairly good indication that the application wants to
>> run the same query many times with different parameters (since it's
>> going to the trouble of preserving the statement object for reuse). But
>
> Or it may just need the safeness of driver/database parameter
> "interpolation", to get a "free" efficient safeguard against SQL
> injection.
In which case, the application usually throws the PreparedStatement
object away after executing it once, and the threshold is never reached.
As I said, the application has to do extra work to preserve exactly the
same PreparedStatement object for reuse before the threshold applies, at
which point it's reasonable to assume that it could be a
performance-sensitive query that would benefit from preserving the query
plan and avoiding parse/plan costs on every execution.
It's just a heuristic because there *is* a tradeoff and many/most
applications are not going to be customized specifically to know about
that tradeoff. And it's configurable because the tradeoff is not the
same in every case.
Do you have a suggestion for a better way to decide when to use a named
statement?
-O
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: oliver@opencloud.com, gc@mnc.ch, pgsql-jdbc@postgresql.org
Subject: Re: [PERFORM] Query much slower when run from postgres function
In-Reply-To: <49B62750.6090103@opencloud.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox