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

From: Jeremy Finzel <finzelj(at)gmail(dot)com>
To: PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org>
Subject: Index corruption / planner issue with one table in my pg 11.6 instance
Date: 2019-12-09 17:59:52
Message-ID: CAMa1XUh9sho+WDMQ6fWWioEfaODiA+n1rNscvxWYG55L+tr=cw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

I repeated this process again for an id index on same table. I created the
index and it would never be chosen no matter what, until I rebuilt the
table using VAC FULL.

I have run bt_index_check and bt_index_parent_check with heapallindexed on
one of these indexes but nothing comes up.

But one other noteworthy thing is that a cluster restart appears to fix the
issue, because on a snapshot of this system (which has been restarted) also
at 11.6, the planner picks up the index.

We quite recently (~ 2-3 weeks) did pgupgrade from 9.6 to 11. This table
in question is fed via pglogical. I checked similar behavior on another
table in this stream and could not reproduce it. So for now, it seems
limited to this one table.

Any suggestions as to how I could verify what is going on here? Anyone
experienced the same?

Thanks!
Jeremy

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Julien Rouhaud 2019-12-09 18:02:43 Re: Online checksums verification in the backend
Previous Message Tom Lane 2019-12-09 17:42:27 Re: log bind parameter values on error