Re: [doc] plan invalidation when statistics are update

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

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION