From: Álvaro Herrera <alvherre@alvh.no-ip.org>
To: Andres Freund <andres@anarazel.de>
Cc: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Cc: yuzuko <yuzukohosoya@gmail.com>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: David Steele <david@pgmasters.net>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: Daniel Gustafsson <daniel@yesql.se>
Cc: Amit Langote <amitlangote09@gmail.com>
Cc: Masahiko Sawada <masahiko.sawada@2ndquadrant.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Cc: Greg Stark <stark@mit.edu>
Subject: Re: Autovacuum on partitioned table (autoanalyze)
Date: Mon, 16 Aug 2021 10:03:18 -0400
Message-ID: <202108161403.moxi73uz5zzf@alvherre.pgsql> (raw)
In-Reply-To: <0794d7ca-5183-486b-9c5e-6d434867cecd@www.fastmail.com>
On 2021-Aug-13, Álvaro Herrera wrote:
> Some doc changes are pending, and some more commentary in parts of the
> code, but I think this is much more sensible. I do lament the lack of
> a syscache for pg_inherits.
Thinking about this again, this one here is the killer problem, I think;
this behaves pretty horribly if you have more than one partition level,
because it'll have to do one indexscan *per level per partition*. (For
example, five partitions two levels down mean ten index scans). There's
no cache for this, and no way to disable it. So for situations with a
lot of partitions, it could be troublesome. Granted, it only needs to
be done for partitions with DML changes since the previous autovacuum
worker run in the affected database, but still it could be significant.
Now we could perhaps have a hash table in partition_analyze_report_ancestors()
to avoid the need for repeated indexscans for partitions of the same
hierarchy (an open-coded cache to take the place of the missing
pg_inherits syscache); and perhaps even use a single seqscan of
pg_inherits to capture the whole story first and then filter down to the
partitions that we were asked to process ... (so are we building a
mini-optimizer to determine which strategy to use in each case?).
That all sounds too much to be doing in the beta.
So I'm leaning towards the idea that we need to revert the patch and
start over for pg15.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"La libertad es como el dinero; el que no la sabe emplear la pierde" (Alvarez)
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: alvherre@alvh.no-ip.org, andres@anarazel.de, horiguchi.kyotaro@lab.ntt.co.jp, yuzukohosoya@gmail.com, tomas.vondra@enterprisedb.com, david@pgmasters.net, horikyota.ntt@gmail.com, pryzby@telsasoft.com, daniel@yesql.se, amitlangote09@gmail.com, masahiko.sawada@2ndquadrant.com, laurenz.albe@cybertec.at, pgsql-hackers@lists.postgresql.org, stark@mit.edu
Subject: Re: Autovacuum on partitioned table (autoanalyze)
In-Reply-To: <202108161403.moxi73uz5zzf@alvherre.pgsql>
* 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