ANALYZE ONLY

From 6e8a1e103d87e5644fea0a547e8b23b77ff8f78d Mon Sep 17 00:00:00 2001
From: Michael Harris <harmic@gmail.com>
Date: Thu, 22 Aug 2024 18:45:07 +1000
Subject: [PATCH] Initial implementation of the 'ONLY' feature for ANALYZE and
 VACUUM.

This patch also changes the behaivour of ANALYZE and
VACUUM when given on parent tables in an inheritance
structure, to align with the case of partitioned tables.
In the absence of ONLY, descendant tables will be
included.

Refer to the thread at:
  https://www.postgresql.org/message-id/flat/CADofcAWATx_haD%3DQkSxHbnTsAe6%2Be0Aw8Eh4H8cXyogGvn_kOg%40mail.gmail.com
---
 compile_flags.txt                    |  1 +
 doc/src/sgml/ref/analyze.sgml        | 11 +++--
 doc/src/sgml/ref/vacuum.sgml         | 10 +++--
 src/backend/commands/vacuum.c        | 30 ++++++++++----
 src/backend/parser/gram.y            |  2 +-
 src/test/regress/expected/vacuum.out | 62 ++++++++++++++++++++++++++++
 src/test/regress/sql/vacuum.sql      | 40 ++++++++++++++++++
 7 files changed, 140 insertions(+), 16 deletions(-)
 create mode 100644 compile_flags.txt

diff --git a/compile_flags.txt b/compile_flags.txt
new file mode 100644
index 0000000000..7297dbe134
--- /dev/null
+++ b/compile_flags.txt
@@ -0,0 +1 @@
+-Isrc/include/
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 2b94b378e9..9ed39b0206 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
+ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ [ ONLY ] <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
 
 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
 
@@ -142,9 +142,12 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
       The name (possibly schema-qualified) of a specific table to
       analyze.  If omitted, all regular tables, partitioned tables, and
       materialized views in the current database are analyzed (but not
-      foreign tables).  If the specified table is a partitioned table, both the
-      inheritance statistics of the partitioned table as a whole and
-      statistics of the individual partitions are updated.
+      foreign tables). If <literal>ONLY</literal> is specified before 
+      the table name, only that table is analyzed. If <literal>ONLY</literal>
+      is not specified, the table and all its descendant tables or partitions
+      (if any) are analyzed.  Optionally, <literal>*</literal>
+      can be specified after the table name to explicitly indicate that
+      descendant tables are included.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 9857b35627..a0e062baa1 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
+VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ [ ONLY ] <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
 
 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
 
@@ -401,8 +401,12 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
     <listitem>
      <para>
       The name (optionally schema-qualified) of a specific table or
-      materialized view to vacuum.  If the specified table is a partitioned
-      table, all of its leaf partitions are vacuumed.
+      materialized view to vacuum.  If <literal>ONLY</literal> is specified before 
+      the table name, only that table is vacuumed. If <literal>ONLY</literal>
+      is not specified, the table and all its descendant tables or partitions 
+      (if any) are vacuumed.  Optionally, <literal>*</literal>
+      can be specified after the table name to explicitly indicate that
+      descendant tables are included.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 7d8e9d2045..cd37f90687 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -24,6 +24,7 @@
 #include "postgres.h"
 
 #include <math.h>
+#include <stdbool.h>
 
 #include "access/clog.h"
 #include "access/commit_ts.h"
