Revert analyze support for partitioned tables

alvherre · alvherre · commit 6f8127b73901 · 2021-08-16T17:27:52.000-04:00
This reverts the following commits: 1b5617e Describe (auto-)analyze behavior for partitioned tables 0e69f70 Set pg_class.reltuples for partitioned tables 41badea Document ANALYZE storage parameters for partitioned tables 0827e8a autovacuum: handle analyze for partitioned tables There are efficiency issues in this code when handling databases with large numbers of partitions, and it doesn't look like there isn't any trivial way to handle those. There are some other issues as well. It's now too late in the cycle for nontrivial fixes, so we'll have to let Postgres 14 users continue to manually deal with ANALYZE their partitioned tables, and hopefully we can fix the issues for Postgres 15. I kept [most of] be280cd ("Don't reset relhasindex for partitioned tables on ANALYZE") because while we added it due to 0827e8a, it is a good bugfix in its own right, since it affects manual analyze as well as autovacuum-induced analyze, and there's no reason to revert it. I retained the addition of relkind 'p' to tables included by pg_stat_user_tables, because reverting that would require a catversion bump. Also, in pg14 only, I keep a struct member that was added to PgStat_TabStatEntry to avoid breaking compatibility with existing stat files. Backpatch to 14. Discussion: https://postgr.es/m/20210722205458.f2bug3z6qzxzpx2s@alap3.anarazel.de
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
@@ -817,12 +817,6 @@ 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, updates and deletes on partitions
-    are counted towards this threshold; however, DDL
-    operations such as <literal>ATTACH</literal>, <literal>DETACH</literal>
-    and <literal>DROP</literal> are not, so running a manual
-    <command>ANALYZE</command> is recommended if the partition added or
-    removed contains a statistically significant volume of data.
    </para>
 
    <para>
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
@@ -1767,8 +1767,7 @@ 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 as well as
-    attaching, detaching or dropping partitions.  Running
+    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
@@ -250,38 +250,20 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
   </para>
 
   <para>
-   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 contrast, 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.
+    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.
   </para>
 
   <para>
-   The autovacuum daemon counts inserts, updates and deletes in the
-   partitions to determine if auto-analyze is needed.  However, adding
-   or removing partitions does not affect autovacuum daemon decisions,
-   so triggering a manual <command>ANALYZE</command> is recommended
-   when this occurs.
-  </para>
-
-  <para>
-   Tuples changed in inheritance children do not count towards analyze
-   on the parent table.  If the parent table is empty or rarely modified,
-   it may never be processed by autovacuum.  It's necessary to
-   periodically run a manual <command>ANALYZE</command> to keep the
-   statistics of the table hierarchy up to date.
-  </para>
-
-  <para>
-    If any of the child tables or partitions are foreign tables whose foreign data wrappers
+    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.
   </para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
@@ -1374,8 +1374,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     If a table parameter value is set and the
     equivalent <literal>toast.</literal> parameter is not, the TOAST table
     will use the table's parameter value.
-    Except where noted, these parameters are not supported on partitioned
-    tables; however, you can specify them on individual leaf partitions.
+    Specifying these parameters for partitioned tables is not supported,
+    but you may specify them for individual leaf partitions.
    </para>
 
    <variablelist>
@@ -1457,8 +1457,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
      If true, the autovacuum daemon will perform automatic <command>VACUUM</command>
      and/or <command>ANALYZE</command> operations on this table following the rules
      discussed in <xref linkend="autovacuum"/>.
-     This parameter can be set for partitioned tables to prevent autovacuum
-     from running <command>ANALYZE</command> on them.
      If false, this table will not be autovacuumed, except to prevent
      transaction ID wraparound. See <xref linkend="vacuum-for-wraparound"/> for
      more about wraparound prevention.
@@ -1590,7 +1588,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
      <para>
       Per-table value for <xref linkend="guc-autovacuum-analyze-threshold"/>
       parameter.
-      This parameter can be set for partitioned tables.
      </para>
     </listitem>
    </varlistentry>
@@ -1606,7 +1603,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
      <para>
       Per-table value for <xref linkend="guc-autovacuum-analyze-scale-factor"/>
       parameter.
