Обсуждение: Index corruption / planner issue with one table in my pg 11.6 instance

On Mon, Dec 09, 2019 at 03:51:39PM -0500, Jeff Janes wrote:
> On Mon, Dec 9, 2019 at 1:00 PM Jeremy Finzel <finzelj@gmail.com> wrote:
>> I have a table with about 7 million records.  I had a query in which I
>> needed 2 indexes added, one for a created timestamp field another for an id
>> field; both very high cardinality.
>>
>> First I noticed the query would not use the timestamp index no matter what
>> session config settings I used.  I finally created a temp table copy of the
>> table and verified index is used.  Then I rebuilt the main table with
>> VACUUM FULL and this caused the index to be used.
>
> Were they built with CONCURRENTLY?  Do you have any long-open snapshots?

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 have not seen that yet as of v11.
--
Michael

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.)

-- 
Peter Geoghegan

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

Jerry Sievers <gsievers19@comcast.net> writes:
> 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.

Yeah.  The reported behavior can mostly be explained if we assume
that there's some HOT chain in the table that involves an update
of this particular column, so that if we build an index on that
column we see a broken HOT chain, but building an index on some
other column doesn't have a problem.

The thing this doesn't easily explain is that the behavior persists
across repeated index rebuilds.  A broken HOT chain is only broken
as long as the older entry is still visible-to-somebody, so that
such situations ought to be self-healing as time passes.  If it
fails repeatedly, this theory requires assuming that either

1. You've got some extremely old open transactions (maybe forgotten
prepared transactions?), or

2. Your workload is constantly generating new broken HOT chains of
the same sort, so that there's usually a live one when you try
to build an index.

The fact that you even notice the indcheckxmin restriction indicates
that you do tend to have long-running transactions in the system,
else the index would come free for use fairly quickly.  So #1 isn't
as implausible as I might otherwise think.  But #2 seems probably
more likely on the whole.  OTOH, neither point is exactly within
the offered evidence.

            regards, tom lane