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

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?

David