Re: n_mod_since_analyze isn't reset at table truncation

On Fri, Mar 5, 2021 at 10:43 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> 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.

Hmm I'm a bit confused. Executing TRANCATE against the table set
pg_class.reltuples to -1, meaning it's never yet vacuumed and the
planner applies 10 pages. Also, it seems to clear plan caches
involving the table being truncated. The table statistics in
pg_statistic and pg_statistic_ext might be out of date but that would
not affect the plan badly since we assume the table has 10 pages. That
behavior makes me think that TRUNCATE is something like "DROP and
CREATE table" in terms of statistics. I'm concerned that if we hasten
autoanalyze after TRUNCATE, we could trigger autoanalyze soon and the
statistics could be out of date at the time when we insert rows enough
to exceed autoanalyze threshold. I might be missing something though.

Regards,

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