Subject: document the need to analyze partitioned tables

From: Justin Pryzby <[email-masked]>
Date: Sun, 12 Sep 2021 22:54:09 -0500

Adding -hackers, sorry for the duplicate.

This seems to be deficient, citing
https://www.postgresql.org/message-id/flat/0d1b394b-bec9-8a71-a336-44df7078b295%40gmail.com

I'm proposing something like the attached.  Ideally, there would be a central
place to put details, and the other places could refer to that.

Since the autoanalyze patch was reverted, this should be easily applied to
backbranches, which is probably most of its value.

commit 4ad2c8f6fd8eb26d76b226e68d3fdb8f0658f113
Author: Justin Pryzby <[email-masked]>
Date:   Thu Jul 22 16:06:18 2021 -0500

    documentation deficiencies for ANALYZE of partitioned tables
    
    This is partially extracted from 1b5617eb844cd2470a334c1d2eec66cf9b39c41a,
    which was reverted.

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 36f975b1e5..decfabff5d 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 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 is necessary to
+    periodically run an manual <command>ANALYZE</command> to keep the statistics
+    of the 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.  Inheritence parents will only be analyzed if the
+     parent is changed - changes to child tables do not trigger autoanalyze on
+     the parent table.  It is necessary to periodically run an manual
+     <command>ANALYZE</command> to keep the statistics of the table hierarchy up to
+     date.
+    </para>
+   </tip>
+
   </sect2>
 
   <sect2 id="vacuum-for-visibility-map">
@@ -817,6 +837,18 @@ 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>.
+
+    Partitioned tables are not processed by autovacuum, and their statistics
+    should be updated by manually running <command>ANALYZE</command> when the
+    table is first populated, and whenever the distribution of data in its
+    partitions changes significantly.
+   </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>
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index 89ff58338e..b84853fd6f 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..20ffbc2d7a 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -250,22 +250,33 @@ 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 or inheritence
+    parents.  It is usually necessary to periodically run a manual
+    <command>ANALYZE</command> to keep the statistics of the table hierarchy
+    up to date (except for nonempty inheritence parents which undergo
+    modifications of their own table data).
+    See...
+  </para>
+
+  <para>
+    If any of the 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>

---

From: Zhihong Yu <[email-masked]>
Date: Sun, 12 Sep 2021 21:38:50 -0700

On Sun, Sep 12, 2021 at 8:54 PM Justin Pryzby <[email-masked]> wrote:

>     This is partially extracted from
> 1b5617eb844cd2470a334c1d2eec66cf9b39c41a,
>     which was reverted.
>
>      within a table, running <link
> linkend="sql-analyze"><command>ANALYZE</command></link> is strongly
> recommended. This
> @@ -250,22 +250,33 @@ ANALYZE [ VERBOSE ] [ <replaceable
> class="parameter">table_and_columns</replacea
>    </para>
>
>    <para>
> Hi,
Minor comment:

periodically run an manual  -> periodically run a manual

Cheers

---

From: Justin Pryzby <[email-masked]>
Date: Fri, 8 Oct 2021 07:58:22 -0500

Cleaned up and attached as a .patch.

The patch implementing autoanalyze on partitioned tables should revert relevant
portions of this patch.

---

From: Tomas Vondra <[email-masked]>
Date: Mon, 28 Mar 2022 15:05:26 +0200

On 3/16/22 00:00, Justin Pryzby wrote:
> On Mon, Mar 14, 2022 at 05:23:54PM -0400, Robert Haas wrote:
>> On Fri, Jan 21, 2022 at 1:31 PM Tomas Vondra <[email-masked]> wrote:
>>> [ new patch ]
>>
>> This patch is originally by Justin. The latest version is by Tomas. I
>> think the next step is for Justin to say whether he's OK with the
>> latest version that Tomas posted. If he is, then I suggest that he
>> also mark it Ready for Committer, and that Tomas commit it. If he's
>> not, he should say what he wants changed and either post a new version
>> himself or wait for Tomas to do that.
> 
> Yes, I think it can be Ready.  Done.
> 
> I amended some of Tomas' changes (see 0003, attached as txt).
> 
> @cfbot: the *.patch file is for your consumption, and the others are only there
> to show my changes.
> 
>> I think the fact that is classified as a "Bug Fix" in the CommitFest
>> application is not particularly good. I would prefer to see it
>> classified under "Documentation". I'm prepared to concede that
>> documentation can have bugs as a general matter, but nobody's data is
>> getting eaten because the documentation wasn't updated. In fact, this
>> is the fourth patch from the "bug fix" section I've studied this
>> afternoon, and, well, none of them have been back-patchable code
>> defects.
> 
> In fact, I consider this to be back-patchable back to v10.  IMO it's an
> omission that this isn't documented.  Not all bugs cause data to be eaten.  If
> someone reads the existing documentation, they might conclude that their
> partitioned tables don't need to be analyzed, and they would've been better
> served by not reading the docs.
> 

I've pushed the last version, and backpatched it to 10 (not sure I'd
call it a bugfix, but I certainly agree with Justin it's worth
mentioning in the docs, even on older branches).

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

