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(at)momjian(dot)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 +