Обсуждение: BUG #17787: Seriously wrong value of pg_class.reltuples for FTS GIN index after VACUUM (INDEX_CLEANUP)
BUG #17787: Seriously wrong value of pg_class.reltuples for FTS GIN index after VACUUM (INDEX_CLEANUP)
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 17787 Logged by: Maxim Boguk Email address: maxim.boguk@gmail.com PostgreSQL version: 14.5 Operating system: Ubuntu Description: Hi, On one of production system our in-house index bloat monitoring started behave weird. After some research I found that the value of pg_class.reltuples for FTS GIN index could have two different values with 2 order magnitude difference between them depends of what had been performed last on the table (auto)vacuum (with index cleanup) or (auto)analyze. Now what I have on production: analyze verbose processor_callbacks; select count(*) from processor_callbacks; count ---------- 33439573 select reltuples from pg_class where relname='payload_index'; reltuples --------------- 3.3456344e+07 So far seems ok... now after: VACUUM (verbose, INDEX_CLEANUP ON) "public"."processor_callbacks"; the database produce: select reltuples from pg_class where relname='payload_index'; reltuples --------------- 3.5565809e+09 After analyze verbose processor_callbacks; value return to original value: analyze processor_callbacks; select reltuples from pg_class where relname='payload_index'; reltuples --------------- 3.3622728e+07 Every time I run analyze on the table - I have 3.3e+07 reltupes for this index, every time I run VACUUM (INDEX_CLEANUP ON) on the table - I have 3.5e+09 reltupes for this index (e.g. issue absolutely repeatable). The table (and index) structure: \d+ processor_callbacks Table "public.processor_callbacks" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description -----------------+-----------------------------+-----------+----------+----------------------------------+----------+-------------+--------------+------------- id | integer | | not null | generated by default as identity | plain | | | ... payload | json | | not null | | extended | | | Indexes: "processor_callbacks_pkey" PRIMARY KEY, btree (id) "payload_index" gin (to_tsvector('english'::regconfig, payload ->> 'params'::text)) ... I think first value better represents realty, but second on could have sense as well (depend of how to define "tupes" in GIN index I suspect), however both of them cannot be correct in same time. While testing on small sample of 100rows I wasn't able to reproduce that behavior, but it easily reproducible with vacuum full: create table test as select payload from processor_callbacks order by id desc limit 100; create index test_gin on test USING GIN (to_tsvector('english'::regconfig, payload ->> 'params'::text)); analyze test; select reltuples from pg_class where relname='test_gin'; reltuples ----------- 100 vacuum FULL test; select reltuples from pg_class where relname='test_gin'; reltuples ----------- 15834 analyze test; select reltuples from pg_class where relname='test_gin'; reltuples ----------- 100 And so on... PS: I suspect that the issue could be related to the following discussion: https://www.postgresql.org/message-id/flat/CAD21AoAA7%2BETUJo%3Dj2L8KAdKF8Q9_5uqwNx6H8rucFm6aRZSBA%40mail.gmail.com#2f06043f394a835c993ebf23a2af1183 Kind Regards, Maxim