Thread: [doc] plan invalidation when statistics are update
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? Regards, -- Atsushi Torikoshi
Attachment
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 manualdoesn'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? Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION
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. Regards, - Atsushi Torikoshi
Attachment
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, themanual 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! Barring any objection, I will commit the patch. Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION
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, themanual 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 Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION
On Wed, Nov 25, 2020 at 1:13 PM Fujii Masao <masao.fujii@oss.nttdata.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
On 2020-11-25 14:13, Fujii Masao 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"? You're right. > 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 Thanks, it seems better. Regards,
On 2020/11/26 14:30, torikoshia wrote: > On 2020-11-25 14:13, Fujii Masao 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, themanual 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) changesundergone', 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"? > > You're right. > >> 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 > > Thanks, it seems better. Pushed. Thanks! > +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. Thanks! Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION