On Wed, 12 Oct 2022 at 04:11, Bruce Momjian <bruce@momjian.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
