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:content-transfer-encoding;
	b=kd+bWvN9ogfnYB9pH7gwqlhjSpOCzb7VAYNx+OyWP3VXmJwUk5x49jPiWIBZPn74g0
	WBfRfYrZt5tR23Q7Q4xwcaUkA46wX0S+xo7qy4Nck4AvdNthPCKXwWQl4ZLgBfoS9cr3
	BU1sV8BuH77f2l8PWjpNYs9GySBx0rsJqInZ4=
MIME-Version: 1.0
In-Reply-To: <87fxhmmygj.fsf@meuh.mnc.lan>
References: <49B51B60.6030600@megafon.hr> <27649.1236616294@sss.pgh.pa.us>
	<87fxhmmygj.fsf@meuh.mnc.lan>
Date: Mon, 9 Mar 2009 18:04:23 +0100
Message-ID: <1d4e0c10903091004m758d7ea2s91e33631be81215a@mail.gmail.com>
Subject: Re: [PERFORM] Query much slower when run from postgres
	function
From: Guillaume Smet <guillaume.smet@gmail.com>
To: Guillaume Cottenceau <gc@mnc.ch>
Cc: Mario Splivalo <mario.splivalo@megafon.hr>,
	pgsql-performance@postgresql.org, pgsql-jdbc@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On Mon, Mar 9, 2009 at 5:51 PM, Guillaume Cottenceau <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=2 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