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 <t...@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
