Re: Warning about using pg_stat_reset() and pg_stat_reset_shared()

On Wed, Oct 12, 2022 at 08:50:19AM +1300, David Rowley wrote:
> On Wed, 12 Oct 2022 at 04:11, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > As far as I can tell, analyze updates pg_statistics values, but not
> > pg_stat_all_tables.n_dead_tup and n_live_tup, which are used by
> > autovacuum to trigger vacuum operations. I am afraid we have to
> > recommand VACUUM ANALYZE after pg_stat_reset(), no?
>
> As far as I can see ANALYZE will update these fields. I'm looking at
> pgstat_report_analyze() called from do_analyze_rel().
>
> It does:
>
> tabentry->n_live_tuples = livetuples;
> tabentry->n_dead_tuples = deadtuples;
>
> I also see it working from testing:
>
> create table t as select x from generate_Series(1,100000)x;
> delete from t where x > 90000;
> select pg_sleep(1);
> select n_live_tup,n_dead_tup from pg_stat_user_tables where relname = 't';
> select pg_stat_reset();
> select n_live_tup,n_dead_tup from pg_stat_user_tables where relname = 't';
> analyze t;
> select n_live_tup,n_dead_tup from pg_stat_user_tables where relname = 't';
>
> The result of the final query is:
>
> n_live_tup | n_dead_tup
> ------------+------------
> 90000 | 10000
>
> Maybe the random sample taken by ANALYZE for your case didn't happen
> to land on any pages with dead tuples?

Ah, good point, I missed that in pgstat_report_analyze(). I will apply
the patch then in a few days, thanks.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson