Re: Index corruption / planner issue with one table in my pg 11.6 instance

Peter Geoghegan <pg@bowt.ie> writes:

> On Mon, Dec 9, 2019 at 6:33 PM Michael Paquier <michael@paquier.xyz> wrote:
>
>> Something new as of 11 is that btree indexes can be built in parallel,
>> and before releasing it we found some bugs with covering indexes.
>> Perhaps we have an issue hidden behind one of these, but hard to be
>> sure.
>
> I doubt it.
>
> Jeremy did not report queries that give wrong answers. He only said
> that the optimizer refused to use one particular index, before a
> VACUUM FULL seemingly corrected the problem. OTOH, Jeremy did report
> using contrib/amcheck on the index, which didn't complain. (Note also
> that the amcheck functions will throw an error with an !indisvalid
> index.)

I suspect this was due to indcheckxmin=true for the involved index and
the documented (but IMO confusing) interplay w/broken hot-chains and
visibility.

Checking the same DB today, I find 35 indexes across the entire system
having indcheckxmin=true, including one on the same table, though not
the same index that Pg refused to use recently.

Many of the indexes have very old xmins and thus s/b all considered in
plans.

I was able to get that remaining index out of the indcheckxmin=true list
by...

1. Reindexing $index (did not change anything)
2. begin; drop; create; commit (still in the list but with a much newer
xmin.)
3. Vac-Full the table again (and now the index is gone from the
indcheckxmin=true list.)

Please advise.

Thx

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net