Skip to content

Commit 62ddf7e

Browse files
david-rowleydavid-rowley
committed
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 Discussion: https://postgr.es/m/CADofcAXVbD0yGp_EaC9chmzsOoSai3jcfBCnyva3j0RRdRvMVA@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>
1 parent bbba59e commit 62ddf7e

File tree

8 files changed

+230
-45
lines changed

8 files changed

+230
-45
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 13 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -3778,12 +3778,14 @@ VALUES ('Albany', NULL, NULL, 'NY');
37783778
not <literal>INSERT</literal> or <literal>ALTER TABLE ...
37793779
RENAME</literal>) typically default to including child tables and
37803780
support the <literal>ONLY</literal> notation to exclude them.
3781-
Commands that do database maintenance and tuning
3782-
(e.g., <literal>REINDEX</literal>, <literal>VACUUM</literal>)
3783-
typically only work on individual, physical tables and do not
3784-
support recursing over inheritance hierarchies. The respective
3785-
behavior of each individual command is documented in its reference
3786-
page (<xref linkend="sql-commands"/>).
3781+
The majority of commands that do database maintenance and tuning
3782+
(e.g., <literal>REINDEX</literal>) only work on individual, physical
3783+
tables and do not support recursing over inheritance hierarchies.
3784+
However, both <literal>VACUUM</literal> and <literal>ANALYZE</literal>
3785+
commands default to including child tables and the <literal>ONLY</literal>
3786+
notation is supported to allow them to be excluded. The respective
3787+
behavior of each individual command is documented in its reference page
3788+
(<xref linkend="sql-commands"/>).
37873789
</para>
37883790

37893791
<para>
@@ -4854,11 +4856,12 @@ ALTER TABLE measurement_y2008m02 INHERIT measurement;
48544856

48554857
<listitem>
48564858
<para>
4857-
If you are using manual <command>VACUUM</command> or
4858-
<command>ANALYZE</command> commands, don't forget that
4859-
you need to run them on each child table individually. A command like:
4859+
Manual <command>VACUUM</command> and <command>ANALYZE</command>
4860+
commands will automatically process all inheritance child tables. If
4861+
this is undesirable, you can use the <literal>ONLY</literal> keyword.
4862+
A command like:
48604863
<programlisting>
4861-
ANALYZE measurement;
4864+
ANALYZE ONLY measurement;
48624865
</programlisting>
48634866
will only process the root table.
48644867
</para>

‎doc/src/sgml/monitoring.sgml

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -5525,9 +5525,9 @@ FROM pg_stat_get_backend_idset() AS backendid;
55255525

55265526
<note>
55275527
<para>
5528-
Note that when <command>ANALYZE</command> is run on a partitioned table,
5529-
all of its partitions are also recursively analyzed.
5530-
In that case, <command>ANALYZE</command>
5528+
Note that when <command>ANALYZE</command> is run on a partitioned table
5529+
without the <literal>ONLY</literal> keyword, all of its partitions are
5530+
also recursively analyzed. In that case, <command>ANALYZE</command>
55315531
progress is reported first for the parent table, whereby its inheritance
55325532
statistics are collected, followed by that for each partition.
55335533
</para>

‎doc/src/sgml/ref/analyze.sgml

Lines changed: 20 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -31,7 +31,7 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
3131

3232
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
3333

