From: | Jerry Sievers <gsievers19(at)comcast(dot)net> |
---|---|
To: | Peter Geoghegan <pg(at)bowt(dot)ie> |
Cc: | Michael Paquier <michael(at)paquier(dot)xyz>, Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Jeremy Finzel <finzelj(at)gmail(dot)com>, PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Index corruption / planner issue with one table in my pg 11.6 instance |
Date: | 2019-12-10 05:10:36 |
Message-ID: | 87k174daur.fsf@jsievers.enova.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Peter Geoghegan <pg(at)bowt(dot)ie> writes:
> On Mon, Dec 9, 2019 at 6:33 PM Michael Paquier <michael(at)paquier(dot)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(dot)consulting(at)comcast(dot)net
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-12-10 06:09:21 | Re: Index corruption / planner issue with one table in my pg 11.6 instance |
Previous Message | Dilip Kumar | 2019-12-10 04:53:19 | Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions |