Re: JDBC, prepared queries, and partitioning

On Wed, 2008-02-13 at 15:47 -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.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