Document autoanalyze limitations for partitioned tables

tvondra · tvondra · commit 61fa6ca79b3c · 2022-03-28T14:30:00.000+02:00
When dealing with partitioned tables, counters for partitioned tables are not updated when modifying child tables. This means autoanalyze may not update optimizer statistics for the parent relations, which can result in poor plans for some queries. It's worth documenting this limitation, so that people are aware of it and can take steps to mitigate it (e.g. by setting up a script executing ANALYZE regularly). Backpatch to v10. Older branches are affected too, of couse, but we no longer maintain those. Author: Justin Pryzby Reviewed-by: Zhihong Yu, Tomas Vondra Backpatch-through: 10 Discussion: https://postgr.es/m/20210913035409.GA10647%40telsasoft.com
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
@@ -290,6 +290,15 @@
     to meaningful statistical changes.
    </para>
 
+   <para>
+    Tuples changed in partitions and inheritance children do not trigger
+    analyze on the parent table.  If the parent table is empty or rarely
+    changed, it may never be processed by autovacuum, and the statistics for
+    the inheritance tree as a whole won't be collected. It is necessary to
+    run <command>ANALYZE</command> on the parent table manually in order to
+    keep the statistics 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 +356,19 @@
      <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.  If your queries require statistics on
+     parent tables for proper planning, it is necessary to periodically run
+     a manual <command>ANALYZE</command> on those tables to keep the statistics
+     up to date.
+    </para>
+   </tip>
+
   </sect2>
 
   <sect2 id="vacuum-for-visibility-map">
@@ -819,6 +841,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 again 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/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
@@ -263,9 +263,35 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
   </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.
+    For partitioned tables, <command>ANALYZE</command> gathers statistics by
+    sampling rows from all partitions; in addition, it will recurse into each
+    partition and update its statistics.  Each leaf partition is analyzed only
+    once, even with multi-level partitioning.  No statistics are collected for
+    only the parent table (without data from its partitions), because with
+    partitioning it's guaranteed to be empty.
+  </para>
+
+  <para>
+    By constrast, if the table being analyzed has inheritance children,
+    <command>ANALYZE</command> gathers two sets of statistics: one on the rows
+    of the parent table only, and a second including rows of both the parent
+    table and all of its children.  This second set of statistics is needed when
+    planning queries that process the inheritance tree as a whole.  The child
+    tables themselves are not individually analyzed in this case.
+  </para>
+
+  <para>
+    The autovacuum daemon does not process partitioned tables, nor does it
+    process inheritance parents if only the children are ever modified.
+    It is usually necessary to periodically run a manual
+    <command>ANALYZE</command> to keep the statistics of the table hierarchy
+    up to date.
+  </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>
