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