Re: n_mod_since_analyze isn't reset at table truncation

On Fri, Mar 5, 2021 at 6:51 PM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
>
> 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".

Makes sense. I had been thinking we can treat TRUNCATE as like "DROP
TABLE and CREATE TABLE" in terms of the statistics but it's rather
"DELETE without WHERE" as you mentioned.

>
> > 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.

I think we can use n_live_tup for that but since it's an estimation
value it doesn't necessarily have the same result as DELETE and I'm
not sure it's reliable.

Regards,

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/