34-
<replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
34+
[ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
3535
</synopsis>
3636
</refsynopsisdiv>
3737

@@ -142,9 +142,12 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
142142
The name (possibly schema-qualified) of a specific table to
143143
analyze. If omitted, all regular tables, partitioned tables, and
144144
materialized views in the current database are analyzed (but not
145-
foreign tables). If the specified table is a partitioned table, both the
146-
inheritance statistics of the partitioned table as a whole and
147-
statistics of the individual partitions are updated.
145+
foreign tables). If <literal>ONLY</literal> is specified before
146+
the table name, only that table is analyzed. If <literal>ONLY</literal>
147+
is not specified, the table and all its inheritance child tables or
148+
partitions (if any) are analyzed. Optionally, <literal>*</literal>
149+
can be specified after the table name to explicitly indicate that
150+
inheritance child tables (or partitions) are to be analyzed.
148151
</para>
149152
</listitem>
150153
</varlistentry>
@@ -284,22 +287,23 @@ ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <r
284287
<command>ANALYZE</command> gathers two sets of statistics: one on the rows
285288
of the parent table only, and a second including rows of both the parent
286289
table and all of its children. This second set of statistics is needed when
287-
planning queries that process the inheritance tree as a whole. The child
288-
tables themselves are not individually analyzed in this case.
289-
The autovacuum daemon, however, will only consider inserts or
290-
updates on the parent table itself when deciding whether to trigger an
291-
automatic analyze for that table. If that table is rarely inserted into
292-
or updated, the inheritance statistics will not be up to date unless you
293-
run <command>ANALYZE</command> manually.
290+
planning queries that process the inheritance tree as a whole. The
291+
autovacuum daemon, however, will only consider inserts or updates on the
292+
parent table itself when deciding whether to trigger an automatic analyze
293+
for that table. If that table is rarely inserted into or updated, the
294+
inheritance statistics will not be up to date unless you run
295+
<command>ANALYZE</command> manually. By default,
296+
<command>ANALYZE</command> will also recursively collect and update the
297+
statistics for each inheritance child table. The <literal>ONLY</literal>
298+
keyword may be used to disable this.
294299
</para>
295300

296301
<para>
297302
For partitioned tables, <command>ANALYZE</command> gathers statistics by
298-
sampling rows from all partitions; in addition, it will recurse into each
299-
partition and update its statistics. Each leaf partition is analyzed only
300-
once, even with multi-level partitioning. No statistics are collected for
301-
only the parent table (without data from its partitions), because with
302-
partitioning it's guaranteed to be empty.
303+
sampling rows from all partitions. By default,
304+
<command>ANALYZE</command> will also recursively collect and update the
305+
statistics for each partition. The <literal>ONLY</literal> keyword may be
306+
used to disable this.
303307
</para>
304308

305309
<para>

‎doc/src/sgml/ref/vacuum.sgml

Lines changed: 8 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -42,7 +42,7 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
4242

4343
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
4444

45-
<replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
45+
[ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
4646
</synopsis>
4747
</refsynopsisdiv>
4848

@@ -401,8 +401,13 @@ VACUUM [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <re
401401
<listitem>
402402
<para>
403403
The name (optionally schema-qualified) of a specific table or
404-
materialized view to vacuum. If the specified table is a partitioned
405-
table, all of its leaf partitions are vacuumed.
404+
materialized view to vacuum. If <literal>ONLY</literal> is specified
405+
before the table name, only that table is vacuumed. If
406+
<literal>ONLY</literal> is not specified, the table and all its
407+
inheritance child tables or partitions (if any) are also vacuumed.
408+
Optionally, <literal>*</literal> can be specified after the table name
409+
to explicitly indicate that inheritance child tables (or partitions) are
410+
to be vacuumed.
406411
</para>
407412
</listitem>
408413
</varlistentry>

‎src/backend/commands/vacuum.c

Lines changed: 27 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -851,7 +851,7 @@ vacuum_open_relation(Oid relid, RangeVar *relation, bits32 options,
851851

852852
/*
853853
* Given a VacuumRelation, fill in the table OID if it wasn't specified,
854-
* and optionally add VacuumRelations for partitions of the table.
854+
* and optionally add VacuumRelations for partitions or inheritance children.
855855
*
856856
* If a VacuumRelation does not have an OID supplied and is a partitioned
857857
* 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,
879879
}
880880
else
881881
{
882-
/* Process a specific relation, and possibly partitions thereof */
882+
/*
883+
* Process a specific relation, and possibly partitions or child
884+
* tables thereof.
885+
*/
883886
Oid relid;
884887
HeapTuple tuple;
885888
Form_pg_class classForm;
886-
bool include_parts;
889+
bool include_children;
890+
bool is_partitioned_table;
887891
int rvr_opts;
888892

889893
/*
@@ -944,20 +948,31 @@ expand_vacuum_rel(VacuumRelation *vrel, MemoryContext vac_context,
944948
MemoryContextSwitchTo(oldcontext);
945949
}
946950

951+
/*
952+
* Vacuuming a partitioned table with ONLY will not do anything since
953+
* the partitioned table itself is empty. Issue a warning if the user
954+
* requests this.
955+
*/
956+
include_children = vrel->relation->inh;
957+
is_partitioned_table = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
958+
if ((options & VACOPT_VACUUM) && is_partitioned_table && !include_children)
959+
ereport(WARNING,
960+
(errmsg("VACUUM ONLY of partitioned table \"%s\" has no effect",
961+
vrel->relation->relname)));
947962

948-
include_parts = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
949963
ReleaseSysCache(tuple);
950964

951965
/*
952-
* If it is, make relation list entries for its partitions. Note that
953-
* the list returned by find_all_inheritors() includes the passed-in
954-
* OID, so we have to skip that. There's no point in taking locks on
955-
* the individual partitions yet, and doing so would just add
956-
* unnecessary deadlock risk. For this last reason we do not check
957-
* yet the ownership of the partitions, which get added to the list to
958-
* process. Ownership will be checked later on anyway.
966+
* Unless the user has specified ONLY, make relation list entries for
967+
* its partitions or inheritance child tables. Note that the list
968+
* returned by find_all_inheritors() includes the passed-in OID, so we
969+
* have to skip that. There's no point in taking locks on the
970+
* individual partitions or child tables yet, and doing so would just
971+
* add unnecessary deadlock risk. For this last reason, we do not yet
972+
* check the ownership of the partitions/tables, which get added to
973+
* the list to process. Ownership will be checked later on anyway.
959974
*/
960-
if (include_parts)
975+
if (include_children)
961976
{
962977
List *part_oids = find_all_inheritors(relid, NoLock, NULL);
963978
ListCell *part_lc;

‎src/backend/parser/gram.y

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11921,7 +11921,7 @@ opt_name_list:
1192111921
;
1192211922

1192311923
vacuum_relation:
11924-
qualified_name opt_name_list
11924+
relation_expr opt_name_list
1192511925
{
1192611926
$$ = (Node *) makeVacuumRelation($1, InvalidOid, $2);
1192711927
}

‎src/test/regress/expected/vacuum.out

Lines changed: 92 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -291,6 +291,98 @@ ANALYZE vactst, vactst;
291291
BEGIN; -- ANALYZE behaves differently inside a transaction block
292292
ANALYZE vactst, vactst;
293293
COMMIT;
294+
--
295+
-- Tests for ANALYZE ONLY / VACUUM ONLY on partitioned tables
296+
--
297+
CREATE TABLE only_parted (a int, b text) PARTITION BY LIST (a);
298+
CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
299+
INSERT INTO only_parted VALUES (1, 'a');
300+
-- Ensure only the partitioned table is analyzed
301+
ANALYZE ONLY only_parted;
302+
SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
303+
FROM pg_stat_user_tables
304+
WHERE relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
305+
ORDER BY relname;
306+
relname | analyzed | vacuumed
307+
--------------+----------+----------
308+
only_parted | t | f
309+
only_parted1 | f | f
310+
(2 rows)
311+
312+
-- Ensure partitioned table and the partitions are analyzed
313+
ANALYZE only_parted;
314+
SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
315+
FROM pg_stat_user_tables
316+
WHERE relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
317+
ORDER BY relname;
318+
relname | analyzed | vacuumed
319+
--------------+----------+----------
320+
only_parted | t | f
321+
only_parted1 | t | f
322+
(2 rows)
323+
324+
DROP TABLE only_parted;
325+
-- VACUUM ONLY on a partitioned table does nothing, ensure we get a warning.
326+
VACUUM ONLY vacparted;
327+
WARNING: VACUUM ONLY of partitioned table "vacparted" has no effect
328+
-- Try ANALYZE ONLY with a column list
329+
ANALYZE ONLY vacparted(a,b);
330+
--
331+
-- Tests for VACUUM ONLY / ANALYZE ONLY on inheritance tables
332+
--
333+
CREATE TABLE only_inh_parent (a int primary key, b TEXT);
334+
CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
335+
INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
336+
-- Ensure only parent is analyzed
337+
ANALYZE ONLY only_inh_parent;
338+
SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
339+
FROM pg_stat_user_tables
340+
WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
341+
ORDER BY relname;
342+
relname | analyzed | vacuumed
343+
-----------------+----------+----------
344+
only_inh_child | f | f
345+
only_inh_parent | t | f
346+
(2 rows)
347+
348+
-- Ensure the parent and child are analyzed
349+
ANALYZE only_inh_parent;
350+
SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
351+
FROM pg_stat_user_tables
352+
WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
353+
ORDER BY relname;
354+
relname | analyzed | vacuumed
355+
-----------------+----------+----------
356+
only_inh_child | t | f
357+
only_inh_parent | t | f
358+
(2 rows)
359+
360+
-- Ensure only the parent is vacuumed
361+
VACUUM ONLY only_inh_parent;
362+
SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
363+
FROM pg_stat_user_tables
364+
WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
365+
ORDER BY relname;
366+
relname | analyzed | vacuumed
367+
-----------------+----------+----------
368+
only_inh_child | t | f
369+
only_inh_parent | t | t
370+
(2 rows)
371+
372+
-- Ensure parent and child are vacuumed
373+
VACUUM only_inh_parent;
374+
SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
375+
FROM pg_stat_user_tables
376+
WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
377+
ORDER BY relname;
378+
relname | analyzed | vacuumed
379+
-----------------+----------+----------
380+
only_inh_child | t | t
381+
only_inh_parent | t | t
382+
(2 rows)
383+
384+
DROP TABLE only_inh_parent CASCADE;
385+
NOTICE: drop cascades to table only_inh_child
294386
-- parenthesized syntax for ANALYZE
295387
ANALYZE (VERBOSE) does_not_exist;
296388
ERROR: relation "does_not_exist" does not exist

‎src/test/regress/sql/vacuum.sql

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -233,6 +233,72 @@ BEGIN; -- ANALYZE behaves differently inside a transaction block
233233
ANALYZE vactst, vactst;
234234
COMMIT;
235235

236+
--
237+
-- Tests for ANALYZE ONLY / VACUUM ONLY on partitioned tables
238+
--
239+
CREATE TABLE only_parted (a int, b text) PARTITION BY LIST (a);
240+
CREATE TABLE only_parted1 PARTITION OF only_parted FOR VALUES IN (1);
241+
INSERT INTO only_parted VALUES (1, 'a');
242+
243+
-- Ensure only the partitioned table is analyzed
244+
ANALYZE ONLY only_parted;
245+
SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
246+
FROM pg_stat_user_tables
247+
WHERE relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
248+
ORDER BY relname;
249+
250+
-- Ensure partitioned table and the partitions are analyzed
251+
ANALYZE only_parted;
252+
SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
253+
FROM pg_stat_user_tables
254+
WHERE relid IN ('only_parted'::regclass, 'only_parted1'::regclass)
255+
ORDER BY relname;
256+
257+
DROP TABLE only_parted;
258+
259+
-- VACUUM ONLY on a partitioned table does nothing, ensure we get a warning.
260+
VACUUM ONLY vacparted;
261+
262+
-- Try ANALYZE ONLY with a column list
263+
ANALYZE ONLY vacparted(a,b);
264+
265+
--
266+
-- Tests for VACUUM ONLY / ANALYZE ONLY on inheritance tables
267+
--
268+
CREATE TABLE only_inh_parent (a int primary key, b TEXT);
269+
CREATE TABLE only_inh_child () INHERITS (only_inh_parent);
270+
INSERT INTO only_inh_child(a,b) VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc');
271+
272+
-- Ensure only parent is analyzed
273+
ANALYZE ONLY only_inh_parent;
274+
SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
275+
FROM pg_stat_user_tables
276+
WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
277+
ORDER BY relname;
278+
279+
-- Ensure the parent and child are analyzed
280+
ANALYZE only_inh_parent;
281+
SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
282+
FROM pg_stat_user_tables
283+
WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
284+
ORDER BY relname;
285+
286+
-- Ensure only the parent is vacuumed
287+
VACUUM ONLY only_inh_parent;
288+
SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
289+
FROM pg_stat_user_tables
290+
WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
291+
ORDER BY relname;
292+
293+
-- Ensure parent and child are vacuumed
294+
VACUUM only_inh_parent;
295+
SELECT relname, last_analyze IS NOT NULL AS analyzed, last_vacuum IS NOT NULL AS vacuumed
296+
FROM pg_stat_user_tables
297+
WHERE relid IN ('only_inh_parent'::regclass, 'only_inh_child'::regclass)
298+
ORDER BY relname;
299+
300+
DROP TABLE only_inh_parent CASCADE;
301+
236302
-- parenthesized syntax for ANALYZE
237303
ANALYZE (VERBOSE) does_not_exist;
238304
ANALYZE (nonexistent-arg) does_not_exist;

0 commit comments

Comments
 (0)