-      This parameter can be set for partitioned tables.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml
@@ -922,10 +922,8 @@ 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.
-   If the table is a partition or an inheritance child, it may also be useful
-   to analyze the parent to update statistics for the table hierarchy.
-   See <xref linkend="vacuum-for-statistics"/> and
+   restored table so the optimizer has useful statistics; see
+   <xref linkend="vacuum-for-statistics"/> and
    <xref linkend="autovacuum"/> for more information.
   </para>
 
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
@@ -108,7 +108,7 @@ static relopt_bool boolRelOpts[] =
 		{
 			"autovacuum_enabled",
 			"Enables autovacuum in this relation",
-			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST | RELOPT_KIND_PARTITIONED,
+			RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
 			ShareUpdateExclusiveLock
 		},
 		true
@@ -237,7 +237,7 @@ static relopt_int intRelOpts[] =
 		{
 			"autovacuum_analyze_threshold",
 			"Minimum number of tuple inserts, updates or deletes prior to analyze",
-			RELOPT_KIND_HEAP | RELOPT_KIND_PARTITIONED,
+			RELOPT_KIND_HEAP,
 			ShareUpdateExclusiveLock
 		},
 		-1, 0, INT_MAX
@@ -411,7 +411,7 @@ static relopt_real realRelOpts[] =
 		{
 			"autovacuum_analyze_scale_factor",
 			"Number of tuple inserts, updates or deletes prior to analyze as a fraction of reltuples",
-			RELOPT_KIND_HEAP | RELOPT_KIND_PARTITIONED,
+			RELOPT_KIND_HEAP,
 			ShareUpdateExclusiveLock
 		},
 		-1, 0.0, 100.0
@@ -1979,11 +1979,12 @@ bytea *
 partitioned_table_reloptions(Datum reloptions, bool validate)
 {
 	/*
-	 * autovacuum_enabled, autovacuum_analyze_threshold and
-	 * autovacuum_analyze_scale_factor are supported for partitioned tables.
+	 * There are no options for partitioned tables yet, but this is able to do
+	 * some validation.
 	 */
-
-	return default_reloptions(reloptions, validate, RELOPT_KIND_PARTITIONED);
+	return (bytea *) build_reloptions(reloptions, validate,
+									  RELOPT_KIND_PARTITIONED,
+									  0, NULL, 0);
 }
 
 /*
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
@@ -626,8 +626,8 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
 								 PROGRESS_ANALYZE_PHASE_FINALIZE_ANALYZE);
 
 	/*
-	 * Update pages/tuples stats in pg_class ... but not if we're doing
-	 * inherited stats.
+	 * Update pages/tuples stats in pg_class, and report ANALYZE to the stats
+	 * collector ... but not if we're doing inherited stats.
 	 *
 	 * We assume that VACUUM hasn't set pg_class.reltuples already, even
 	 * during a VACUUM ANALYZE.  Although VACUUM often updates pg_class,
@@ -668,47 +668,19 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
 								InvalidMultiXactId,
 								in_outer_xact);
 		}
-	}
-	else if (onerel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-	{
+
 		/*
-		 * Partitioned tables don't have storage, so we don't set any fields
-		 * in their pg_class entries except for reltuples, which is necessary
-		 * for auto-analyze to work properly, and relhasindex.
+		 * Now report ANALYZE to the stats collector.
+		 *
+		 * We deliberately don't report to the stats collector when doing
+		 * inherited stats, because the stats collector only tracks per-table
+		 * stats.
+		 *
+		 * Reset the changes_since_analyze counter only if we analyzed all
+		 * columns; otherwise, there is still work for auto-analyze to do.
 		 */