---

From: Daniel Gustafsson <[email-masked]>
Date: Thu, 31 Mar 2022 12:17:42 +0200

> On 28 Mar 2022, at 15:05, Tomas Vondra <[email-masked]> wrote:

> mentioning in the docs, even on older branches).

I happened to spot a small typo in this commit in the ANALYZE docs, and have
just pushed a fix all the way down to 10 as per the original commit.

--
Daniel Gustafsson		https://vmware.com/

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 05 Oct 2022 10:37:01 +0200

> mentioning in the docs, even on older branches).

I'd like to suggest an improvement to this.  The current wording could
be read to mean that dead tuples won't get cleaned up in partitioned tables.

By the way, where are the statistics of a partitioned tables used?  The actual
tables scanned are always the partitions, and in the execution plans that
I have seen, the optimizer always used the statistics of the partitions.

Yours,
Laurenz Albe
From 5209f228f09e52780535edacfee5f7efd2c25081 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <[email-masked]>
Date: Wed, 5 Oct 2022 10:31:47 +0200
Subject: [PATCH] Improve autovacuum doc on partitioned tables

The documentation mentioned that autovacuum doesn't process
partitioned tables, but it was unclear about the impact.
The old wording could be interpreted to mean that there are
problems with dead tuple cleanup on partitioned tables.
Clarify that the only potential problem is autoanalyze, and
that statistics for the partitions will be gathered.
---
 doc/src/sgml/maintenance.sgml | 13 +++++++++----
 1 file changed, 9 insertions(+), 4 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 759ea5ac9c..53e3fadbaf 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -860,10 +860,15 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu
    </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.
+    Partitioned tables are not processed by autovacuum.  This is no problem
+    as far as <command>VACUUM</command> is concerned, since autovacuum will process
+    the partitions.  But, as mentioned in <xref linkend="vacuum-for-statistics"/>,
+    it also means that autovacuum won't run <command>ANALYZE</command> on the
+    partitioned table itself.  While statistics are gathered for the partitions,
+    some queries may rely on the statistics for the partitioned table.  You should
+    collect statistics by running a manual <command>ANALYZE</command> when the
+    partitioned table is first populated, and again whenever the distribution
+    of data in its partitions changes significantly.
    </para>
 
    <para>
-- 
2.37.3

---

From: Andrey Lepikhov <[email-masked]>
Date: Thu, 6 Oct 2022 11:02:07 +0500

> I have seen, the optimizer always used the statistics of the partitions.
For example, it is used to estimate selectivity of join clause:

CREATE TABLE test (id integer, val integer) PARTITION BY hash (id);
CREATE TABLE test_0 PARTITION OF test
   FOR VALUES WITH (modulus 2, remainder 0);
CREATE TABLE test_1 PARTITION OF test
   FOR VALUES WITH (modulus 2, remainder 1);

INSERT INTO test (SELECT q, q FROM generate_series(1,10) AS q);
VACUUM ANALYZE test;
INSERT INTO test (SELECT q, q%2 FROM generate_series(11,200) AS q);
VACUUM ANALYZE test_0,test_1;

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM test t1, test t2 WHERE t1.id = t2.val;
VACUUM ANALYZE test;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM test t1, test t2 WHERE t1.id = t2.val;

Here without actual statistics on parent table we make wrong prediction.

-- 
Regards
Andrey Lepikhov
Postgres Professional