Re: [doc] plan invalidation when statistics are update

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,