Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 467DF63409A; Tue, 10 Mar 2009 00:39:03 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 00604-04; Tue, 10 Mar 2009 00:39:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from EXHUB018-1.exch018.msoutlookonline.net (exhub018-1.exch018.msoutlookonline.net [64.78.17.16]) by mail.postgresql.org (Postfix) with ESMTP id 5AF6B6344C9; Tue, 10 Mar 2009 00:38:53 -0300 (ADT) Received: from EXVMBX018-1.exch018.msoutlookonline.net ([64.78.17.47]) by EXHUB018-1.exch018.msoutlookonline.net ([64.78.17.16]) with mapi; Mon, 9 Mar 2009 20:38:51 -0700 From: Scott Carey To: Oliver Jowett CC: Guillaume Smet , Guillaume Cottenceau , Mario Splivalo , "pgsql-performance@postgresql.org" , "pgsql-jdbc@postgresql.org" Date: Mon, 9 Mar 2009 20:38:50 -0700 Subject: Re: [PERFORM] Query much slower when run from postgres function Thread-Topic: [JDBC] [PERFORM] Query much slower when run from postgres function Thread-Index: Acmg/7ot1VLQW1keSEeJdogTs9dmwgAMf52o Message-ID: In-Reply-To: <49B58CCA.8060308@opencloud.com> Accept-Language: en-US Content-Language: en X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: multipart/alternative; boundary="_000_C5DB2ED831FDscottrichrelevancecom_" MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200903/44 X-Sequence-Number: 19374 --_000_C5DB2ED831FDscottrichrelevancecom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable On 3/9/09 1:40 PM, "Oliver Jowett" wrote: Scott Carey wrote: > > 1. And how do you do that from JDBC? There is no standard concept of I've suggested that as a protocol-level addition in the past, but it would mean a new protocol version. The named vs. unnamed statement behaviour was an attempt to crowbar it into the protocol without requiring a version change. If it's really a planner behaviour thing, maybe it does belong at the SET level, but I believe that there's usually an aversion to having to SET anything per query to get reasonable plans. There's a strong aversion, but I find myself re-writing queries to get good= plans, a de-facto hint really. Its mandatory in the land of partitioned t= ables and large aggregates, much more rare elsewhere. I have a higher aver= sion to rewriting queries then telling the planner to use more information = or to provide it with more information. > 2. How do you use those on a granularity other than global from jdbc? prepareThreshold=3DN (as part of a connection URL), org.postgresql.PGConnection.setPrepareThreshold() (connection-level granularity), org.postgresql.PGStatement.setPrepareThreshold() (statement-level granularity). See the driver docs. I know I've tried the connection URL thing one time and that did not fix th= e performance problem. I did not know if it was user error. Without knowi= ng how to trace what the query really was or if the setting was working pro= perly, or having any other easy avenue to see if an unnamed prepared statem= ent even fixed my problem, I had to resort to what would clearly fix it (th= ere was only 1 day to fix it, and there was one proven way to fix it). I w= ould love to be able to try out an unnamed prepared statement in psql, to p= rove that it even works to solve the query planning issue or not. In the e= nd, it was simpler to change the code and probably less time consuming than= all the options other than the connection URL setting. > ( - I tried setting max_prepared_transactions to 0 but this > didn't seem to work either, and it would be global if it did). max_prepared_transactions is to do with two-phase commit, not prepared statements. Thanks! Good to know, the configuration documentation could be more clear..= . I got the two prepares confused. > In the end, we had to write our own client side code to deal with sql > injection safely and avoid jdbc prepared statements to get acceptable It's unfortunate that ended up doing this, because it >is< all configurable on the JDBC side. Did you ask on pgsql-jdbc? -O I searched the archives, and did find a reference to the connection URL set= ting and recall trying that but not seeing the expected result. Rather tha= n debugging, a decision was made to go with the solution that worked and be= done with it. This was also when we were in production on 8.3.1 or 8.3.2= or so, so the bugs there might have caused some confusion in the rush to s= olve the issue. I'm still not sure that unnamed prepared statements will help my case. If = the driver is using unnamed prepared statements for the first 5 uses of a q= uery then naming it, I should see the first 5 uses significantly faster tha= n those after. I'll keep an eye out for that in the places where we are st= ill using prepared statements that can cause problems and in the old log fi= les. Until another issue comes up, there isn't sufficient motivation to fi= x what is no longer broken for us. Thanks for the good info on dealing with configuring unnamed prepared state= ments with the jdbc driver. That may come in very handy later. --_000_C5DB2ED831FDscottrichrelevancecom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Re: [JDBC] [PERFORM] Query much slower when run from postgres  = function On 3/9/09 1:40 PM, "Oliver Jowett" <oliver@opencloud.com> wrote:

Scott Carey wrote:
>
>    1. And how do you do that from JDBC?  There is = no standard concept of

I've suggested that as a protocol-level addition in the past, but it
would mean a new protocol version. The named vs. unnamed statement
behaviour was an attempt to crowbar it into the protocol without
requiring a version change. If it's really a planner behaviour thing,
maybe it does belong at the SET level, but I believe that there's
usually an aversion to having to SET anything per query to get
reasonable plans.

>    2. How do you use those on a granularity other than = global from jdbc?

prepareThreshold=3DN (as part of a connection URL),
org.postgresql.PGConnection.setPrepareThreshold() (connection-level
granularity), org.postgresql.PGStatement.setPrepareThreshold()
(statement-level granularity). See the driver docs.


>          ( — I trie= d setting max_prepared_transactions to 0 but this
>       didn’t seem to work either, = and it would be global if it did).

max_prepared_transactions is to do with two-phase commit, not prepared
statements.


> In the end, we had to write our own client side code to deal with sql<= BR> > injection safely and avoid jdbc prepared statements to get acceptable<= BR>
It's unfortunate that ended up doing this, because it >is< all
configurable on the JDBC side. Did you ask on pgsql-jdbc?

-O