On Wed, Oct 12, 2022 at 08:50:19AM +1300, David Rowley wrote: > On Wed, 12 Oct 2022 at 04:11, Bruce Momjian <br...@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?
Ah, good point, I missed that in pgstat_report_analyze(). I will apply the patch then in a few days, thanks. -- Bruce Momjian <br...@momjian.us> https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson
