Re: pgsql: autovacuum: handle analyze for partitioned tables

On Thu, May 13, 2021 at 05:33:33PM -0400, Alvaro Herrera wrote:
> +++ b/doc/src/sgml/maintenance.sgml
> @@ -817,6 +817,11 @@ 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>.
> +    For partitioned tables, inserts and updates on partitions are counted
> +    towards this threshold; however partition meta-operations such as
> +    attachment, detachment or drop are not, so running a manual
> +    <command>ANALYZE</command> is recommended if the partition added or
> +    removed contains a statistically significant volume of data.

I suggest: "Inserts, updates and deletes on partitions of a partitioned table
are counted towards this threshold; however DDL operations such as ATTACH,
DETACH and DROP are not, ...

> +   and in addition it will analyze each individual partition separately.

remove "and" and say in addition COMMA
Or:
"it will also recursive into each partition and update its statistics."

> +   By constrast, if the table being analyzed has inheritance children,
> +   <command>ANALYZE</command> will gather statistics for that table 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 children tables are not individually analyzed
> +   in this case.

say "The child tables themselves.."

> +  <para>
> +   For tables with inheritance children, the autovacuum daemon only
> +   counts inserts and deletes in 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>

This should be emphasized:
Tuples changed in 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's necesary to periodically run an manual
ANALYZE to keep the statistics of the table hierarchy up to date.

I don't know why it says "inserted or updated" but doesn't say "or deleted" -
that seems like a back-patchable fix.

> +++ 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.

maybe say: "analyze the parent to update statistics for the table hierarchy".