Thread: raising the default prepareTheshold

raising the default prepareTheshold

From
Oliver Jowett
Date:
I'd like to raise the default prepareThreshold to some non-zero value,
perhaps 5 or so. This means that PreparedStatements that are used more
than 5 times will allocate and use a named backend statement.

Usually, using a named statement is a performance win as it avoids
reparsing and replanning the query on each execution; I've seen
measurable improvement (at least 15%, from memory) on heavily-reused
simple queries where the parse/plan cost is a noticable fraction of
total execution time.

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.

So using named statements always by default (default prepareThreshold =
1) probably isn't a good idea. Using a default prepareThreshold > 1
seems like a reasonable compromise.

What does everyone think?

-O

Re: raising the default prepareTheshold

From
Kris Jurka
Date:

On Sun, 19 Sep 2004, Oliver Jowett wrote:

> I'd like to raise the default prepareThreshold to some non-zero value,
> perhaps 5 or so. This means that PreparedStatements that are used more
> than 5 times will allocate and use a named backend statement.
>

Seems like a good idea and I think five is a nice conservative number.

At the same time do you think we should change the default fetchSize to
1000 or so?  Even though it only works for FORWARD_ONLY ResultSets it
could reduce the number of complaints we see here without much penalty.
Previously the cursor implementation was more limiting by ruling out
server prepared statements.

Kris Jurka

Re: raising the default prepareTheshold

From
Markus Schaber
Date:
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.

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

HTH,
Markus

--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com

Re: raising the default prepareTheshold

From
Oliver Jowett
Date:
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

Re: raising the default prepareTheshold

From
Oliver Jowett
Date:
Kris Jurka wrote:
>
> At the same time do you think we should change the default fetchSize to
> 1000 or so?  Even though it only works for FORWARD_ONLY ResultSets it
> could reduce the number of complaints we see here without much penalty.
> Previously the cursor implementation was more limiting by ruling out
> server prepared statements.

Sounds like a good idea.

-O

Re: raising the default prepareTheshold

From
Kris Jurka
Date:

On Mon, 20 Sep 2004, Kris Jurka wrote:

> At the same time do you think we should change the default fetchSize to
> 1000 or so?  Even though it only works for FORWARD_ONLY ResultSets it
> could reduce the number of complaints we see here without much penalty.
> Previously the cursor implementation was more limiting by ruling out
> server prepared statements.
>

Actually a little testing shows that a non-zero fetchSize implies using a
named statement instead of the unnamed statement.  So there will be some
potentially negative planning differences, so I'm going to retract this
suggestion.

Kris Jurka

Re: raising the default prepareTheshold

From
Oliver Jowett
Date:
Kris Jurka wrote:
>
> On Mon, 20 Sep 2004, Kris Jurka wrote:
>
>
>>At the same time do you think we should change the default fetchSize to
>>1000 or so?  Even though it only works for FORWARD_ONLY ResultSets it
>>could reduce the number of complaints we see here without much penalty.
>>Previously the cursor implementation was more limiting by ruling out
>>server prepared statements.
>>
>
>
> Actually a little testing shows that a non-zero fetchSize implies using a
> named statement instead of the unnamed statement.  So there will be some
> potentially negative planning differences, so I'm going to retract this
> suggestion.

Ah, this is because if we used the unnamed statement, the created portal
gets closed when the unnamed statement is reused. I forgot about that.

-O