Re: pgsql: autovacuum: handle analyze for partitioned tables

On Thu, Apr 22, 2021 at 12:43:46PM -0500, Justin Pryzby wrote:
> Maybe the behavior should be documented, though. Actually, I thought the
> pre-existing (non)behavior of autoanalyze would've been documented, and we'd
> now update that. All I can find is this:
>
> https://www.postgresql.org/docs/current/sql-analyze.html
> |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
>
> 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

From a7ae56a879b6bacc4fc22cbd769851713be89840 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj(at)telsasoft(dot)com>
Date: Fri, 23 Apr 2021 09:15:58 -0500
Subject: [PATCH] WIP: Add docs for autovacuum processing of partitioned tables

---
doc/src/sgml/perform.sgml | 3 ++-
doc/src/sgml/ref/analyze.sgml | 4 +++-
doc/src/sgml/ref/pg_restore.sgml | 6 ++++--
3 files changed, 9 insertions(+), 4 deletions(-)

diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index 89ff58338e..814c3cffbe 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,
+ or attaching/detaching 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..179ae3555d 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -255,11 +255,13 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
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
+ tree. For legacy inheritence, the autovacuum daemon, only considers 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.
+ For partitioned tables, inserts and updates on the partitions are counted
+ towards auto-analyze on the parent.
</para>

<para>
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
index 93ea937ac8..260bf0feb7 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 inheritence 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>

--
2.17.0