-		vac_update_relstats(onerel, -1, totalrows,
-							0, hasindex, InvalidTransactionId,
-							InvalidMultiXactId,
-							in_outer_xact);
-	}
-
-	/*
-	 * Now report ANALYZE to the stats collector.  For regular tables, we do
-	 * it only if not doing inherited stats.  For partitioned tables, we only
-	 * do it for inherited stats. (We're never called for not-inherited stats
-	 * on partitioned tables anyway.)
-	 *
-	 * Reset the changes_since_analyze counter only if we analyzed all
-	 * columns; otherwise, there is still work for auto-analyze to do.
-	 */
-	if (!inh || onerel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 		pgstat_report_analyze(onerel, totalrows, totaldeadrows,
 							  (va_cols == NIL));
-
-	/*
-	 * If this is a manual analyze of all columns of a permanent leaf
-	 * partition, and not doing inherited stats, also let the collector know
-	 * about the ancestor tables of this partition.  Autovacuum does the
-	 * equivalent of this at the start of its run, so there's no reason to do
-	 * it there.
-	 */
-	if (!inh && !IsAutoVacuumWorkerProcess() &&
-		(va_cols == NIL) &&
-		onerel->rd_rel->relispartition &&
-		onerel->rd_rel->relkind == RELKIND_RELATION &&
-		onerel->rd_rel->relpersistence == RELPERSISTENCE_PERMANENT)
-	{
-		pgstat_report_anl_ancestors(RelationGetRelid(onerel));
 	}
 
 	/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
@@ -336,7 +336,6 @@ typedef struct ForeignTruncateInfo
 static void truncate_check_rel(Oid relid, Form_pg_class reltuple);
 static void truncate_check_perms(Oid relid, Form_pg_class reltuple);
 static void truncate_check_activity(Relation rel);
-static void truncate_update_partedrel_stats(List *parted_rels);
 static void RangeVarCallbackForTruncate(const RangeVar *relation,
 										Oid relId, Oid oldRelId, void *arg);
 static List *MergeAttributes(List *schema, List *supers, char relpersistence,
@@ -1740,7 +1739,6 @@ ExecuteTruncateGuts(List *explicit_rels,
 {
 	List	   *rels;
 	List	   *seq_relids = NIL;
-	List	   *parted_rels = NIL;
 	HTAB	   *ft_htab = NULL;
 	EState	   *estate;
 	ResultRelInfo *resultRelInfos;
@@ -1889,15 +1887,9 @@ ExecuteTruncateGuts(List *explicit_rels,
 	{
 		Relation	rel = (Relation) lfirst(cell);
 
-		/*
-		 * Save OID of partitioned tables for later; nothing else to do for
-		 * them here.
-		 */
+		/* Skip partitioned tables as there is nothing to do */
 		if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
-		{
-			parted_rels = lappend_oid(parted_rels, RelationGetRelid(rel));
 			continue;
-		}
 
 		/*
 		 * Build the lists of foreign tables belonging to each foreign server
@@ -2045,9 +2037,6 @@ ExecuteTruncateGuts(List *explicit_rels,
 		ResetSequence(seq_relid);
 	}
 
-	/* Reset partitioned tables' pg_class.reltuples */
-	truncate_update_partedrel_stats(parted_rels);
-
 	/*
 	 * Write a WAL record to allow this set of actions to be logically
 	 * decoded.
@@ -2194,40 +2183,6 @@ truncate_check_activity(Relation rel)
 	CheckTableNotInUse(rel, "TRUNCATE");
 }
 
-/*
- * Update pg_class.reltuples for all the given partitioned tables to 0.
- */
-static void
-truncate_update_partedrel_stats(List *parted_rels)
-{
-	Relation	pg_class;
-	ListCell   *lc;
-
-	pg_class = table_open(RelationRelationId, RowExclusiveLock);
-
-	foreach(lc, parted_rels)
-	{
-		Oid			relid = lfirst_oid(lc);
-		HeapTuple	tuple;
-		Form_pg_class rd_rel;
-
-		tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid));
-		if (!HeapTupleIsValid(tuple))
-			elog(ERROR, "could not find tuple for relation %u", relid);
-		rd_rel = (Form_pg_class) GETSTRUCT(tuple);
-		if (rd_rel->reltuples != (float4) 0)
-		{
-			rd_rel->reltuples = (float4) 0;
-
-			heap_inplace_update(pg_class, tuple);
-		}
-
-		heap_freetuple(tuple);
-	}
-
-	table_close(pg_class, RowExclusiveLock);
-}
-
 /*
  * storage_name
  *	  returns the name corresponding to a typstorage/attstorage enum value
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
