Обсуждение: pg15: reltuples stuck at -1 after pg_upgrade and VACUUM

Since 3d351d916 (pg14), reltuples -1 means that the rel has never been
vacuumed nor analyzed.

But since 4496020e6d (backpatched to pg15), following pg_upgrade, vacuum
can leave reltuples=-1.

commit 4496020e6dfaffe8217e4d3f85567bb2b6927b45
Author: Peter Geoghegan <pg@bowt.ie>
Date:   Fri Aug 19 09:26:06 2022 -0700

    Avoid reltuples distortion in very small tables.

$ /usr/pgsql-15/bin/initdb -N -D ./pg15.dat2
$ /usr/pgsql-15/bin/initdb -N -D ./pg15.dat3

$ /usr/pgsql-15/bin/postgres -c logging_collector=no -p 5678 -k /tmp -D ./pg15.dat2& # old cluster, pre-upgrade
postgres=# CREATE TABLE t AS SELECT generate_series(1,9999);
postgres=# SELECT reltuples FROM pg_class WHERE oid='t'::regclass;
reltuples | -1
postgres=# VACUUM FREEZE t;
postgres=# SELECT reltuples FROM pg_class WHERE oid='t'::regclass;
reltuples | 9999

$ /usr/pgsql-15/bin/pg_upgrade -b /usr/pgsql-15/bin -d ./pg15.dat2 -D./pg15.dat3 # -c logging_collector=no -p 5678 -k
/tmp&

$ /usr/pgsql-15/bin/postgres -c logging_collector=no -p 5678 -k /tmp -D ./pg15.dat3& # new cluster, post-upgrade
postgres=# VACUUM FREEZE VERBOSE t;
postgres=# SELECT reltuples FROM pg_class WHERE oid='t'::regclass;
reltuples | -1

The problem isn't that reltuples == -1 after the upgrade (which is
normal).  The issue is that if VACUUM skips all the pages, it can leave
reltuples -1.  My expectation is that after running "vacuum", no tables
are left in the "never have been vacuumed" state.

If the table was already frozen, then VACUUM (FREEZE) is inadequate to
fix it, and you need to use DISABLE_PAGE_SKIPPING.

-- 
Justin

On Tue, Aug 8, 2023 at 8:43 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
> The problem isn't that reltuples == -1 after the upgrade (which is
> normal).  The issue is that if VACUUM skips all the pages, it can leave
> reltuples -1.  My expectation is that after running "vacuum", no tables
> are left in the "never have been vacuumed" state.

But -1 isn't the "never have been vacuumed" state, exactly. At best it
is the "never been vacuumed or analyzed" state.

--
Peter Geoghegan

Peter Geoghegan <pg@bowt.ie> writes:
> On Tue, Aug 8, 2023 at 8:43 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
>> The problem isn't that reltuples == -1 after the upgrade (which is
>> normal).  The issue is that if VACUUM skips all the pages, it can leave
>> reltuples -1.  My expectation is that after running "vacuum", no tables
>> are left in the "never have been vacuumed" state.

> But -1 isn't the "never have been vacuumed" state, exactly. At best it
> is the "never been vacuumed or analyzed" state.

Yeah.  -1 effectively pleads ignorance about the number of live tuples.
If VACUUM has skipped every page, then it is still ignorant about
the true number of live tuples, so setting the value to something
else would be inappropriate.

Perhaps, though, there's a case for forcing all pages to be visited
if we start with reltuples == -1?  I'm not sure it matters much
though given that you also need an ANALYZE run to be really in a
good place after pg_upgrade.  The ANALYZE should update this.

            regards, tom lane

On Wed, Aug 9, 2023 at 6:18 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Perhaps, though, there's a case for forcing all pages to be visited
> if we start with reltuples == -1?  I'm not sure it matters much
> though given that you also need an ANALYZE run to be really in a
> good place after pg_upgrade.  The ANALYZE should update this.

Right. VACUUM is sometimes much less efficient than just using ANALYZE
to establish an initial reltuples. Other times it is much less
accurate. I can't see any argument for opting to use VACUUM instead of
ANALYZE after an upgrade.

--
Peter Geoghegan