Message-ID: <49B58CCA.8060308@opencloud.com>
Date: Tue, 10 Mar 2009 10:40:26 +1300
From: Oliver Jowett <oliver@opencloud.com>
User-Agent: Thunderbird 2.0.0.19 (X11/20090105)
MIME-Version: 1.0
To: Scott Carey <scott@richrelevance.com>
CC: Guillaume Smet <guillaume.smet@gmail.com>,
	Guillaume Cottenceau <gc@mnc.ch>,
	Mario Splivalo <mario.splivalo@megafon.hr>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>,
	"pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.org>
Subject: Re: [PERFORM] Query much slower when run from postgres  function
References: <C5DAD06F.31CB%scott@richrelevance.com>
In-Reply-To: <C5DAD06F.31CB%scott@richrelevance.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Scott Carey wrote:
> 
>    1. And how do you do that from JDBC?  There is no standard concept of
>       ‘unnamed’ prepared statements in most database APIs, and if there
>       were the behavior would be db specific.  Telling PG to plan after
>       binding should be more flexible than unnamed prepared statements —
>       or at least more transparent to standard APIs.  E.g. SET
>       plan_prepared_postbind=’true’.

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=N (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 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.

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

It's unfortunate that ended up doing this, because it >is< all
configurable on the JDBC side. Did you ask on pgsql-jdbc?

-O