Message-ID: <4BC64B64.5070608@postnewspapers.com.au>
Date: Thu, 15 Apr 2010 07:10:28 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.9) Gecko/20100404 Lightning/1.0b1 Thunderbird/3.0.4
MIME-Version: 1.0
To: Dave Crooke <dcrooke@gmail.com>
CC: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: JDBC question for PG 8.3.9
References: <y2xca24673e1004141349l531c9710i5ed197a258d6ca8d@mail.gmail.com>
In-Reply-To: <y2xca24673e1004141349l531c9710i5ed197a258d6ca8d@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 15/04/10 04:49, Dave Crooke wrote:
> Hi foilks
>
> I am using PG 8.3 from Java. I am considering a performance tweak which
> will involve holding about 150 java.sql.PreparedStatment objects open
> against a single PGSQL connection. Is this safe?
>
> I know that MySQL does not support prepared statements /per se/, and so
> their implementation of PreparedStatement is nothing more than some
> client-side convenience code that knows how to escape and format
> constants for you. Is this the case for PG, or does the PG JDBC driver
> do the real thing?

Pg supports real server-side prepared statements, as does the JDBC driver.

IIRC (and I can't say this with 100% certainty without checking the 
sources or a good look at TFM) the PostgreSQL JDBC driver initially does 
only a client-side prepare. However, if the PreparedStatement is re-used 
more than a certain number of times (five by default?) it switches to 
server-side prepared statements.

This has actually caused a bunch of performance complaints on the jdbc 
list, because the query plan may change at that switch-over point, since 
with a server-side prepared statement Pg no longer has a specific value 
for each parameter and may pick a more generic plan.

Again only IIRC there's a configurable threshold for prepared statement 
switch-over. I thought all this was in the PgJDBC documentation and/or 
javadoc - if it's not, it needs to be.

--
Craig Ringer