Re: parametrized statements, but always replan?

[Mark Lewis <mark(dot)lewis(at)mir3(dot)com>]

Set the prepareThreshold to 0, meaning that it will never create a
"real" server-side prepared statement.

There will still be a protocol level PREPARE/BIND/EXECUTE passed to the
back-end because that's the way the V3 protocol works, but that's not
the same thing.  The planner will still see the actual query values.

-- Mark Lewis

On Wed, 2006-12-20 at 14:44 -0500, 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