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

parameterized queries and prepareThreshold

From: Gene <genekhart(at)gmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: parameterized queries and prepareThreshold
Date: 2007-02-24 20:47:56
Message-ID: 430d92a20702241247m421b575bnfb1cb604fe8d6649@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
I'm using version 8.2.3 with latest jdbc and noticed that certain
queries take a very
long time even though I have partial indexes that it should be using.
It uses them and performs very quickly if I hard code the constants
(no preparedStmt)
and do an explain.

I noticed this in the changelog and figured it was the problem but
wanted to make sure I wasn't on a wild goose chase:

"Protocol-level unnamed prepared statements are re-planned for each
set of BIND values (Tom) This improves performance because the exact
parameter values can be used in the plan."

I'm using hibernate and can't determine from the docs if what they use
are considered named/unnamed as stated in the docs above but i'm
guessing by the results they are (S_123/C_124).

LOG:  duration: 23926.439 ms  execute S_123/C_124: select ... PIV1 = ?
OR PIV2 = ? ...
DETAIL:  parameters: $1 = 'PARTIALINDEXVALUE1', $2 = 'PARTIALINDEXVALUE2'

In hibernate this is my connection string:

<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
<property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
<property name="hibernate.connection.url">
jdbc:postgresql://192.168.106.191/isis?autoReconnect=true;prepareThreshold=0
</property>

can i verify somehow that the prepareThreshold parameter is making it
into the jdbc driver?

thanks for any help

-- 
Gene Hart

Responses

pgsql-jdbc by date

Next:From: Mario SplivaloDate: 2007-02-26 16:12:35
Subject: Re: Calling functions with table-based-type parametars
Previous:From: Jonathan HallidayDate: 2007-02-23 20:56:49
Subject: bug with PGXADataSource in JNDI

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