Re: [doc] plan invalidation when statistics are update - Mailing list pgsql-hackers

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,