Re: Autovacuum on partitioned table (autoanalyze)

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)