public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tomas Vondra <[email protected]>
To: Justin Pryzby <[email protected]>
Cc: Álvaro Herrera <[email protected]>
Cc: yuzuko <[email protected]>
Cc: [email protected]
Subject: Re: document the need to analyze partitioned tables
Date: Fri, 21 Jan 2022 19:31:38 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
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
view thread (10+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: document the need to analyze partitioned tables
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox