Skip site navigation (1) Skip section navigation (2)

Re: [PERFORM] Query much slower when run from postgres function

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Guillaume Cottenceau <gc(at)mnc(dot)ch>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [PERFORM] Query much slower when run from postgres function
Date: 2009-03-10 08:39:44
Message-ID: 49B62750.6090103@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-jdbcpgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Guillaume CottenceauDate: 2009-03-10 09:41:19
Subject: Re: [PERFORM] Query much slower when run from postgres function
Previous:From: Guillaume CottenceauDate: 2009-03-10 08:05:52
Subject: Re: [PERFORM] Query much slower when run from postgres function

pgsql-jdbc by date

Next:From: Guillaume CottenceauDate: 2009-03-10 09:41:19
Subject: Re: [PERFORM] Query much slower when run from postgres function
Previous:From: Guillaume CottenceauDate: 2009-03-10 08:05:52
Subject: Re: [PERFORM] Query much slower when run from postgres function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group