Re: [doc] plan invalidation when statistics are update

On Wed, Nov 25, 2020 at 1:13 PM Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com>
wrote:

>
>
> On 2020/11/24 23:14, Fujii Masao wrote:
> >
> >
> > On 2020/11/19 14:33, torikoshia wrote:
> >> On 2020-11-18 11:35, Fujii Masao wrote:
> >>
> >> Thanks for your comment!
> >>
> >>> On 2020/11/18 11:04, torikoshia wrote:
> >>>> Hi,
> >>>>
> >>>> AFAIU, when the planner statistics are updated, generic plans are
> invalidated and PostgreSQL recreates. However, the manual doesn't seem to
> explain it explicitly.
> >>>>
> >>>> https://www.postgresql.org/docs/devel/sql-prepare.html
> >>>>
> >>>> I guess this case is included in 'whenever database objects used in
> the statement have definitional (DDL) changes undergone', but I feel it's
> hard to infer.
> >>>>
> >>>> Since updates of the statistics can often happen, how about
> describing this case explicitly like an attached patch?
> >>>
> >>> +1 to add that note.
> >>>
> >>> - statement. Also, if the value of <xref
> linkend="guc-search-path"/> changes
> >>> + statement. For example, when the planner statistics of the
> statement
> >>> + are updated, <productname>PostgreSQL</productname> re-analyzes and
> >>> + re-plans the statement.
> >>>
> >>> I don't think "For example," is necessary.
> >>>
> >>> "planner statistics of the statement" sounds vague? Does the statement
> >>> is re-analyzed and re-planned only when the planner statistics of
> database
> >>> objects used in the statement are updated? If yes, we should describe
> >>> that to make the note a bit more explicitly?
> >>
> >> Yes. As far as I confirmed, updating statistics which are not used in
> >> prepared statements doesn't trigger re-analyze and re-plan.
> >>
> >> Since plan invalidations for DDL changes and statistcal changes are
> caused
> >> by PlanCacheRelCallback(Oid 'relid'), only the prepared statements using
> >> 'relid' relation seem invalidated.> Attached updated patch.
> >
> > Thanks for confirming that and updating the patch!
>
> force re-analysis and re-planning of the statement before using it
> whenever database objects used in the statement have undergone
> definitional (DDL) changes since the previous use of the prepared
> - statement. Also, if the value of <xref linkend="guc-search-path"/>
> changes
> + statement. Similarly, whenever the planner statistics of database
> + objects used in the statement have updated, re-analysis and re-planning
> + happen.
>
> "been" should be added between "have" and "updated" in the above "objects
> used in the statement have updated"?
>
> I'm inclined to add "since the previous use of the prepared statement" into
> also the second description, to make it clear. But if we do that, it's
> better
> to merge the above two description into one, as follows?
>
> whenever database objects used in the statement have undergone
> - definitional (DDL) changes since the previous use of the prepared
> + definitional (DDL) changes or the planner statistics of them have
> + been updated since the previous use of the prepared
> statement. Also, if the value of <xref linkend="guc-search-path"/>
> changes
>
>
> +1 for documenting this case since I just spent time reading code last
week for it. and
+1 for the above sentence to describe this case.

--
Best Regards
Andy Fan