On Mon, Mar 14, 2022 at 05:23:54PM -0400, Robert Haas wrote:
> On Fri, Jan 21, 2022 at 1:31 PM Tomas Vondra <tomas.von...@enterprisedb.com>
> wrote:
> > [ new patch ]
>
> This patch is originally by Justin. The latest version is by Tomas. I
> think the next step is for Justin to say whether he's OK with the
> latest version that Tomas posted. If he is, then I suggest that he
> also mark it Ready for Committer, and that Tomas commit it. If he's
> not, he should say what he wants changed and either post a new version
> himself or wait for Tomas to do that.
Yes, I think it can be Ready. Done.
I amended some of Tomas' changes (see 0003, attached as txt).
@cfbot: the *.patch file is for your consumption, and the others are only there
to show my changes.
> I think the fact that is classified as a "Bug Fix" in the CommitFest
> application is not particularly good. I would prefer to see it
> classified under "Documentation". I'm prepared to concede that
> documentation can have bugs as a general matter, but nobody's data is
> getting eaten because the documentation wasn't updated. In fact, this
> is the fourth patch from the "bug fix" section I've studied this
> afternoon, and, well, none of them have been back-patchable code
> defects.
In fact, I consider this to be back-patchable back to v10. IMO it's an
omission that this isn't documented. Not all bugs cause data to be eaten. If
someone reads the existing documentation, they might conclude that their
partitioned tables don't need to be analyzed, and they would've been better
served by not reading the docs.
--
Justin
>From c237d91ec258ebbf24ebd3a38e139777582817f6 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Thu, 22 Jul 2021 16:06:18 -0500
Subject: [PATCH] documentation deficiencies for ANALYZE of partitioned tables
This is partially extracted from 1b5617eb844cd2470a334c1d2eec66cf9b39c41a,
which was reverted.
---
doc/src/sgml/maintenance.sgml | 29 +++++++++++++++++++++++++++++
doc/src/sgml/ref/analyze.sgml | 32 +++++++++++++++++++++++++++++---
2 files changed, 58 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 36f975b1e5b..34d72dba784 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -290,6 +290,15 @@
to meaningful statistical changes.
</para>
+ <para>
+ Tuples changed in partitions and inheritance children do not trigger
+ analyze on the parent table. If the parent table is empty or rarely
+ changed, it may never be processed by autovacuum, and the statistics for
+ the inheritance tree as a whole won't be collected. It is necessary to
+ run <command>ANALYZE</command> on the parent table manually in order to
+ keep the statistics up to date.
+ </para>
+
<para>
As with vacuuming for space recovery, frequent updates of statistics
are more useful for heavily-updated tables than for seldom-updated
@@ -347,6 +356,19 @@
<command>ANALYZE</command> commands on those tables on a suitable schedule.
</para>
</tip>
+
+ <tip>
+ <para>
+ The autovacuum daemon does not issue <command>ANALYZE</command> commands
+ for partitioned tables. Inheritance parents will only be analyzed if the
+ parent itself is changed - changes to child tables do not trigger
+ autoanalyze on the parent table. If your queries require statistics on
+ parent tables for proper planning, it is necessary to periodically run
+ a manual <command>ANALYZE</command> on those tables to keep the statistics
+ up to date.
+ </para>
+ </tip>
+
</sect2>
<sect2 id="vacuum-for-visibility-map">
@@ -819,6 +841,13 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu
since the last <command>ANALYZE</command>.
</para>
+ <para>
+ Partitioned tables are not processed by autovacuum. Statistics
+ should be collected by running a manual <command>ANALYZE</command> when it is
+ first populated, and again whenever the distribution of data in its
+ partitions changes significantly.
+ </para>
+
<para>
Temporary tables cannot be accessed by autovacuum. Therefore,
appropriate vacuum and analyze operations should be performed via
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index c423aeeea5e..8268ba87f63 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -263,9 +263,35 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
</para>
<para>
- If any of the child tables are foreign tables whose foreign data wrappers
- do not support <command>ANALYZE</command>, those child tables are ignored while
- gathering inheritance statistics.
+ For partitioned tables, <command>ANALYZE</command> gathers statistics by
+ sampling rows from all partitions; in addition, it will recurse into each
+ partition and update its statistics. Each leaf partition is analyzed only
+ once, even with multi-level partitioning. No statistics are collected for
+ only the parent table (without data from its partitions), because with
+ partitioning it's guaranteed to be empty.
+ </para>
+
+ <para>
+ By constrast, if the table being analyzed has inheritance children,
+ <command>ANALYZE</command> gathers two sets of statistics: one on the rows
+ of the parent table only, and a second including rows of both the parent
+ table and all of its children. This second set of statistics is needed when
+ planning queries that process the inheritance tree as a whole. The child
+ tables themselves are not individually analyzed in this case.
+ </para>
+
+ <para>
+ The autovacuum daemon does not process partitioned tables, nor does it
+ process inheritance parents if only the children are ever modified.
+ It is usually necessary to periodically run a manual
+ <command>ANALYZE</command> to keep the statistics of the table hierarchy
+ up to date.
+ </para>
+
+ <para>
+ If any child tables or partitions are foreign tables whose foreign
+ data wrappers do not support <command>ANALYZE</command>, those tables are
+ ignored while gathering inheritance statistics.
</para>
<para>
--
2.17.1
>From ed314a6b257d22fe21b2d13fc52a5d3fe16137b0 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Thu, 22 Jul 2021 16:06:18 -0500
Subject: [PATCH 1/3] documentation deficiencies for ANALYZE of partitioned
tables
This is partially extracted from 1b5617eb844cd2470a334c1d2eec66cf9b39c41a,
which was reverted.
---
doc/src/sgml/maintenance.sgml | 27 ++++++++++++++++++++++++++
doc/src/sgml/ref/analyze.sgml | 36 ++++++++++++++++++++++-------------
2 files changed, 50 insertions(+), 13 deletions(-)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 36f975b1e5b..b7c806cc906 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -290,6 +290,14 @@
to meaningful statistical changes.
</para>
+ <para>
+ Tuples changed in partitions and inheritance children do not count towards
+ analyze on the parent table. If the parent table is empty or rarely
+ changed, it may never be processed by autovacuum. It is necessary to
+ periodically manual run <command>ANALYZE</command> on the parent table to
+ keep the statistics of its table hierarchy up to date.
+ </para>
+
<para>
As with vacuuming for space recovery, frequent updates of statistics
are more useful for heavily-updated tables than for seldom-updated
@@ -347,6 +355,18 @@
<command>ANALYZE</command> commands on those tables on a suitable
schedule.
</para>
</tip>
+
+ <tip>
+ <para>
+ The autovacuum daemon does not issue <command>ANALYZE</command> commands
for
+ partitioned tables. Inheritance parents will only be analyzed if the
+ parent itself is changed - changes to child tables do not trigger
+ autoanalyze on the parent table. It is necessary to periodically run a
+ manual <command>ANALYZE</command> to keep the statistics of the table
+ hierarchy up to date.
+ </para>
+ </tip>
+
</sect2>
<sect2 id="vacuum-for-visibility-map">
@@ -819,6 +839,13 @@ analyze threshold = analyze base threshold + analyze scale
factor * number of tu
since the last <command>ANALYZE</command>.
</para>
+ <para>
+ Partitioned tables are not processed by autovacuum. Statistics
+ should be collected by running a manual <command>ANALYZE</command> when it
is
+ first populated, and updated whenever the distribution of data in its
+ partitions changes significantly.
+ </para>
+
<para>
Temporary tables cannot be accessed by autovacuum. Therefore,
appropriate vacuum and analyze operations should be performed via
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index c423aeeea5e..3bf904e36f9 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -250,22 +250,32 @@ ANALYZE [ VERBOSE ] [ <replaceable
class="parameter">table_and_columns</replacea
</para>
<para>
- If the table being analyzed has one or more children,
- <command>ANALYZE</command> will gather statistics twice: once on the
- rows of the parent table only, and a second time on the rows of the
- parent table with all of its children. This second set of statistics
- is needed when planning queries that traverse the entire inheritance
- tree. The autovacuum daemon, however, will only consider inserts or
- updates on the parent table itself when deciding whether to trigger an
- automatic analyze for that table. If that table is rarely inserted into
- or updated, the inheritance statistics will not be up to date unless you
- run <command>ANALYZE</command> manually.
+ If the table being analyzed is partitioned, <command>ANALYZE</command>
+ will gather statistics by sampling blocks randomly from its partitions;
+ in addition, it will recurse into each partition and update its statistics.
+ (However, in multi-level partitioning scenarios, each leaf partition
+ will only be analyzed once.)
+ By constrast, if the table being analyzed has inheritance children,
+ <command>ANALYZE</command> will gather statistics for it twice:
+ once on the rows of the parent table only, and a second time on the
+ rows of the parent table with all of its children. This second set of
+ statistics is needed when planning queries that traverse the entire
+ inheritance tree. The child tables themselves are not individually
+ analyzed in this case.
</para>
<para>
- If any of the child tables are foreign tables whose foreign data wrappers
- do not support <command>ANALYZE</command>, those child tables are ignored
while
- gathering inheritance statistics.
+ The autovacuum daemon does not process partitioned tables, nor does it
+ process inheritance parents due to modifications to its child tables.
+ It is usually necessary to periodically run a manual
+ <command>ANALYZE</command> to keep the statistics of the table hierarchy
+ up to date.
+ </para>
+
+ <para>
+ If any child tables or partitions are foreign tables whose foreign
+ data wrappers do not support <command>ANALYZE</command>, those tables are
+ ignored while gathering inheritance statistics.
</para>
<para>
--
2.17.1
>From c2203a7757f574922edf70b4cd5d85ed4edff7da Mon Sep 17 00:00:00 2001
From: Tomas Vondra <tomas.von...@postgresql.org>
Date: Fri, 21 Jan 2022 18:05:56 +0100
Subject: [PATCH 2/3] minor changes / rewordings
---
doc/src/sgml/maintenance.sgml | 20 +++++++++---------
doc/src/sgml/ref/analyze.sgml | 38 +++++++++++++++++++++++++----------
2 files changed, 38 insertions(+), 20 deletions(-)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index b7c806cc906..9e6909cd748 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -291,11 +291,12 @@
</para>
<para>
- Tuples changed in partitions and inheritance children do not count towards
+ Tuples changed in partitions and inheritance children do not trigger
analyze on the parent table. If the parent table is empty or rarely
- changed, it may never be processed by autovacuum. It is necessary to
- periodically manual run <command>ANALYZE</command> on the parent table to
- keep the statistics of its table hierarchy up to date.
+ changed, it may never be processed by autovacuum, and the statistics for
+ the inheritance tree as a whole won't be collected. It is necessary to
+ run <command>ANALYZE</command> on the parent table manually, to keep
+ the statistics up to date.
</para>
<para>
@@ -358,12 +359,13 @@
<tip>
<para>
- The autovacuum daemon does not issue <command>ANALYZE</command> commands
for
- partitioned tables. Inheritance parents will only be analyzed if the
+ The autovacuum daemon may not issue <command>ANALYZE</command> commands
+ for partitioned tables. Inheritance parents will only be analyzed if the
parent itself is changed - changes to child tables do not trigger
- autoanalyze on the parent table. It is necessary to periodically run a
- manual <command>ANALYZE</command> to keep the statistics of the table
- hierarchy up to date.
+ autoanalyze on the parent table. If your queries require statistics on
+ parent tables for proper planning, it's necessary to periodically run
+ a manual <command>ANALYZE</command> on those tables to keep the statistics
+ up to date.
</para>
</tip>
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 3bf904e36f9..764cfb19718 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -250,18 +250,34 @@ ANALYZE [ VERBOSE ] [ <replaceable
class="parameter">table_and_columns</replacea
</para>
<para>
- If the table being analyzed is partitioned, <command>ANALYZE</command>
- will gather statistics by sampling blocks randomly from its partitions;
- in addition, it will recurse into each partition and update its statistics.
- (However, in multi-level partitioning scenarios, each leaf partition
- will only be analyzed once.)
+ If the table being analyzed has one or more children,
+ <command>ANALYZE</command> will gather statistics twice: once on the
+ rows of the parent table only, and a second time on the rows of the
+ parent table with all of its children. This second set of statistics
+ is needed when planning queries that traverse the entire inheritance
+ tree. The autovacuum daemon, however, will only consider inserts or
+ updates on the parent table itself when deciding whether to trigger an
+ automatic analyze for that table. If that table is rarely inserted into
+ or updated, the inheritance statistics will not be up to date unless you
+ run <command>ANALYZE</command> manually.
+ </para>
+
+ <para>
+ For partitioned tables, <command>ANALYZE</command> gathers statistics by
+ sampling rows from all partitions; in addition, it will recurse into each
+ partition and update its statistics. Each leaf partition is analyzed only
+ once, even with multi-level partitioning. No statistics are collected for
+ the parent table (ignoring data from partitions), because with partitioning
+ it's guaranteed to be empty.
+ </para>
+
+ <para>
By constrast, if the table being analyzed has inheritance children,
- <command>ANALYZE</command> will gather statistics for it twice:
- once on the rows of the parent table only, and a second time on the
- rows of the parent table with all of its children. This second set of
- statistics is needed when planning queries that traverse the entire
- inheritance tree. The child tables themselves are not individually
- analyzed in this case.
+ <command>ANALYZE</command> gathers two sets of statistics: once on the rows
+ of the parent table only, and a second one including rows of both the
parent
+ table and all of its children. This second set of statistics is needed
when
+ planning queries that process the whole inheritance tree at once. The
child
+ tables themselves are not individually analyzed in this case.
</para>
<para>
--
2.17.1
>From 773d02724c8aa4294f46b183afccd021be9dd610 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryz...@telsasoft.com>
Date: Tue, 15 Mar 2022 13:38:08 -0500
Subject: [PATCH 3/3] f!3
---
doc/src/sgml/maintenance.sgml | 10 +++++-----
doc/src/sgml/ref/analyze.sgml | 12 ++++++------
2 files changed, 11 insertions(+), 11 deletions(-)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 9e6909cd748..34d72dba784 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -295,8 +295,8 @@
analyze on the parent table. If the parent table is empty or rarely
changed, it may never be processed by autovacuum, and the statistics for
the inheritance tree as a whole won't be collected. It is necessary to
- run <command>ANALYZE</command> on the parent table manually, to keep
- the statistics up to date.
+ run <command>ANALYZE</command> on the parent table manually in order to
+ keep the statistics up to date.
</para>
<para>
@@ -359,11 +359,11 @@
<tip>
<para>
- The autovacuum daemon may not issue <command>ANALYZE</command> commands
+ The autovacuum daemon does not issue <command>ANALYZE</command> commands
for partitioned tables. Inheritance parents will only be analyzed if the
parent itself is changed - changes to child tables do not trigger
autoanalyze on the parent table. If your queries require statistics on
- parent tables for proper planning, it's necessary to periodically run
+ parent tables for proper planning, it is necessary to periodically run
a manual <command>ANALYZE</command> on those tables to keep the statistics
up to date.
</para>
@@ -844,7 +844,7 @@ analyze threshold = analyze base threshold + analyze scale
factor * number of tu
<para>
Partitioned tables are not processed by autovacuum. Statistics
should be collected by running a manual <command>ANALYZE</command> when it
is
- first populated, and updated whenever the distribution of data in its
+ first populated, and again whenever the distribution of data in its
partitions changes significantly.
</para>
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 764cfb19718..8268ba87f63 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -267,22 +267,22 @@ ANALYZE [ VERBOSE ] [ <replaceable
class="parameter">table_and_columns</replacea
sampling rows from all partitions; in addition, it will recurse into each
partition and update its statistics. Each leaf partition is analyzed only
once, even with multi-level partitioning. No statistics are collected for
- the parent table (ignoring data from partitions), because with partitioning
- it's guaranteed to be empty.
+ only the parent table (without data from its partitions), because with
+ partitioning it's guaranteed to be empty.
</para>
<para>
By constrast, if the table being analyzed has inheritance children,
- <command>ANALYZE</command> gathers two sets of statistics: once on the rows
- of the parent table only, and a second one including rows of both the
parent
+ <command>ANALYZE</command> gathers two sets of statistics: one on the rows
+ of the parent table only, and a second including rows of both the parent
table and all of its children. This second set of statistics is needed
when
- planning queries that process the whole inheritance tree at once. The
child
+ planning queries that process the inheritance tree as a whole. The child
tables themselves are not individually analyzed in this case.
</para>
<para>
The autovacuum daemon does not process partitioned tables, nor does it
- process inheritance parents due to modifications to its child tables.
+ process inheritance parents if only the children are ever modified.
It is usually necessary to periodically run a manual
<command>ANALYZE</command> to keep the statistics of the table hierarchy
up to date.
--
2.17.1