@@ -879,11 +880,12 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 	}
 	else
 	{
-		/* Process a specific relation, and possibly partitions thereof */
+		/* Process a specific relation, and possibly partitions and/or child tables thereof */
 		Oid			relid;
 		HeapTuple	tuple;
 		Form_pg_class classForm;
-		bool		include_parts;
+		bool		include_children;
+		bool		is_partitioned_table;
 		int			rvr_opts;
 
 		/*
@@ -944,20 +946,32 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 			MemoryContextSwitchTo(oldcontext);
 		}
 
+		/*
+		 * Vacuuming a partitioned table with ONLY will not do anything
+		 * since the partitioned table itself is empty. Issue a warning
+		 * if the user requests this.
+		 */
+		include_children = vrel->relation->inh;
+		is_partitioned_table = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
+		if ((options & VACOPT_VACUUM) && is_partitioned_table && ! include_children)
+			ereport(WARNING,
+				(errmsg("VACUUM ONLY of partitioned table \"%s\" has no effect",
+							vrel->relation->relname)));
+
 
-		include_parts = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
 		ReleaseSysCache(tuple);
 
 		/*
-		 * If it is, make relation list entries for its partitions.  Note that
+		 * Unless the user has specified ONLY, make relation list entries for 
+		 * its partitions and/or descendant tables.  Note that
 		 * the list returned by find_all_inheritors() includes the passed-in
 		 * OID, so we have to skip that.  There's no point in taking locks on
-		 * the individual partitions yet, and doing so would just add
+		 * the individual partitions/tables yet, and doing so would just add
 		 * unnecessary deadlock risk.  For this last reason we do not check
-		 * yet the ownership of the partitions, which get added to the list to
-		 * process.  Ownership will be checked later on anyway.
+		 * yet the ownership of the partitions/tables, which get added to the
+		 * list to process.  Ownership will be checked later on anyway.
 		 */
-		if (include_parts)
+		if (include_children)
 		{
 			List	   *part_oids = find_all_inheritors(relid, NoLock, NULL);
 			ListCell   *part_lc;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b7d98eb9f0..7cb3a9092a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11939,7 +11939,7 @@ opt_name_list:
 		;
 
 vacuum_relation:
-			qualified_name opt_name_list
+			relation_expr opt_name_list
 				{
 					$$ = (Node *) makeVacuumRelation($1, InvalidOid, $2);
 				}
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 2eba712887..e1827e6e10 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -291,6 +291,68 @@ ANALYZE vactst, vactst;
 BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
+-- ANALYZE ONLY / VACUUM ONLY on partitioned table
+CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+CREATE TABLE only_parted1 PARTITION OF vacparted FOR VALUES IN (1);
+ERROR:  partition "only_parted1" would overlap partition "vacparted1"
+LINE 1: ...TABLE only_parted1 PARTITION OF vacparted FOR VALUES IN (1);
+                                                                    ^
+INSERT INTO only_parted1 VALUES (1, 'a');
+ERROR:  relation "only_parted1" does not exist
+LINE 1: INSERT INTO only_parted1 VALUES (1, 'a');
+                    ^
+-- Only partitioned table is analyzed
+ANALYZE ONLY only_parted;
+SELECT relname, last_analyze is null, last_vacuum is null FROM pg_stat_user_tables 
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+   relname   | ?column? | ?column? 
+-------------+----------+----------
+ only_parted | t        | t
+(1 row)
+
+-- Partitioned table and partitions are analyzed
+ANALYZE only_parted;
+SELECT relname, last_analyze is null, last_vacuum is null FROM pg_stat_user_tables 
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+   relname   | ?column? | ?column? 
+-------------+----------+----------
+ only_parted | t        | t
+(1 row)
+
+VACUUM ONLY vacparted; -- gives warning
+WARNING:  VACUUM ONLY of partitioned table "vacparted" has no effect
+ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
+-- ANALYZE ONLY on inherited tables
+CREATE TABLE only_inh_parent (a int primary key, b TEXT);
+CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
+INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
+-- Only parent is ANALYZED
+ANALYZE ONLY only_inh_parent;
+SELECT relname, last_analyze is null, last_vacuum is null FROM pg_stat_user_tables 
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | ?column? | ?column? 
+-----------------+----------+----------
+ only_inh_child  | t        | t
+ only_inh_parent | f        | t
+(2 rows)
+
+-- Parent and child is ANALYZED
+ANALYZE only_inh_parent;
+SELECT relname, last_analyze is null, last_vacuum is null FROM pg_stat_user_tables 
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | ?column? | ?column? 
+-----------------+----------+----------
+ only_inh_child  | f        | t
+ only_inh_parent | f        | t
+(2 rows)
+
+DROP TABLE only_parted CASCADE;
+DROP TABLE only_inh_parent CASCADE;
+NOTICE:  drop cascades to table only_inh_child
 -- parenthesized syntax for ANALYZE
 ANALYZE (VERBOSE) does_not_exist;
 ERROR:  relation "does_not_exist" does not exist
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 548cd7acca..510991377a 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -233,6 +233,46 @@ BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
 
+-- ANALYZE ONLY / VACUUM ONLY on partitioned table
+CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+CREATE TABLE only_parted1 PARTITION OF vacparted FOR VALUES IN (1);
+INSERT INTO only_parted1 VALUES (1, 'a');
+
+-- Only partitioned table is analyzed
+ANALYZE ONLY only_parted;
+SELECT relname, last_analyze is null, last_vacuum is null FROM pg_stat_user_tables 
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+
+-- Partitioned table and partitions are analyzed
+ANALYZE only_parted;
+SELECT relname, last_analyze is null, last_vacuum is null FROM pg_stat_user_tables 
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+
+VACUUM ONLY vacparted; -- gives warning
+ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
+
+-- ANALYZE ONLY on inherited tables
+CREATE TABLE only_inh_parent (a int primary key, b TEXT);
+CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
+INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
+
+-- Only parent is ANALYZED
+ANALYZE ONLY only_inh_parent;
+SELECT relname, last_analyze is null, last_vacuum is null FROM pg_stat_user_tables 
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Parent and child is ANALYZED
+ANALYZE only_inh_parent;
+SELECT relname, last_analyze is null, last_vacuum is null FROM pg_stat_user_tables 
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+DROP TABLE only_parted CASCADE;
+DROP TABLE only_inh_parent CASCADE;
+
 -- parenthesized syntax for ANALYZE
 ANALYZE (VERBOSE) does_not_exist;
 ANALYZE (nonexistent-arg) does_not_exist;
-- 
2.31.1

From 837a792664c9430f05510593b2171a7a22ff82f4 Mon Sep 17 00:00:00 2001
From: Michael Harris <harmic@gmail.com>
Date: Fri, 23 Aug 2024 19:58:17 +1000
Subject: [PATCH v2] Initial implementation of the 'ONLY' feature for ANALYZE
 and VACUUM.

This patch also changes the behaivour of ANALYZE and
VACUUM when given on parent tables in an inheritance
structure, to align with the case of partitioned tables.
In the absence of ONLY, descendant tables will be
included.

Refer to the thread at:
  https://www.postgresql.org/message-id/flat/CADofcAWATx_haD%3DQkSxHbnTsAe6%2Be0Aw8Eh4H8cXyogGvn_kOg%40mail.gmail.com
---
 doc/src/sgml/ref/analyze.sgml        | 24 +++++----
 doc/src/sgml/ref/vacuum.sgml         | 12 +++--
 src/backend/commands/vacuum.c        | 29 +++++++---
 src/backend/parser/gram.y            |  2 +-
 src/test/regress/expected/vacuum.out | 80 ++++++++++++++++++++++++++++
 src/test/regress/sql/vacuum.sql      | 52 ++++++++++++++++++
 6 files changed, 176 insertions(+), 23 deletions(-)

diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 2b94b378e9..d3bf865ef3 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
+ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ [ ONLY ] <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
 
 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
 
@@ -31,7 +31,7 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
 
 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
 
-    <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+    <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -142,9 +142,12 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
       The name (possibly schema-qualified) of a specific table to
       analyze.  If omitted, all regular tables, partitioned tables, and
       materialized views in the current database are analyzed (but not
-      foreign tables).  If the specified table is a partitioned table, both the
-      inheritance statistics of the partitioned table as a whole and
-      statistics of the individual partitions are updated.
+      foreign tables). If <literal>ONLY</literal> is specified before
+      the table name, only that table is analyzed. If <literal>ONLY</literal>
+      is not specified, the table and all its descendant tables or partitions
+      (if any) are analyzed.  Optionally, <literal>*</literal>
+      can be specified after the table name to explicitly indicate that
+      descendant tables are included.
      </para>
     </listitem>
    </varlistentry>
@@ -295,11 +298,12 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
 
   <para>
     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.
+    sampling rows from all partitions. If <literal>ONLY</literal> was not
+    specified, it will also 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 is guaranteed to be empty.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 9857b35627..cb52c270c6 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
+VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ [ ONLY ] <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
 
 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
 
@@ -42,7 +42,7 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
 
 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
 
-    <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+    <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -401,8 +401,12 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
     <listitem>
      <para>
       The name (optionally schema-qualified) of a specific table or
-      materialized view to vacuum.  If the specified table is a partitioned
-      table, all of its leaf partitions are vacuumed.
+      materialized view to vacuum.  If <literal>ONLY</literal> is specified before
+      the table name, only that table is vacuumed. If <literal>ONLY</literal>
+      is not specified, the table and all its descendant tables or partitions
+      (if any) are vacuumed.  Optionally, <literal>*</literal>
+      can be specified after the table name to explicitly indicate that
+      descendant tables are included.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 7d8e9d2045..55000e7451 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -879,11 +879,12 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 	}
 	else
 	{
-		/* Process a specific relation, and possibly partitions thereof */
+		/* Process a specific relation, and possibly partitions and/or child tables thereof */
 		Oid			relid;
 		HeapTuple	tuple;
 		Form_pg_class classForm;
-		bool		include_parts;
+		bool		include_children;
+		bool		is_partitioned_table;
 		int			rvr_opts;
 
 		/*
@@ -944,20 +945,32 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 			MemoryContextSwitchTo(oldcontext);
 		}
 
+		/*
+		 * Vacuuming a partitioned table with ONLY will not do anything
+		 * since the partitioned table itself is empty. Issue a warning
+		 * if the user requests this.
+		 */
+		include_children = vrel->relation->inh;
+		is_partitioned_table = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
+		if ((options & VACOPT_VACUUM) && is_partitioned_table && ! include_children)
+			ereport(WARNING,
+				(errmsg("VACUUM ONLY of partitioned table \"%s\" has no effect",
+							vrel->relation->relname)));
+
 
-		include_parts = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
 		ReleaseSysCache(tuple);
 
 		/*
-		 * If it is, make relation list entries for its partitions.  Note that
+		 * Unless the user has specified ONLY, make relation list entries for
+		 * its partitions and/or descendant tables.  Note that
 		 * the list returned by find_all_inheritors() includes the passed-in
 		 * OID, so we have to skip that.  There's no point in taking locks on
-		 * the individual partitions yet, and doing so would just add
+		 * the individual partitions/tables yet, and doing so would just add
 		 * unnecessary deadlock risk.  For this last reason we do not check
-		 * yet the ownership of the partitions, which get added to the list to
-		 * process.  Ownership will be checked later on anyway.
+		 * yet the ownership of the partitions/tables, which get added to the
+		 * list to process.  Ownership will be checked later on anyway.
 		 */
-		if (include_parts)
+		if (include_children)
 		{
 			List	   *part_oids = find_all_inheritors(relid, NoLock, NULL);
 			ListCell   *part_lc;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b7d98eb9f0..7cb3a9092a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11939,7 +11939,7 @@ opt_name_list:
 		;
 
 vacuum_relation:
-			qualified_name opt_name_list
+			relation_expr opt_name_list
 				{
 					$$ = (Node *) makeVacuumRelation($1, InvalidOid, $2);
 				}
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 2eba712887..7165235ece 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -291,6 +291,86 @@ ANALYZE vactst, vactst;
 BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
+-- ANALYZE ONLY / VACUUM ONLY on partitioned table
+CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
+INSERT INTO only_parted VALUES (1, 'a');
+-- Only partitioned table is analyzed
+ANALYZE ONLY only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+   relname    | analyzed | vacuumed 
+--------------+----------+----------
+ only_parted  | t        | f
+ only_parted1 | f        | f
+(2 rows)
+
+-- Partitioned table and partitions are analyzed
+ANALYZE only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+   relname    | analyzed | vacuumed 
+--------------+----------+----------
+ only_parted  | t        | f
+ only_parted1 | t        | f
+(2 rows)
+
+VACUUM ONLY vacparted; -- gives warning
+WARNING:  VACUUM ONLY of partitioned table "vacparted" has no effect
+ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
+-- ANALYZE ONLY on inherited tables
+CREATE TABLE only_inh_parent (a int primary key, b TEXT);
+CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
+INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
+-- Only parent is ANALYZED
+ANALYZE ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | f        | f
+ only_inh_parent | t        | f
+(2 rows)
+
+-- Parent and child are ANALYZED
+ANALYZE only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | f
+ only_inh_parent | t        | f
+(2 rows)
+
+-- Only parent is VACUUMED
+VACUUM ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | f
+ only_inh_parent | t        | t
+(2 rows)
+
+-- Parent and child are VACUUMED
+VACUUM only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | t
+ only_inh_parent | t        | t
+(2 rows)
+
+DROP TABLE only_parted CASCADE;
+DROP TABLE only_inh_parent CASCADE;
+NOTICE:  drop cascades to table only_inh_child
 -- parenthesized syntax for ANALYZE
 ANALYZE (VERBOSE) does_not_exist;
 ERROR:  relation "does_not_exist" does not exist
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 548cd7acca..fc84df0f07 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -233,6 +233,58 @@ BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
 
+-- ANALYZE ONLY / VACUUM ONLY on partitioned table
+CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
+INSERT INTO only_parted VALUES (1, 'a');
+
+-- Only partitioned table is analyzed
+ANALYZE ONLY only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+
+-- Partitioned table and partitions are analyzed
+ANALYZE only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+
+VACUUM ONLY vacparted; -- gives warning
+ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
+
+-- ANALYZE ONLY on inherited tables
+CREATE TABLE only_inh_parent (a int primary key, b TEXT);
+CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
+INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
+
+-- Only parent is ANALYZED
+ANALYZE ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Parent and child are ANALYZED
+ANALYZE only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Only parent is VACUUMED
+VACUUM ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Parent and child are VACUUMED
+VACUUM only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+DROP TABLE only_parted CASCADE;
+DROP TABLE only_inh_parent CASCADE;
+
 -- parenthesized syntax for ANALYZE
 ANALYZE (VERBOSE) does_not_exist;
 ANALYZE (nonexistent-arg) does_not_exist;
-- 
2.31.1

From 55f4109bd78f84d45912c14cfceffd105ed05485 Mon Sep 17 00:00:00 2001
From: Michael Harris <harmic@gmail.com>
Date: Sun, 1 Sep 2024 11:27:56 +1000
Subject: [PATCH v3] Implementation of the 'ONLY' feature for ANALYZE and
 VACUUM.

This patch also changes the behaivour of ANALYZE and
VACUUM when given on parent tables in an inheritance
structure, to align with the case of partitioned tables.
In the absence of ONLY, descendant tables will be
included.

Refer to the thread at:
  https://www.postgresql.org/message-id/flat/CADofcAWATx_haD%3DQkSxHbnTsAe6%2Be0Aw8Eh4H8cXyogGvn_kOg%40mail.gmail.com
---
 doc/src/sgml/ddl.sgml                |  8 +--
 doc/src/sgml/monitoring.sgml         |  6 +--
 doc/src/sgml/ref/analyze.sgml        | 22 ++++----
 doc/src/sgml/ref/vacuum.sgml         | 10 ++--
 src/backend/commands/vacuum.c        | 36 +++++++++----
 src/backend/parser/gram.y            |  2 +-
 src/test/regress/expected/vacuum.out | 80 ++++++++++++++++++++++++++++
 src/test/regress/sql/vacuum.sql      | 52 ++++++++++++++++++
 8 files changed, 186 insertions(+), 30 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index b671858627..4aa945af02 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4854,11 +4854,11 @@ ALTER TABLE measurement_y2008m02 INHERIT measurement;
 
       <listitem>
        <para>
-        If you are using manual <command>VACUUM</command> or
-        <command>ANALYZE</command> commands, don't forget that
-        you need to run them on each child table individually. A command like:
+        Manual <command>VACUUM</command> or <command>ANALYZE</command> commands
+        will automatically process all descendant tables. If this is undesirable,
+        you can use the <literal>ONLY</literal> keyword. A command like:
 <programlisting>
-ANALYZE measurement;
+ANALYZE ONLY measurement;
 </programlisting>
         will only process the root table.
        </para>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 55417a6fa9..c428d673fd 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5449,9 +5449,9 @@ FROM pg_stat_get_backend_idset() AS backendid;
 
   <note>
    <para>
-    Note that when <command>ANALYZE</command> is run on a partitioned table,
-    all of its partitions are also recursively analyzed.
-    In that case, <command>ANALYZE</command>
+    Note that when <command>ANALYZE</command> is run on a partitioned table
+    without the <literal>ONLY</literal> keyword, all of its partitions are
+    also recursively analyzed. In that case, <command>ANALYZE</command>
     progress is reported first for the parent table, whereby its inheritance
     statistics are collected, followed by that for each partition.
    </para>
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 2b94b378e9..ef0f4f0caf 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -31,7 +31,7 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
 
 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
 
-    <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+    [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -142,9 +142,12 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
       The name (possibly schema-qualified) of a specific table to
       analyze.  If omitted, all regular tables, partitioned tables, and
       materialized views in the current database are analyzed (but not
-      foreign tables).  If the specified table is a partitioned table, both the
-      inheritance statistics of the partitioned table as a whole and
-      statistics of the individual partitions are updated.
+      foreign tables). If <literal>ONLY</literal> is specified before
+      the table name, only that table is analyzed. If <literal>ONLY</literal>
+      is not specified, the table and all its descendant tables or partitions
+      (if any) are analyzed.  Optionally, <literal>*</literal>
+      can be specified after the table name to explicitly indicate that
+      descendant tables are included.
      </para>
     </listitem>
    </varlistentry>
@@ -295,11 +298,12 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
 
   <para>
     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.
+    sampling rows from all partitions. If <literal>ONLY</literal> was not
+    specified, it will also 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 is guaranteed to be empty.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 9857b35627..a3347bde35 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -42,7 +42,7 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
 
 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
 
-    <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+    [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -401,8 +401,12 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
     <listitem>
      <para>
       The name (optionally schema-qualified) of a specific table or
-      materialized view to vacuum.  If the specified table is a partitioned
-      table, all of its leaf partitions are vacuumed.
+      materialized view to vacuum.  If <literal>ONLY</literal> is specified before
+      the table name, only that table is vacuumed. If <literal>ONLY</literal>
+      is not specified, the table and all its descendant tables or partitions
+      (if any) are vacuumed.  Optionally, <literal>*</literal>
+      can be specified after the table name to explicitly indicate that
+      descendant tables are included.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 7d8e9d2045..9400bd7bf6 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -879,11 +879,15 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 	}
 	else
 	{
-		/* Process a specific relation, and possibly partitions thereof */
+		/*
+		 * Process a specific relation, and possibly partitions and/or child
+		 * tables thereof
+		 */
 		Oid			relid;
 		HeapTuple	tuple;
 		Form_pg_class classForm;
-		bool		include_parts;
+		bool		include_children;
+		bool		is_partitioned_table;
 		int			rvr_opts;
 
 		/*
@@ -944,20 +948,32 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 			MemoryContextSwitchTo(oldcontext);
 		}
 
+		/*
+		 * Vacuuming a partitioned table with ONLY will not do anything since
+		 * the partitioned table itself is empty. Issue a warning if the user
+		 * requests this.
+		 */
+		include_children = vrel->relation->inh;
+		is_partitioned_table = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
+		if ((options & VACOPT_VACUUM) && is_partitioned_table && !include_children)
+			ereport(WARNING,
+					(errmsg("VACUUM ONLY of partitioned table \"%s\" has no effect",
+							vrel->relation->relname)));
+
 
-		include_parts = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
 		ReleaseSysCache(tuple);
 
 		/*
-		 * If it is, make relation list entries for its partitions.  Note that
-		 * the list returned by find_all_inheritors() includes the passed-in
-		 * OID, so we have to skip that.  There's no point in taking locks on
-		 * the individual partitions yet, and doing so would just add
+		 * Unless the user has specified ONLY, make relation list entries for
+		 * its partitions and/or descendant tables.  Note that the list
+		 * returned by find_all_inheritors() includes the passed-in OID, so we
+		 * have to skip that.  There's no point in taking locks on the
+		 * individual partitions/tables yet, and doing so would just add
 		 * unnecessary deadlock risk.  For this last reason we do not check
-		 * yet the ownership of the partitions, which get added to the list to
-		 * process.  Ownership will be checked later on anyway.
+		 * yet the ownership of the partitions/tables, which get added to the
+		 * list to process.  Ownership will be checked later on anyway.
 		 */
-		if (include_parts)
+		if (include_children)
 		{
 			List	   *part_oids = find_all_inheritors(relid, NoLock, NULL);
 			ListCell   *part_lc;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 84cef57a70..99d4ece308 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11886,7 +11886,7 @@ opt_name_list:
 		;
 
 vacuum_relation:
-			qualified_name opt_name_list
+			relation_expr opt_name_list
 				{
 					$$ = (Node *) makeVacuumRelation($1, InvalidOid, $2);
 				}
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 2eba712887..7165235ece 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -291,6 +291,86 @@ ANALYZE vactst, vactst;
 BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
+-- ANALYZE ONLY / VACUUM ONLY on partitioned table
+CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
+INSERT INTO only_parted VALUES (1, 'a');
+-- Only partitioned table is analyzed
+ANALYZE ONLY only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+   relname    | analyzed | vacuumed 
+--------------+----------+----------
+ only_parted  | t        | f
+ only_parted1 | f        | f
+(2 rows)
+
+-- Partitioned table and partitions are analyzed
+ANALYZE only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+   relname    | analyzed | vacuumed 
+--------------+----------+----------
+ only_parted  | t        | f
+ only_parted1 | t        | f
+(2 rows)
+
+VACUUM ONLY vacparted; -- gives warning
+WARNING:  VACUUM ONLY of partitioned table "vacparted" has no effect
+ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
+-- ANALYZE ONLY on inherited tables
+CREATE TABLE only_inh_parent (a int primary key, b TEXT);
+CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
+INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
+-- Only parent is ANALYZED
+ANALYZE ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | f        | f
+ only_inh_parent | t        | f
+(2 rows)
+
+-- Parent and child are ANALYZED
+ANALYZE only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | f
+ only_inh_parent | t        | f
+(2 rows)
+
+-- Only parent is VACUUMED
+VACUUM ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | f
+ only_inh_parent | t        | t
+(2 rows)
+
+-- Parent and child are VACUUMED
+VACUUM only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | t
+ only_inh_parent | t        | t
+(2 rows)
+
+DROP TABLE only_parted CASCADE;
+DROP TABLE only_inh_parent CASCADE;
+NOTICE:  drop cascades to table only_inh_child
 -- parenthesized syntax for ANALYZE
 ANALYZE (VERBOSE) does_not_exist;
 ERROR:  relation "does_not_exist" does not exist
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 548cd7acca..fc84df0f07 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -233,6 +233,58 @@ BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
 
+-- ANALYZE ONLY / VACUUM ONLY on partitioned table
+CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
+INSERT INTO only_parted VALUES (1, 'a');
+
+-- Only partitioned table is analyzed
+ANALYZE ONLY only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+
+-- Partitioned table and partitions are analyzed
+ANALYZE only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+
+VACUUM ONLY vacparted; -- gives warning
+ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
+
+-- ANALYZE ONLY on inherited tables
+CREATE TABLE only_inh_parent (a int primary key, b TEXT);
+CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
+INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
+
+-- Only parent is ANALYZED
+ANALYZE ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Parent and child are ANALYZED
+ANALYZE only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Only parent is VACUUMED
+VACUUM ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Parent and child are VACUUMED
+VACUUM only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+DROP TABLE only_parted CASCADE;
+DROP TABLE only_inh_parent CASCADE;
+
 -- parenthesized syntax for ANALYZE
 ANALYZE (VERBOSE) does_not_exist;
 ANALYZE (nonexistent-arg) does_not_exist;
-- 
2.31.1

From 7e50c93e1fedbb547405a1f26331156f9c5121b4 Mon Sep 17 00:00:00 2001
From: Michael Harris <harmic@gmail.com>
Date: Mon, 9 Sep 2024 17:44:18 +1000
Subject: [PATCH v4] Implementation of the 'ONLY' feature for ANALYZE and
 VACUUM.

This patch also changes the behaivour of ANALYZE and
VACUUM when given on parent tables in an inheritance
structure, to align with the case of partitioned tables.
In the absence of ONLY, descendant tables will be
included.

Refer to the thread at:
  https://www.postgresql.org/message-id/flat/CADofcAWATx_haD%3DQkSxHbnTsAe6%2Be0Aw8Eh4H8cXyogGvn_kOg%40mail.gmail.com
---
 doc/src/sgml/ddl.sgml                |  8 +--
 doc/src/sgml/monitoring.sgml         |  6 +--
 doc/src/sgml/ref/analyze.sgml        | 22 ++++----
 doc/src/sgml/ref/vacuum.sgml         | 10 ++--
 src/backend/commands/vacuum.c        | 36 +++++++++----
 src/backend/parser/gram.y            |  2 +-
 src/test/regress/expected/vacuum.out | 80 ++++++++++++++++++++++++++++
 src/test/regress/sql/vacuum.sql      | 52 ++++++++++++++++++
 8 files changed, 186 insertions(+), 30 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index b671858627..4aa945af02 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4854,11 +4854,11 @@ ALTER TABLE measurement_y2008m02 INHERIT measurement;
 
       <listitem>
        <para>
-        If you are using manual <command>VACUUM</command> or
-        <command>ANALYZE</command> commands, don't forget that
-        you need to run them on each child table individually. A command like:
+        Manual <command>VACUUM</command> or <command>ANALYZE</command> commands
+        will automatically process all descendant tables. If this is undesirable,
+        you can use the <literal>ONLY</literal> keyword. A command like:
 <programlisting>
-ANALYZE measurement;
+ANALYZE ONLY measurement;
 </programlisting>
         will only process the root table.
        </para>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 933de6fe07..a0545e3da5 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5522,9 +5522,9 @@ FROM pg_stat_get_backend_idset() AS backendid;
 
   <note>
    <para>
-    Note that when <command>ANALYZE</command> is run on a partitioned table,
-    all of its partitions are also recursively analyzed.
-    In that case, <command>ANALYZE</command>
+    Note that when <command>ANALYZE</command> is run on a partitioned table
+    without the <literal>ONLY</literal> keyword, all of its partitions are
+    also recursively analyzed. In that case, <command>ANALYZE</command>
     progress is reported first for the parent table, whereby its inheritance
     statistics are collected, followed by that for each partition.
    </para>
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 2b94b378e9..dfd8779be7 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -31,7 +31,7 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
 
 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
 
-    <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+    [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -142,9 +142,12 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
       The name (possibly schema-qualified) of a specific table to
       analyze.  If omitted, all regular tables, partitioned tables, and
       materialized views in the current database are analyzed (but not
-      foreign tables).  If the specified table is a partitioned table, both the
-      inheritance statistics of the partitioned table as a whole and
-      statistics of the individual partitions are updated.
+      foreign tables). If <literal>ONLY</literal> is specified before
+      the table name, only that table is analyzed. If <literal>ONLY</literal>
+      is not specified, the table and all its descendant tables or partitions
+      (if any) are analyzed.  Optionally, <literal>*</literal>
+      can be specified after the table name to explicitly indicate that
+      descendant tables are included.
      </para>
     </listitem>
    </varlistentry>
@@ -295,11 +298,12 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
 
   <para>
     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.
+    sampling rows from all partitions. If <literal>ONLY</literal> is not
+    specified, it will also 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 is guaranteed to be empty.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 9857b35627..465f9fecf7 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -42,7 +42,7 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
 
 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
 
-    <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+    [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -401,8 +401,12 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
     <listitem>
      <para>
       The name (optionally schema-qualified) of a specific table or
-      materialized view to vacuum.  If the specified table is a partitioned
-      table, all of its leaf partitions are vacuumed.
+      materialized view to vacuum.  If <literal>ONLY</literal> is specified before
+      the table name, only that table is vacuumed. If <literal>ONLY</literal>
+      is not specified, the table and all its descendant tables or partitions
+      (if any) are also vacuumed.  Optionally, <literal>*</literal>
+      can be specified after the table name to explicitly indicate that
+      descendant tables are included.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 7d8e9d2045..9400bd7bf6 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -879,11 +879,15 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 	}
 	else
 	{
-		/* Process a specific relation, and possibly partitions thereof */
+		/*
+		 * Process a specific relation, and possibly partitions and/or child
+		 * tables thereof
+		 */
 		Oid			relid;
 		HeapTuple	tuple;
 		Form_pg_class classForm;
-		bool		include_parts;
+		bool		include_children;
+		bool		is_partitioned_table;
 		int			rvr_opts;
 
 		/*
@@ -944,20 +948,32 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 			MemoryContextSwitchTo(oldcontext);
 		}
 
+		/*
+		 * Vacuuming a partitioned table with ONLY will not do anything since
+		 * the partitioned table itself is empty. Issue a warning if the user
+		 * requests this.
+		 */
+		include_children = vrel->relation->inh;
+		is_partitioned_table = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
+		if ((options & VACOPT_VACUUM) && is_partitioned_table && !include_children)
+			ereport(WARNING,
+					(errmsg("VACUUM ONLY of partitioned table \"%s\" has no effect",
+							vrel->relation->relname)));
+
 
-		include_parts = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
 		ReleaseSysCache(tuple);
 
 		/*
-		 * If it is, make relation list entries for its partitions.  Note that
-		 * the list returned by find_all_inheritors() includes the passed-in
-		 * OID, so we have to skip that.  There's no point in taking locks on
-		 * the individual partitions yet, and doing so would just add
+		 * Unless the user has specified ONLY, make relation list entries for
+		 * its partitions and/or descendant tables.  Note that the list
+		 * returned by find_all_inheritors() includes the passed-in OID, so we
+		 * have to skip that.  There's no point in taking locks on the
+		 * individual partitions/tables yet, and doing so would just add
 		 * unnecessary deadlock risk.  For this last reason we do not check
-		 * yet the ownership of the partitions, which get added to the list to
-		 * process.  Ownership will be checked later on anyway.
+		 * yet the ownership of the partitions/tables, which get added to the
+		 * list to process.  Ownership will be checked later on anyway.
 		 */
-		if (include_parts)
+		if (include_children)
 		{
 			List	   *part_oids = find_all_inheritors(relid, NoLock, NULL);
 			ListCell   *part_lc;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 84cef57a70..99d4ece308 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11886,7 +11886,7 @@ opt_name_list:
 		;
 
 vacuum_relation:
-			qualified_name opt_name_list
+			relation_expr opt_name_list
 				{
 					$$ = (Node *) makeVacuumRelation($1, InvalidOid, $2);
 				}
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 2eba712887..7165235ece 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -291,6 +291,86 @@ ANALYZE vactst, vactst;
 BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
+-- ANALYZE ONLY / VACUUM ONLY on partitioned table
+CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
+INSERT INTO only_parted VALUES (1, 'a');
+-- Only partitioned table is analyzed
+ANALYZE ONLY only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+   relname    | analyzed | vacuumed 
+--------------+----------+----------
+ only_parted  | t        | f
+ only_parted1 | f        | f
+(2 rows)
+
+-- Partitioned table and partitions are analyzed
+ANALYZE only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+   relname    | analyzed | vacuumed 
+--------------+----------+----------
+ only_parted  | t        | f
+ only_parted1 | t        | f
+(2 rows)
+
+VACUUM ONLY vacparted; -- gives warning
+WARNING:  VACUUM ONLY of partitioned table "vacparted" has no effect
+ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
+-- ANALYZE ONLY on inherited tables
+CREATE TABLE only_inh_parent (a int primary key, b TEXT);
+CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
+INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
+-- Only parent is ANALYZED
+ANALYZE ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | f        | f
+ only_inh_parent | t        | f
+(2 rows)
+
+-- Parent and child are ANALYZED
+ANALYZE only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | f
+ only_inh_parent | t        | f
+(2 rows)
+
+-- Only parent is VACUUMED
+VACUUM ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | f
+ only_inh_parent | t        | t
+(2 rows)
+
+-- Parent and child are VACUUMED
+VACUUM only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | t
+ only_inh_parent | t        | t
+(2 rows)
+
+DROP TABLE only_parted CASCADE;
+DROP TABLE only_inh_parent CASCADE;
+NOTICE:  drop cascades to table only_inh_child
 -- parenthesized syntax for ANALYZE
 ANALYZE (VERBOSE) does_not_exist;
 ERROR:  relation "does_not_exist" does not exist
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 548cd7acca..fc84df0f07 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -233,6 +233,58 @@ BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
 
+-- ANALYZE ONLY / VACUUM ONLY on partitioned table
+CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
+INSERT INTO only_parted VALUES (1, 'a');
+
+-- Only partitioned table is analyzed
+ANALYZE ONLY only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+
+-- Partitioned table and partitions are analyzed
+ANALYZE only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+
+VACUUM ONLY vacparted; -- gives warning
+ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
+
+-- ANALYZE ONLY on inherited tables
+CREATE TABLE only_inh_parent (a int primary key, b TEXT);
+CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
+INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
+
+-- Only parent is ANALYZED
+ANALYZE ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Parent and child are ANALYZED
+ANALYZE only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Only parent is VACUUMED
+VACUUM ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Parent and child are VACUUMED
+VACUUM only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+DROP TABLE only_parted CASCADE;
+DROP TABLE only_inh_parent CASCADE;
+
 -- parenthesized syntax for ANALYZE
 ANALYZE (VERBOSE) does_not_exist;
 ANALYZE (nonexistent-arg) does_not_exist;
-- 
2.31.1

From cc829794cb5cc0bdc331d30a6f2721444d8365ba Mon Sep 17 00:00:00 2001
From: Michael Harris <harmic@gmail.com>
Date: Wed, 11 Sep 2024 17:44:00 +1000
Subject: [PATCH v5] Implementation of the 'ONLY' feature for ANALYZE and
 VACUUM.

This patch also changes the behaivour of ANALYZE and
VACUUM when given on parent tables in an inheritance
structure, to align with the case of partitioned tables.
In the absence of ONLY, descendant tables will be
included.

Refer to the thread at:
  https://www.postgresql.org/message-id/flat/CADofcAWATx_haD%3DQkSxHbnTsAe6%2Be0Aw8Eh4H8cXyogGvn_kOg%40mail.gmail.com
---
 doc/src/sgml/ddl.sgml                |  8 +--
 doc/src/sgml/monitoring.sgml         |  6 +--
 doc/src/sgml/ref/analyze.sgml        | 22 ++++----
 doc/src/sgml/ref/vacuum.sgml         | 10 ++--
 src/backend/commands/vacuum.c        | 39 ++++++++++----
 src/backend/parser/gram.y            |  2 +-
 src/test/regress/expected/vacuum.out | 80 ++++++++++++++++++++++++++++
 src/test/regress/sql/vacuum.sql      | 52 ++++++++++++++++++
 8 files changed, 188 insertions(+), 31 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index b671858627..4aa945af02 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4854,11 +4854,11 @@ ALTER TABLE measurement_y2008m02 INHERIT measurement;
 
       <listitem>
        <para>
-        If you are using manual <command>VACUUM</command> or
-        <command>ANALYZE</command> commands, don't forget that
-        you need to run them on each child table individually. A command like:
+        Manual <command>VACUUM</command> or <command>ANALYZE</command> commands
+        will automatically process all descendant tables. If this is undesirable,
+        you can use the <literal>ONLY</literal> keyword. A command like:
 <programlisting>
-ANALYZE measurement;
+ANALYZE ONLY measurement;
 </programlisting>
         will only process the root table.
        </para>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 933de6fe07..a0545e3da5 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5522,9 +5522,9 @@ FROM pg_stat_get_backend_idset() AS backendid;
 
   <note>
    <para>
-    Note that when <command>ANALYZE</command> is run on a partitioned table,
-    all of its partitions are also recursively analyzed.
-    In that case, <command>ANALYZE</command>
+    Note that when <command>ANALYZE</command> is run on a partitioned table
+    without the <literal>ONLY</literal> keyword, all of its partitions are
+    also recursively analyzed. In that case, <command>ANALYZE</command>
     progress is reported first for the parent table, whereby its inheritance
     statistics are collected, followed by that for each partition.
    </para>
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 2b94b378e9..dfd8779be7 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -31,7 +31,7 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
 
 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
 
-    <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+    [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -142,9 +142,12 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
       The name (possibly schema-qualified) of a specific table to
       analyze.  If omitted, all regular tables, partitioned tables, and
       materialized views in the current database are analyzed (but not
-      foreign tables).  If the specified table is a partitioned table, both the
-      inheritance statistics of the partitioned table as a whole and
-      statistics of the individual partitions are updated.
+      foreign tables). If <literal>ONLY</literal> is specified before
+      the table name, only that table is analyzed. If <literal>ONLY</literal>
+      is not specified, the table and all its descendant tables or partitions
+      (if any) are analyzed.  Optionally, <literal>*</literal>
+      can be specified after the table name to explicitly indicate that
+      descendant tables are included.
      </para>
     </listitem>
    </varlistentry>
@@ -295,11 +298,12 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
 
   <para>
     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.
+    sampling rows from all partitions. If <literal>ONLY</literal> is not
+    specified, it will also 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 is guaranteed to be empty.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 9857b35627..465f9fecf7 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -42,7 +42,7 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
 
 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
 
-    <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+    [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -401,8 +401,12 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
     <listitem>
      <para>
       The name (optionally schema-qualified) of a specific table or
-      materialized view to vacuum.  If the specified table is a partitioned
-      table, all of its leaf partitions are vacuumed.
+      materialized view to vacuum.  If <literal>ONLY</literal> is specified before
+      the table name, only that table is vacuumed. If <literal>ONLY</literal>
+      is not specified, the table and all its descendant tables or partitions
+      (if any) are also vacuumed.  Optionally, <literal>*</literal>
+      can be specified after the table name to explicitly indicate that
+      descendant tables are included.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 7d8e9d2045..951e00acee 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -851,7 +851,8 @@ vacuum_open_relation(Oid relid, RangeVar *relation, bits32 options,
 
 /*
  * Given a VacuumRelation, fill in the table OID if it wasn't specified,
- * and optionally add VacuumRelations for partitions of the table.
+ * and optionally add VacuumRelations for partitions or descendant tables
+ * of the table.
  *
  * If a VacuumRelation does not have an OID supplied and is a partitioned
  * table, an extra entry will be added to the output for each partition.
@@ -879,11 +880,15 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 	}
 	else
 	{
-		/* Process a specific relation, and possibly partitions thereof */
+		/*
+		 * Process a specific relation, and possibly partitions and/or child
+		 * tables thereof
+		 */
 		Oid			relid;
 		HeapTuple	tuple;
 		Form_pg_class classForm;
-		bool		include_parts;
+		bool		include_children;
+		bool		is_partitioned_table;
 		int			rvr_opts;
 
 		/*
@@ -944,20 +949,32 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 			MemoryContextSwitchTo(oldcontext);
 		}
 
+		/*
+		 * Vacuuming a partitioned table with ONLY will not do anything since
+		 * the partitioned table itself is empty. Issue a warning if the user
+		 * requests this.
+		 */
+		include_children = vrel->relation->inh;
+		is_partitioned_table = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
+		if ((options & VACOPT_VACUUM) && is_partitioned_table && !include_children)
+			ereport(WARNING,
+					(errmsg("VACUUM ONLY of partitioned table \"%s\" has no effect",
+							vrel->relation->relname)));
+
 
-		include_parts = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
 		ReleaseSysCache(tuple);
 
 		/*
-		 * If it is, make relation list entries for its partitions.  Note that
-		 * the list returned by find_all_inheritors() includes the passed-in
-		 * OID, so we have to skip that.  There's no point in taking locks on
-		 * the individual partitions yet, and doing so would just add
+		 * Unless the user has specified ONLY, make relation list entries for
+		 * its partitions and/or descendant tables.  Note that the list
+		 * returned by find_all_inheritors() includes the passed-in OID, so we
+		 * have to skip that.  There's no point in taking locks on the
+		 * individual partitions/tables yet, and doing so would just add
 		 * unnecessary deadlock risk.  For this last reason we do not check
-		 * yet the ownership of the partitions, which get added to the list to
-		 * process.  Ownership will be checked later on anyway.
+		 * yet the ownership of the partitions/tables, which get added to the
+		 * list to process.  Ownership will be checked later on anyway.
 		 */
-		if (include_parts)
+		if (include_children)
 		{
 			List	   *part_oids = find_all_inheritors(relid, NoLock, NULL);
 			ListCell   *part_lc;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 84cef57a70..99d4ece308 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11886,7 +11886,7 @@ opt_name_list:
 		;
 
 vacuum_relation:
-			qualified_name opt_name_list
+			relation_expr opt_name_list
 				{
 					$$ = (Node *) makeVacuumRelation($1, InvalidOid, $2);
 				}
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 2eba712887..7165235ece 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -291,6 +291,86 @@ ANALYZE vactst, vactst;
 BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
+-- ANALYZE ONLY / VACUUM ONLY on partitioned table
+CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
+INSERT INTO only_parted VALUES (1, 'a');
+-- Only partitioned table is analyzed
+ANALYZE ONLY only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+   relname    | analyzed | vacuumed 
+--------------+----------+----------
+ only_parted  | t        | f
+ only_parted1 | f        | f
+(2 rows)
+
+-- Partitioned table and partitions are analyzed
+ANALYZE only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+   relname    | analyzed | vacuumed 
+--------------+----------+----------
+ only_parted  | t        | f
+ only_parted1 | t        | f
+(2 rows)
+
+VACUUM ONLY vacparted; -- gives warning
+WARNING:  VACUUM ONLY of partitioned table "vacparted" has no effect
+ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
+-- ANALYZE ONLY on inherited tables
+CREATE TABLE only_inh_parent (a int primary key, b TEXT);
+CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
+INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
+-- Only parent is ANALYZED
+ANALYZE ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | f        | f
+ only_inh_parent | t        | f
+(2 rows)
+
+-- Parent and child are ANALYZED
+ANALYZE only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | f
+ only_inh_parent | t        | f
+(2 rows)
+
+-- Only parent is VACUUMED
+VACUUM ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | f
+ only_inh_parent | t        | t
+(2 rows)
+
+-- Parent and child are VACUUMED
+VACUUM only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | t
+ only_inh_parent | t        | t
+(2 rows)
+
+DROP TABLE only_parted CASCADE;
+DROP TABLE only_inh_parent CASCADE;
+NOTICE:  drop cascades to table only_inh_child
 -- parenthesized syntax for ANALYZE
 ANALYZE (VERBOSE) does_not_exist;
 ERROR:  relation "does_not_exist" does not exist
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 548cd7acca..fc84df0f07 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -233,6 +233,58 @@ BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
 
+-- ANALYZE ONLY / VACUUM ONLY on partitioned table
+CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
+INSERT INTO only_parted VALUES (1, 'a');
+
+-- Only partitioned table is analyzed
+ANALYZE ONLY only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+
+-- Partitioned table and partitions are analyzed
+ANALYZE only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+
+VACUUM ONLY vacparted; -- gives warning
+ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
+
+-- ANALYZE ONLY on inherited tables
+CREATE TABLE only_inh_parent (a int primary key, b TEXT);
+CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
+INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
+
+-- Only parent is ANALYZED
+ANALYZE ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Parent and child are ANALYZED
+ANALYZE only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Only parent is VACUUMED
+VACUUM ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Parent and child are VACUUMED
+VACUUM only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+DROP TABLE only_parted CASCADE;
+DROP TABLE only_inh_parent CASCADE;
+
 -- parenthesized syntax for ANALYZE
 ANALYZE (VERBOSE) does_not_exist;
 ANALYZE (nonexistent-arg) does_not_exist;
-- 
2.31.1

From eff0406d5ca8132e8a199ced72b8a0cc3b84ea22 Mon Sep 17 00:00:00 2001
From: Michael Harris <harmic@gmail.com>
Date: Fri, 20 Sep 2024 11:17:07 +1000
Subject: [PATCH v6] Implementation of the 'ONLY' feature for ANALYZE and
 VACUUM.

This patch also changes the behaivour of ANALYZE and
VACUUM when given on parent tables in an inheritance
structure, to align with the case of partitioned tables.
In the absence of ONLY, descendant tables will be
included.

Refer to the thread at:
  https://www.postgresql.org/message-id/flat/CADofcAWATx_haD%3DQkSxHbnTsAe6%2Be0Aw8Eh4H8cXyogGvn_kOg%40mail.gmail.com
---
 doc/src/sgml/ddl.sgml                | 22 ++++----
 doc/src/sgml/monitoring.sgml         |  6 +--
 doc/src/sgml/ref/analyze.sgml        | 36 +++++++------
 doc/src/sgml/ref/vacuum.sgml         | 10 ++--
 src/backend/commands/vacuum.c        | 39 ++++++++++----
 src/backend/parser/gram.y            |  2 +-
 src/test/regress/expected/vacuum.out | 80 ++++++++++++++++++++++++++++
 src/test/regress/sql/vacuum.sql      | 52 ++++++++++++++++++
 8 files changed, 203 insertions(+), 44 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index b671858627..c7de29ae71 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3778,12 +3778,14 @@ VALUES ('Albany', NULL, NULL, 'NY');
    not <literal>INSERT</literal> or <literal>ALTER TABLE ...
    RENAME</literal>) typically default to including child tables and
    support the <literal>ONLY</literal> notation to exclude them.
-   Commands that do database maintenance and tuning
-   (e.g., <literal>REINDEX</literal>, <literal>VACUUM</literal>)
-   typically only work on individual, physical tables and do not
-   support recursing over inheritance hierarchies.  The respective
-   behavior of each individual command is documented in its reference
-   page (<xref linkend="sql-commands"/>).
+   Some commands that do database maintenance and tuning
+   (e.g., <literal>REINDEX</literal>) only work on individual, physical
+   tables and do not support recursing over inheritance hierarchies,
+   while other maintenance commands (e.g., <literal>VACUUM</literal>,
+   <literal>ANALYZE</literal>) default to including child tables
+   and support the <literal>ONLY</literal> notation to exclude them.
+   The respective behavior of each individual command is documented
+   in its reference page (<xref linkend="sql-commands"/>).
   </para>
 
   <para>
@@ -4854,11 +4856,11 @@ ALTER TABLE measurement_y2008m02 INHERIT measurement;
 
       <listitem>
        <para>
-        If you are using manual <command>VACUUM</command> or
-        <command>ANALYZE</command> commands, don't forget that
-        you need to run them on each child table individually. A command like:
+        Manual <command>VACUUM</command> or <command>ANALYZE</command> commands
+        will automatically process all descendant tables. If this is undesirable,
+        you can use the <literal>ONLY</literal> keyword. A command like:
 <programlisting>
-ANALYZE measurement;
+ANALYZE ONLY measurement;
 </programlisting>
         will only process the root table.
        </para>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index a2fda4677d..82d6aef858 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5525,9 +5525,9 @@ FROM pg_stat_get_backend_idset() AS backendid;
 
   <note>
    <para>
-    Note that when <command>ANALYZE</command> is run on a partitioned table,
-    all of its partitions are also recursively analyzed.
-    In that case, <command>ANALYZE</command>
+    Note that when <command>ANALYZE</command> is run on a partitioned table
+    without the <literal>ONLY</literal> keyword, all of its partitions are
+    also recursively analyzed. In that case, <command>ANALYZE</command>
     progress is reported first for the parent table, whereby its inheritance
     statistics are collected, followed by that for each partition.
    </para>
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 2b94b378e9..92e465ca59 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -31,7 +31,7 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
 
 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
 
-    <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+    [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -142,9 +142,12 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
       The name (possibly schema-qualified) of a specific table to
       analyze.  If omitted, all regular tables, partitioned tables, and
       materialized views in the current database are analyzed (but not
-      foreign tables).  If the specified table is a partitioned table, both the
-      inheritance statistics of the partitioned table as a whole and
-      statistics of the individual partitions are updated.
+      foreign tables). If <literal>ONLY</literal> is specified before
+      the table name, only that table is analyzed. If <literal>ONLY</literal>
+      is not specified, the table and all its descendant tables or partitions
+      (if any) are analyzed.  Optionally, <literal>*</literal>
+      can be specified after the table name to explicitly indicate that
+      descendant tables are included.
      </para>
     </listitem>
    </varlistentry>
@@ -284,22 +287,23 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
     <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.
-    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.
+    planning queries that process the inheritance tree as a whole.  If the
+    <literal>ONLY</literal> keyword is used, child tables themselves are not
+    individually analyzed. 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>
     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.
+    sampling rows from all partitions. If <literal>ONLY</literal> is not
+    specified, it will also 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 is guaranteed to be empty.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 9857b35627..465f9fecf7 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -42,7 +42,7 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
 
 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
 
-    <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+    [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -401,8 +401,12 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
     <listitem>
      <para>
       The name (optionally schema-qualified) of a specific table or
-      materialized view to vacuum.  If the specified table is a partitioned
-      table, all of its leaf partitions are vacuumed.
+      materialized view to vacuum.  If <literal>ONLY</literal> is specified before
+      the table name, only that table is vacuumed. If <literal>ONLY</literal>
+      is not specified, the table and all its descendant tables or partitions
+      (if any) are also vacuumed.  Optionally, <literal>*</literal>
+      can be specified after the table name to explicitly indicate that
+      descendant tables are included.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 7d8e9d2045..951e00acee 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -851,7 +851,8 @@ vacuum_open_relation(Oid relid, RangeVar *relation, bits32 options,
 
 /*
  * Given a VacuumRelation, fill in the table OID if it wasn't specified,
- * and optionally add VacuumRelations for partitions of the table.
+ * and optionally add VacuumRelations for partitions or descendant tables
+ * of the table.
  *
  * If a VacuumRelation does not have an OID supplied and is a partitioned
  * table, an extra entry will be added to the output for each partition.
@@ -879,11 +880,15 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 	}
 	else
 	{
-		/* Process a specific relation, and possibly partitions thereof */
+		/*
+		 * Process a specific relation, and possibly partitions and/or child
+		 * tables thereof
+		 */
 		Oid			relid;
 		HeapTuple	tuple;
 		Form_pg_class classForm;
-		bool		include_parts;
+		bool		include_children;
+		bool		is_partitioned_table;
 		int			rvr_opts;
 
 		/*
@@ -944,20 +949,32 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 			MemoryContextSwitchTo(oldcontext);
 		}
 
+		/*
+		 * Vacuuming a partitioned table with ONLY will not do anything since
+		 * the partitioned table itself is empty. Issue a warning if the user
+		 * requests this.
+		 */
+		include_children = vrel->relation->inh;
+		is_partitioned_table = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
+		if ((options & VACOPT_VACUUM) && is_partitioned_table && !include_children)
+			ereport(WARNING,
+					(errmsg("VACUUM ONLY of partitioned table \"%s\" has no effect",
+							vrel->relation->relname)));
+
 
-		include_parts = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
 		ReleaseSysCache(tuple);
 
 		/*
-		 * If it is, make relation list entries for its partitions.  Note that
-		 * the list returned by find_all_inheritors() includes the passed-in
-		 * OID, so we have to skip that.  There's no point in taking locks on
-		 * the individual partitions yet, and doing so would just add
+		 * Unless the user has specified ONLY, make relation list entries for
+		 * its partitions and/or descendant tables.  Note that the list
+		 * returned by find_all_inheritors() includes the passed-in OID, so we
+		 * have to skip that.  There's no point in taking locks on the
+		 * individual partitions/tables yet, and doing so would just add
 		 * unnecessary deadlock risk.  For this last reason we do not check
-		 * yet the ownership of the partitions, which get added to the list to
-		 * process.  Ownership will be checked later on anyway.
+		 * yet the ownership of the partitions/tables, which get added to the
+		 * list to process.  Ownership will be checked later on anyway.
 		 */
-		if (include_parts)
+		if (include_children)
 		{
 			List	   *part_oids = find_all_inheritors(relid, NoLock, NULL);
 			ListCell   *part_lc;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ab304ca989..b1d4642c59 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11921,7 +11921,7 @@ opt_name_list:
 		;
 
 vacuum_relation:
-			qualified_name opt_name_list
+			relation_expr opt_name_list
 				{
 					$$ = (Node *) makeVacuumRelation($1, InvalidOid, $2);
 				}
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 2eba712887..7165235ece 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -291,6 +291,86 @@ ANALYZE vactst, vactst;
 BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
+-- ANALYZE ONLY / VACUUM ONLY on partitioned table
+CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
+INSERT INTO only_parted VALUES (1, 'a');
+-- Only partitioned table is analyzed
+ANALYZE ONLY only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+   relname    | analyzed | vacuumed 
+--------------+----------+----------
+ only_parted  | t        | f
+ only_parted1 | f        | f
+(2 rows)
+
+-- Partitioned table and partitions are analyzed
+ANALYZE only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+   relname    | analyzed | vacuumed 
+--------------+----------+----------
+ only_parted  | t        | f
+ only_parted1 | t        | f
+(2 rows)
+
+VACUUM ONLY vacparted; -- gives warning
+WARNING:  VACUUM ONLY of partitioned table "vacparted" has no effect
+ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
+-- ANALYZE ONLY on inherited tables
+CREATE TABLE only_inh_parent (a int primary key, b TEXT);
+CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
+INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
+-- Only parent is ANALYZED
+ANALYZE ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | f        | f
+ only_inh_parent | t        | f
+(2 rows)
+
+-- Parent and child are ANALYZED
+ANALYZE only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | f
+ only_inh_parent | t        | f
+(2 rows)
+
+-- Only parent is VACUUMED
+VACUUM ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | f
+ only_inh_parent | t        | t
+(2 rows)
+
+-- Parent and child are VACUUMED
+VACUUM only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | t
+ only_inh_parent | t        | t
+(2 rows)
+
+DROP TABLE only_parted CASCADE;
+DROP TABLE only_inh_parent CASCADE;
+NOTICE:  drop cascades to table only_inh_child
 -- parenthesized syntax for ANALYZE
 ANALYZE (VERBOSE) does_not_exist;
 ERROR:  relation "does_not_exist" does not exist
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 548cd7acca..fc84df0f07 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -233,6 +233,58 @@ BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
 
+-- ANALYZE ONLY / VACUUM ONLY on partitioned table
+CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
+INSERT INTO only_parted VALUES (1, 'a');
+
+-- Only partitioned table is analyzed
+ANALYZE ONLY only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+
+-- Partitioned table and partitions are analyzed
+ANALYZE only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+
+VACUUM ONLY vacparted; -- gives warning
+ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
+
+-- ANALYZE ONLY on inherited tables
+CREATE TABLE only_inh_parent (a int primary key, b TEXT);
+CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
+INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
+
+-- Only parent is ANALYZED
+ANALYZE ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Parent and child are ANALYZED
+ANALYZE only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Only parent is VACUUMED
+VACUUM ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Parent and child are VACUUMED
+VACUUM only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+DROP TABLE only_parted CASCADE;
+DROP TABLE only_inh_parent CASCADE;
+
 -- parenthesized syntax for ANALYZE
 ANALYZE (VERBOSE) does_not_exist;
 ANALYZE (nonexistent-arg) does_not_exist;
-- 
2.31.1

From debf2bbe6007f0e913637a0a736819e58369c884 Mon Sep 17 00:00:00 2001
From: Michael Harris <harmic@gmail.com>
Date: Fri, 20 Sep 2024 11:17:07 +1000
Subject: [PATCH v7 1/2] Add ONLY support for VACUUM and ANALYZE

Since autovacuum does not trigger an ANALYZE for partitioned tables,
users must perform these manually.  However, performing a manual ANALYZE
on a partitioned table would always result in recursively analyzing each
partition and that could be undesirable as autovacuum takes care of that.
For partitioned tables that contain a large number of partitions, having
to analyze each partition could take an unreasonably long time, especially
so for tables with a large number of columns.

Here we allow the ONLY keyword to prefix the name of the table to allow
users to have ANALYZE skip processing partitions.  This option can also
be used with VACUUM, but there is no work to do if VACUUM ONLY is used on
a partitioned table.

This commit also changes the behavior of VACUUM	and ANALYZE for
inheritance parents.  Previously inheritance child tables would not be
processed when operating on the parent.  Now, by default we *do* operate
on the child tables.  ONLY can be used to obtain the old behavior.
The release notes should note this as an incompatibility.  The default
behavior has not changed for partitioned tables as these always
recursively processed the partitions.

Author: Michael Harris <harmic@gmail.com>
Discussion: https://postgr.es/m/CADofcAWATx_haD=QkSxHbnTsAe6+e0Aw8Eh4H8cXyogGvn_kOg@mail.gmail.com
Reviewed-by: Jelte Fennema-Nio <postgres@jeltef.nl>
Reviewed-by: Melih Mutlu <m.melihmutlu@gmail.com>
Reviewed-by: Atsushi Torikoshi <torikoshia@oss.nttdata.com>
Reviewed-by: jian he <jian.universality@gmail.com>
Reviewed-by: David Rowley <dgrowleyml@gmail.com>
---
 doc/src/sgml/ddl.sgml                | 22 ++++----
 doc/src/sgml/monitoring.sgml         |  6 +--
 doc/src/sgml/ref/analyze.sgml        | 36 +++++++------
 doc/src/sgml/ref/vacuum.sgml         | 10 ++--
 src/backend/commands/vacuum.c        | 39 ++++++++++----
 src/backend/parser/gram.y            |  2 +-
 src/test/regress/expected/vacuum.out | 80 ++++++++++++++++++++++++++++
 src/test/regress/sql/vacuum.sql      | 52 ++++++++++++++++++
 8 files changed, 203 insertions(+), 44 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index b671858627..c7de29ae71 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3778,12 +3778,14 @@ VALUES ('Albany', NULL, NULL, 'NY');
    not <literal>INSERT</literal> or <literal>ALTER TABLE ...
    RENAME</literal>) typically default to including child tables and
    support the <literal>ONLY</literal> notation to exclude them.
-   Commands that do database maintenance and tuning
-   (e.g., <literal>REINDEX</literal>, <literal>VACUUM</literal>)
-   typically only work on individual, physical tables and do not
-   support recursing over inheritance hierarchies.  The respective
-   behavior of each individual command is documented in its reference
-   page (<xref linkend="sql-commands"/>).
+   Some commands that do database maintenance and tuning
+   (e.g., <literal>REINDEX</literal>) only work on individual, physical
+   tables and do not support recursing over inheritance hierarchies,
+   while other maintenance commands (e.g., <literal>VACUUM</literal>,
+   <literal>ANALYZE</literal>) default to including child tables
+   and support the <literal>ONLY</literal> notation to exclude them.
+   The respective behavior of each individual command is documented
+   in its reference page (<xref linkend="sql-commands"/>).
   </para>
 
   <para>
@@ -4854,11 +4856,11 @@ ALTER TABLE measurement_y2008m02 INHERIT measurement;
 
       <listitem>
        <para>
-        If you are using manual <command>VACUUM</command> or
-        <command>ANALYZE</command> commands, don't forget that
-        you need to run them on each child table individually. A command like:
+        Manual <command>VACUUM</command> or <command>ANALYZE</command> commands
+        will automatically process all descendant tables. If this is undesirable,
+        you can use the <literal>ONLY</literal> keyword. A command like:
 <programlisting>
-ANALYZE measurement;
+ANALYZE ONLY measurement;
 </programlisting>
         will only process the root table.
        </para>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index a2fda4677d..82d6aef858 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5525,9 +5525,9 @@ FROM pg_stat_get_backend_idset() AS backendid;
 
   <note>
    <para>
-    Note that when <command>ANALYZE</command> is run on a partitioned table,
-    all of its partitions are also recursively analyzed.
-    In that case, <command>ANALYZE</command>
+    Note that when <command>ANALYZE</command> is run on a partitioned table
+    without the <literal>ONLY</literal> keyword, all of its partitions are
+    also recursively analyzed. In that case, <command>ANALYZE</command>
     progress is reported first for the parent table, whereby its inheritance
     statistics are collected, followed by that for each partition.
    </para>
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 2b94b378e9..92e465ca59 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -31,7 +31,7 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
 
 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
 
-    <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+    [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -142,9 +142,12 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
       The name (possibly schema-qualified) of a specific table to
       analyze.  If omitted, all regular tables, partitioned tables, and
       materialized views in the current database are analyzed (but not
-      foreign tables).  If the specified table is a partitioned table, both the
-      inheritance statistics of the partitioned table as a whole and
-      statistics of the individual partitions are updated.
+      foreign tables). If <literal>ONLY</literal> is specified before
+      the table name, only that table is analyzed. If <literal>ONLY</literal>
+      is not specified, the table and all its descendant tables or partitions
+      (if any) are analyzed.  Optionally, <literal>*</literal>
+      can be specified after the table name to explicitly indicate that
+      descendant tables are included.
      </para>
     </listitem>
    </varlistentry>
@@ -284,22 +287,23 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
     <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.
-    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.
+    planning queries that process the inheritance tree as a whole.  If the
+    <literal>ONLY</literal> keyword is used, child tables themselves are not
+    individually analyzed. 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>
     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.
+    sampling rows from all partitions. If <literal>ONLY</literal> is not
+    specified, it will also 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 is guaranteed to be empty.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 9857b35627..465f9fecf7 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -42,7 +42,7 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
 
 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
 
-    <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+    [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -401,8 +401,12 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
     <listitem>
      <para>
       The name (optionally schema-qualified) of a specific table or
-      materialized view to vacuum.  If the specified table is a partitioned
-      table, all of its leaf partitions are vacuumed.
+      materialized view to vacuum.  If <literal>ONLY</literal> is specified before
+      the table name, only that table is vacuumed. If <literal>ONLY</literal>
+      is not specified, the table and all its descendant tables or partitions
+      (if any) are also vacuumed.  Optionally, <literal>*</literal>
+      can be specified after the table name to explicitly indicate that
+      descendant tables are included.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 7d8e9d2045..951e00acee 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -851,7 +851,8 @@ vacuum_open_relation(Oid relid, RangeVar *relation, bits32 options,
 
 /*
  * Given a VacuumRelation, fill in the table OID if it wasn't specified,
- * and optionally add VacuumRelations for partitions of the table.
+ * and optionally add VacuumRelations for partitions or descendant tables
+ * of the table.
  *
  * If a VacuumRelation does not have an OID supplied and is a partitioned
  * table, an extra entry will be added to the output for each partition.
@@ -879,11 +880,15 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 	}
 	else
 	{
-		/* Process a specific relation, and possibly partitions thereof */
+		/*
+		 * Process a specific relation, and possibly partitions and/or child
+		 * tables thereof
+		 */
 		Oid			relid;
 		HeapTuple	tuple;
 		Form_pg_class classForm;
-		bool		include_parts;
+		bool		include_children;
+		bool		is_partitioned_table;
 		int			rvr_opts;
 
 		/*
@@ -944,20 +949,32 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 			MemoryContextSwitchTo(oldcontext);
 		}
 
+		/*
+		 * Vacuuming a partitioned table with ONLY will not do anything since
+		 * the partitioned table itself is empty. Issue a warning if the user
+		 * requests this.
+		 */
+		include_children = vrel->relation->inh;
+		is_partitioned_table = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
+		if ((options & VACOPT_VACUUM) && is_partitioned_table && !include_children)
+			ereport(WARNING,
+					(errmsg("VACUUM ONLY of partitioned table \"%s\" has no effect",
+							vrel->relation->relname)));
+
 
-		include_parts = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
 		ReleaseSysCache(tuple);
 
 		/*
-		 * If it is, make relation list entries for its partitions.  Note that
-		 * the list returned by find_all_inheritors() includes the passed-in
-		 * OID, so we have to skip that.  There's no point in taking locks on
-		 * the individual partitions yet, and doing so would just add
+		 * Unless the user has specified ONLY, make relation list entries for
+		 * its partitions and/or descendant tables.  Note that the list
+		 * returned by find_all_inheritors() includes the passed-in OID, so we
+		 * have to skip that.  There's no point in taking locks on the
+		 * individual partitions/tables yet, and doing so would just add
 		 * unnecessary deadlock risk.  For this last reason we do not check
-		 * yet the ownership of the partitions, which get added to the list to
-		 * process.  Ownership will be checked later on anyway.
+		 * yet the ownership of the partitions/tables, which get added to the
+		 * list to process.  Ownership will be checked later on anyway.
 		 */
-		if (include_parts)
+		if (include_children)
 		{
 			List	   *part_oids = find_all_inheritors(relid, NoLock, NULL);
 			ListCell   *part_lc;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ab304ca989..b1d4642c59 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11921,7 +11921,7 @@ opt_name_list:
 		;
 
 vacuum_relation:
-			qualified_name opt_name_list
+			relation_expr opt_name_list
 				{
 					$$ = (Node *) makeVacuumRelation($1, InvalidOid, $2);
 				}
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 2eba712887..7165235ece 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -291,6 +291,86 @@ ANALYZE vactst, vactst;
 BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
+-- ANALYZE ONLY / VACUUM ONLY on partitioned table
+CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
+INSERT INTO only_parted VALUES (1, 'a');
+-- Only partitioned table is analyzed
+ANALYZE ONLY only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+   relname    | analyzed | vacuumed 
+--------------+----------+----------
+ only_parted  | t        | f
+ only_parted1 | f        | f
+(2 rows)
+
+-- Partitioned table and partitions are analyzed
+ANALYZE only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+   relname    | analyzed | vacuumed 
+--------------+----------+----------
+ only_parted  | t        | f
+ only_parted1 | t        | f
+(2 rows)
+
+VACUUM ONLY vacparted; -- gives warning
+WARNING:  VACUUM ONLY of partitioned table "vacparted" has no effect
+ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
+-- ANALYZE ONLY on inherited tables
+CREATE TABLE only_inh_parent (a int primary key, b TEXT);
+CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
+INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
+-- Only parent is ANALYZED
+ANALYZE ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | f        | f
+ only_inh_parent | t        | f
+(2 rows)
+
+-- Parent and child are ANALYZED
+ANALYZE only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | f
+ only_inh_parent | t        | f
+(2 rows)
+
+-- Only parent is VACUUMED
+VACUUM ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | f
+ only_inh_parent | t        | t
+(2 rows)
+
+-- Parent and child are VACUUMED
+VACUUM only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | t
+ only_inh_parent | t        | t
+(2 rows)
+
+DROP TABLE only_parted CASCADE;
+DROP TABLE only_inh_parent CASCADE;
+NOTICE:  drop cascades to table only_inh_child
 -- parenthesized syntax for ANALYZE
 ANALYZE (VERBOSE) does_not_exist;
 ERROR:  relation "does_not_exist" does not exist
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 548cd7acca..fc84df0f07 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -233,6 +233,58 @@ BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
 
+-- ANALYZE ONLY / VACUUM ONLY on partitioned table
+CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
+INSERT INTO only_parted VALUES (1, 'a');
+
+-- Only partitioned table is analyzed
+ANALYZE ONLY only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+
+-- Partitioned table and partitions are analyzed
+ANALYZE only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+
+VACUUM ONLY vacparted; -- gives warning
+ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
+
+-- ANALYZE ONLY on inherited tables
+CREATE TABLE only_inh_parent (a int primary key, b TEXT);
+CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
+INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
+
+-- Only parent is ANALYZED
+ANALYZE ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Parent and child are ANALYZED
+ANALYZE only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Only parent is VACUUMED
+VACUUM ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Parent and child are VACUUMED
+VACUUM only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+DROP TABLE only_parted CASCADE;
+DROP TABLE only_inh_parent CASCADE;
+
 -- parenthesized syntax for ANALYZE
 ANALYZE (VERBOSE) does_not_exist;
 ANALYZE (nonexistent-arg) does_not_exist;
-- 
2.34.1

From a9ead4e7073059b2da51b461097e27b6b1a37076 Mon Sep 17 00:00:00 2001
From: David Rowley <dgrowley@gmail.com>
Date: Sun, 22 Sep 2024 23:56:39 +1200
Subject: [PATCH v7 2/2] fixup! Implementation of the 'ONLY' feature for
 ANALYZE and VACUUM.

---
 doc/src/sgml/ddl.sgml                | 21 +++++-----
 doc/src/sgml/monitoring.sgml         |  2 +-
 doc/src/sgml/ref/analyze.sgml        | 39 +++++++++--------
 doc/src/sgml/ref/vacuum.sgml         | 13 +++---
 src/backend/commands/vacuum.c        | 12 +++---
 src/test/regress/expected/vacuum.out | 60 ++++++++++++++++-----------
 src/test/regress/sql/vacuum.sql      | 62 +++++++++++++++++-----------
 7 files changed, 119 insertions(+), 90 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index c7de29ae71..8ab0ddb112 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3778,14 +3778,14 @@ VALUES ('Albany', NULL, NULL, 'NY');
    not <literal>INSERT</literal> or <literal>ALTER TABLE ...
    RENAME</literal>) typically default to including child tables and
    support the <literal>ONLY</literal> notation to exclude them.
-   Some commands that do database maintenance and tuning
+   The majority of commands that do database maintenance and tuning
    (e.g., <literal>REINDEX</literal>) only work on individual, physical
-   tables and do not support recursing over inheritance hierarchies,
-   while other maintenance commands (e.g., <literal>VACUUM</literal>,
-   <literal>ANALYZE</literal>) default to including child tables
-   and support the <literal>ONLY</literal> notation to exclude them.
-   The respective behavior of each individual command is documented
-   in its reference page (<xref linkend="sql-commands"/>).
+   tables and do not support recursing over inheritance hierarchies.
+   However, both <literal>VACUUM</literal> and <literal>ANALYZE</literal>
+   commands default to including child tables and the <literal>ONLY</literal>
+   notation is supported to allow them to be excluded.  The respective
+   behavior of each individual command is documented in its reference page
+   (<xref linkend="sql-commands"/>).
   </para>
 
   <para>
@@ -4856,9 +4856,10 @@ ALTER TABLE measurement_y2008m02 INHERIT measurement;
 
       <listitem>
        <para>
-        Manual <command>VACUUM</command> or <command>ANALYZE</command> commands
-        will automatically process all descendant tables. If this is undesirable,
-        you can use the <literal>ONLY</literal> keyword. A command like:
+        Manual <command>VACUUM</command> and <command>ANALYZE</command>
+        commands will automatically process all inheritance child tables.  If
+        this is undesirable, you can use the <literal>ONLY</literal> keyword.
+        A command like:
 <programlisting>
 ANALYZE ONLY measurement;
 </programlisting>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 82d6aef858..db7f35a451 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5527,7 +5527,7 @@ FROM pg_stat_get_backend_idset() AS backendid;
    <para>
     Note that when <command>ANALYZE</command> is run on a partitioned table
     without the <literal>ONLY</literal> keyword, all of its partitions are
-    also recursively analyzed. In that case, <command>ANALYZE</command>
+    also recursively analyzed.  In that case, <command>ANALYZE</command>
     progress is reported first for the parent table, whereby its inheritance
     statistics are collected, followed by that for each partition.
    </para>
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 92e465ca59..78a565f792 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -142,12 +142,12 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
       The name (possibly schema-qualified) of a specific table to
       analyze.  If omitted, all regular tables, partitioned tables, and
       materialized views in the current database are analyzed (but not
-      foreign tables). If <literal>ONLY</literal> is specified before
-      the table name, only that table is analyzed. If <literal>ONLY</literal>
-      is not specified, the table and all its descendant tables or partitions
-      (if any) are analyzed.  Optionally, <literal>*</literal>
+      foreign tables).  If <literal>ONLY</literal> is specified before
+      the table name, only that table is analyzed.  If <literal>ONLY</literal>
+      is not specified, the table and all its inheritance child tables or
+      partitions (if any) are analyzed.  Optionally, <literal>*</literal>
       can be specified after the table name to explicitly indicate that
-      descendant tables are included.
+      inheritance child tables (or partitions) are to be analyzed.
      </para>
     </listitem>
    </varlistentry>
@@ -287,23 +287,26 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
     <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.  If the
-    <literal>ONLY</literal> keyword is used, child tables themselves are not
-    individually analyzed. 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.
+    planning queries that process the inheritance tree as a whole.  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.  By default,
+    <command>ANALYZE</command> will also recursively collect statistics for
+    each inheritance child table.  The <literal>ONLY</literal> keyword may be
+    used to disable this.
   </para>
 
   <para>
     For partitioned tables, <command>ANALYZE</command> gathers statistics by
-    sampling rows from all partitions. If <literal>ONLY</literal> is not
-    specified, it will also 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 is guaranteed to be empty.
+    sampling rows from all partitions.  Each leaf partition is also
+    recursively analyzed and the statistics updated.  This recursive part
+    may be disabled by using the <literal>ONLY</literal> keyword, otherwise,
+    leaf partitions are 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>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 465f9fecf7..9110938fab 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -401,12 +401,13 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
     <listitem>
      <para>
       The name (optionally schema-qualified) of a specific table or
-      materialized view to vacuum.  If <literal>ONLY</literal> is specified before
-      the table name, only that table is vacuumed. If <literal>ONLY</literal>
-      is not specified, the table and all its descendant tables or partitions
-      (if any) are also vacuumed.  Optionally, <literal>*</literal>
-      can be specified after the table name to explicitly indicate that
-      descendant tables are included.
+      materialized view to vacuum.  If <literal>ONLY</literal> is specified
+      before the table name, only that table is vacuumed.  If
+      <literal>ONLY</literal> is not specified, the table and all its
+      inheritance child tables or partitions (if any) are also vacuumed.
+      Optionally, <literal>*</literal> can be specified after the table name
+      to explicitly indicate that inheritance child tables (or partitions) are
+      to be vacuumed.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 951e00acee..bcadca4214 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -851,8 +851,7 @@ vacuum_open_relation(Oid relid, RangeVar *relation, bits32 options,
 
 /*
  * Given a VacuumRelation, fill in the table OID if it wasn't specified,
- * and optionally add VacuumRelations for partitions or descendant tables
- * of the table.
+ * and optionally add VacuumRelations for partitions or inheritance children.
  *
  * If a VacuumRelation does not have an OID supplied and is a partitioned
  * table, an extra entry will be added to the output for each partition.
@@ -881,8 +880,8 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 	else
 	{
 		/*
-		 * Process a specific relation, and possibly partitions and/or child
-		 * tables thereof
+		 * Process a specific relation, and possibly partitions or child
+		 * tables thereof.
 		 */
 		Oid			relid;
 		HeapTuple	tuple;
@@ -951,7 +950,7 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 
 		/*
 		 * Vacuuming a partitioned table with ONLY will not do anything since
-		 * the partitioned table itself is empty. Issue a warning if the user
+		 * the partitioned table itself is empty.  Issue a warning if the user
 		 * requests this.
 		 */
 		include_children = vrel->relation->inh;
@@ -961,12 +960,11 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 					(errmsg("VACUUM ONLY of partitioned table \"%s\" has no effect",
 							vrel->relation->relname)));
 
-
 		ReleaseSysCache(tuple);
 
 		/*
 		 * Unless the user has specified ONLY, make relation list entries for
-		 * its partitions and/or descendant tables.  Note that the list
+		 * its partitions or inheritance child tables.  Note that the list
 		 * returned by find_all_inheritors() includes the passed-in OID, so we
 		 * have to skip that.  There's no point in taking locks on the
 		 * individual partitions/tables yet, and doing so would just add
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 7165235ece..709d53047c 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -291,14 +291,17 @@ ANALYZE vactst, vactst;
 BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
--- ANALYZE ONLY / VACUUM ONLY on partitioned table
-CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+--
+-- Tests for ANALYZE ONLY / VACUUM ONLY on partitioned tables
+--
+CREATE TABLE only_parted (a int, b text) PARTITION BY LIST (a);
 CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
 INSERT INTO only_parted VALUES (1, 'a');
--- Only partitioned table is analyzed
+-- Ensure only the partitioned table is analyzed
 ANALYZE ONLY only_parted;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_parted%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
   ORDER BY relname;
    relname    | analyzed | vacuumed 
 --------------+----------+----------
@@ -306,10 +309,11 @@ SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as
  only_parted1 | f        | f
 (2 rows)
 
--- Partitioned table and partitions are analyzed
+-- Ensure partitioned table and the partitions are analyzed
 ANALYZE only_parted;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_parted%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
   ORDER BY relname;
    relname    | analyzed | vacuumed 
 --------------+----------+----------
@@ -317,17 +321,23 @@ SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as
  only_parted1 | t        | f
 (2 rows)
 
-VACUUM ONLY vacparted; -- gives warning
+DROP TABLE only_parted;
+-- VACUUM ONLY on a partitioned table does nothing, ensure we get a warning.
+VACUUM ONLY vacparted;
 WARNING:  VACUUM ONLY of partitioned table "vacparted" has no effect
-ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
--- ANALYZE ONLY on inherited tables
+-- Try ANALYZE ONLY with a column list
+ANALYZE ONLY vacparted(a,b); 
+--
+-- Tests for VACUUM ONLY / ANALYZE ONLY on inheritance tables
+--
 CREATE TABLE only_inh_parent (a int primary key, b TEXT);
 CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
 INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
--- Only parent is ANALYZED
+-- Ensure only parent is analyzed
 ANALYZE ONLY only_inh_parent;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_inh%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
   ORDER BY relname;
      relname     | analyzed | vacuumed 
 -----------------+----------+----------
@@ -335,10 +345,11 @@ SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as
  only_inh_parent | t        | f
 (2 rows)
 
--- Parent and child are ANALYZED
+-- Ensure the parent and child are analyzed
 ANALYZE only_inh_parent;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_inh%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
   ORDER BY relname;
      relname     | analyzed | vacuumed 
 -----------------+----------+----------
@@ -346,10 +357,11 @@ SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as
  only_inh_parent | t        | f
 (2 rows)
 
--- Only parent is VACUUMED
+-- Ensure only the parent is vacuumed
 VACUUM ONLY only_inh_parent;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_inh%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
   ORDER BY relname;
      relname     | analyzed | vacuumed 
 -----------------+----------+----------
@@ -357,10 +369,11 @@ SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as
  only_inh_parent | t        | t
 (2 rows)
 
--- Parent and child are VACUUMED
+-- Ensure parent and child are vacuumed
 VACUUM only_inh_parent;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_inh%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
   ORDER BY relname;
      relname     | analyzed | vacuumed 
 -----------------+----------+----------
@@ -368,7 +381,6 @@ SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as
  only_inh_parent | t        | t
 (2 rows)
 
-DROP TABLE only_parted CASCADE;
 DROP TABLE only_inh_parent CASCADE;
 NOTICE:  drop cascades to table only_inh_child
 -- parenthesized syntax for ANALYZE
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index fc84df0f07..5e55079e71 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -233,56 +233,70 @@ BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
 
--- ANALYZE ONLY / VACUUM ONLY on partitioned table
-CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+--
+-- Tests for ANALYZE ONLY / VACUUM ONLY on partitioned tables
+--
+CREATE TABLE only_parted (a int, b text) PARTITION BY LIST (a);
 CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
 INSERT INTO only_parted VALUES (1, 'a');
 
--- Only partitioned table is analyzed
+-- Ensure only the partitioned table is analyzed
 ANALYZE ONLY only_parted;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_parted%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
   ORDER BY relname;
 
--- Partitioned table and partitions are analyzed
+-- Ensure partitioned table and the partitions are analyzed
 ANALYZE only_parted;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_parted%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
   ORDER BY relname;
 
-VACUUM ONLY vacparted; -- gives warning
-ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
+DROP TABLE only_parted;
+
+-- VACUUM ONLY on a partitioned table does nothing, ensure we get a warning.
+VACUUM ONLY vacparted;
 
--- ANALYZE ONLY on inherited tables
+-- Try ANALYZE ONLY with a column list
+ANALYZE ONLY vacparted(a,b);
+
+--
+-- Tests for VACUUM ONLY / ANALYZE ONLY on inheritance tables
+--
 CREATE TABLE only_inh_parent (a int primary key, b TEXT);
 CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
 INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
 
--- Only parent is ANALYZED
+-- Ensure only parent is analyzed
 ANALYZE ONLY only_inh_parent;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_inh%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
   ORDER BY relname;
 
--- Parent and child are ANALYZED
+-- Ensure the parent and child are analyzed
 ANALYZE only_inh_parent;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_inh%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
   ORDER BY relname;
 
--- Only parent is VACUUMED
+-- Ensure only the parent is vacuumed
 VACUUM ONLY only_inh_parent;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_inh%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
   ORDER BY relname;
 
--- Parent and child are VACUUMED
+-- Ensure parent and child are vacuumed
 VACUUM only_inh_parent;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_inh%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
   ORDER BY relname;
 
-DROP TABLE only_parted CASCADE;
 DROP TABLE only_inh_parent CASCADE;
 
 -- parenthesized syntax for ANALYZE
-- 
2.34.1

From 2218eda1bdafe47bc1bdfffeb9466d9f99f8d9b6 Mon Sep 17 00:00:00 2001
From: Michael Harris <harmic@gmail.com>
Date: Fri, 20 Sep 2024 11:17:07 +1000
Subject: [PATCH v8 1/2] Add ONLY support for VACUUM and ANALYZE

Since autovacuum does not trigger an ANALYZE for partitioned tables,
users must perform these manually.  However, performing a manual ANALYZE
on a partitioned table would always result in recursively analyzing each
partition and that could be undesirable as autovacuum takes care of that.
For partitioned tables that contain a large number of partitions, having
to analyze each partition could take an unreasonably long time, especially
so for tables with a large number of columns.

Here we allow the ONLY keyword to prefix the name of the table to allow
users to have ANALYZE skip processing partitions.  This option can also
be used with VACUUM, but there is no work to do if VACUUM ONLY is used on
a partitioned table.

This commit also changes the behavior of VACUUM	and ANALYZE for
inheritance parents.  Previously inheritance child tables would not be
processed when operating on the parent.  Now, by default we *do* operate
on the child tables.  ONLY can be used to obtain the old behavior.
The release notes should note this as an incompatibility.  The default
behavior has not changed for partitioned tables as these always
recursively processed the partitions.

Author: Michael Harris <harmic@gmail.com>
Discussion: https://postgr.es/m/CADofcAWATx_haD=QkSxHbnTsAe6+e0Aw8Eh4H8cXyogGvn_kOg@mail.gmail.com
Reviewed-by: Jelte Fennema-Nio <postgres@jeltef.nl>
Reviewed-by: Melih Mutlu <m.melihmutlu@gmail.com>
Reviewed-by: Atsushi Torikoshi <torikoshia@oss.nttdata.com>
Reviewed-by: jian he <jian.universality@gmail.com>
Reviewed-by: David Rowley <dgrowleyml@gmail.com>
---
 doc/src/sgml/ddl.sgml                | 22 ++++----
 doc/src/sgml/monitoring.sgml         |  6 +--
 doc/src/sgml/ref/analyze.sgml        | 36 +++++++------
 doc/src/sgml/ref/vacuum.sgml         | 10 ++--
 src/backend/commands/vacuum.c        | 39 ++++++++++----
 src/backend/parser/gram.y            |  2 +-
 src/test/regress/expected/vacuum.out | 80 ++++++++++++++++++++++++++++
 src/test/regress/sql/vacuum.sql      | 52 ++++++++++++++++++
 8 files changed, 203 insertions(+), 44 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index b671858627..c7de29ae71 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3778,12 +3778,14 @@ VALUES ('Albany', NULL, NULL, 'NY');
    not <literal>INSERT</literal> or <literal>ALTER TABLE ...
    RENAME</literal>) typically default to including child tables and
    support the <literal>ONLY</literal> notation to exclude them.
-   Commands that do database maintenance and tuning
-   (e.g., <literal>REINDEX</literal>, <literal>VACUUM</literal>)
-   typically only work on individual, physical tables and do not
-   support recursing over inheritance hierarchies.  The respective
-   behavior of each individual command is documented in its reference
-   page (<xref linkend="sql-commands"/>).
+   Some commands that do database maintenance and tuning
+   (e.g., <literal>REINDEX</literal>) only work on individual, physical
+   tables and do not support recursing over inheritance hierarchies,
+   while other maintenance commands (e.g., <literal>VACUUM</literal>,
+   <literal>ANALYZE</literal>) default to including child tables
+   and support the <literal>ONLY</literal> notation to exclude them.
+   The respective behavior of each individual command is documented
+   in its reference page (<xref linkend="sql-commands"/>).
   </para>
 
   <para>
@@ -4854,11 +4856,11 @@ ALTER TABLE measurement_y2008m02 INHERIT measurement;
 
       <listitem>
        <para>
-        If you are using manual <command>VACUUM</command> or
-        <command>ANALYZE</command> commands, don't forget that
-        you need to run them on each child table individually. A command like:
+        Manual <command>VACUUM</command> or <command>ANALYZE</command> commands
+        will automatically process all descendant tables. If this is undesirable,
+        you can use the <literal>ONLY</literal> keyword. A command like:
 <programlisting>
-ANALYZE measurement;
+ANALYZE ONLY measurement;
 </programlisting>
         will only process the root table.
        </para>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index a2fda4677d..82d6aef858 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5525,9 +5525,9 @@ FROM pg_stat_get_backend_idset() AS backendid;
 
   <note>
    <para>
-    Note that when <command>ANALYZE</command> is run on a partitioned table,
-    all of its partitions are also recursively analyzed.
-    In that case, <command>ANALYZE</command>
+    Note that when <command>ANALYZE</command> is run on a partitioned table
+    without the <literal>ONLY</literal> keyword, all of its partitions are
+    also recursively analyzed. In that case, <command>ANALYZE</command>
     progress is reported first for the parent table, whereby its inheritance
     statistics are collected, followed by that for each partition.
    </para>
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 2b94b378e9..92e465ca59 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -31,7 +31,7 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
 
 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
 
-    <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+    [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -142,9 +142,12 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
       The name (possibly schema-qualified) of a specific table to
       analyze.  If omitted, all regular tables, partitioned tables, and
       materialized views in the current database are analyzed (but not
-      foreign tables).  If the specified table is a partitioned table, both the
-      inheritance statistics of the partitioned table as a whole and
-      statistics of the individual partitions are updated.
+      foreign tables). If <literal>ONLY</literal> is specified before
+      the table name, only that table is analyzed. If <literal>ONLY</literal>
+      is not specified, the table and all its descendant tables or partitions
+      (if any) are analyzed.  Optionally, <literal>*</literal>
+      can be specified after the table name to explicitly indicate that
+      descendant tables are included.
      </para>
     </listitem>
    </varlistentry>
@@ -284,22 +287,23 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
     <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.
-    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.
+    planning queries that process the inheritance tree as a whole.  If the
+    <literal>ONLY</literal> keyword is used, child tables themselves are not
+    individually analyzed. 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>
     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.
+    sampling rows from all partitions. If <literal>ONLY</literal> is not
+    specified, it will also 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 is guaranteed to be empty.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 9857b35627..465f9fecf7 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -42,7 +42,7 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
 
 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
 
-    <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+    [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -401,8 +401,12 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
     <listitem>
      <para>
       The name (optionally schema-qualified) of a specific table or
-      materialized view to vacuum.  If the specified table is a partitioned
-      table, all of its leaf partitions are vacuumed.
+      materialized view to vacuum.  If <literal>ONLY</literal> is specified before
+      the table name, only that table is vacuumed. If <literal>ONLY</literal>
+      is not specified, the table and all its descendant tables or partitions
+      (if any) are also vacuumed.  Optionally, <literal>*</literal>
+      can be specified after the table name to explicitly indicate that
+      descendant tables are included.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 7d8e9d2045..951e00acee 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -851,7 +851,8 @@ vacuum_open_relation(Oid relid, RangeVar *relation, bits32 options,
 
 /*
  * Given a VacuumRelation, fill in the table OID if it wasn't specified,
- * and optionally add VacuumRelations for partitions of the table.
+ * and optionally add VacuumRelations for partitions or descendant tables
+ * of the table.
  *
  * If a VacuumRelation does not have an OID supplied and is a partitioned
  * table, an extra entry will be added to the output for each partition.
@@ -879,11 +880,15 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 	}
 	else
 	{
-		/* Process a specific relation, and possibly partitions thereof */
+		/*
+		 * Process a specific relation, and possibly partitions and/or child
+		 * tables thereof
+		 */
 		Oid			relid;
 		HeapTuple	tuple;
 		Form_pg_class classForm;
-		bool		include_parts;
+		bool		include_children;
+		bool		is_partitioned_table;
 		int			rvr_opts;
 
 		/*
@@ -944,20 +949,32 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 			MemoryContextSwitchTo(oldcontext);
 		}
 
+		/*
+		 * Vacuuming a partitioned table with ONLY will not do anything since
+		 * the partitioned table itself is empty. Issue a warning if the user
+		 * requests this.
+		 */
+		include_children = vrel->relation->inh;
+		is_partitioned_table = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
+		if ((options & VACOPT_VACUUM) && is_partitioned_table && !include_children)
+			ereport(WARNING,
+					(errmsg("VACUUM ONLY of partitioned table \"%s\" has no effect",
+							vrel->relation->relname)));
+
 
-		include_parts = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
 		ReleaseSysCache(tuple);
 
 		/*
-		 * If it is, make relation list entries for its partitions.  Note that
-		 * the list returned by find_all_inheritors() includes the passed-in
-		 * OID, so we have to skip that.  There's no point in taking locks on
-		 * the individual partitions yet, and doing so would just add
+		 * Unless the user has specified ONLY, make relation list entries for
+		 * its partitions and/or descendant tables.  Note that the list
+		 * returned by find_all_inheritors() includes the passed-in OID, so we
+		 * have to skip that.  There's no point in taking locks on the
+		 * individual partitions/tables yet, and doing so would just add
 		 * unnecessary deadlock risk.  For this last reason we do not check
-		 * yet the ownership of the partitions, which get added to the list to
-		 * process.  Ownership will be checked later on anyway.
+		 * yet the ownership of the partitions/tables, which get added to the
+		 * list to process.  Ownership will be checked later on anyway.
 		 */
-		if (include_parts)
+		if (include_children)
 		{
 			List	   *part_oids = find_all_inheritors(relid, NoLock, NULL);
 			ListCell   *part_lc;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ab304ca989..b1d4642c59 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11921,7 +11921,7 @@ opt_name_list:
 		;
 
 vacuum_relation:
-			qualified_name opt_name_list
+			relation_expr opt_name_list
 				{
 					$$ = (Node *) makeVacuumRelation($1, InvalidOid, $2);
 				}
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 2eba712887..7165235ece 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -291,6 +291,86 @@ ANALYZE vactst, vactst;
 BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
+-- ANALYZE ONLY / VACUUM ONLY on partitioned table
+CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
+INSERT INTO only_parted VALUES (1, 'a');
+-- Only partitioned table is analyzed
+ANALYZE ONLY only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+   relname    | analyzed | vacuumed 
+--------------+----------+----------
+ only_parted  | t        | f
+ only_parted1 | f        | f
+(2 rows)
+
+-- Partitioned table and partitions are analyzed
+ANALYZE only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+   relname    | analyzed | vacuumed 
+--------------+----------+----------
+ only_parted  | t        | f
+ only_parted1 | t        | f
+(2 rows)
+
+VACUUM ONLY vacparted; -- gives warning
+WARNING:  VACUUM ONLY of partitioned table "vacparted" has no effect
+ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
+-- ANALYZE ONLY on inherited tables
+CREATE TABLE only_inh_parent (a int primary key, b TEXT);
+CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
+INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
+-- Only parent is ANALYZED
+ANALYZE ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | f        | f
+ only_inh_parent | t        | f
+(2 rows)
+
+-- Parent and child are ANALYZED
+ANALYZE only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | f
+ only_inh_parent | t        | f
+(2 rows)
+
+-- Only parent is VACUUMED
+VACUUM ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | f
+ only_inh_parent | t        | t
+(2 rows)
+
+-- Parent and child are VACUUMED
+VACUUM only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+     relname     | analyzed | vacuumed 
+-----------------+----------+----------
+ only_inh_child  | t        | t
+ only_inh_parent | t        | t
+(2 rows)
+
+DROP TABLE only_parted CASCADE;
+DROP TABLE only_inh_parent CASCADE;
+NOTICE:  drop cascades to table only_inh_child
 -- parenthesized syntax for ANALYZE
 ANALYZE (VERBOSE) does_not_exist;
 ERROR:  relation "does_not_exist" does not exist
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index 548cd7acca..fc84df0f07 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -233,6 +233,58 @@ BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
 
+-- ANALYZE ONLY / VACUUM ONLY on partitioned table
+CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
+INSERT INTO only_parted VALUES (1, 'a');
+
+-- Only partitioned table is analyzed
+ANALYZE ONLY only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+
+-- Partitioned table and partitions are analyzed
+ANALYZE only_parted;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_parted%'
+  ORDER BY relname;
+
+VACUUM ONLY vacparted; -- gives warning
+ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
+
+-- ANALYZE ONLY on inherited tables
+CREATE TABLE only_inh_parent (a int primary key, b TEXT);
+CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
+INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
+
+-- Only parent is ANALYZED
+ANALYZE ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Parent and child are ANALYZED
+ANALYZE only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Only parent is VACUUMED
+VACUUM ONLY only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+-- Parent and child are VACUUMED
+VACUUM only_inh_parent;
+SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
+  WHERE relname like 'only_inh%'
+  ORDER BY relname;
+
+DROP TABLE only_parted CASCADE;
+DROP TABLE only_inh_parent CASCADE;
+
 -- parenthesized syntax for ANALYZE
 ANALYZE (VERBOSE) does_not_exist;
 ANALYZE (nonexistent-arg) does_not_exist;
-- 
2.34.1

From c1f364e4a64fec0a7acb9acf3da5acdd2d21789e Mon Sep 17 00:00:00 2001
From: David Rowley <dgrowley@gmail.com>
Date: Sun, 22 Sep 2024 23:56:39 +1200
Subject: [PATCH v8 2/2] fixup! Implementation of the 'ONLY' feature for
 ANALYZE and VACUUM.

---
 doc/src/sgml/ddl.sgml                | 21 +++++-----
 doc/src/sgml/monitoring.sgml         |  2 +-
 doc/src/sgml/ref/analyze.sgml        | 36 ++++++++--------
 doc/src/sgml/ref/vacuum.sgml         | 13 +++---
 src/backend/commands/vacuum.c        | 12 +++---
 src/test/regress/expected/vacuum.out | 60 ++++++++++++++++-----------
 src/test/regress/sql/vacuum.sql      | 62 +++++++++++++++++-----------
 7 files changed, 116 insertions(+), 90 deletions(-)

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index c7de29ae71..8ab0ddb112 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3778,14 +3778,14 @@ VALUES ('Albany', NULL, NULL, 'NY');
    not <literal>INSERT</literal> or <literal>ALTER TABLE ...
    RENAME</literal>) typically default to including child tables and
    support the <literal>ONLY</literal> notation to exclude them.
-   Some commands that do database maintenance and tuning
+   The majority of commands that do database maintenance and tuning
    (e.g., <literal>REINDEX</literal>) only work on individual, physical
-   tables and do not support recursing over inheritance hierarchies,
-   while other maintenance commands (e.g., <literal>VACUUM</literal>,
-   <literal>ANALYZE</literal>) default to including child tables
-   and support the <literal>ONLY</literal> notation to exclude them.
-   The respective behavior of each individual command is documented
-   in its reference page (<xref linkend="sql-commands"/>).
+   tables and do not support recursing over inheritance hierarchies.
+   However, both <literal>VACUUM</literal> and <literal>ANALYZE</literal>
+   commands default to including child tables and the <literal>ONLY</literal>
+   notation is supported to allow them to be excluded.  The respective
+   behavior of each individual command is documented in its reference page
+   (<xref linkend="sql-commands"/>).
   </para>
 
   <para>
@@ -4856,9 +4856,10 @@ ALTER TABLE measurement_y2008m02 INHERIT measurement;
 
       <listitem>
        <para>
-        Manual <command>VACUUM</command> or <command>ANALYZE</command> commands
-        will automatically process all descendant tables. If this is undesirable,
-        you can use the <literal>ONLY</literal> keyword. A command like:
+        Manual <command>VACUUM</command> and <command>ANALYZE</command>
+        commands will automatically process all inheritance child tables.  If
+        this is undesirable, you can use the <literal>ONLY</literal> keyword.
+        A command like:
 <programlisting>
 ANALYZE ONLY measurement;
 </programlisting>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 82d6aef858..db7f35a451 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5527,7 +5527,7 @@ FROM pg_stat_get_backend_idset() AS backendid;
    <para>
     Note that when <command>ANALYZE</command> is run on a partitioned table
     without the <literal>ONLY</literal> keyword, all of its partitions are
-    also recursively analyzed. In that case, <command>ANALYZE</command>
+    also recursively analyzed.  In that case, <command>ANALYZE</command>
     progress is reported first for the parent table, whereby its inheritance
     statistics are collected, followed by that for each partition.
    </para>
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 92e465ca59..a0db56ae74 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -142,12 +142,12 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
       The name (possibly schema-qualified) of a specific table to
       analyze.  If omitted, all regular tables, partitioned tables, and
       materialized views in the current database are analyzed (but not
-      foreign tables). If <literal>ONLY</literal> is specified before
-      the table name, only that table is analyzed. If <literal>ONLY</literal>
-      is not specified, the table and all its descendant tables or partitions
-      (if any) are analyzed.  Optionally, <literal>*</literal>
+      foreign tables).  If <literal>ONLY</literal> is specified before
+      the table name, only that table is analyzed.  If <literal>ONLY</literal>
+      is not specified, the table and all its inheritance child tables or
+      partitions (if any) are analyzed.  Optionally, <literal>*</literal>
       can be specified after the table name to explicitly indicate that
-      descendant tables are included.
+      inheritance child tables (or partitions) are to be analyzed.
      </para>
     </listitem>
    </varlistentry>
@@ -287,23 +287,23 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
     <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.  If the
-    <literal>ONLY</literal> keyword is used, child tables themselves are not
-    individually analyzed. 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.
+    planning queries that process the inheritance tree as a whole.  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.  By default,
+    <command>ANALYZE</command> will also recursively collect and update the
+    statistics for each inheritance child table.  The <literal>ONLY</literal>
+    keyword may be used to disable this.
   </para>
 
   <para>
     For partitioned tables, <command>ANALYZE</command> gathers statistics by
-    sampling rows from all partitions. If <literal>ONLY</literal> is not
-    specified, it will also 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 is guaranteed to be empty.
+    sampling rows from all partitions.  By default,
+    <command>ANALYZE</command> will also recursively collect and update the
+    statistics for each partition.  The <literal>ONLY</literal> keyword may be
+    used to disable this.
   </para>
 
   <para>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 465f9fecf7..9110938fab 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -401,12 +401,13 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
     <listitem>
      <para>
       The name (optionally schema-qualified) of a specific table or
-      materialized view to vacuum.  If <literal>ONLY</literal> is specified before
-      the table name, only that table is vacuumed. If <literal>ONLY</literal>
-      is not specified, the table and all its descendant tables or partitions
-      (if any) are also vacuumed.  Optionally, <literal>*</literal>
-      can be specified after the table name to explicitly indicate that
-      descendant tables are included.
+      materialized view to vacuum.  If <literal>ONLY</literal> is specified
+      before the table name, only that table is vacuumed.  If
+      <literal>ONLY</literal> is not specified, the table and all its
+      inheritance child tables or partitions (if any) are also vacuumed.
+      Optionally, <literal>*</literal> can be specified after the table name
+      to explicitly indicate that inheritance child tables (or partitions) are
+      to be vacuumed.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 951e00acee..bcadca4214 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -851,8 +851,7 @@ vacuum_open_relation(Oid relid, RangeVar *relation, bits32 options,
 
 /*
  * Given a VacuumRelation, fill in the table OID if it wasn't specified,
- * and optionally add VacuumRelations for partitions or descendant tables
- * of the table.
+ * and optionally add VacuumRelations for partitions or inheritance children.
  *
  * If a VacuumRelation does not have an OID supplied and is a partitioned
  * table, an extra entry will be added to the output for each partition.
@@ -881,8 +880,8 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 	else
 	{
 		/*
-		 * Process a specific relation, and possibly partitions and/or child
-		 * tables thereof
+		 * Process a specific relation, and possibly partitions or child
+		 * tables thereof.
 		 */
 		Oid			relid;
 		HeapTuple	tuple;
@@ -951,7 +950,7 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 
 		/*
 		 * Vacuuming a partitioned table with ONLY will not do anything since
-		 * the partitioned table itself is empty. Issue a warning if the user
+		 * the partitioned table itself is empty.  Issue a warning if the user
 		 * requests this.
 		 */
 		include_children = vrel->relation->inh;
@@ -961,12 +960,11 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
 					(errmsg("VACUUM ONLY of partitioned table \"%s\" has no effect",
 							vrel->relation->relname)));
 
-
 		ReleaseSysCache(tuple);
 
 		/*
 		 * Unless the user has specified ONLY, make relation list entries for
-		 * its partitions and/or descendant tables.  Note that the list
+		 * its partitions or inheritance child tables.  Note that the list
 		 * returned by find_all_inheritors() includes the passed-in OID, so we
 		 * have to skip that.  There's no point in taking locks on the
 		 * individual partitions/tables yet, and doing so would just add
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 7165235ece..1a07dbf67d 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -291,14 +291,17 @@ ANALYZE vactst, vactst;
 BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
--- ANALYZE ONLY / VACUUM ONLY on partitioned table
-CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+--
+-- Tests for ANALYZE ONLY / VACUUM ONLY on partitioned tables
+--
+CREATE TABLE only_parted (a int, b text) PARTITION BY LIST (a);
 CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
 INSERT INTO only_parted VALUES (1, 'a');
--- Only partitioned table is analyzed
+-- Ensure only the partitioned table is analyzed
 ANALYZE ONLY only_parted;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_parted%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
   ORDER BY relname;
    relname    | analyzed | vacuumed 
 --------------+----------+----------
@@ -306,10 +309,11 @@ SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as
  only_parted1 | f        | f
 (2 rows)
 
--- Partitioned table and partitions are analyzed
+-- Ensure partitioned table and the partitions are analyzed
 ANALYZE only_parted;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_parted%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
   ORDER BY relname;
    relname    | analyzed | vacuumed 
 --------------+----------+----------
@@ -317,17 +321,23 @@ SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as
  only_parted1 | t        | f
 (2 rows)
 
-VACUUM ONLY vacparted; -- gives warning
+DROP TABLE only_parted;
+-- VACUUM ONLY on a partitioned table does nothing, ensure we get a warning.
+VACUUM ONLY vacparted;
 WARNING:  VACUUM ONLY of partitioned table "vacparted" has no effect
-ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
--- ANALYZE ONLY on inherited tables
+-- Try ANALYZE ONLY with a column list
+ANALYZE ONLY vacparted(a,b);
+--
+-- Tests for VACUUM ONLY / ANALYZE ONLY on inheritance tables
+--
 CREATE TABLE only_inh_parent (a int primary key, b TEXT);
 CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
 INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
--- Only parent is ANALYZED
+-- Ensure only parent is analyzed
 ANALYZE ONLY only_inh_parent;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_inh%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
   ORDER BY relname;
      relname     | analyzed | vacuumed 
 -----------------+----------+----------
@@ -335,10 +345,11 @@ SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as
  only_inh_parent | t        | f
 (2 rows)
 
--- Parent and child are ANALYZED
+-- Ensure the parent and child are analyzed
 ANALYZE only_inh_parent;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_inh%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
   ORDER BY relname;
      relname     | analyzed | vacuumed 
 -----------------+----------+----------
@@ -346,10 +357,11 @@ SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as
  only_inh_parent | t        | f
 (2 rows)
 
--- Only parent is VACUUMED
+-- Ensure only the parent is vacuumed
 VACUUM ONLY only_inh_parent;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_inh%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
   ORDER BY relname;
      relname     | analyzed | vacuumed 
 -----------------+----------+----------
@@ -357,10 +369,11 @@ SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as
  only_inh_parent | t        | t
 (2 rows)
 
--- Parent and child are VACUUMED
+-- Ensure parent and child are vacuumed
 VACUUM only_inh_parent;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_inh%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
   ORDER BY relname;
      relname     | analyzed | vacuumed 
 -----------------+----------+----------
@@ -368,7 +381,6 @@ SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as
  only_inh_parent | t        | t
 (2 rows)
 
-DROP TABLE only_parted CASCADE;
 DROP TABLE only_inh_parent CASCADE;
 NOTICE:  drop cascades to table only_inh_child
 -- parenthesized syntax for ANALYZE
diff --git a/src/test/regress/sql/vacuum.sql b/src/test/regress/sql/vacuum.sql
index fc84df0f07..5e55079e71 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -233,56 +233,70 @@ BEGIN;  -- ANALYZE behaves differently inside a transaction block
 ANALYZE vactst, vactst;
 COMMIT;
 
--- ANALYZE ONLY / VACUUM ONLY on partitioned table
-CREATE TABLE only_parted (a int, b char) PARTITION BY LIST (a);
+--
+-- Tests for ANALYZE ONLY / VACUUM ONLY on partitioned tables
+--
+CREATE TABLE only_parted (a int, b text) PARTITION BY LIST (a);
 CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
 INSERT INTO only_parted VALUES (1, 'a');
 
--- Only partitioned table is analyzed
+-- Ensure only the partitioned table is analyzed
 ANALYZE ONLY only_parted;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_parted%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
   ORDER BY relname;
 
--- Partitioned table and partitions are analyzed
+-- Ensure partitioned table and the partitions are analyzed
 ANALYZE only_parted;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_parted%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
   ORDER BY relname;
 
-VACUUM ONLY vacparted; -- gives warning
-ANALYZE ONLY vacparted(a,b); -- combine ONLY with column list
+DROP TABLE only_parted;
+
+-- VACUUM ONLY on a partitioned table does nothing, ensure we get a warning.
+VACUUM ONLY vacparted;
 
--- ANALYZE ONLY on inherited tables
+-- Try ANALYZE ONLY with a column list
+ANALYZE ONLY vacparted(a,b);
+
+--
+-- Tests for VACUUM ONLY / ANALYZE ONLY on inheritance tables
+--
 CREATE TABLE only_inh_parent (a int primary key, b TEXT);
 CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
 INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
 
--- Only parent is ANALYZED
+-- Ensure only parent is analyzed
 ANALYZE ONLY only_inh_parent;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_inh%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
   ORDER BY relname;
 
--- Parent and child are ANALYZED
+-- Ensure the parent and child are analyzed
 ANALYZE only_inh_parent;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_inh%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
   ORDER BY relname;
 
--- Only parent is VACUUMED
+-- Ensure only the parent is vacuumed
 VACUUM ONLY only_inh_parent;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_inh%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
   ORDER BY relname;
 
--- Parent and child are VACUUMED
+-- Ensure parent and child are vacuumed
 VACUUM only_inh_parent;
-SELECT relname, last_analyze is not null as analyzed, last_vacuum is not null as vacuumed FROM pg_stat_user_tables
-  WHERE relname like 'only_inh%'
+SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
+  FROM pg_stat_user_tables
+  WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
   ORDER BY relname;
 
-DROP TABLE only_parted CASCADE;
 DROP TABLE only_inh_parent CASCADE;
 
 -- parenthesized syntax for ANALYZE
-- 
2.34.1