Re: Potential autovacuum optimization: new tables

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Now, I look at this, and ask myself: why didn't autoanalyze kick in at
> step 3? After all, this was a table which had 0 rows, we inserted 45
> rows, making the table infinitely larger. It should have got on the
> autoanalyze list, no?

> Well, no. It seems that any table with less than
> autovacuum_analyze_threshold rows will NEVER be autoanalyzed. Ever.

Yeah ...

> This seems easy to fix. If a table has no stats and has any write stats
> at all, it should automatically go on the autoanalyze list. Or if it's
> easier, one where last_autoanalyze is null.

No, it's not that easy. The question you have to ask is "when has that
initial write burst stopped?". As an example, if autovacuum happened to
see that table in the instant after CREATE, it might autovacuum it while
it's still empty, and then this rule fails to trigger any further effort.

Personally I've always thought that autovacuum's rules should be based
on a percentage of rows changed, not an absolute threshold (or maybe in
addition to an absolute threshold). This way, if you create a table and
insert 10 rows, that would make it subject to analyze on-sight, even if
autovac had managed to pass by while it was still empty, because the
percentage-changed is infinite. Then, if you insert the other 35 rows
you meant to insert, it's *again* subject to autoanalyze on the next
pass, because the percentage-changed is still 350%.

I remember having got voted down on the percentage approach back when
we first put AV into core, but I remain convinced that decision was a
bad one.

regards, tom lane