Re: Decoupling antiwraparound autovacuum from special rules around auto cancellation - Mailing list pgsql-hackers

Hi,

On 2023-01-18 13:08:44 -0800, Andres Freund wrote:
> One complicating factor is that VACUUM sometimes computes an incrementally
> more bogus n_live_tup when it skips pages due to the VM, whereas ANALYZE
> computes something sane. I unintentionally encountered one when I was trying
> something while writing this email, reproducer attached.
> 
> 
> VACUUM (DISABLE_PAGE_SKIPPING) foo;
> SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relid = 'foo'::regclass;
> ┌────────────┬────────────┐
> │ n_live_tup │ n_dead_tup │
> ├────────────┼────────────┤
> │    9000001 │     500000 │
> └────────────┴────────────┘
> 
> after one VACUUM:
> ┌────────────┬────────────┐
> │ n_live_tup │ n_dead_tup │
> ├────────────┼────────────┤
> │    8549905 │     500000 │
> └────────────┴────────────┘
> 
> after 9 more VACUUMs:
> ┌────────────┬────────────┐
> │ n_live_tup │ n_dead_tup │
> ├────────────┼────────────┤
> │    5388421 │     500000 │
> └────────────┴────────────┘
> (1 row)
> 
> 
> I briefly tried it out, and it does *not* reproduce in 11, but does in
> 12. Haven't dug into what the cause is, but we probably use the wrong
> denominator somewhere...

Oh, it does actually reproduce in 11 too - my script just didn't see it
because it was "too fast". For some reason < 12 it takes longer for the new
pgstat snapshot to be available. If I add a few sleeps, it shows in 11.

The real point of change appears to be 10->11.

There's a relevant looking difference in the vac_estimate_reltuples call:
10:
    /* now we can compute the new value for pg_class.reltuples */
    vacrelstats->new_rel_tuples = vac_estimate_reltuples(onerel, false,
                                                         nblocks,
                                                         vacrelstats->tupcount_pages,
                                                         num_tuples);

11:
    /* now we can compute the new value for pg_class.reltuples */
    vacrelstats->new_live_tuples = vac_estimate_reltuples(onerel,
                                                          nblocks,
                                                          vacrelstats->tupcount_pages,
                                                          live_tuples);
which points to:

commit 7c91a0364fcf5d739a09cc87e7adb1d4a33ed112
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   2018-03-22 15:47:29 -0400

    Sync up our various ways of estimating pg_class.reltuples.

Greetings,

Andres Freund