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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-jdbc by date

  From Date Subject
Next Message Simon Riggs 2008-02-13 21:26:45 Re: JDBC, prepared queries, and partitioning
Previous Message Kris Jurka 2008-02-13 21:06:08 Re: JDBC, prepared queries, and partitioning