Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 95B8F63471F; Mon, 9 Mar 2009 17:56:14 -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 05087-10; Mon, 9 Mar 2009 17:56:09 -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-3.exch018.msoutlookonline.net (exhub018-3.exch018.msoutlookonline.net [64.78.17.18]) by mail.postgresql.org (Postfix) with ESMTP id 1E730634863; Mon, 9 Mar 2009 17:56:02 -0300 (ADT) Received: from EXVMBX018-1.exch018.msoutlookonline.net ([64.78.17.47]) by EXHUB018-3.exch018.msoutlookonline.net ([64.78.17.18]) with mapi; Mon, 9 Mar 2009 13:56:01 -0700 From: Scott Carey To: Guillaume Smet , Guillaume Cottenceau CC: Mario Splivalo , "pgsql-performance@postgresql.org" , "pgsql-jdbc@postgresql.org" Date: Mon, 9 Mar 2009 13:56:01 -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: Acmg2TOkBrIQXEhjT6G3VGEdIGHkjQAID8kx Message-ID: In-Reply-To: <1d4e0c10903091004m758d7ea2s91e33631be81215a@mail.gmail.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_C5DAD06F31CBscottrichrelevancecom_" 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/35 X-Sequence-Number: 19365 --_000_C5DAD06F31CBscottrichrelevancecom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 1. And how do you do that from JDBC? There is no standard concept of 'un= named' prepared statements in most database APIs, and if there were the beh= avior would be db specific. Telling PG to plan after binding should be mor= e flexible than unnamed prepared statements - or at least more transparent = to standard APIs. E.g. SET plan_prepared_postbind=3D'true'. 2. How do you use those on a granularity other than global from jdbc? = ( - I tried setting max_prepared_transactions to 0 but this didn't seem to = work either, and it would be global if it did). Prepared statements are st= ill great for things like selecting off a primary key, or especially insert= s. Controls at the connection or user level would be significantly more va= luable than global ones. 3. Is it possible to test them from psql? (documentation is weak, PREPARE= requires a name, functions require names, etc .. C api has docs but that's= not of use for most). I'd love to know if there were answers to the above that were workable. In the end, we had to write our own client side code to deal with sql injec= tion safely and avoid jdbc prepared statements to get acceptable performanc= e in many cases (all cases involving partitioned tables, a few others). At= least dollar-quotes are powerful and useful for dealing with this. Since = the most important benefit of prepared statements is code clarity and sql i= njection protection, its sad to see weakness in control/configuration over = prepared statement behavior at the parse/plan level get in the way of using= them for those benefits. On 3/9/09 9:04 AM, "Guillaume Smet" wrote: On Mon, Mar 9, 2009 at 5:51 PM, Guillaume Cottenceau wrote: > Until it's possible to specifically tell the JDBC driver (and/or > PG?) to not plan once for all runs (or is there something better > to think of?), or the whole thing would be more clever (off the > top of my head, PG could try to replan with the first actual > values - or first xx actual values - and if the plan is > different, then flag that prepared statement for replanning each > time if the overall time estimate is different enough), I've > opted to tell the JDBC driver to use the protocol version 2, as > prepared statements were not so much prepared back then (IIRC > parameter interpolation is performed in driver and the whole SQL > query is passed each time, parsed, and planned) using > protocolVersion=3D2 in the JDBC URL. So far it worked very well for > us. Unnamed prepared statements are planned after binding the values, starting with 8.3, or more precisely starting with 8.3.2 as early 8.3 versions were partially broken on this behalf. It's not always possible to use protocol version 2 as it's quite limited (especially considering the exceptions returned). -- Guillaume -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance --_000_C5DAD06F31CBscottrichrelevancecom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Re: [JDBC] [PERFORM] Query much slower when run from postgres  = function
  1. And how do you do that from JDBC?  There i= s no standard concept of ‘unnamed’ prepared statements in most = database APIs, and if there were the behavior would be db specific.  T= elling PG to plan after binding should be more flexible than unnamed prepar= ed statements — or at least more transparent to standard APIs.  = E.g. SET plan_prepared_postbind=3D’true’.
  2. How do you use those on a granularity other than gl= obal from jdbc?    ( — I tried setting max_prepared_tr= ansactions to 0 but this didn’t seem to work either, and it would be = global if it did).  Prepared statements are still great for things lik= e selecting off a primary key, or especially inserts.  Controls at the= connection or user level would be significantly more valuable than global = ones.
  3. Is it possible to test them from psql? (documentati= on is weak, PREPARE requires a name, functions require names, etc .. C api = has docs but that’s not of use for most).

I’d love to know if there were answers to the above that were workabl= e.

In the end, we had to write our own client side code to deal with sql injec= tion safely and avoid jdbc prepared statements to get acceptable performanc= e in many cases (all cases involving partitioned tables, a few others). &nb= sp;At least dollar-quotes are powerful and useful for dealing with this. &n= bsp;Since the most important benefit of prepared statements is code clarity= and sql injection protection, its sad to see weakness in control/configura= tion over prepared statement behavior at the parse/plan level get in the wa= y of using them for those benefits.  



On 3/9/09 9:04 AM, "Guillaume Smet" <guillaume.smet@gmail.com> wrote:

On Mon, Mar 9, 2009 at 5:51 PM, Guillaume C= ottenceau <gc@mnc.ch> wrote:
> Until it's possible to specifically tell the JDBC driver (and/or
> PG?) to not plan once for all runs (or is there something better
> to think of?), or the whole thing would be more clever (off the
> top of my head, PG could try to replan with the first actual
> values - or first xx actual values - and if the plan is
> different, then flag that prepared statement for replanning each
> time if the overall time estimate is different enough), I've
> opted to tell the JDBC driver to use the protocol version 2, as
> prepared statements were not so much prepared back then (IIRC
> parameter interpolation is performed in driver and the whole SQL
> query is passed each time, parsed, and planned) using
> protocolVersion=3D2 in the JDBC URL. So far it worked very well for > us.

Unnamed prepared statements are planned after binding the values,
starting with 8.3, or more precisely starting with 8.3.2 as early 8.3
versions were partially broken on this behalf.

It's not always possible to use protocol version 2 as it's quite
limited (especially considering the exceptions returned).

--
Guillaume

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www= .postgresql.org/mailpref/pgsql-performance

--_000_C5DAD06F31CBscottrichrelevancecom_--