Re: n_mod_since_analyze isn't reset at table truncation

On Fri, Mar 05, 2021 at 06:07:05PM +0900, Fujii Masao wrote:
>
> On 2021/03/05 15:59, Julien Rouhaud wrote:
> >
> > I don't especially want to defer autoanalyze in that case. But an autoanalyze
> > happening quickly after a TRUNCATE is critical for performance, I'd prefer to
> > find a way to trigger autoanalyze reliably.
>
> One just idea is to make TRUNCATE increase n_mod_since_analyze by
> the number of records to truncate. That is, we treat TRUNCATE
> in the same way as "DELETE without WHERE".

Yes, that's the approach I had in mind to make it more reliable.

> If the table has lots of records and is truncated, n_mod_since_analyze
> will be increased very much and which would trigger autoanalyze soon.
> This might be expected behavior because the statistics collected before
> truncate is very "different" from the status of the table after truncate.
>
> OTOH, if the table is very small, TRUNCATE doesn't increase
> n_mod_since_analyze so much. So analyze might not be triggered soon.
> But this might be ok because the statistics collected before truncate is
> not so "different" from the status of the table after truncate.
>
> I'm not sure how much this idea is "reliable" and would be helpful in
> practice, though.

It seems like a better approach as it it would have the same results on
autovacuum as a DELETE, so +1 from me.