Re: parametrized statements, but always replan?

Kevin,

The only time prepared statements won't replan is if you re-use the
prepared statement, which your code does not.

However... AFAIK, postgresql does not plan based on the parameter.

Dave
On 20-Dec-06, at 2:44 PM, Kevin Murphy wrote:

> Sorry for the newbie question here.  The documentation
> (documentation/81/server-prepare.html) isn't clear about this
> point.  I'm using PG 8.1 with the matching jdbc driver.
>
> Will the following code execute the query with a custom plan based
> on the value of var (assuming no use of the prepare threshold)?
>
>        prestmt = conn.prepareStatement(query);
>        prestmt.setString(1, var);
>        resultSet = prestmt.executeQuery();
>        // ... use result set ...
>        resultSet.close();
>        prestmt.close();
>
> I'd like the safety of parametrized SQL statements combined with
> the greater performance (for certain queries) of having every
> statement planned based on the specific parameters.  Ideally, I'd
> like to use prepareStatement() and executeQuery() with out-of-band
> parameters, but have the finished statement be custom-planned.
> Most of my queries wouldn't benefit from the "plan once, execute
> many times" model.
>
> I've read documentation/81/server-prepare.html, but somehow it
> wasn't clear.  It makes it sound as if by default, server-side
> prepared statements are not really used ("There are a number of
> ways to enable server side prepared statements ..."), which would
> be good for me.
>
> But looking at my postgresql log, that doesn't seem to be the case;
> I see PREPARE, BIND, and EXECUTE lines for each query.
>
> Thanks,
> Kevin Murphy
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>