The following bug has been logged on the website:
Bug reference: 17205
Logged by: Maxim Boguk
Email address: maxim.boguk@gmail.com
PostgreSQL version: 13.4
Operating system: Linux
Description:
Hi,
pg_class.reltuples wrong by many order of magnitude after (auto)vacuum table
for brin indexes.
Analyze table set corrent value back, and following vacuum set it wrong
again (so correctness of value in production db depend on exact sequence of
autovacuu/autoanalyze runs).
Test case:
mboguk=# create table t (id integer);
CREATE TABLE
mboguk=# insert into t select id from generate_series(1, 10000000);
mboguk=# insert into t select id from generate_series(1, 10000000) as
g(id);
INSERT 0 10000000
mboguk=# create index t_brin on t using BRIN(id);
CREATE INDEX
mboguk=# vacuum ANALYZE t;
VACUUM
mboguk=# select reltuples from pg_class where relname='t_brin';
reltuples
--------------
9.999977e+06
(1 row)
mboguk=# vacuum t;
VACUUM
mboguk=# select reltuples from pg_class where relname='t_brin';
reltuples
-----------
345
(1 row)
mboguk=# analyze t;
ANALYZE
mboguk=# select reltuples from pg_class where relname='t_brin';
reltuples
--------------
9.999977e+06
(1 row)
mboguk=# vacuum t;
VACUUM
mboguk=# select reltuples from pg_class where relname='t_brin';
reltuples
-----------
345
(1 row)
And so on...
Tested on 12.* and 13.* versions.
Kind Regards,
Maksym