[doc] plan invalidation when statistics are update
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?
Regards,
--
Atsushi Torikoshi
Attachments:
v1-0001-add-an-example-of-replanning-generic-plan.patchtext/x-diff; charset=us-ascii; name=v1-0001-add-an-example-of-replanning-generic-plan.patchDownload
From d71dbb0b100f706f19d92175b72f9e1833a8a442 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <torikoshia@oss.nttdata.com>
Date: Thu, 12 Nov 2020 17:18:29 +0900
Subject: [PATCH v1] When the planner statistics are updated, generic plans are
invalidated and PostgreSQL recreates. However, the manual didn't explain it
explicitly. This patch adds this case as a example.
---
doc/src/sgml/ref/prepare.sgml | 5 ++++-
1 file changed, 4 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/prepare.sgml b/doc/src/sgml/ref/prepare.sgml
index 57a34ff83c..4075de5689 100644
--- a/doc/src/sgml/ref/prepare.sgml
+++ b/doc/src/sgml/ref/prepare.sgml
@@ -185,7 +185,10 @@ EXPLAIN EXECUTE <replaceable>name</replaceable>(<replaceable>parameter_values</r
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. For example, when the planner statistics of the statement
+ are updated, <productname>PostgreSQL</productname> re-analyzes and
+ re-plans the statement.
+ Also, if the value of <xref linkend="guc-search-path"/> changes
from one use to the next, the statement will be re-parsed using the new
<varname>search_path</varname>. (This latter behavior is new as of
<productname>PostgreSQL</productname> 9.3.) These rules make use of a
--
2.18.1
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?
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
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.
Regards,
-
Atsushi Torikoshi
Attachments:
v2-0001-add-an-example-of-replanning-generic-plan.patchtext/x-diff; name=v2-0001-add-an-example-of-replanning-generic-plan.patchDownload
From f8c051e57e1ca15e2b91d3e69fe0531c0b7bf7ca Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <torikoshia@oss.nttdata.com>
Date: Thu, 19 Nov 2020 13:23:18 +0900
Subject: [PATCH v2] When the planner statistics are updated, generic plans are
invalidated and PostgreSQL recreates. However, the manual didn't explain it
explicitly. This patch adds an explanation for this case.
---
doc/src/sgml/ref/prepare.sgml | 4 +++-
1 file changed, 3 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/prepare.sgml b/doc/src/sgml/ref/prepare.sgml
index 57a34ff83c..5a6dd481bc 100644
--- a/doc/src/sgml/ref/prepare.sgml
+++ b/doc/src/sgml/ref/prepare.sgml
@@ -185,7 +185,9 @@ EXPLAIN EXECUTE <replaceable>name</replaceable>(<replaceable>parameter_values</r
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. Also, if the value of <xref linkend="guc-search-path"/> changes
from one use to the next, the statement will be re-parsed using the new
<varname>search_path</varname>. (This latter behavior is new as of
<productname>PostgreSQL</productname> 9.3.) These rules make use of a
--
2.18.1
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!
Barring any objection, I will commit the patch.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
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"/> changesRegards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
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 ofdatabase
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
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,
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"/> changesThanks, 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