Re: [doc] plan invalidation when statistics are update

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, 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.

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