Re: Prepared statements and generic plans

David G. Johnston wrote:
> On Thu, Jun 2, 2016 at 9:56 PM, Bruce Momjian <bruce@momjian.us> wrote:
>> In Postgres 9.2 we improved the logic of when generic plans are used by
>> EXECUTE.  We weren't sure how well it would work, and the docs included
>> a vague description on when generic plans are chosen.
>> 
>> I have gotten a few questions lately about how prepared statements are
>> handled with multiple executions so I have updated the PREPARE manual
>> page with the attached patch to more clearly explain generic plans and
>> when they are chosen.
>> 
>> I would like to apply this to the 9.6 docs.

I think that this is a very good idea.

I had to dig through the sources before to answer such questions.

> ​While putting the proposed patch in context I came across this.
> 
> ​"""
> ​
> Prepared statements have the largest performance advantage when a single session is being used to
> execute a large number of similar statements. The performance difference will be particularly
> significant if the statements are complex to plan or rewrite, for example, if the query involves a
> join of many tables or requires the application of several rules. If the statement is relatively
> simple to plan and rewrite but relatively expensive to execute, the performance advantage of prepared
> statements will be less noticeable.
> 
> ​"""
> 
> Until and unless the generic plan is used the  "...if statements are complex to plan..." doesn't make
> sense; and no where in the description itself do we introduce the generic plan concept.  This is
> inconsistent but I'm not addressing it below though its worth considering before a patch to this area
> is committed.

I think that the paragraph is still true; it talks about "a large number of similar statements".
As long as "large" is sufficiently greater than five, that is.

> As to the patch...
> 
> Isn't this backwards? <note> [change]
> 
> """
> otherwise it <switching to a generic plan> occurs only after five or more executions produce
> [execution /strike plans] plus planning costs that are, on average, [roughly equal to /strike cheaper]
> than the generic plan.
> """

I agree.
I also fouund this sentence hard to read.

> I'd maybe go with something like this:
> 
> All executions of a prepared statement having zero parameters will use the same plan so the planning
> time taken during the first execution will be spread across all subsequent executions.  For statements
> having parameters the first five executions will result in value-specific plans as previously
> described.  However, on the sixth execution a generic plan will also be computed and if the average
> planning + execution cost of all previous value-specific plans is about equal to the execution cost of
> the generic plan the generic plan will be chosen for that and all subsequent executions.

I think that is much better, but I suggest this wording:

"All executions of a prepared statement having zero parameters use the same plan, so theywill use the generic plan
immediately. For statements having parameters the first five executionswill result in value-specific plans as
previouslydescribed.However, on the sixth execution a generic plan will also be computed, and if the average cost
estimateofall previous value-specific plans is about equal to the cost estimate of the generic plan,the generic plan
willbe chosen for that and all subsequent executions."
 

This emphasizes that it is only estimates we are dealing with, otherwise it would be hard
to understand why estimation errors can lead to generic plans being chosen that are much worse.

> <existing next paragraph>
> 
> If we are getting generic plans significantly cheaper than the value-specific plans I suspect there is
> a problem...so any comparison that indicates "less-than" is prone to cause confusion.  The original is
> worded well on this point: "...generic plan appears to be not much more expensive..." but lacks detail
> elsewhere.

I don't quite get that.  Do you mean the same thing that I wrote above?

> This part:
> 
> !    A generic plan assumes each value supplied to <command>EXECUTE</command>
> !    is one of the column's distinct values and that column values are
> !    uniformly distributed.  For example, if statistics records three
> !    distinct column values, a generic plan assumes a column equality
> !    comparison will match 33% of processed rows.  Column statistics
> !    also allows generic plans to accurately compute the selectivity of
> !    unique columns.  Comparisons on non-uniformly-distributed columns and
> !    specification of non-existent values affects the average plan cost,
> !    and hence if and when a generic plan is chosen.  [elided the last sentence, placed in the first
> paragraph]
> 
> I'm not sure of the specific goal here but this level detail seems a bit out-of-place in the SQL
> Command documentation.  So, do we want this user-facing and if so do we want it here?

[...]

> This leaves Bruce's second alteration: which probably should follow the rest over to chapter 66.  The
> point of the existing sentence is to give the casual user the means to detect the current type of plan
> and I think that is all that is needed here.

I agree that this is too much detail.
I would vote for omitting it altogether.

Anybody who needs that level of detail is better served with the source anyway.

Yours,
Laurenz Albe