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>

<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;

ANALYZEONLYmeasurement;

</programlisting>

will only process the root table.

</para>

<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>

<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>

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>

<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>

<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>

<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>

}

{

Oidrelid;

HeapTupletuple;

Form_pg_classclassForm;

boolinclude_parts;

boolinclude_children;

boolis_partitioned_table;

intrvr_opts;

MemoryContextSwitchTo(oldcontext);

}

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 (include_parts)

if (include_children)

{

List*part_oids=find_all_inheritors(relid,NoLock,NULL);

ListCell*part_lc;

;

vacuum_relation:

{

$$ = (Node *) makeVacuumRelation($1, InvalidOid,$2);

}

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

ANALYZE vactst, vactst;

COMMIT;

CREATETABLEonly_parted (aint, btext) PARTITION BY LIST (a);

CREATETABLEonly_parted1 PARTITION OF only_parted FORVALUESIN (1);

INSERT INTO only_partedVALUES (1,'a');

ANALYZE ONLY only_parted;

SELECT relname, last_analyzeIS NOT NULLAS analyzed, last_vacuumIS NOT NULLAS vacuumed

FROM pg_stat_user_tables

WHERE relidIN ('only_parted'::regclass,'only_parted1'::regclass)

ORDER BY relname;

ANALYZE only_parted;

SELECT relname, last_analyzeIS NOT NULLAS analyzed, last_vacuumIS NOT NULLAS vacuumed

FROM pg_stat_user_tables

WHERE relidIN ('only_parted'::regclass,'only_parted1'::regclass)

ORDER BY relname;

DROPTABLE only_parted;

VACUUM ONLY vacparted;

ANALYZE ONLY vacparted(a,b);

CREATETABLEonly_inh_parent (aintprimary key, bTEXT);

CREATETABLEonly_inh_child () INHERITS (only_inh_parent);

INSERT INTO only_inh_child(a,b)VALUES (1,'aaa'), (2,'bbb'), (3,'ccc');

ANALYZE ONLY only_inh_parent;

SELECT relname, last_analyzeIS NOT NULLAS analyzed, last_vacuumIS NOT NULLAS vacuumed

FROM pg_stat_user_tables

WHERE relidIN ('only_inh_parent'::regclass,'only_inh_child'::regclass)

ORDER BY relname;

ANALYZE only_inh_parent;

SELECT relname, last_analyzeIS NOT NULLAS analyzed, last_vacuumIS NOT NULLAS vacuumed

FROM pg_stat_user_tables

WHERE relidIN ('only_inh_parent'::regclass,'only_inh_child'::regclass)

ORDER BY relname;

VACUUM ONLY only_inh_parent;

SELECT relname, last_analyzeIS NOT NULLAS analyzed, last_vacuumIS NOT NULLAS vacuumed

FROM pg_stat_user_tables

WHERE relidIN ('only_inh_parent'::regclass,'only_inh_child'::regclass)

ORDER BY relname;

VACUUM only_inh_parent;

SELECT relname, last_analyzeIS NOT NULLAS analyzed, last_vacuumIS NOT NULLAS vacuumed

FROM pg_stat_user_tables

WHERE relidIN ('only_inh_parent'::regclass,'only_inh_child'::regclass)

ORDER BY relname;

DROPTABLE only_inh_parent CASCADE;

ANALYZE (VERBOSE) does_not_exist;

ANALYZE (nonexistent-arg) does_not_exist;