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

Re: JDBC question for PG 8.3.9

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Cc: Dave Crooke <dcrooke(at)gmail(dot)com>, pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: JDBC question for PG 8.3.9
Date: 2010-04-15 10:59:22
Message-ID: r2p491f66a51004150359j633411aeid84cf21354094fe9@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Apr 14, 2010 at 7:10 PM, Craig Ringer
<craig(at)postnewspapers(dot)com(dot)au> wrote:
> 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 is partially true. The driver uses an unnamed prepared statement
on the server.

> 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.

This is a limitation of the server, not the driver

In response to

pgsql-performance by date

Next:From: nornDate: 2010-04-15 14:23:28
Subject: Re: significant slow down with various LIMIT
Previous:From: Dave CrookeDate: 2010-04-15 03:03:09
Subject: Re: JDBC question for PG 8.3.9

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