Date: Tue, 11 May 2021 17:56:16 -0400
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: Robert Haas <robertmhaas@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Zhihong Yu <zyu@yugabyte.com>, pgsql-hackers@lists.postgresql.org
Subject: Re: pgsql: autovacuum: handle analyze for partitioned tables
Message-ID: <20210511215616.GA13282@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="NzB8fVQJ5HfG6fxh"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20210423180152.GA17270@telsasoft.com>
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/20210511215616.GA13282%40alvherre.pgsql>
Precedence: bulk


--NzB8fVQJ5HfG6fxh
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

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

--NzB8fVQJ5HfG6fxh
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="autoanalyze-doc.patch"

diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index 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.sgml
index 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.sgml
index 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>
 

--NzB8fVQJ5HfG6fxh--