pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedJDBC question for PG 8.3.9
4+ messages / 3 participants
[nested] [flat]
* JDBC question for PG 8.3.9
@ 2010-04-14 20:49 Dave Crooke <dcrooke@gmail.com>
2010-04-14 23:10 ` Re: JDBC question for PG 8.3.9 Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 1 reply; 4+ messages in thread
From: Dave Crooke @ 2010-04-14 20:49 UTC (permalink / raw)
To: pgsql-performance
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? I'm assuming if it's just a client side constant escaper that there
won't be an issue.
Cheers
Dave
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: JDBC question for PG 8.3.9
2010-04-14 20:49 JDBC question for PG 8.3.9 Dave Crooke <dcrooke@gmail.com>
@ 2010-04-14 23:10 ` Craig Ringer <craig@postnewspapers.com.au>
2010-04-15 03:03 ` Re: JDBC question for PG 8.3.9 Dave Crooke <dcrooke@gmail.com>
2010-04-15 10:59 ` Re: JDBC question for PG 8.3.9 Dave Cramer <pg@fastcrypt.com>
0 siblings, 2 replies; 4+ messages in thread
From: Craig Ringer @ 2010-04-14 23:10 UTC (permalink / raw)
To: Dave Crooke <dcrooke@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: JDBC question for PG 8.3.9
2010-04-14 20:49 JDBC question for PG 8.3.9 Dave Crooke <dcrooke@gmail.com>
2010-04-14 23:10 ` Re: JDBC question for PG 8.3.9 Craig Ringer <craig@postnewspapers.com.au>
@ 2010-04-15 03:03 ` Dave Crooke <dcrooke@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Dave Crooke @ 2010-04-15 03:03 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: pgsql-performance
Mine is a single record INSERT, so no issues with plans :-) Little Java ETL
job.
Is there any setting I'd need to tweak assuming I'm using 150-200 of these
at once?
Cheers
Dave
On Wed, Apr 14, 2010 at 6:10 PM, Craig Ringer
<craig@postnewspapers.com.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 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: JDBC question for PG 8.3.9
2010-04-14 20:49 JDBC question for PG 8.3.9 Dave Crooke <dcrooke@gmail.com>
2010-04-14 23:10 ` Re: JDBC question for PG 8.3.9 Craig Ringer <craig@postnewspapers.com.au>
@ 2010-04-15 10:59 ` Dave Cramer <pg@fastcrypt.com>
1 sibling, 0 replies; 4+ messages in thread
From: Dave Cramer @ 2010-04-15 10:59 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: Dave Crooke <dcrooke@gmail.com>; pgsql-performance
On Wed, Apr 14, 2010 at 7:10 PM, Craig Ringer
<craig@postnewspapers.com.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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-04-15 10:59 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-14 20:49 JDBC question for PG 8.3.9 Dave Crooke <dcrooke@gmail.com>
2010-04-14 23:10 ` Craig Ringer <craig@postnewspapers.com.au>
2010-04-15 03:03 ` Dave Crooke <dcrooke@gmail.com>
2010-04-15 10:59 ` Dave Cramer <pg@fastcrypt.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