Cleaned up and attached as a .patch. The patch implementing autoanalyze on partitioned tables should revert relevant portions of this patch.
>From cec31df3772ca51bbf14ebee207bcfd22e498073 Mon Sep 17 00:00:00 2001 From: Justin Pryzby <pryz...@telsasoft.com> Date: Thu, 22 Jul 2021 16:06:18 -0500 Subject: [PATCH] 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 36f975b1e5..b7c806cc90 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 9cf8ebea80..caf703129d 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 c423aeeea5..9a904262df 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.17.0