Re: Prepared statements and generic plans

On Fri, Jun 3, 2016 at 3:17 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
wrote:

>
> > 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 they
> will use the generic plan immediately. 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 cost estimate
> of all previous value-specific plans is about equal to the cost estimate
> of the generic plan,
> the generic plan will be 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.
>
>
​You've dropped what I think is the essential point of comparing (planning
+ execution) cost of the per-value setup to just the execution costs of the
generic plan. You also make it sound like only the sixth plan is performs
the comparison and if the value-specific plan is still preferred the
generic plan will never again be considered. Change "on" to "beginning
with" and that goes away. That, and adding back in the costs types, will
make me happy.

> > <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?
>

​Yeah. I was pointing out the Bruce's was trying to compare using a
less-than which I think is prone to confusion if not outright wrong.
​

>
> > 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.
>

​This goes back to Bruce's motivation but as long as it goes into the
internals section I have no problem adding material that someone felt was
worth their time to write.

​David J.