Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 3A61C632757 for ; Thu, 15 Apr 2010 00:03:27 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 53583-01 for ; Thu, 15 Apr 2010 03:03:17 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f228.google.com (mail-bw0-f228.google.com [209.85.218.228]) by mail.postgresql.org (Postfix) with ESMTP id 4FEE86323EB for ; Thu, 15 Apr 2010 00:03:11 -0300 (ADT) Received: by bwz28 with SMTP id 28so887251bwz.34 for ; Wed, 14 Apr 2010 20:03:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:received:message-id:subject:from:to:cc:content-type; bh=X2zawWMhAPlVFV6gyNjF8Mm+NSkqjh3ifgwnadK7psk=; b=jlGjotniLzkgqoJGMSzU0kX9vTUrgvGsbXrWTcIAYo/c3xyYQIHjgFsdOWLcdjsgNA g+93W8Ph8ILhiox9Kedx1B0dybkYuSXELq9AM2YnYnFC1ir1AiWUPDOaxNchoxbosqJP AtlBSp3iQzeh5kSP5CPnIHaN9O+NTMIJTltSY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=kg/YbnHnam0rT2W5NDaMo6Gn9AXpUPdljoLXh5P8gYo+rwCX3RdQgylxQqlJ0Oerse GnZb8hQYYgwfBe78nsLI/GN/5TZX84Wg0Y9FP94a7czyQiN+5kRCZLCXLsmsGtYJwrPy //DvL4DiOlCR/+PSxP1L2ZZUaFjeSMVfzIjj8= MIME-Version: 1.0 Received: by 10.204.69.73 with HTTP; Wed, 14 Apr 2010 20:03:09 -0700 (PDT) In-Reply-To: <4BC64B64.5070608@postnewspapers.com.au> References: <4BC64B64.5070608@postnewspapers.com.au> Date: Wed, 14 Apr 2010 22:03:09 -0500 Received: by 10.204.5.87 with SMTP id 23mr791772bku.206.1271300589807; Wed, 14 Apr 2010 20:03:09 -0700 (PDT) Message-ID: Subject: Re: JDBC question for PG 8.3.9 From: Dave Crooke To: Craig Ringer Cc: pgsql-performance Content-Type: multipart/alternative; boundary=00151758ac540eea3004843dbc82 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.898 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201004/182 X-Sequence-Number: 38432 --00151758ac540eea3004843dbc82 Content-Type: text/plain; charset=UTF-8 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 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 > --00151758ac540eea3004843dbc82 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 us= ing 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.<= br>
IIRC (and I can't say this with 100% certainty without checking the sou= rces 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 th= an 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 jav= adoc - if it's not, it needs to be.

--
Craig Ringer

--00151758ac540eea3004843dbc82--