pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feeddoes prepareThreshold work? forced to use old driver
4+ messages / 3 participants
[nested] [flat]
* does prepareThreshold work? forced to use old driver
@ 2007-02-26 16:12 Gene <genekhart@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Gene @ 2007-02-26 16:12 UTC (permalink / raw)
To: pgsql-jdbc@postgresql.org; pgsql-performance
hi!
I've been having some serious performance issues with
postgresql8.2/hibernate/jdbc due to postgres reusing bad cached query
plans. It doesn't look at the parameter values and therefore does not
use any partial indexes.
After trying to set prepareThreshold=0 in the connection string which
didnt work, even modifying the jdbc driver and forcing it to 0 and not
working I realized that it must be being ignored. After giving up
pretty much I tried a much older driver which doesn't use server
prepared statements at all the problem has gone away and it is once
again using the partial indexes. How can I get this to work properly
on the new jdbc driver? I don't really like having to use a 2 year old
driver to get good performance as you can imagine :)
Could someone point me to a jdbc src file where I could just disable
server-side prepared statements entirely?
--
thanks, G
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: does prepareThreshold work? forced to use old driver
@ 2007-02-26 16:45 Kris Jurka <books@ejurka.com>
parent: Gene <genekhart@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Kris Jurka @ 2007-02-26 16:45 UTC (permalink / raw)
To: gene@sotech.us; +Cc: pgsql-jdbc@postgresql.org; pgsql-performance
On Mon, 26 Feb 2007, Gene wrote:
> I've been having some serious performance issues with
> postgresql8.2/hibernate/jdbc due to postgres reusing bad cached query
> plans. It doesn't look at the parameter values and therefore does not
> use any partial indexes.
>
> After trying to set prepareThreshold=0 in the connection string which
> didnt work, even modifying the jdbc driver and forcing it to 0 and not
> working I realized that it must be being ignored. After giving up
> pretty much I tried a much older driver which doesn't use server
> prepared statements at all the problem has gone away and it is once
> again using the partial indexes. How can I get this to work properly
> on the new jdbc driver? I don't really like having to use a 2 year old
> driver to get good performance as you can imagine :)
Something must be going wrong in the setting to zero or your code may be
setting it to non-zero at some later point. I believe prepareThreshold=0
should work. Do you have a test case showing it doesn't?
Kris Jurka
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: does prepareThreshold work? forced to use old driver
@ 2007-02-26 17:28 Dave Cramer <pg@fastcrypt.com>
parent: Gene <genekhart@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Dave Cramer @ 2007-02-26 17:28 UTC (permalink / raw)
To: gene@sotech.us; +Cc: pgsql-jdbc@postgresql.org; pgsql-performance
On 26-Feb-07, at 11:12 AM, Gene wrote:
> hi!
>
> I've been having some serious performance issues with
> postgresql8.2/hibernate/jdbc due to postgres reusing bad cached query
> plans. It doesn't look at the parameter values and therefore does not
> use any partial indexes.
>
> After trying to set prepareThreshold=0 in the connection string which
> didnt work, even modifying the jdbc driver and forcing it to 0 and not
> working I realized that it must be being ignored. After giving up
> pretty much I tried a much older driver which doesn't use server
> prepared statements at all the problem has gone away and it is once
> again using the partial indexes. How can I get this to work properly
> on the new jdbc driver? I don't really like having to use a 2 year old
> driver to get good performance as you can imagine :)
>
> Could someone point me to a jdbc src file where I could just disable
> server-side prepared statements entirely?
>
you can just add protocolVersion=2 to the url and it will not use
prepared statements.
setting prepareThreshold=0 just tells it not to use named statements.
It will still use statements but won't cache them.
Are you sure the problem is with cached statements ? There are issues
where prepared statements won't use the index if you don't use the
correct type.
Dave
> --
> thanks, G
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: does prepareThreshold work? forced to use old driver
@ 2007-02-26 17:41 Gene <genekhart@gmail.com>
parent: Dave Cramer <pg@fastcrypt.com>
0 siblings, 0 replies; 4+ messages in thread
From: Gene @ 2007-02-26 17:41 UTC (permalink / raw)
To: Dave Cramer <pg@fastcrypt.com>; +Cc: pgsql-jdbc@postgresql.org; pgsql-performance
Thank you! setting the protocolVersion=2 works with the newer driver.
I'm still puzzled as to why the prepareThreshold=0 doesn't force the
replan though.
On 2/26/07, Dave Cramer <pg@fastcrypt.com> wrote:
>
> On 26-Feb-07, at 11:12 AM, Gene wrote:
>
> > hi!
> >
> > I've been having some serious performance issues with
> > postgresql8.2/hibernate/jdbc due to postgres reusing bad cached query
> > plans. It doesn't look at the parameter values and therefore does not
> > use any partial indexes.
> >
> > After trying to set prepareThreshold=0 in the connection string which
> > didnt work, even modifying the jdbc driver and forcing it to 0 and not
> > working I realized that it must be being ignored. After giving up
> > pretty much I tried a much older driver which doesn't use server
> > prepared statements at all the problem has gone away and it is once
> > again using the partial indexes. How can I get this to work properly
> > on the new jdbc driver? I don't really like having to use a 2 year old
> > driver to get good performance as you can imagine :)
> >
> > Could someone point me to a jdbc src file where I could just disable
> > server-side prepared statements entirely?
> >
> you can just add protocolVersion=2 to the url and it will not use
> prepared statements.
>
> setting prepareThreshold=0 just tells it not to use named statements.
> It will still use statements but won't cache them.
>
> Are you sure the problem is with cached statements ? There are issues
> where prepared statements won't use the index if you don't use the
> correct type.
>
> Dave
> > --
> > thanks, G
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq
> >
>
>
--
Gene Hart
cell: 443-604-2679
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-02-26 17:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-26 16:12 does prepareThreshold work? forced to use old driver Gene <genekhart@gmail.com>
2007-02-26 16:45 ` Kris Jurka <books@ejurka.com>
2007-02-26 17:28 ` Dave Cramer <pg@fastcrypt.com>
2007-02-26 17:41 ` Gene <genekhart@gmail.com>
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