Skip site navigation (1) Skip section navigation (2)

Re: [PERFORM] Query much slower when run from postgres function

From: Guillaume Smet <guillaume(dot)smet(at)gmail(dot)com>
To: Guillaume Cottenceau <gc(at)mnc(dot)ch>
Cc: Mario Splivalo <mario(dot)splivalo(at)megafon(dot)hr>, pgsql-performance(at)postgresql(dot)org, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [PERFORM] Query much slower when run from postgres function
Date: 2009-03-09 17:04:23
Message-ID: 1d4e0c10903091004m758d7ea2s91e33631be81215a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbcpgsql-performance
On Mon, Mar 9, 2009 at 5:51 PM, Guillaume Cottenceau <gc(at)mnc(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2009-03-09 17:16:34
Subject: Re: [PERFORM] Query much slower when run from postgres function
Previous:From: Guillaume CottenceauDate: 2009-03-09 16:51:24
Subject: Re: [PERFORM] Query much slower when run from postgres function

pgsql-jdbc by date

Next:From: Tom LaneDate: 2009-03-09 17:16:34
Subject: Re: [PERFORM] Query much slower when run from postgres function
Previous:From: Guillaume CottenceauDate: 2009-03-09 16:51:24
Subject: Re: [PERFORM] Query much slower when run from postgres function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group