Re: Prepared statements, parameters and logging

[Oliver Jowett <oliver(at)opencloud(dot)com>]

Csaba Nagy wrote:

> My real question is: am I turning the right knob with
> "prepareThreshold=0" ? I want all queries to be planned taking into
> account the parameter values by default. I can turn this off on special
> cases by setting the prepare threashold on the statement level - if the
> prepareThreshold is affecting at all the fact that parameters are sent
> for planning or not... is it ?

Short answer: yes, prepareThreshold=0 is the right knob. (with one 
exception, see below)

Long answer:

The parameters are always sent out-of-line from the query when using 
protocol version 3.

When the driver decides to use a "server prepared statement" (i.e. when 
prepareThreshold > 0 and you've reused the same PreparedStatement 
enough) it parses the query into a named statement at the protocol 
level. Otherwise, it parses it into the unnamed statement.

When a named statement is parsed, the server also generates a generic 
plan immediately.

When an unnamed statement is parsed, the parse phase is done but query 
planning is not done. Later, when actual parameter values are bound, 
planning is done using those actual parameter values for selectivity 
purposes.

So the issue is not so much "how are the parameters sent?" but "is a 
named statement being used or not?"

If you set prepareThreshold=0 then an unnamed statement should always be 
used (& therefore actual parameter values should be taken into account 
when planning), with one exception: the setFetchSize() case I described 
in my earlier email. In that case the driver simply can't use the 
unnamed statement because it needs the statement and portal to survive 
for longer than using the unnamed statement would allow. Ideally the 
protocol would let clients specify when to defer planning on a 
per-statement basis, but the current situation is a result of wanting to 
avoid exactly that sort of protocol change.. it's a bit of a compromise.

If you set logLevel=2 on the driver, you should be able to see the 
differences in the protocol flow in more detail than the server logging 
lets you see.

-O