Re: Prepared statements and generic plans

On Mon, Jun  6, 2016 at 07:19:37AM +0000, Albe Laurenz wrote:
> Bruce Momjian wrote:
> > OK, updated version attached.  I added "potential" to the first
> > paragraph, and added "estimated cost" to the later part, fixed the
> > "cheaper than", and clarified that we add the plan time cost to the
> > non-generic plan, which is how it can be cheaper than the generic plan.
> > I also moved the "Once a generic plan is chosen" line.
> >
> > Yeah, that's a lot of changes, but they all improved the text.  Thanks.
>
> Thanks for working on this.
>
> !    Prepared statements can optionally use generic plans rather than
> !    re-planning with each set of supplied <command>EXECUTE</command> values.
> !    This occurs immediately for prepared statements with no parameters;
> !    otherwise it occurs only after five or more executions produce estimated
> !    plan costs, with planning overhead added, that are, on average, more
> !    expensive than the generic plan cost.
>
> The following might be easier to understand:
> ... after five or more executions produce plans whose estimated cost average
> (including planning overhead) is more expensive than the generic plan cost estimate.

Agreed.

> !    A generic plan assumes each value supplied to <command>EXECUTE</command>
>
> ... assumes *that* each value ...

Agreed.

> !    is one of the column's distinct values and that column values are
> !    uniformly distributed.  For example, if statistics records three
>
> Shouldn't it be "record"?
> The documentation treats "statistics" as a plural word throughout.

Agreed, not sure how I missed that.

> !    distinct column values, a generic plan assumes a column equality
> !    comparison will match 33% of processed rows.  Column statistics
>
> ... assumes *that* a column equality comparison will match 33% of *the* processed rows.

Uh, that seems overly wordy.  I think the rule is that if the sentence
makes sense without the words, you should not use them, but it is
clearly a judgement call in this case.  Do you agree?

> !    also allows generic plans to accurately compute the selectivity of
>
> Column statistics also *allow* ...

Yep.

Updated patch attached.

One more thing --- there was talk of moving some of this into chapter
66, but as someone already mentioned, there are no subsections there
because it is a dedicated topic:

    66. How the Planner Uses Statistics.

I am not inclined to add a prepare-only section to that chapter.  On the
other hand, the issues described apply to PREPARE and to protocol-level
prepare, so having it in PREPARE also seems illogical.  However, I am
inclined to leave it in PREPARE until we are ready to move all of this
to chapter 66.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +