Re: n_mod_since_analyze isn't reset at table truncation

On Mon, Mar 08, 2021 at 10:49:20AM +0900, Masahiko Sawada wrote:
> 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.

Ah, I mentioned previously that the planner would already come up with sensible
estimates as it takes into account the relation size, but if truncates actually
sets pg_class.reltuples to -1 then indeed it's kind of behaving as a DROP/CREATE
for the size estimate.

But the previous stats will be kept so estimates will still be done according
to what used to be in that table, but there's no guarantee that further writes
on that table will have the same pattern as before.

Maybe we should also delete the related pg_statistic entries, reset
n_mod_since_analyze and let autoanalyze behave as it would do after a
DROP/CREATE?