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