aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/ddl.sgml23
-rw-r--r--doc/src/sgml/monitoring.sgml6
-rw-r--r--doc/src/sgml/ref/analyze.sgml36
-rw-r--r--doc/src/sgml/ref/vacuum.sgml11
-rw-r--r--src/backend/commands/vacuum.c39
-rw-r--r--src/backend/parser/gram.y2
-rw-r--r--src/test/regress/expected/vacuum.out92
-rw-r--r--src/test/regress/sql/vacuum.sql66
8 files changed, 230 insertions, 45 deletions
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index b671858627b..8ab0ddb112f 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"/>).
+ 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.
+ 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>
@@ -4854,11 +4856,12 @@ 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> 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 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 a2fda4677d7..db7f35a4515 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 2b94b378e9f..a0db56ae743 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 inheritance child tables or
+ partitions (if any) are analyzed. Optionally, <literal>*</literal>
+ can be specified after the table name to explicitly indicate that
+ inheritance child tables (or partitions) are to be analyzed.
</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. 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; 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. 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 9857b35627b..9110938fab6 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,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 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
+ 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 7d8e9d20454..23aabdc90dc 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -851,7 +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 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.
@@ -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 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,31 @@ 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
- * 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.
+ * Unless the user has specified ONLY, make relation list entries for
+ * 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 or child tables yet, and doing so would just
+ * add unnecessary deadlock risk. For this last reason, we do not yet
+ * check 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 ab304ca989d..b1d4642c59b 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 2eba7128876..1a07dbf67d6 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -291,6 +291,98 @@ ANALYZE vactst, vactst;
BEGIN; -- ANALYZE behaves differently inside a transaction block
ANALYZE vactst, vactst;
COMMIT;
+--
+-- 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');
+-- 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 relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
+ ORDER BY relname;
+ relname | analyzed | vacuumed
+--------------+----------+----------
+ only_parted | t | f
+ only_parted1 | f | f
+(2 rows)
+
+-- 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 relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
+ ORDER BY relname;
+ relname | analyzed | vacuumed
+--------------+----------+----------
+ only_parted | t | f
+ only_parted1 | t | f
+(2 rows)
+
+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
+-- 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');
+-- 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 relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
+ ORDER BY relname;
+ relname | analyzed | vacuumed
+-----------------+----------+----------
+ only_inh_child | f | f
+ only_inh_parent | t | f
+(2 rows)
+
+-- 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 relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
+ ORDER BY relname;
+ relname | analyzed | vacuumed
+-----------------+----------+----------
+ only_inh_child | t | f
+ only_inh_parent | t | f
+(2 rows)
+
+-- 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 relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
+ ORDER BY relname;
+ relname | analyzed | vacuumed
+-----------------+----------+----------
+ only_inh_child | t | f
+ only_inh_parent | t | t
+(2 rows)
+
+-- 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 relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
+ ORDER BY relname;
+ relname | analyzed | vacuumed
+-----------------+----------+----------
+ only_inh_child | t | t
+ only_inh_parent | t | t
+(2 rows)
+
+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 548cd7accac..5e55079e718 100644
--- a/src/test/regress/sql/vacuum.sql
+++ b/src/test/regress/sql/vacuum.sql
@@ -233,6 +233,72 @@ BEGIN; -- ANALYZE behaves differently inside a transaction block
ANALYZE vactst, vactst;
COMMIT;
+--
+-- 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');
+
+-- 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 relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
+ ORDER BY relname;
+
+-- 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 relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
+ ORDER BY relname;
+
+DROP TABLE only_parted;
+
+-- VACUUM ONLY on a partitioned table does nothing, ensure we get a warning.
+VACUUM ONLY vacparted;
+
+-- 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');
+
+-- 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 relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
+ ORDER BY relname;
+
+-- 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 relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
+ ORDER BY relname;
+
+-- 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 relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
+ ORDER BY relname;
+
+-- 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 relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
+ ORDER BY relname;
+
+DROP TABLE only_inh_parent CASCADE;
+
-- parenthesized syntax for ANALYZE
ANALYZE (VERBOSE) does_not_exist;
ANALYZE (nonexistent-arg) does_not_exist;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-31 11:09:12 +0000