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
