Re: Autovacuum on partitioned table (autoanalyze)

At Thu, 29 Jul 2021 18:03:55 -0700, Andres Freund <andres(at)anarazel(dot)de> wrote in
> And if one instead inverts the order of pgstat_report_analyze() and
> pgstat_report_anl_ancestors() one gets a slightly different problem: A manual
> ANALYZE of the partition root results in the partition root having a non-zero
> changes_since_analyze afterwards. expand_vacuum() causes child partitions to be
> added to the list of relations, which *first* causes the partition root to be
> analyzed, and *then* partitions. The partitions then report their
> changes_since_analyze upwards.

For the last behavior, as Andres suggested, the scan order need to be
reversed (or to be in the same order with autovacuum). Since
find_all_inheritors scans breadth-first so just reversing the result
works. The breadth-first is currently not in the contract of the
interface of the function. I suppose we can add such a contract?

Finally, I ended up with the attached.

- reverse the relation order within a tree
- reverse the order of pgstat_report_analyze and pgstat_report_analyze.

Inheritance expansion is performed per-tree basis so it works fine
even if multiple relations are given to vacuum().

> I don't think the code as is is fit for v14. It looks like it was rewritten
> with a new approach just before the freeze ([1]), and as far as I can tell the
> concerns I quoted above weren't even discussed in the whole thread. Alvaro,
> any comments?
>
> Greetings,
>
> Andres Freund
>
> [1] https://www.postgresql.org/message-id/20210408032235.GA6842%40alvherre.pgsql

FYI: this bahaves as the follows.

CREATE TABLE p (a int) PARTITION BY RANGE (a);
CREATE TABLE c1 PARTITION OF p FOR VALUES FROM (0) TO (200) PARTITION BY RANGE(a);
CREATE TABLE c11 PARTITION OF c1 FOR VALUES FROM (0) TO (100);
CREATE TABLE c12 PARTITION OF c1 FOR VALUES FROM (100) TO (200);
CREATE TABLE c2 PARTITION OF p FOR VALUES FROM (200) TO (400) PARTITION BY RANGE(a);
CREATE TABLE c21 PARTITION OF c2 FOR VALUES FROM (200) TO (300);
CREATE TABLE c22 PARTITION OF c2 FOR VALUES FROM (300) TO (400);
INSERT INTO p (SELECT a FROM generate_series(0, 400 - 1) a, generate_series(0, 10) b);

INSERT INTO p (SELECT 200 FROM generate_series(0, 99));

SELECT relid, relname, n_mod_since_analyze FROM pg_stat_user_tables ORDER BY relid;
relid | relname | n_mod_since_analyze
-------+---------+---------------------
16426 | p | 0
16429 | c1 | 0
16432 | c11 | 0
16435 | c12 | 0
16438 | c2 | 0
16441 | c21 | 100
16444 | c22 | 0
16447 | sa | 0
(8 rows)

After "ANALYZE c21;"
relid | relname | n_mod_since_analyze
-------+---------+---------------------
16426 | p | 100
16429 | c1 | 0
16432 | c11 | 0
16435 | c12 | 0
16438 | c2 | 100
16441 | c21 | 0
16444 | c22 | 0
16447 | sa | 0

After "ANALYZE c2;"
relid | relname | n_mod_since_analyze
-------+---------+---------------------
16426 | p | 100
16429 | c1 | 0
16432 | c11 | 0
16435 | c12 | 0
16438 | c2 | 0
16441 | c21 | 0
16444 | c22 | 0
16447 | sa | 0

After "ANALYZE p;"
(all zero)

However, this gives a strange-looking side-effect, which affected
regression results.

=# VACUUM ANALYZE p(a, a);
ERROR: column "a" of relation "c22" appears more than once

(Prevously it complained about p.)

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center