On 25.10.2021 1:28, Peter Geoghegan wrote:
> Does it make any difference if you run "./pg_amcheck -U postgres
> --heapallindexed azurlane_wiki" instead?
>
> Typically I would expect this to be less interesting, because you're
> asking pg_amcheck to do less checking. But this is not a typical case.
No, there seem to be no differences between using different sets of
arguments:
[xiatian@freebsd pg_amcheck]$ ./pg_amcheck -U postgres --heapallindexed
azurlane_wiki
[xiatian@freebsd pg_amcheck]$
> Do you mean that even a REINDEX isn't enough to stop the index from
> giving this particular wrong answer, where it thinks that 'Belfast' is
> the same as 'Kaga'? Even a REINDEX won't make it stop doing that? Or,
> are you concerned that pg_amcheck doesn't detect a remaining problem
> with one index?
The latter. REINDEX fixes the problem with page_main_title. I apologise
for not being clear enough; I have never looked too deep into
PostgreSQL's inner workings before and have been using it purely as a
regular application developer.
> What does pg_stat_user_tables say about autovacuum?
For "mediawiki.page" specifically:
last_vacuum: 2021-10-24 12:21:22.256194+09
last_autovacuum: 2021-10-05 18:57:43.850106+09
last_analyze: 2021-10-24 18:48:04.706226+09
last_autoanalyze: 2021-10-09 19:38:36.441795+09
vacuum_count is 20 (the periodic daily script), autovacuum_count is 1.
Considering the constraints for the index in question, I can tell that
changes affecting it were made on the 22nd of October and before, and
then yesterday on the 24th. The first user report regarding the problem
was on the 23rd, unless, of course, it went unnoticed before.
--
K. R.