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