public inbox for [email protected]
help / color / mirror / Atom feed[PATCH 1/5] pg_rewind: Move syncTargetDirectory() to file_ops.c
13+ messages / 8 participants
[nested] [flat]
* [PATCH 1/5] pg_rewind: Move syncTargetDirectory() to file_ops.c
@ 2020-08-19 12:34 Heikki Linnakangas <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Heikki Linnakangas @ 2020-08-19 12:34 UTC (permalink / raw)
For consistency. All the other low-level functions that operate on the
target directory are in file_ops.c.
---
src/bin/pg_rewind/file_ops.c | 19 +++++++++++++++++++
src/bin/pg_rewind/file_ops.h | 1 +
src/bin/pg_rewind/pg_rewind.c | 22 +---------------------
src/bin/pg_rewind/pg_rewind.h | 1 +
4 files changed, 22 insertions(+), 21 deletions(-)
diff --git a/src/bin/pg_rewind/file_ops.c b/src/bin/pg_rewind/file_ops.c
index b3bf091c546..55439db20ba 100644
--- a/src/bin/pg_rewind/file_ops.c
+++ b/src/bin/pg_rewind/file_ops.c
@@ -19,6 +19,7 @@
#include <unistd.h>
#include "common/file_perm.h"
+#include "common/file_utils.h"
#include "file_ops.h"
#include "filemap.h"
#include "pg_rewind.h"
@@ -266,6 +267,24 @@ remove_target_symlink(const char *path)
dstpath);
}
+/*
+ * Sync target data directory to ensure that modifications are safely on disk.
+ *
+ * We do this once, for the whole data directory, for performance reasons. At
+ * the end of pg_rewind's run, the kernel is likely to already have flushed
+ * most dirty buffers to disk. Additionally fsync_pgdata uses a two-pass
+ * approach (only initiating writeback in the first pass), which often reduces
+ * the overall amount of IO noticeably.
+ */
+void
+sync_target_dir(void)
+{
+ if (!do_sync || dry_run)
+ return;
+
+ fsync_pgdata(datadir_target, PG_VERSION_NUM);
+}
+
/*
* Read a file into memory. The file to be read is <datadir>/<path>.
diff --git a/src/bin/pg_rewind/file_ops.h b/src/bin/pg_rewind/file_ops.h
index 025f24141c9..d8466385cf5 100644
--- a/src/bin/pg_rewind/file_ops.h
+++ b/src/bin/pg_rewind/file_ops.h
@@ -19,6 +19,7 @@ extern void remove_target_file(const char *path, bool missing_ok);
extern void truncate_target_file(const char *path, off_t newsize);
extern void create_target(file_entry_t *t);
extern void remove_target(file_entry_t *t);
+extern void sync_target_dir(void);
extern char *slurpFile(const char *datadir, const char *path, size_t *filesize);
diff --git a/src/bin/pg_rewind/pg_rewind.c b/src/bin/pg_rewind/pg_rewind.c
index 23fc749e445..c9b9e480c0f 100644
--- a/src/bin/pg_rewind/pg_rewind.c
+++ b/src/bin/pg_rewind/pg_rewind.c
@@ -20,7 +20,6 @@
#include "catalog/pg_control.h"
#include "common/controldata_utils.h"
#include "common/file_perm.h"
-#include "common/file_utils.h"
#include "common/restricted_token.h"
#include "common/string.h"
#include "fe_utils/recovery_gen.h"
@@ -38,7 +37,6 @@ static void createBackupLabel(XLogRecPtr startpoint, TimeLineID starttli,
static void digestControlFile(ControlFileData *ControlFile, char *source,
size_t size);
-static void syncTargetDirectory(void);
static void getRestoreCommand(const char *argv0);
static void sanityChecks(void);
static void findCommonAncestorTimeline(XLogRecPtr *recptr, int *tliIndex);
@@ -455,7 +453,7 @@ main(int argc, char **argv)
if (showprogress)
pg_log_info("syncing target data directory");
- syncTargetDirectory();
+ sync_target_dir();
if (writerecoveryconf && !dry_run)
WriteRecoveryConfig(conn, datadir_target,
@@ -803,24 +801,6 @@ digestControlFile(ControlFileData *ControlFile, char *src, size_t size)
checkControlFile(ControlFile);
}
-/*
- * Sync target data directory to ensure that modifications are safely on disk.
- *
- * We do this once, for the whole data directory, for performance reasons. At
- * the end of pg_rewind's run, the kernel is likely to already have flushed
- * most dirty buffers to disk. Additionally fsync_pgdata uses a two-pass
- * approach (only initiating writeback in the first pass), which often reduces
- * the overall amount of IO noticeably.
- */
-static void
-syncTargetDirectory(void)
-{
- if (!do_sync || dry_run)
- return;
-
- fsync_pgdata(datadir_target, PG_VERSION_NUM);
-}
-
/*
* Get value of GUC parameter restore_command from the target cluster.
*
diff --git a/src/bin/pg_rewind/pg_rewind.h b/src/bin/pg_rewind/pg_rewind.h
index 8a9319ed675..67f90c2a38c 100644
--- a/src/bin/pg_rewind/pg_rewind.h
+++ b/src/bin/pg_rewind/pg_rewind.h
@@ -24,6 +24,7 @@ extern char *datadir_source;
extern char *connstr_source;
extern bool showprogress;
extern bool dry_run;
+extern bool do_sync;
extern int WalSegSz;
/* Target history */
--
2.20.1
--------------D93EDEBFB124D563B723F4BD
Content-Type: text/x-patch; charset=UTF-8;
name="0002-Refactor-pg_rewind-for-more-clear-decision-making.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename*0="0002-Refactor-pg_rewind-for-more-clear-decision-making.patch"
^ permalink raw reply [nested|flat] 13+ messages in thread
* document the need to analyze partitioned tables
@ 2021-09-13 03:54 Justin Pryzby <[email protected]>
0 siblings, 2 replies; 13+ messages in thread
From: Justin Pryzby @ 2021-09-13 03:54 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: yuzuko <[email protected]>; pgsql-hackers
Adding -hackers, sorry for the duplicate.
This seems to be deficient, citing
https://www.postgresql.org/message-id/flat/0d1b394b-bec9-8a71-a336-44df7078b295%40gmail.com
I'm proposing something like the attached. Ideally, there would be a central
place to put details, and the other places could refer to that.
Since the autoanalyze patch was reverted, this should be easily applied to
backbranches, which is probably most of its value.
commit 4ad2c8f6fd8eb26d76b226e68d3fdb8f0658f113
Author: Justin Pryzby <[email protected]>
Date: Thu Jul 22 16:06:18 2021 -0500
documentation deficiencies for ANALYZE of partitioned tables
This is partially extracted from 1b5617eb844cd2470a334c1d2eec66cf9b39c41a,
which was reverted.
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 36f975b1e5..decfabff5d 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 inheritence 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 run an manual <command>ANALYZE</command> to keep the statistics
+ of the 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. Inheritence parents will only be analyzed if the
+ parent is changed - changes to child tables do not trigger autoanalyze on
+ the parent table. It is necessary to periodically run an manual
+ <command>ANALYZE</command> to keep the statistics of the table hierarchy up to
+ date.
+ </para>
+ </tip>
+
</sect2>
<sect2 id="vacuum-for-visibility-map">
@@ -817,6 +837,18 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu
</programlisting>
is compared to the total number of tuples inserted, updated, or deleted
since the last <command>ANALYZE</command>.
+
+ Partitioned tables are not processed by autovacuum, and their statistics
+ should be updated by manually running <command>ANALYZE</command> when the
+ table is first populated, and whenever the distribution of data in its
+ partitions changes significantly.
+ </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>
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index 89ff58338e..b84853fd6f 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -1765,9 +1765,11 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
<title>Run <command>ANALYZE</command> Afterwards</title>
<para>
+
Whenever you have significantly altered the distribution of data
within a table, running <link linkend="sql-analyze"><command>ANALYZE</command></link> is strongly recommended. This
includes bulk loading large amounts of data into the table. Running
+
<command>ANALYZE</command> (or <command>VACUUM ANALYZE</command>)
ensures that the planner has up-to-date statistics about the
table. With no statistics or obsolete statistics, the planner might
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index c423aeeea5..20ffbc2d7a 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -250,22 +250,33 @@ 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 or inheritence
+ parents. It is usually necessary to periodically run a manual
+ <command>ANALYZE</command> to keep the statistics of the table hierarchy
+ up to date (except for nonempty inheritence parents which undergo
+ modifications of their own table data).
+ See...
+ </para>
+
+ <para>
+ If any of the 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>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: document the need to analyze partitioned tables
@ 2021-09-13 04:38 Zhihong Yu <[email protected]>
parent: Justin Pryzby <[email protected]>
1 sibling, 0 replies; 13+ messages in thread
From: Zhihong Yu @ 2021-09-13 04:38 UTC (permalink / raw)
To: Justin Pryzby <[email protected]>; +Cc: Álvaro Herrera <[email protected]>; yuzuko <[email protected]>; pgsql-hackers
On Sun, Sep 12, 2021 at 8:54 PM Justin Pryzby <[email protected]> wrote:
> Adding -hackers, sorry for the duplicate.
>
> This seems to be deficient, citing
>
> https://www.postgresql.org/message-id/flat/0d1b394b-bec9-8a71-a336-44df7078b295%40gmail.com
>
> I'm proposing something like the attached. Ideally, there would be a
> central
> place to put details, and the other places could refer to that.
>
> Since the autoanalyze patch was reverted, this should be easily applied to
> backbranches, which is probably most of its value.
>
> commit 4ad2c8f6fd8eb26d76b226e68d3fdb8f0658f113
> Author: Justin Pryzby <[email protected]>
> Date: Thu Jul 22 16:06:18 2021 -0500
>
> documentation deficiencies for ANALYZE of partitioned tables
>
> This is partially extracted from
> 1b5617eb844cd2470a334c1d2eec66cf9b39c41a,
> which was reverted.
>
> diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
> index 36f975b1e5..decfabff5d 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 inheritence 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 run an manual <command>ANALYZE</command> to keep the
> statistics
> + of the 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. Inheritence parents will only be analyzed if the
> + parent is changed - changes to child tables do not trigger
> autoanalyze on
> + the parent table. It is necessary to periodically run an manual
> + <command>ANALYZE</command> to keep the statistics of the table
> hierarchy up to
> + date.
> + </para>
> + </tip>
> +
> </sect2>
>
> <sect2 id="vacuum-for-visibility-map">
> @@ -817,6 +837,18 @@ analyze threshold = analyze base threshold + analyze
> scale factor * number of tu
> </programlisting>
> is compared to the total number of tuples inserted, updated, or
> deleted
> since the last <command>ANALYZE</command>.
> +
> + Partitioned tables are not processed by autovacuum, and their
> statistics
> + should be updated by manually running <command>ANALYZE</command> when
> the
> + table is first populated, and whenever the distribution of data in its
> + partitions changes significantly.
> + </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>
> diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
> index 89ff58338e..b84853fd6f 100644
> --- a/doc/src/sgml/perform.sgml
> +++ b/doc/src/sgml/perform.sgml
> @@ -1765,9 +1765,11 @@ SELECT * FROM x, y, a, b, c WHERE something AND
> somethingelse;
> <title>Run <command>ANALYZE</command> Afterwards</title>
>
> <para>
> +
> Whenever you have significantly altered the distribution of data
> within a table, running <link
> linkend="sql-analyze"><command>ANALYZE</command></link> is strongly
> recommended. This
> includes bulk loading large amounts of data into the table. Running
> +
> <command>ANALYZE</command> (or <command>VACUUM ANALYZE</command>)
> ensures that the planner has up-to-date statistics about the
> table. With no statistics or obsolete statistics, the planner might
> diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
> index c423aeeea5..20ffbc2d7a 100644
> --- a/doc/src/sgml/ref/analyze.sgml
> +++ b/doc/src/sgml/ref/analyze.sgml
> @@ -250,22 +250,33 @@ 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 or
> inheritence
> + parents. It is usually necessary to periodically run a manual
> + <command>ANALYZE</command> to keep the statistics of the table
> hierarchy
> + up to date (except for nonempty inheritence parents which undergo
> + modifications of their own table data).
> + See...
> + </para>
> +
> + <para>
> + If any of the 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>
>
>
> Hi,
Minor comment:
periodically run an manual -> periodically run a manual
Cheers
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: document the need to analyze partitioned tables
@ 2021-10-08 12:58 Justin Pryzby <[email protected]>
parent: Justin Pryzby <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Justin Pryzby @ 2021-10-08 12:58 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: yuzuko <[email protected]>; pgsql-hackers
Cleaned up and attached as a .patch.
The patch implementing autoanalyze on partitioned tables should revert relevant
portions of this patch.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: document the need to analyze partitioned tables
@ 2022-01-21 17:21 Tomas Vondra <[email protected]>
parent: Justin Pryzby <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Tomas Vondra @ 2022-01-21 17:21 UTC (permalink / raw)
To: Justin Pryzby <[email protected]>; Álvaro Herrera <[email protected]>; +Cc: yuzuko <[email protected]>; pgsql-hackers
Hi,
On 10/8/21 14:58, Justin Pryzby wrote:
> Cleaned up and attached as a .patch.
>
> The patch implementing autoanalyze on partitioned tables should
> revert relevant portions of this patch.
I went through this patch and I'd like to propose a couple changes, per
the 0002 patch:
1) I've reworded the changes in maintenance.sgml a bit. It sounded a bit
strange before, but I'm not a native speaker so maybe it's worse ...
2) Remove unnecessary whitespace changes in perform.sgml.
3) Simplify the analyze.sgml changes a bit - it was trying to cram too
much stuff into a single paragraph, so I split that.
Does that seem OK, or did omit something important?
FWIW I think it's really confusing we have inheritance and partitioning,
and partitions and child tables. And sometimes we use partitioning in
the generic sense (i.e. including the inheritance approach), and
sometimes only the declarative variant. Same for partitions vs child
tables. I can't even imagine how confusing this has to be for people
just learning this stuff. They must be in permanent WTF?! state ...
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Attachments:
[text/x-patch] v2-0001-documentation-deficiencies-for-ANALYZE-of-partiti.patch (5.9K, ../../[email protected]/2-v2-0001-documentation-deficiencies-for-ANALYZE-of-partiti.patch)
download | inline diff:
From c5105f2f067dff553045792d4a171fc835fe2109 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email protected]>
Date: Thu, 22 Jul 2021 16:06:18 -0500
Subject: [PATCH v2 1/2] 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/perform.sgml | 2 ++
doc/src/sgml/ref/analyze.sgml | 36 ++++++++++++++++++++++-------------
3 files changed, 52 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/perform.sgml b/doc/src/sgml/perform.sgml
index 89ff58338e5..b84853fd6ff 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -1765,9 +1765,11 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
<title>Run <command>ANALYZE</command> Afterwards</title>
<para>
+
Whenever you have significantly altered the distribution of data
within a table, running <link linkend="sql-analyze"><command>ANALYZE</command></link> is strongly recommended. This
includes bulk loading large amounts of data into the table. Running
+
<command>ANALYZE</command> (or <command>VACUUM ANALYZE</command>)
ensures that the planner has up-to-date statistics about the
table. With no statistics or obsolete statistics, the planner might
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index c8fcebc1612..ea78f0d0387 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. See...
+ </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.31.1
[text/x-patch] v2-0002-minor-changes-rewordings.patch (5.7K, ../../[email protected]/3-v2-0002-minor-changes-rewordings.patch)
download | inline diff:
From 993a42ee2c73a1098845319b30811b5b7497f2f8 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Fri, 21 Jan 2022 18:05:56 +0100
Subject: [PATCH v2 2/2] minor changes / rewordings
---
doc/src/sgml/maintenance.sgml | 20 +++++++++---------
doc/src/sgml/perform.sgml | 2 --
doc/src/sgml/ref/analyze.sgml | 38 +++++++++++++++++++++++++----------
3 files changed, 38 insertions(+), 22 deletions(-)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index b7c806cc906..2086dae61b2 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 relations 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/perform.sgml b/doc/src/sgml/perform.sgml
index b84853fd6ff..89ff58338e5 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -1765,11 +1765,9 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
<title>Run <command>ANALYZE</command> Afterwards</title>
<para>
-
Whenever you have significantly altered the distribution of data
within a table, running <link linkend="sql-analyze"><command>ANALYZE</command></link> is strongly recommended. This
includes bulk loading large amounts of data into the table. Running
-
<command>ANALYZE</command> (or <command>VACUUM ANALYZE</command>)
ensures that the planner has up-to-date statistics about the
table. With no statistics or obsolete statistics, the planner might
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index ea78f0d0387..1482fc2d4bb 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 child relations. 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.31.1
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: document the need to analyze partitioned tables
@ 2022-01-21 18:02 Justin Pryzby <[email protected]>
parent: Tomas Vondra <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Justin Pryzby @ 2022-01-21 18:02 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: Álvaro Herrera <[email protected]>; yuzuko <[email protected]>; pgsql-hackers
Thanks for looking at this
On Fri, Jan 21, 2022 at 06:21:57PM +0100, Tomas Vondra wrote:
> Hi,
>
> On 10/8/21 14:58, Justin Pryzby wrote:
> > Cleaned up and attached as a .patch.
> >
> > The patch implementing autoanalyze on partitioned tables should
> > revert relevant portions of this patch.
>
> I went through this patch and I'd like to propose a couple changes, per the
> 0002 patch:
>
> 1) I've reworded the changes in maintenance.sgml a bit. It sounded a bit
> strange before, but I'm not a native speaker so maybe it's worse ...
+ autoanalyze on the parent table. If your queries require statistics on
+ parent relations for proper planning, it's necessary to periodically run
You added two references to "relations", but everything else talks about
"tables", which is all that analyze processes.
> 2) Remove unnecessary whitespace changes in perform.sgml.
Those were a note to myself and to any reviewer - should that be updated too ?
> 3) Simplify the analyze.sgml changes a bit - it was trying to cram too much
> stuff into a single paragraph, so I split that.
>
> Does that seem OK, or did omit something important?
+ If the table being analyzed has one or more children,
I think you're referring to both legacy inheritance and and partitioning. That
should be more clear.
+ <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 child relations. This second set of statistics is needed when
I think should say ".. and all of its children".
> FWIW I think it's really confusing we have inheritance and partitioning, and
> partitions and child tables. And sometimes we use partitioning in the
> generic sense (i.e. including the inheritance approach), and sometimes only
> the declarative variant. Same for partitions vs child tables. I can't even
> imagine how confusing this has to be for people just learning this stuff.
> They must be in permanent WTF?! state ...
The docs were cleaned up some in 0c06534bd. At least the word "partitioned"
should never be used for legacy inheritance - but "partitioning" is.
--
Justin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: document the need to analyze partitioned tables
@ 2022-01-21 18:31 Tomas Vondra <[email protected]>
parent: Justin Pryzby <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Tomas Vondra @ 2022-01-21 18:31 UTC (permalink / raw)
To: Justin Pryzby <[email protected]>; +Cc: Álvaro Herrera <[email protected]>; yuzuko <[email protected]>; pgsql-hackers
On 1/21/22 19:02, Justin Pryzby wrote:
> Thanks for looking at this
>
> On Fri, Jan 21, 2022 at 06:21:57PM +0100, Tomas Vondra wrote:
>> Hi,
>>
>> On 10/8/21 14:58, Justin Pryzby wrote:
>>> Cleaned up and attached as a .patch.
>>>
>>> The patch implementing autoanalyze on partitioned tables should
>>> revert relevant portions of this patch.
>>
>> I went through this patch and I'd like to propose a couple changes, per the
>> 0002 patch:
>>
>> 1) I've reworded the changes in maintenance.sgml a bit. It sounded a bit
>> strange before, but I'm not a native speaker so maybe it's worse ...
>
> + autoanalyze on the parent table. If your queries require statistics on
> + parent relations for proper planning, it's necessary to periodically run
>
> You added two references to "relations", but everything else talks about
> "tables", which is all that analyze processes.
>
Good point, that should use "tables" too.
>> 2) Remove unnecessary whitespace changes in perform.sgml.
>
> Those were a note to myself and to any reviewer - should that be updated too ?
>
Ah, I see. I don't think that part needs updating - it talks about
having to analyze after a bulk load, and that applies to all tables
anyway. I don't think it needs to mention partitioned tables need an
analyze too.
>> 3) Simplify the analyze.sgml changes a bit - it was trying to cram too much
>> stuff into a single paragraph, so I split that.
>>
>> Does that seem OK, or did omit something important?
>
> + If the table being analyzed has one or more children,
>
> I think you're referring to both legacy inheritance and and partitioning. That
> should be more clear.
>
I think it applies to both types of partitioning - it's just that in the
declarative partitioning case the table is always empty so no stats with
inherit=false are built.
> + <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 child relations. This second set of statistics is needed when
>
> I think should say ".. and all of its children".
>
OK
>> FWIW I think it's really confusing we have inheritance and partitioning, and
>> partitions and child tables. And sometimes we use partitioning in the
>> generic sense (i.e. including the inheritance approach), and sometimes only
>> the declarative variant. Same for partitions vs child tables. I can't even
>> imagine how confusing this has to be for people just learning this stuff.
>> They must be in permanent WTF?! state ...
>
> The docs were cleaned up some in 0c06534bd. At least the word "partitioned"
> should never be used for legacy inheritance - but "partitioning" is.
>
OK
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Attachments:
[text/x-patch] v3-0001-documentation-deficiencies-for-ANALYZE-of-partiti.patch (5.9K, ../../[email protected]/2-v3-0001-documentation-deficiencies-for-ANALYZE-of-partiti.patch)
download | inline diff:
From c5105f2f067dff553045792d4a171fc835fe2109 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email protected]>
Date: Thu, 22 Jul 2021 16:06:18 -0500
Subject: [PATCH v3 1/2] 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/perform.sgml | 2 ++
doc/src/sgml/ref/analyze.sgml | 36 ++++++++++++++++++++++-------------
3 files changed, 52 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/perform.sgml b/doc/src/sgml/perform.sgml
index 89ff58338e5..b84853fd6ff 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -1765,9 +1765,11 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
<title>Run <command>ANALYZE</command> Afterwards</title>
<para>
+
Whenever you have significantly altered the distribution of data
within a table, running <link linkend="sql-analyze"><command>ANALYZE</command></link> is strongly recommended. This
includes bulk loading large amounts of data into the table. Running
+
<command>ANALYZE</command> (or <command>VACUUM ANALYZE</command>)
ensures that the planner has up-to-date statistics about the
table. With no statistics or obsolete statistics, the planner might
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index c8fcebc1612..ea78f0d0387 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. See...
+ </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.31.1
[text/x-patch] v3-0002-minor-changes-rewordings.patch (5.7K, ../../[email protected]/3-v3-0002-minor-changes-rewordings.patch)
download | inline diff:
From 6ea713010faf1d6a51a8f2d90f5c3741b97eb153 Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
Date: Fri, 21 Jan 2022 18:05:56 +0100
Subject: [PATCH v3 2/2] minor changes / rewordings
---
doc/src/sgml/maintenance.sgml | 20 +++++++++---------
doc/src/sgml/perform.sgml | 2 --
doc/src/sgml/ref/analyze.sgml | 38 +++++++++++++++++++++++++----------
3 files changed, 38 insertions(+), 22 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/perform.sgml b/doc/src/sgml/perform.sgml
index b84853fd6ff..89ff58338e5 100644
--- a/doc/src/sgml/perform.sgml
+++ b/doc/src/sgml/perform.sgml
@@ -1765,11 +1765,9 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
<title>Run <command>ANALYZE</command> Afterwards</title>
<para>
-
Whenever you have significantly altered the distribution of data
within a table, running <link linkend="sql-analyze"><command>ANALYZE</command></link> is strongly recommended. This
includes bulk loading large amounts of data into the table. Running
-
<command>ANALYZE</command> (or <command>VACUUM ANALYZE</command>)
ensures that the planner has up-to-date statistics about the
table. With no statistics or obsolete statistics, the planner might
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index ea78f0d0387..c89947ea9fe 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.31.1
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: document the need to analyze partitioned tables
@ 2022-03-14 21:23 Robert Haas <[email protected]>
parent: Tomas Vondra <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Robert Haas @ 2022-03-14 21:23 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: Justin Pryzby <[email protected]>; Álvaro Herrera <[email protected]>; yuzuko <[email protected]>; pgsql-hackers
On Fri, Jan 21, 2022 at 1:31 PM Tomas Vondra
<[email protected]> 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.
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.
--
Robert Haas
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: document the need to analyze partitioned tables
@ 2022-03-15 23:00 Justin Pryzby <[email protected]>
parent: Robert Haas <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Justin Pryzby @ 2022-03-15 23:00 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Tomas Vondra <[email protected]>; Álvaro Herrera <[email protected]>; yuzuko <[email protected]>; pgsql-hackers
On Mon, Mar 14, 2022 at 05:23:54PM -0400, Robert Haas wrote:
> On Fri, Jan 21, 2022 at 1:31 PM Tomas Vondra <[email protected]> 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 ed314a6b257d22fe21b2d13fc52a5d3fe16137b0 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email protected]>
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 <[email protected]>
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 <[email protected]>
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
Attachments:
[text/x-diff] 0001-documentation-deficiencies-for-ANALYZE-of-partitione.patch (4.6K, ../../[email protected]/2-0001-documentation-deficiencies-for-ANALYZE-of-partitione.patch)
download | inline diff:
From c237d91ec258ebbf24ebd3a38e139777582817f6 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email protected]>
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
[text/plain] 0001-documentation-deficiencies-for-ANALYZE-of-partitione.patch.txt (5.1K, ../../[email protected]/3-0001-documentation-deficiencies-for-ANALYZE-of-partitione.patch.txt)
download | inline diff:
From ed314a6b257d22fe21b2d13fc52a5d3fe16137b0 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email protected]>
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
[text/plain] 0002-minor-changes-rewordings.patch.txt (4.9K, ../../[email protected]/4-0002-minor-changes-rewordings.patch.txt)
download | inline diff:
From c2203a7757f574922edf70b4cd5d85ed4edff7da Mon Sep 17 00:00:00 2001
From: Tomas Vondra <[email protected]>
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
[text/plain] 0003-f-3.patch.txt (4.0K, ../../[email protected]/5-0003-f-3.patch.txt)
download | inline diff:
From 773d02724c8aa4294f46b183afccd021be9dd610 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <[email protected]>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: document the need to analyze partitioned tables
@ 2022-03-28 13:05 Tomas Vondra <[email protected]>
parent: Justin Pryzby <[email protected]>
0 siblings, 2 replies; 13+ messages in thread
From: Tomas Vondra @ 2022-03-28 13:05 UTC (permalink / raw)
To: Justin Pryzby <[email protected]>; Robert Haas <[email protected]>; +Cc: Álvaro Herrera <[email protected]>; yuzuko <[email protected]>; pgsql-hackers
On 3/16/22 00:00, Justin Pryzby wrote:
> On Mon, Mar 14, 2022 at 05:23:54PM -0400, Robert Haas wrote:
>> On Fri, Jan 21, 2022 at 1:31 PM Tomas Vondra <[email protected]> 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.
>
I've pushed the last version, and backpatched it to 10 (not sure I'd
call it a bugfix, but I certainly agree with Justin it's worth
mentioning in the docs, even on older branches).
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: document the need to analyze partitioned tables
@ 2022-03-31 10:17 Daniel Gustafsson <[email protected]>
parent: Tomas Vondra <[email protected]>
1 sibling, 0 replies; 13+ messages in thread
From: Daniel Gustafsson @ 2022-03-31 10:17 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: Justin Pryzby <[email protected]>; Robert Haas <[email protected]>; Álvaro Herrera <[email protected]>; yuzuko <[email protected]>; pgsql-hackers
> On 28 Mar 2022, at 15:05, Tomas Vondra <[email protected]> wrote:
> I've pushed the last version, and backpatched it to 10 (not sure I'd
> call it a bugfix, but I certainly agree with Justin it's worth
> mentioning in the docs, even on older branches).
I happened to spot a small typo in this commit in the ANALYZE docs, and have
just pushed a fix all the way down to 10 as per the original commit.
--
Daniel Gustafsson https://vmware.com/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: document the need to analyze partitioned tables
@ 2022-10-05 08:37 Laurenz Albe <[email protected]>
parent: Tomas Vondra <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Laurenz Albe @ 2022-10-05 08:37 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; Justin Pryzby <[email protected]>; Robert Haas <[email protected]>; +Cc: Álvaro Herrera <[email protected]>; yuzuko <[email protected]>; pgsql-hackers
On Mon, 2022-03-28 at 15:05 +0200, Tomas Vondra wrote:
> I've pushed the last version, and backpatched it to 10 (not sure I'd
> call it a bugfix, but I certainly agree with Justin it's worth
> mentioning in the docs, even on older branches).
I'd like to suggest an improvement to this. The current wording could
be read to mean that dead tuples won't get cleaned up in partitioned tables.
By the way, where are the statistics of a partitioned tables used? The actual
tables scanned are always the partitions, and in the execution plans that
I have seen, the optimizer always used the statistics of the partitions.
Yours,
Laurenz Albe
Attachments:
[text/x-patch] 0001-Improve-autovacuum-doc-on-partitioned-tables.patch (1.9K, ../../[email protected]/2-0001-Improve-autovacuum-doc-on-partitioned-tables.patch)
download | inline diff:
From 5209f228f09e52780535edacfee5f7efd2c25081 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <[email protected]>
Date: Wed, 5 Oct 2022 10:31:47 +0200
Subject: [PATCH] Improve autovacuum doc on partitioned tables
The documentation mentioned that autovacuum doesn't process
partitioned tables, but it was unclear about the impact.
The old wording could be interpreted to mean that there are
problems with dead tuple cleanup on partitioned tables.
Clarify that the only potential problem is autoanalyze, and
that statistics for the partitions will be gathered.
---
doc/src/sgml/maintenance.sgml | 13 +++++++++----
1 file changed, 9 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 759ea5ac9c..53e3fadbaf 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -860,10 +860,15 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu
</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.
+ Partitioned tables are not processed by autovacuum. This is no problem
+ as far as <command>VACUUM</command> is concerned, since autovacuum will process
+ the partitions. But, as mentioned in <xref linkend="vacuum-for-statistics"/>,
+ it also means that autovacuum won't run <command>ANALYZE</command> on the
+ partitioned table itself. While statistics are gathered for the partitions,
+ some queries may rely on the statistics for the partitioned table. You should
+ collect statistics by running a manual <command>ANALYZE</command> when the
+ partitioned table is first populated, and again whenever the distribution
+ of data in its partitions changes significantly.
</para>
<para>
--
2.37.3
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: document the need to analyze partitioned tables
@ 2022-10-06 06:02 Andrey Lepikhov <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Andrey Lepikhov @ 2022-10-06 06:02 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; Tomas Vondra <[email protected]>; Justin Pryzby <[email protected]>; Robert Haas <[email protected]>; +Cc: Álvaro Herrera <[email protected]>; yuzuko <[email protected]>; pgsql-hackers
On 10/5/22 13:37, Laurenz Albe wrote:
> On Mon, 2022-03-28 at 15:05 +0200, Tomas Vondra wrote:
>> I've pushed the last version, and backpatched it to 10 (not sure I'd
>> call it a bugfix, but I certainly agree with Justin it's worth
>> mentioning in the docs, even on older branches).
>
> I'd like to suggest an improvement to this. The current wording could
> be read to mean that dead tuples won't get cleaned up in partitioned tables.
>
>
> By the way, where are the statistics of a partitioned tables used? The actual
> tables scanned are always the partitions, and in the execution plans that
> I have seen, the optimizer always used the statistics of the partitions.
For example, it is used to estimate selectivity of join clause:
CREATE TABLE test (id integer, val integer) PARTITION BY hash (id);
CREATE TABLE test_0 PARTITION OF test
FOR VALUES WITH (modulus 2, remainder 0);
CREATE TABLE test_1 PARTITION OF test
FOR VALUES WITH (modulus 2, remainder 1);
INSERT INTO test (SELECT q, q FROM generate_series(1,10) AS q);
VACUUM ANALYZE test;
INSERT INTO test (SELECT q, q%2 FROM generate_series(11,200) AS q);
VACUUM ANALYZE test_0,test_1;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM test t1, test t2 WHERE t1.id = t2.val;
VACUUM ANALYZE test;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM test t1, test t2 WHERE t1.id = t2.val;
Here without actual statistics on parent table we make wrong prediction.
--
Regards
Andrey Lepikhov
Postgres Professional
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2022-10-06 06:02 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-08-19 12:34 [PATCH 1/5] pg_rewind: Move syncTargetDirectory() to file_ops.c Heikki Linnakangas <[email protected]>
2021-09-13 03:54 document the need to analyze partitioned tables Justin Pryzby <[email protected]>
2021-09-13 04:38 ` Re: document the need to analyze partitioned tables Zhihong Yu <[email protected]>
2021-10-08 12:58 ` Re: document the need to analyze partitioned tables Justin Pryzby <[email protected]>
2022-01-21 17:21 ` Re: document the need to analyze partitioned tables Tomas Vondra <[email protected]>
2022-01-21 18:02 ` Re: document the need to analyze partitioned tables Justin Pryzby <[email protected]>
2022-01-21 18:31 ` Re: document the need to analyze partitioned tables Tomas Vondra <[email protected]>
2022-03-14 21:23 ` Re: document the need to analyze partitioned tables Robert Haas <[email protected]>
2022-03-15 23:00 ` Re: document the need to analyze partitioned tables Justin Pryzby <[email protected]>
2022-03-28 13:05 ` Re: document the need to analyze partitioned tables Tomas Vondra <[email protected]>
2022-03-31 10:17 ` Re: document the need to analyze partitioned tables Daniel Gustafsson <[email protected]>
2022-10-05 08:37 ` Re: document the need to analyze partitioned tables Laurenz Albe <[email protected]>
2022-10-06 06:02 ` Re: document the need to analyze partitioned tables Andrey Lepikhov <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox