Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6b262f3

Browse files
committed
Document autoanalyze limitations for partitioned tables
When dealing with partitioned tables, counters for partitioned tablesare not updated when modifying child tables. This means autoanalyze maynot update optimizer statistics for the parent relations, which canresult in poor plans for some queries.It's worth documenting this limitation, so that people are aware of itand can take steps to mitigate it (e.g. by setting up a script executingANALYZE regularly).Backpatch to v10. Older branches are affected too, of couse, but we nolonger maintain those.Author: Justin PryzbyReviewed-by: Zhihong Yu, Tomas VondraBackpatch-through: 10Discussion:https://postgr.es/m/20210913035409.GA10647%40telsasoft.com
1 parentc1a0d7d commit6b262f3

File tree

2 files changed

+58
-3
lines changed

2 files changed

+58
-3
lines changed

‎doc/src/sgml/maintenance.sgml‎

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -290,6 +290,15 @@
290290
to meaningful statistical changes.
291291
</para>
292292

293+
<para>
294+
Tuples changed in partitions and inheritance children do not trigger
295+
analyze on the parent table. If the parent table is empty or rarely
296+
changed, it may never be processed by autovacuum, and the statistics for
297+
the inheritance tree as a whole won't be collected. It is necessary to
298+
run <command>ANALYZE</command> on the parent table manually in order to
299+
keep the statistics up to date.
300+
</para>
301+
293302
<para>
294303
As with vacuuming for space recovery, frequent updates of statistics
295304
are more useful for heavily-updated tables than for seldom-updated
@@ -347,6 +356,19 @@
347356
<command>ANALYZE</command> commands on those tables on a suitable schedule.
348357
</para>
349358
</tip>
359+
360+
<tip>
361+
<para>
362+
The autovacuum daemon does not issue <command>ANALYZE</command> commands
363+
for partitioned tables. Inheritance parents will only be analyzed if the
364+
parent itself is changed - changes to child tables do not trigger
365+
autoanalyze on the parent table. If your queries require statistics on
366+
parent tables for proper planning, it is necessary to periodically run
367+
a manual <command>ANALYZE</command> on those tables to keep the statistics
368+
up to date.
369+
</para>
370+
</tip>
371+
350372
</sect2>
351373

352374
<sect2 id="vacuum-for-visibility-map">
@@ -819,6 +841,13 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu
819841
since the last <command>ANALYZE</command>.
820842
</para>
821843

844+
<para>
845+
Partitioned tables are not processed by autovacuum. Statistics
846+
should be collected by running a manual <command>ANALYZE</command> when it is
847+
first populated, and again whenever the distribution of data in its
848+
partitions changes significantly.
849+
</para>
850+
822851
<para>
823852
Temporary tables cannot be accessed by autovacuum. Therefore,
824853
appropriate vacuum and analyze operations should be performed via

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

Lines changed: 29 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -263,9 +263,35 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
263263
</para>
264264

265265
<para>
266-
If any of the child tables are foreign tables whose foreign data wrappers
267-
do not support <command>ANALYZE</command>, those child tables are ignored while
268-
gathering inheritance statistics.
266+
For partitioned tables, <command>ANALYZE</command> gathers statistics by
267+
sampling rows from all partitions; in addition, it will recurse into each
268+
partition and update its statistics. Each leaf partition is analyzed only
269+
once, even with multi-level partitioning. No statistics are collected for
270+
only the parent table (without data from its partitions), because with
271+
partitioning it's guaranteed to be empty.
272+
</para>
273+
274+
<para>
275+
By constrast, if the table being analyzed has inheritance children,
276+
<command>ANALYZE</command> gathers two sets of statistics: one on the rows
277+
of the parent table only, and a second including rows of both the parent
278+
table and all of its children. This second set of statistics is needed when
279+
planning queries that process the inheritance tree as a whole. The child
280+
tables themselves are not individually analyzed in this case.
281+
</para>
282+
283+
<para>
284+
The autovacuum daemon does not process partitioned tables, nor does it
285+
process inheritance parents if only the children are ever modified.
286+
It is usually necessary to periodically run a manual
287+
<command>ANALYZE</command> to keep the statistics of the table hierarchy
288+
up to date.
289+
</para>
290+
291+
<para>
292+
If any child tables or partitions are foreign tables whose foreign
293+
data wrappers do not support <command>ANALYZE</command>, those tables are
294+
ignored while gathering inheritance statistics.
269295
</para>
270296

271297
<para>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp