[HACKERS] All caught up

[Bruce Momjian *EXTERN*]

On Fri, Apr 18, 2014 at 07:42:06AM +, Albe Laurenz wrote:
 Bruce Momjian wrote:
  I suggest the attached documentation fix.
  
  Patch applied and backpatched to 9.3.  Thanks.
 
 What would PostgreSQL do without Bruce who undertakes the
 Herculean task of making sure that nothing gets forgotten
 and slips through the cracks?

Thanks.  

FYI, I finished going through all the unresolved email threads
yesterday, so I have done as much as I can to prepare for beta.  I will
start the 9.4 major release notes now.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Prepared statements and generic plans

['Bruce Momjian *EXTERN*']

On Mon, Jun  6, 2016 at 07:19:37AM +, 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 EXECUTE 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 EXECUTE
> 
> ... 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  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 +
diff --git a/doc/src/sgml/ref/prepare.sgml b/doc/src/sgml/ref/prepare.sgml
new file mode 100644
index dbce8f2..1bf9179
*** a/doc/src/sgml/ref/prepare.sgml
--- b/doc/src/sgml/ref/prepare.sgml
*** PREPARE n
*** 70,80 

  

!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.
--- 70,80 

  

!Prepared statements potentially 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, e.g. 
!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.
*** PREPARE n
*** 127,140 
Notes
  

!If a prepared statement is executed enough times, the server may eventually
!decide to save and re-use a generic plan rather than re-planning each time.
!This will occur immediately if the prepared statement has no parameters;
!otherwise it occurs only if the generic plan appears to be not much more
!expensive than a plan that depends on specific parameter values.
!Typically, a generic plan will be selected only if the query's performance
!is estimated to be fairly insensitive to the specific parameter values
!supplied.

  

--- 127,151 
Notes