From: Bruce Momjian <bruce@momjian.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: Nathan Bossart <nathandbossart@gmail.com>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Álvaro Herrera <alvherre@alvh.no-ip.org>
Cc: yuzuko <yuzukohosoya@gmail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: document the need to analyze partitioned tables
Date: Fri, 29 Sep 2023 18:08:17 -0400
Message-ID: <ZRdK0eP37cKF/G18@momjian.us> (raw)
In-Reply-To: <596c6bea8b7768e24b093d26f15daa4ce0df566f.camel@cybertec.at>
References: <1fd81ddc7710a154834030133c6fea41e55c8efb.camel@cybertec.at>
<20230112232747.GA2111950@nathanxps13>
<Y8cKxCRXBwEVFfot@momjian.us>
<20230117210050.GX9837@telsasoft.com>
<Y8cQJIMFAe7QT73/@momjian.us>
<fae7e128fd78965a1fa4dc9c571a9f5187d16f85.camel@cybertec.at>
<CAApHDvq45bKqmULb_n_53NEk_buw1HSfL98trjehwPO3ZXt1Sg@mail.gmail.com>
<216da165b47db064dcaa55b15b9b6cf470a088aa.camel@cybertec.at>
<CAApHDvqZmtV9hOeSUMjc4vJSCt5QbShLQMBAEAXisqs+ap1n1Q@mail.gmail.com>
<596c6bea8b7768e24b093d26f15daa4ce0df566f.camel@cybertec.at>
On Wed, Sep 6, 2023 at 05:53:56AM +0200, Laurenz Albe wrote:
> > We may have different mental models here. This relates to the part
> > that I wasn't keen on in your patch, i.e:
> >
> > + The partitions of a partitioned table are normal tables and get processed
> > + by autovacuum
> >
> > While I agree that the majority of partitions are likely to be
> > relkind='r', which you might ordinarily consider a "normal table", you
> > just might change your mind when you try to INSERT or UPDATE records
> > that would violate the partition constraint. Some partitions might
> > also be themselves partitioned tables and others might be foreign
> > tables. That does not really matter much when it comes to what
> > autovacuum does or does not do, but I'm not really keen to imply in
> > our documents that partitions are "normal tables".
>
> Agreed, there are differences between partitions and normal tables.
> And this is not the place in the documentation where we would like to
> get into detail about the differences.
>
> Attached is the next version of my patch.
I adjusted your patch to be shorter and clearer, patch attached. I am
planning to apply this back to PG 11.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
Attachments:
[text/x-diff] autovacuum.diff (1.3K, ../ZRdK0eP37cKF%2FG18@momjian.us/2-autovacuum.diff)
download | inline diff:diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgmlindex 9cf9d030a8..70b5576037 100644--- a/doc/src/sgml/maintenance.sgml+++ b/doc/src/sgml/maintenance.sgml@@ -861,10 +861,16 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu
</para>
<para>
- Partitioned tables are not processed by autovacuum. Statistics- should be collected by running a manual <command>ANALYZE</command> when it is- first populated, and again whenever the distribution of data in its- partitions changes significantly.+ Partitioned tables do not directly store tuples and consequently do not+ require autovacuum to perform automated <command>VACUUM</command>.+ (Autovacuum does perform <command>VACUUM</command> on table+ partitions just like other tables.) Unfortunately, this also means+ that autovacuum doesn't run <command>ANALYZE</command> on partitioned+ tables, and this can cause suboptimal plans for queries that reference+ partitioned table statistics. You can work around this problem by+ manually running <command>ANALYZE</command> on partitioned tables+ when they are first populated, and again whenever the distribution+ of data in their partitions changes significantly.
</para>
<para>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: bruce@momjian.us, laurenz.albe@cybertec.at, dgrowleyml@gmail.com, pryzby@telsasoft.com, nathandbossart@gmail.com, tomas.vondra@enterprisedb.com, robertmhaas@gmail.com, alvherre@alvh.no-ip.org, yuzukohosoya@gmail.com
Subject: Re: document the need to analyze partitioned tables
In-Reply-To: <ZRdK0eP37cKF/G18@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox