From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Zhihong Yu <zyu@yugabyte.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: pgsql: autovacuum: handle analyze for partitioned tables
Date: Tue, 11 May 2021 17:56:16 -0400
Message-ID: <20210511215616.GA13282@alvherre.pgsql> (raw)
In-Reply-To: <20210423180152.GA17270@telsasoft.com>
On 2021-Apr-23, Justin Pryzby wrote:
> On Thu, Apr 22, 2021 at 12:43:46PM -0500, Justin Pryzby wrote:
> >
> > I think that should probably have been written down somewhere other than for
> > the manual ANALYZE command, but in any case it seems to be outdated now.
>
> Starting with this
Agreed, we need some more docs here. I lightly edited yours and ended
up with this -- mostly I think partitioned tables should not be in the
same paragraph as legacy inheritance because the behavior is different
enough (partitioned tables are not analyzed twice).
I'll give a deeper look tomorrow to see if other places also need edits.
Thanks
--
Álvaro Herrera Valdivia, Chile
Attachments:
[text/x-diff] autoanalyze-doc.patch (3.1K, ../20210511215616.GA13282@alvherre.pgsql/2-autoanalyze-doc.patch)
download | inline diff:diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgmlindex 89ff58338e..ddd6c3ff3e 100644--- a/doc/src/sgml/perform.sgml+++ b/doc/src/sgml/perform.sgml@@ -1767,7 +1767,8 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
<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+ includes bulk loading large amounts of data into the table as well as+ attaching, detaching or dropping partitions. 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.sgmlindex c8fcebc161..9d5e2a9626 100644--- a/doc/src/sgml/ref/analyze.sgml+++ b/doc/src/sgml/ref/analyze.sgml@@ -250,7 +250,17 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
</para>
<para>
- If the table being analyzed has one or more children,+ If the table being analyzed is partitioned, <command>ANALYZE</command>+ will gather statistics by scanning all of its partitions.+ The autovacuum daemon counts inserts and updates in the partitions+ to determine if auto-analyze is needed. However, adding or+ removing partitions does not affect the autovacuum daemon decisions,+ so triggering a manual <command>ANALYZE</command> is recommended when+ they occur.+ </para>++ <para>+ If the table being analyzed has one or more legacy inheritance 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
@@ -263,7 +273,7 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
</para>
<para>
- If any of the child tables are foreign tables whose foreign data wrappers+ If any of the child tables or partitions are foreign tables whose foreign data wrappers
do not support <command>ANALYZE</command>, those child tables are ignored while
gathering inheritance statistics.
</para>
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgmlindex 93ea937ac8..474f18c73f 100644--- a/doc/src/sgml/ref/pg_restore.sgml+++ b/doc/src/sgml/ref/pg_restore.sgml@@ -922,8 +922,10 @@ CREATE DATABASE foo WITH TEMPLATE template0;
<para>
Once restored, it is wise to run <command>ANALYZE</command> on each
- restored table so the optimizer has useful statistics; see- <xref linkend="vacuum-for-statistics"/> and+ restored table so the optimizer has useful statistics.+ If the table is a partition or an inheritance child, it may also be useful+ to analyze the parent table.+ See <xref linkend="vacuum-for-statistics"/> and
<xref linkend="autovacuum"/> for more information.
</para>
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: pgsql-committers@postgresql.org
Cc: alvherre@alvh.no-ip.org, pryzby@telsasoft.com, robertmhaas@gmail.com, tgl@sss.pgh.pa.us, zyu@yugabyte.com, pgsql-hackers@lists.postgresql.org
Subject: Re: pgsql: autovacuum: handle analyze for partitioned tables
In-Reply-To: <20210511215616.GA13282@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox