Re: [doc] plan invalidation when statistics are update - Mailing list pgsql-hackers
From | Andy Fan |
---|---|
Subject | Re: [doc] plan invalidation when statistics are update |
Date | |
Msg-id | CAKU4AWo7dfk4nOTQ1u7SkjK8ahTWb=4JdA8VqwBxLaNENz5yZg@mail.gmail.com Whole thread Raw |
In response to | Re: [doc] plan invalidation when statistics are update (Fujii Masao <masao.fujii@oss.nttdata.com>) |
List | pgsql-hackers |
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
pgsql-hackers by date: