Re: raising the default prepareTheshold

Markus Schaber wrote:
> Hi, Oliver,
>
> On Sun, 19 Sep 2004 09:29:04 +1200
> Oliver Jowett <oliver@opencloud.com> wrote:
>
>
>>However, with a 8.0 backend, if the planner benefits from knowing the
>>particular parameter values in use for a particular query, then using an
>>unnamed statement may be faster due to using an improved plan. Under 7.4
>>there will be no difference; both named and unnamed statements will use
>>the generic plan.
>
>
> Quoting from http://developer.postgresql.org/beta-history.txt:
>
>      * Plan prepared queries only when first executed so constants can be
>        used for statistics (Oliver Jowett)
>        Prepared statements plan queries once and execute them many times.
>        While prepared queries avoid the overhead of re-planning on each
>        use, the quality of the plan suffers from not knowing the exact
>        parameters to be used in the query. In this release, planning of
>        unnamed prepared statements is delayed until the first execution,
>        and the actual parameter values of that execution are used as
>        optimization hints.

Yes, this is the end result of the patch I submitted.

> So it seems that your issue is addressed in 8.0 - as long as the Beta of
> the Readme is correct :-)

The readme is right (although the first sentence is a bit general).
However, it's not a perfect solution. If you use named statements then
the query is planned only once, when the Parse message is received. This
plan cannot take the actual parameter values being used into account. In
contrast, the unnamed statement is planned when the first Bind is
received, and uses the actual parameter values for cost estimation. So
using a named statement might produce a worse plan than using the
unnamed statement each time.

By default, the JDBC driver uses the unnamed statement for each query
execution, which should produce the same plans as if it just did direct
parameter substitution into the query string. If the prepareThreshold is
reached for a particular PreparedStatement, the JDBC driver starts
(re)using a named statement for that query, which is not *always* a win.
It does avoids re-parsing and re-planning the query each time, but each
execution may run slower if the plan becomes worse.

-O