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

Re: JDBC, prepared queries, and partitioning

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Kris Jurka <books(at)ejurka(dot)com>, Josh Berkus <josh(at)agliodbs(dot)com>, PostgreSQL - JDBC <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: JDBC, prepared queries, and partitioning
Date: 2008-02-13 21:13:42
Message-ID: 1202937222.16770.505.camel@ebony.site (view raw or flat)
Thread:
Lists: pgsql-jdbc
On Wed, 2008-02-13 at 15:47 -0500, Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > On Wed, 2008-02-13 at 13:51 -0500, Kris Jurka wrote:
> >> Using protocol version 2 will not prepare queries so constraint exclusion 
> >> can be used.  Just append protocolVersion=2 to your URL.
> 
> > What about the prepare threshold? 

> I suppose this could be implemented with a GUC variable rather than an
> explicit protocol change, but I'm not sure if that'd be a good idea or
> not.  It's not entirely clear to me which layers on the client side need
> to be aware of such behavior.

What I liked about prepare threshold was that it gave you the ability to
control the re-optimization.

ISTM we need a way to say "this query's parameters vary wildly at
execution time, so I really need to re-plan it every time". We don't
like hints, but that isn't something the database can easily assess.

I don't really like the idea of an on/off "replan every time switch".
DB2 has that and its a little clunky. I do like the idea of being able
to specify re-plan every time (-1) and prepare always (0) or switch from
re-plan every time to prepare after (N) executions.

Planning with the first set of parameters is OK, but only as long as the
first parameter it receives is a "good" one. There's no way to guarantee
that without jerking around more than I think we should.

We talked about this in 2006 but I think the "Option Node" approach
doesn't really cut it from an application developer perspective.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com 


In response to

Responses

pgsql-jdbc by date

Next:From: Simon RiggsDate: 2008-02-13 21:26:45
Subject: Re: JDBC, prepared queries, and partitioning
Previous:From: Kris JurkaDate: 2008-02-13 21:06:08
Subject: Re: JDBC, prepared queries, and partitioning

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